面试题目如上。
第一题的问题主要是理解问题,容易写成去掉"李明"的课程的学生,而不是没选他课的学生
比如错误sql:SELECT
S.SNO
FROM
S
LEFT JOIN SC ON S.SNO = SC.SNO
WHERE
SC.CNO NOT IN (
SELECT
CNO
FROM
C
WHERE
CTEACHER = '李明'
)
正确写法应该是查出来选了他课的学生的id,然后查询id不在此列的学生id,正确sql是:
SELECT
S.SNAME
FROM
S
WHERE
S.SNO NOT IN (
SELECT
S.SNO
FROM
S
LEFT JOIN SC ON S.SNO = SC.SNO
WHERE
SC.CNO IN (
SELECT
CNO
FROM
C
WHERE
CTEACHER = '李明'
)
)
第二个题,用到了HAVING语句来限制函数,正确sql是:
SELECT
S.SNAME,
AVG(sc.SCGRADE)
FROM
S
INNER JOIN SC ON S.SNO = sc.SNO
INNER JOIN C ON sc.CNO = C.CNO
WHERE
sc.SCGRADE < 60
GROUP BY
S.SNO
HAVING
COUNT(sc.SCGRADE) >= 2
学习sql任重而道远,今天这个sql想了半个多小时,如果面试去的话,估计就GG了