sql面试题

问题描述： 
已知关系模式： 
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