已知数据库中有三张基本表:
S(Sno,Sname,Ssex,Sage,Sdept)
学生表(学号,姓名,性别,年龄,所在系)
SC(Sno,Cno,Grade)
选修表(学号,课程号,成绩)
C(Cno,Cname,Credit,Cpno)
课程表(课程 号,课程名,学分,先行课)
根据基本表完成如下查询:
1、求选修了C1课程,成绩比S2高的学生的学号和成绩;
select s.sno,sc.grade
from s join sc
on s.sno=sc.sno and sc.cno='c1'
and sc.grade>
(select sc.grade from s,sc
where s.sno=sc.sno and s.sname='s2')
2、求C1课程的成绩高于张三的学生学号和成绩;
select s.sno,sc.grade
from s join sc
on s.sno=sc.sno and sc.cno='c1'
and sc.grade>
(select sc.grade from s,sc
where s.sno=sc.sno and s.sname='张三')
3、求其他系中比计算机系学生年龄都小的学生;
select s.* from s
where sage < all
(select s.sage from s
where s.dept='计算机')
4、求和“刘晨”选修了相同课程的学生名单;
select s.*
from s where cno in
(select cno from sc
where sc.sno=
(select sno from s where sname='刘晨')
)
5、求没有选修C2课程的学生的名单;
select s.sname from s
where s.sno =any
(select sc.sno from sc
where sc.cno!='C2')
6、查询选修了全部课程的学生姓名;
select s.sname from s
where s.sno=
(select sc.sno from sc
where sc.cno=
(select c.cno from c
where cname=all)
)
7、查询选修了“数据结构”的全部学生;
select s.sname from s
where s.sno=
(select sc.sno from sc
where sc.cno=
(select c.cno from c
where cname='数据库')
)
8、查出既选修了C1课程又选修了C2课程的学生姓名;
select s.sname from s
where s.no=any
(select sc.sno from sc
where sc.cno=’c1’ or sc.cno=’c2’)
9、列出C1课成绩比C2课程成绩高的所有学生的学号;
select s.no from s
where s.no=any
(select c.no from c
where c1.score>c2.score)
10、求至少选修了学号为“S2”的学生所选修的全部课程的学生学号的姓名。
select s.sname,s.sno from s join sc
on s.sno = sc.sno
and sc.cno=any
(select sc.cno from s join sc
on s.sno = sc.sno
and s.sno='S2')