写给一个哥们的SQL
 
有三张表:
学生:S(SNO,SNAME)
课程:C(CNO,CNAME)
成绩:SC(SNO,CNO,SCORE)
 
目标:实现一些查询或修改。
 
-- 1、姓名为张明的学生选修的课程的课程号
SELECT DISTINCT C.CNO
  FROM 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 = '数据库系统概论');
 
没有经过实际验证,如有写错的地方,还请各位留言指正!