查询语句
设教学数据库Education有三个表:
学生表S(SNO,SNAME,AGE,SEX,SDEPT);
学习表SC(SNO,CNO,GRADE);
课程表C(CNO,CNAME,CDEPT,TNAME)
问:(1)检索至少选修课程号为C2和C4的学生学号;
(2)检索学习全部课程的学生姓名;
(3)查询所学课程包含学生S3所学课程的学生学号。
SELECT
Sno
FROM
SC X,SC Y
WHERE
X.Sno=Y.Sno
AND
X.Cno=
'C2'
AND
Y.Cno=
'C4'
SELECT
Sname
FROM
S
WHERE
NOT
EXISTS (
SELECT
*
FROM
C
WHERE
NOT
EXISTS (
SELECT
*
FROM
SC
WHERE
SC.Sno=S.Sno
AND
SC.Cno=C.CnO))
SELECT
DISTINCT
Sno
FROM
SC
AS
X
WHERE
NOT
EXISTS (
SELECT
*
FROM
SC
AS
Y
WHERE
Y.Sno=
'S3'
AND
NOT
EXISTS (
SELECT
*
FROM
SC
AS
Z
WHERE
Z.Sno=X.Sno
AND
Z.Cno=Y.Cno))