请教:北京华建集团SQL面试题第1大题第1小题如何解答?【题目描述】1.已知关系模式：S (SNO,SNAME） 学生关系。SNO 为学号，SNAME 为姓名C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩要求实现如下5个处理： 1． 找出没有选修过“李明”老师讲授课程的所有学生姓名

答案是：
1．找出没有选修过“李明”老师讲授课程的所有学生姓名
--实现代码:
Select Sname As 学生姓名
  From s
Where Not Exists (Select *
          From c, Sc
         Where c.Cno = Sc.Cno
           And Cteacher = '李明'
           And Sc.Sno = s.Sno);

Select Sname As 学生姓名
  From s
Where Sno Not In (Select Sno
                     From c, Sc
                    Where c.Cno = Sc.Cno
                      And Cteacher = '李明');

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

  --实现代码:
Select s.Sno As 学生学号, s.Sname As 学生姓名, Avg(Sc.Scgrade) As 平均成绩
  From s, Sc
Where Sc.Sno = s.Sno
   And Sc.Sno In (Select Sc.Sno
                    From Sc
                   Where Sc.Scgrade < 60
                   Group By Sc.Sno
                  Having Count(*) > 2)
Group By s.Sno, s.Sname;

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

  --实现代码:
select s.sno as 学生学号,s.sname as 学生姓名 from s where sno in(select sc.sno as 学生学号 from c,sc where c.cno=sc.cno and c.cno in('01','02') group by sno having count(distinct sc.cno)=2);

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

  --实现代码:
select  sc1.sno as 学生学号 from sc as sc1,c as c1,sc as sc2,c as c2
where sc1.cno=c1.cno and c1.cno='01' and sc2.cno=c2.cno and c2.cno='02'
and sc1.scgrade>sc2.scgrade group by sc1.sno;

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

  --实现代码:
select  sc1.sno as 学生学号, sc1.scgrade as no1grade ,sc2.scgrade as no2grade
from sc as sc1,c as c1,sc as sc2,c as c2
where sc1.cno=c1.cno and c1.cno='01' and sc2.cno=c2.cno and c2.cno='02'
and sc1.scgrade>sc2.scgrade group by sc1.sno;