常用SQL查询语句的学习

题目来源：数据库系统教程（施伯乐第三版）...

供理解和学习SQL语句使用，长期不定时更新例题。

已知教学书库中4个关系分别为,（主键加了下划线，外键颜色分辨）

教师关系 T(T#,TNAME,TITLE)

课程关系 C(C#,CNAME,T#)

学生关系 S(S#,SNAME,AGE,SEX)

选课关系 SC(S#,C#,SCORE)     //该表主键为S#和C#

/*1.检索学习课程号为C2的学生学号与成绩*/

SELECT S#,SCORE FROM SC 
WHERE C#='C2';

/*2.检所学习课程号为C2的学生学号与姓名*/

SELECT S.S#,SNAME FROM S,SC 
WHERE S.S#=SC.S# AND C#='C2'

/*3.检索至少选修LIU老师所授课程中一门课程的学生学号与姓名*/

SELECT S.S#,SNAME FROM S,SC,C,T 
WHERE S.S#=SC.S# AND SC.C#=C.C# AND C.T#=T.T# AND TNAME='LIU';

/*4.检索至少选修课程号为C2或C4课程的学生学号*/

SELECT DISTINCT S# FROM SC 
WHERE SC.C#='C2' OR SC.C#='C4';

/*5.检索至少选修课程号为C2和C4课程的学生学号*/

SELECT X.S# FROM SC AS X,SC AS Y
WHERE X.S#=Y.S# AND X.C#='C2' AND Y.C#='C4';

/*6.检索不学C2课的学生姓名和年龄*/

SELECT SNAME,AGE FROM S
WHERE NOT EXISTS(        /*没有学C2*/
SELECT * FROM SC 
WHERE SC.S#=S.S# AND C#='C2');

/*7.检索学习全部课程的学生姓名*/

SELECT SNAME FROM S 
WHERE NOT EXISTS(     /*C中不存在一门课程*/
SELECT * FROM C 
WHERE NOT EXISTS(     /*该学生没有学*/
SELECT * FROM SC
WHERE SC.S#=S.S# AND SC.C#=C.C#));

/*8.检索所学课程包含学生S3所学课程的学生学号*/

SELECT DISTINCT S# FROM SC AS X
WHERE NOT EXISTS(    /*不存在S3学的一门课*/
SELECT * FROM SC AS Y 
WHERE Y.S#='S3' 
AND NOT EXISTS(     /*该学生没有学*/
SELECT * FROM SC AS Z WHERE Z.S#=X.S# AND Z.C#=Y.C#));

/*1.检索年龄小于17岁的女学生的学号和姓名*/

SELECT S#,SNAME FROM S
WHERE S.AGE<'17' AND S.SEX='女';

/*2.检索男同学所学课程的课程号和课程名*/

SELECT DISTINCT C.C#,CNAME FROM C,SC,S
WHERE S.S#=SC.S# AND SC.C#=C.C# AND S.SEX='男';

/*3.检索男学生所学课程的任课老师的工号和姓名*/

SELECT DISTINCT T.T#,TNAME FROM T,S,SC,C
WHERE T.T#=C.T# AND C.C#=SC.C# AND SC.S#=S.S# AND S.SEX='男';

/*4.检索至少选修两门课程的学生学号*/

SELECT DISTINCT X.S# FROM SC AS X,SC AS Y
WHERE X.S#=Y.S# AND X.C#!=Y.C#;

/*5.检索至少有学号为S2和S4的学生选修的课程的课程号*/

SELECT DISTINCT X.C# FROM SC AS X,SC AS Y
WHERE X.S#='S2' AND Y.S#='S4' AND X.C#=Y.C#;

/*6.检索WANG同学不学的课程的课程号*/

SELECT C# FROM C 
WHERE NOT EXISTS(      /*WANG同学没有选*/
SELECT * FROM S,SC 
WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME='WANG');

/*7.检索全部学生所学的课程的课程号与课程名*/

SELECT C#,CNAME FROM C
WHERE NOT EXISTS(     /*不存在一个学生*/
SELECT * FROM S 
WHERE NOT EXISTS(     /*没有选该课程*/
SELECT * FROM SC 
WHERE SC.S#=S.S# AND SC.C#=C.C#));

/*8.检索选修课程中包含LIU老师所授全部课程的学生学号*/

SELECT DISTINCT S# FROM SC AS X 
WHERE NOT EXISTS(     /*不存在LIU老师的一门课*/
SELECT * FROM C,T 
WHERE C.T#=T.T# AND TNAME='LIU' 
AND NOT EXISTS(       /*该学生没有选*/
SELECT * FROM SC AS Y 
WHERE Y.S#=X.S# AND Y.C#=C.C#));