一道经典的SQL笔试题
有3个表S,C,SC
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
1,找出没选过“黎明”老师的所有学生姓名。
2,列出2门以上(含2门)不及格学生姓名及平均成绩。
3,即学过1号课程有学过2号课所有学生的姓名。
请用标准SQL语言写出答案,方言也行(请说明是使用什么方言)。
1.SELECT SNAME FROM S JOIN SC ON
(S.SNO=SC.SNO) JOIN C ON (https://www.360docs.net/doc/8e10130597.html,O=https://www.360docs.net/doc/8e10130597.html,O) WHERE C.CTEACHER
<>’黎明’;
2.SELECT t.av,s.SNAME FROM (
SELECT sc.SNO,A VG(sc.SCGRADE) AS av FROM Stu_Cau sc
WHERE sc.SNO IN
(SELECT SNO FROM Stu_Cau WHERE SCGRADE<60 GROUP BY SNO HA VING COUNT(SNO) >= 2)
GROUP BY sc.SNO ) AS t
LEFT JOIN Student s ON s.SNO = t.SNO
3.SELECT s.SNAME FROM Student s WHERE s.SNO IN
(
SELECT a.SNO FROM Stu_Cau a CROSS JOIN Stu_Cau b
WHERE a.SNO = b.SNO AND https://www.360docs.net/doc/8e10130597.html,O = 1 AND https://www.360docs.net/doc/8e10130597.html,O = 2
)