供理解和学习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#));
