#学生信息表 s
sno sname age sex
#学生选课成绩表 sc
sno cno grade
#课程信息表 c
cno cname teacher
练习
1. 检索至少选修“程军”老师所授全部课程的学生姓名SNAME
SELECT SNAME
FROM S
WHERENOTEXISTS
(SELECT *
FROM C
WHERE TEACHER=’程军’ ANDNOTEXISTS
(SELECT *
FROM SC
WHERE SC.SNO=S.SNO AND SC.CNO=C.CNO));
2. 检索“李强”同学不学课程的课程号。
SELECT CNO
FROM C
WHERE CNO NOTIN
(SELECT CNO
FROM SC,S
WHERE SC.SNO=S.SNO AND S.SNAME=’李强’)
或
SELECT CNO
FROM C
EXCEPTSELECT 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
WHERENOTEXISTS
(SELECT *
FROM S
WHERENOTEXISTS
(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’
INTERSECTSELECT 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’ ;