sql exists常用示例

表结构

#学生信息表  s
sno sname age sex
#学生选课成绩表  sc
sno cno grade
#课程信息表  c
cno cname teacher

练习

1. 检索至少选修“程军”老师所授全部课程的学生姓名SNAME
SELECT SNAME
  FROM S
  WHERE NOT EXISTS
      (SELECT *
      FROM C
       WHERE TEACHER=’程军’ AND NOT EXISTS  
             (SELECT *
            FROM SC
            WHERE SC.SNO=S.SNO AND  SC.CNO=C.CNO)); 

2. 检索“李强”同学不学课程的课程号。 
SELECT CNO
  FROM C
  WHERE CNO NOT IN
     (SELECT CNO
       FROM SC,S
WHERE SC.SNO=S.SNO AND S.SNAME=’李强’)

或

SELECT CNO
FROM C
EXCEPT 
SELECT CNO
FROM SC,S
WHERE SC.SNO=S.SNO AND S.SNAME=’李强’;

3. 检索至少选修两门课程的学生学号
SELECT SNO
  FROM SC A, SC B
  WHERE A.SNO=B.SNO AND A.CNO<>B.CNO; 

4. 检索全部学生都选修的课程的课程号和课程名。
SELECT CNO,CNAME
  FROM C
  WHERE NOT EXISTS
(SELECT *
 FROM S
 WHERE NOT EXISTS
     (SELECT *
     FROM SC
     WHERE SC.CNO=C.CNO AND SC.SNO=S.SNO)); 

5. 检索选修课程包含“程军”老师所授课程之一的学生学号
SELECT SNO
  FROM C,SC
  WHERE C.CNO=SC.CNO AND C.TEACHER='程军';

6. 检索选修课程号为k1和k5的学生学号

SELECT SNO
  FROM SC
  WHERE SC.CNO=’k1’
  INTERSECT
  SELECT SNO
  FROM SC
  WHERE SC.CNO=’k5’;
或
SELECT SNO
FROM SC A, SC B
WHERE A.SNO=B.SNO AND A.CNO=’k1’ AND B.CNO=’k5’ ;