目录
查询问题:
设教学数据库Education有三个关系:
学生关系S(SNO,SNAME,AGE,SEX,SDEPT);
学习关系SC(SNO,CNO,GRADE);
课程关系C(CNO,CNAME,CDEPT,TNAME)
(1)检索计算机系的全体学生的学号,姓名和性别;
SELECT SNO,SNAME,SEX
FROM S
WHERE SDEPT = '计算机'
(2)检索学习课程号为C2的学生学号与姓名;
SELECT SNO,SNAME
FROM S,SC
WHERE SC = 'C2' AND S.SNO = SC.SNO
(3)检索选修课程名为“DS”的学生学号与姓名;
SELECT SNO,SNAME
FROM S,SC,C
WHERE CNAME = 'DS' AND S.SNO = SC.SNO
AND C.CNO = SC.CNO
(4)检索选修课程号为C2或C4的学生学号;
SELECT SNO
FROM SC
WHERE CNO IN ('C2','C4')
(5)检索至少选修课程号为C2和C4的学生学号;
SELECT SNO
FROM SC A,SC B
WHERE A.SNO = 'C2' AND B.SNO = 'C4' AND A.SNO = B.SNO
分别求选了C2和C4的学生,求其交集。
(6)检索不学C2课的学生姓名和年龄;
SELECT SNAME,AGE
FROM S
WHERE SNO NOT IN
(SELECT SNO
FROM SC
WHERE CNO = 'C2')
排除学习了C2课号的学生。
(7)检索学习全部课程的学生姓名.
SELECT SNAME
FROM SC
WHERE NOT EXISTS
(SELECT *
FROM C
WHERE NOT EXISTS
(SELECT *
FROM S,C,SC
WHERE S.SNO = SC.SNO AND C.CNO = SC.CNO))
查找学习了所有课程的学生,即是在C中不存在一门课程没有学。