create table student
(
sno char(9) primary key,
sname char(20) unique,
ssex char(2),
sage smallint,
sdept char(20)
);
create table course
(
cno char(4) primary key,
cname char(40),
cpno char(4),
ccredit smallint,
foreign key(cpno) references course(cno)
);
create table sc
(
sno char(9),
cno char(4),
grade smallint,
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);
alter table student alter column sage int;
alter table course add unique(cname);
create unique index stusno on student(sno);
create unique index coucno on course(cno);
create unique index scno on sc(sno asc,cno desc);
select sno,sname from student;
select sname,sno,sdept from student;
select * from student;
select sname,2004-sage from student;
select sname NAME,'year of birth:' BIRTH,2004-sage BIRTHDAY,lower(sdept) DEPARTMENT from student;
select distinct sno NUMBER
from sc;
select sname NAME
from student
where sdept='cs';
select sname,sage
from student
where sage<20;
select distinct sno
from sc
where grade<60;
select sno
from student
where sno between 12436220 and 12436240;
select sname,ssex
from student
where sdept in('cs','ma','is');
select sname,ssex
from student
where sdept not in('cs','ma','is');
select *
from student
where sno like '200215121';
select sno
from student
where sname like '刘%';
select sno
from student
where sname like '欧阳_';
select sno
from student
where sname in('刘%','欧阳_');
select sname
from student
where sname like '_阳%';
select sno
from student
where snamd not like '刘%';
select sname
from student
where sname like 'DB\_Design'escape'\';
select sno,cno
from sc
where grade is null;
select sno,cno
from sc
where grade is not null;
