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;