写给一个哥们的SQL
-- 1、姓名为张明的学生选修的课程的课程号 SELECT DISTINCT C.CNOFROM C, S, SC
WHERE S.SNO = SC.SNO
AND C.CNO = SC.CNO
AND S.SNAME = '张明';
-- 2、选修了学号为10005的学生所修所有课程的学生学号
SELECT S.SNO
FROM C, S, SC
WHERE S.SNO = SC.SNO
AND C.CNO = SC.CNO
AND (SELECT DISTINCT C.CNO
FROM C, S, SC
WHERE S.SNO = SC.SNO
AND C.CNO = SC.CNO
AND C.CNO = 10005) IN C.CNO;
-- 3、所修课程有5门成绩>=90分的学生学号
SELECT X.XSNO
FROM (SELECT COUNT(*) AS ROWCOUNT, S.SNO AS XSNO
FROM C, S, SC
WHERE S.SNO = SC.SNO
AND C.CNO = SC.CNO
AND SC.SCORE >= 90
GROUP BY S.SNO) AS X
WHERE X.ROWCOUNT >= 5;
-- 4、所有课程名为数据库系统概论的成绩+10分
UPDATE SC
SET SCORE = SCORE + 10
WHERE SC.CNO = (SELECT CNO FORM C WHERE C.CNAME = '数据库系统概论');
页:
[1]