sql练习题2

问题描述：
已知关系模式：
s (sno,sname）                       学生关系。

sno 为学号，

sname 为姓名

c (cno,cname,cteacher)  课程关系。

cno 为课程号，

cname 为课程名，

cteacher 为任课教师
sc(sno,cno,scgrade) 选课关系。

scgrade 为成绩

要求实现如下5个处理：
   1． 找出没有选修过“李明”老师讲授课程的所有学生姓名
   2． 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
   3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
   4． 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
   5． 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

1. 找出没有选修过“李明”老师讲授课程的所有学生姓名

--实现代码：
select sname from s
where not exists(
    select * from sc,c
where sc.cno=c.cno
         and c.cteacher='李明'
           and sc.sno=s.sno)

2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩

--实现代码：
select s.sno,s.sname,avg_scgrade=avg(sc.scgrade)
from s,sc,(
     select sno
     from sc
     where scgrade<60
     group by sno
     having count(distinct cno)>=2
)a where s.sno=a.sno and sc.sno=a.sno
group by s.sno,s.sname

3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

--实现代码：
select s.sno,s.sname
from s,(
     select sc.sno
     from sc,c
     where sc.cno=c.cno
         and c.cname in('1','2')
     group by sno
     having count(distinct cno)=2
)sc where s.sno=sc.sno

4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号

--实现代码：
select s.sno,s.sname
from s,sc sc1,sc sc2
     where sc1.cno='1'
         and sc2.sno='2'
         and sc1.cno=s.cno
         and sc1.scgrade>sc2.scgrade

5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

--实现代码：
select sc1.sno,[1号课成绩]=sc1.scgrade,[2号课成绩]=sc2.scgrade
from sc sc1,sc sc2
where sc1.cno='1'
         and sc2.cno='2'
         and sc1.sno=sc2.sno
         and sc1.scgrade>sc2.scgrade