数据库题目6

题目描述：
已知关系模式：
S (SNO,SNAME) 学生关系。SNO为学号，SNAME为姓名；
C (CNO,CNAME,CTEACHER) 课程关系。CNO为课程号，CNAME为课程名，CTEACHER为任课教师；
SC(SNO,CNO,SCGRADE)选课关系。SCGRADE为成绩。
问题：
1) 找出没有选修过“李明”老师讲授课程的所有学生姓名；
SELECT SNAME
FROM S WHERE
NOT EXISTS (SELECT *
FROM C, SC
WHERE SC.CNO = C.CNO
AND CTEACHER = ‘李明’);

2) 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩；
SELECT SNAME, AVG(SCGRADE)
FROM S, SC
WHERE S.SNO = SC.SNO
AND SNO
IN (SELECT SNO
FROM SC
WHERE SCGRADE < 60
GROUP BY SNO
HAVING COUNT(*) >= 2);

3) 列出既学过课程名为“1”的课程，又学过课程名为“2”的课程的所有学生姓名；
SELECT 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) K
WHERE S.SNO=K.SNO;

4) 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩。
SELECT S.SNO, T.GRADE1, T.GRADE2
FROM S,
(SELECT SC1.SNO, GRADE1 = SC1.SGRADE, GRADE2 = SC2.SGRADE
FROM SC SC1, C C1, SC SC2, C C2
WHERE SC1.SCGRADE > SC2.SCGRADE
AND SC1.CNO = C1.CNO
AND C1.CNAME = ‘1’
AND SC2.CNO = C2.CNO
AND C2.CNAME = ‘2’ ) T
WHERE S.SNO = T.SNO;