设有一个教学数据库education，包括student、course和sc三个表

create table sc(

sno char(100),

cno char(100),

grade number

)

alter table sc add constraint pk_1 primary key(sno,cno)

alter table sc add constraint sno_1 foreign key(sno) references student(sno)

alter table sc add constraint cno_1 foreign key(cno) references course(cno)

select a.*,b.*,c.grade from student a, course b ,sc c

where a.sno=c.sno and b.cno=c.cno and a.name like'张%'

select a.*,b.*,c.grade from student a, course b ,sc c

where a.sno=c.sno and b.cno=c.cno and a.xi_name in('IS','EB')

select sex,sum(age)/count(*) from student a

group by sex

select a.Stu_no,a.name,b.course_name,c.grade from student a, course b ,sc c

where a.sno=c.sno and b.cno=c.cno

select a.stu_no,c.grade from student a, course b ,sc c

where a.sno=c.sno and b.cno=c.cno and b.cno='1'

order by c.grade desc

alter table student add(inyear char(4))

update sc set grade =60 where grade between 55 and 59

delete sc where sno='200910001'

insert student into

(sno,

name,

sex

)

values

('20110001',

'张小涛',

'男'

)

（1）检索计算机系的全体学生的学号，姓名和性别；

SELECT SNO,SNAME,SEX FROM S

WHERE SNO IN (

SELECT SNO FROM SC WHERE CNO IN (SELECT SNO FROM C WHERE CDEPT='计算机')

)

（2）检索学习课程号为C2的学生学号与姓名；

SELECT SNO,SNAME FROM S

WHERE SNO IN (SELECT SNO FROM SC WHERE CNO='C2')

（3）检索选修课程名为“DS”的学生学号与姓名；

SELECT SNO,SNAME FROM S

WHERE SNO IN (SELECT SNO FROM SC WHERE CNO IN (SELECT CNO FROM C WHERE CNAME='DS'))

（4）检索选修课程号为C2或C4的学生学号；

SELECT SNO FROM SC WHERE CNO IN ('C2','C4')

（5）检索至少选修课程号为C2和C4的学生学号；

SELECT SNO FROM S

WHERE SNO IN (SELECT SNO FROM SC WHERE CNO='C2')

AND SNO IN (SELECT SNO FROM SC WHERE CNO='C4')

（6）检索不学C2课的学生姓名和年龄；

SELECT SNAME,AGE FROM S

WHERE SNO NOT IN (SELECT SNO FROM SC WHERE CNO='C2')

AND SNO NOT IN (SELECT SNO FROM SC WHERE CNO='C4')

（7）检索学习全部课程的学生姓名；

SELECT SNAME FROM S

WHERE SNO IN (

SELECT SNO FROM SC WHERE NOT EXISTS (SELECT * FROM C WHERE SC.CNO=C.CNO)

)

（8）查询所学课程包含学生S3所学课程的学生学号。

SELECT SNAME FROM S

WHERE SNO IN (

SELECT SNO FROM SC WHERE NOT EXISTS (SELECT * FROM SC SC2 WHERE SC2.CNO=SC.CNO)

)

---