有 3 个表 S(学生表),C(课程表),SC(学生选课表)
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
1,找出没选过“黎明”老师的所有学生姓名。
2,列出 2 门以上(含2 门)不及格学生姓名及平均成绩。
3,即学过 1 号课程又学过 2 号课所有学生的姓名。
题解:
---------------------------第一题------------------------------------
SELECT
SNAME
FROM
S
WHERE
SNO NOT IN (
SELECT DISTINCT
SNO
FROM
SC
WHERE
CNO IN (SELECT CNO FROM C WHERE CTEACHER='黎明')
)
---------------------------第二题------------------------------------
-- SELECT SNO, SCGRADE FROM SC WHERE SCGRADE < 60
-- SELECT
-- SNO
-- FROM
-- (SELECT SNO, SCGRADE FROM SC WHERE SCGRADE < 60) g
-- GROUP BY
-- SNO
-- HAVING
-- COUNT(*) >= 2
SELECT
SNAME
FROM
S
WHERE
SNO IN (
SELECT
SNO
FROM
(SELECT SNO, SCGRADE FROM SC WHERE SCGRADE < 60) T
GROUP BY
SNO
HAVING
COUNT(SNO) >= 2
)
---------------------------第三题------------------------------------
-- SELECT SNO FROM SC WHERE CNO=1
-- SELECT SNO FROM SC WHERE CNO=2
-- SELECT
-- A.SNO
-- FROM
-- (SELECT SNO FROM SC WHERE CNO=1) A
-- JOIN
-- (SELECT SNO FROM SC WHERE CNO=2) B
-- ON
-- A.SNO = B.SNO
SELECT
SNAME
FROM
S
WHERE
SNO IN (
SELECT
A.SNO
FROM
(SELECT SNO FROM SC WHERE CNO=1) A
JOIN
(SELECT SNO FROM SC WHERE CNO=2) B
ON
A.SNO = B.SNO
)
解题收获:
在子句查询时,必须将子句的表起别名,否则会报以下错误:
ERROR 1248 (42000): Every derived table must have its own alias
比如上面的第二题FROM
后面的子句(SELECT SNO, SCGRADE FROM SC WHERE SCGRADE < 60) T
如果末尾的别名T
去掉,则会报以上错误,即使后续不需要用到这个别名