复杂SQL练习题.ppt

1、1,S,C,SC,查询至少选修了1号学生所选全部课程的学生编号,2,查询至少选修了1号学生所选全部课程的学生编号,SQL主体结构 Select sno From S Where(?) ?：选择条件是“这些学生的所选课程包含了1号学生所选课程” （1）这些学生所选课程 = 集合A Select cno From SC where sno=这些学生的sno （2） 1号学生所选课程 = 集合B Select cno From SC where sno=1 B in A = True,3,查询至少选修了1号学生所选全部课程的学生编号,扩展SQL主体结构 Select sno From S Where

2、 (B IN A);,Select sno From S Where ( (Select cno From SC where sno=1) IN (Select cno From SC where sno=S.sno) ); ERROR 1242 (21000): Subquery returns more than 1 row,4,查询至少选修了1号学生所选全部课程的学生编号,Select sno From S Where (B IN A) B IN A B-A为空 B-A为空 NOT EXISTS(B-A) = True B-A 从“1号学生所选课程”中去除 “属于集合A的课程” Sele

3、ct cno From SC where sno=1 and (该课程不属于集合A) Select cno From SC where sno=1 and ( cno NOT IN (Select cno From SC where sno=这些学生的sno) ),5,查询至少选修了1号学生所选全部课程的学生编号,扩展SQL主体结构 Select sno From S Where (NOT EXISTS(B-A);,Select sno From S Where (NOT EXISTS ( Select cno From SC where sno=1 and ( cno NOT IN (Sel

4、ect cno From SC where sno=S.sno) ) ) );,6,查询至少选修了1号学生所选全部课程的学生编号,选择这样的学生：不存在1号学生所选课程不在该学生所选课程中的情况,Select sno From S Where (NOT EXISTS ( Select cno From SC where sno=1 and ( cno NOT IN (Select cno From SC where sno=S.sno) ) ) );,7,查询至少选修了1号学生所选全部课程的学生编号,另一种解法 （1）这些学生所选课程 = 集合A （2） 1号学生所选课程 = 集合B B-A

5、Select cno From SC where sno=1 and (该课程不属于集合A) Select cno From SC where sno=1 and (该课程没有被这些学生选择) Select cno From SC where sno=1 and (NOT EXISTS (这些学生选择该课程的元组),8,查询至少选修了1号学生所选全部课程的学生编号,Select sno From S Where (NOT EXISTS ( Select cno From SC Where sno=1 And ( NOT EXISTS (这些学生选择该课程的元组) );,Select sno F

6、rom S Where (NOT EXISTS ( Select cno From SC Where sno=1 And ( NOT EXISTS ( Select cno From SC As SC1 Where SC1.sno = S.sno and SCo = SC.cno ) );,9,查询至少选修了1号学生所选全部课程的学生编号,总结：填空法 （1）写出查询语句的主体框架； Select * from * where (简单条件) and (复杂条件) （2）填写简单条件的部分； （3）对复杂条件进行数学描述，如： B IN A （4）如果SQL不支持描述中的谓词，则对复杂条件进行语

7、义变换 NOT EXISTS (B-A) NOT EXISTS (从B集合中排除属于A的元组),10,查询被所有学生选修的课程编号,SQL语句的主体框架 Select cno From C where (?) ?是比较复杂的条件 : 该课程被所有学生选修 数学描述： （1）选修该课程的学生 = 集合A （2）所有学生 = 集合B ?: B - A = 空集 SQL不支持差运算，需要对其进行变换,11,查询被所有学生选修的课程编号,SQL语句的主体框架 Select cno From C where (B-A=空集) SQL不支持空集判断和差运算，需进行变换 NOT EXISTS (B-A) B

8、-A：从所有学生中去除掉选择了该课程的学生 Select sno From S Where sno NOT IN ( (Select sno From SC Where cno=该课程),12,查询被所有学生选修的课程编号,Select cno From C where (NOT EXISTS (B-A) Select sno From S Where sno NOT IN ( (Select sno From SC Where cno=该课程),Select cno From C where (NOT EXISTS ( Select sno From S Where sno NOT IN (

9、Select sno From SC Where cno=C.cno) );,13,查询被所有学生选修的课程编号,Select cno From C where (NOT EXISTS (B-A) B-A：从所有学生中去除掉选择了该课程的学生；也等价于“保留所有学生中没有选择该课程的学生” Select sno From S Where NOT EXISTS ( Select sno From SC Where sno=S.sno And cno=该课程),14,查询被所有学生选修的课程编号,Select cno From C where NOT EXISTS ( Select sno Fro

10、m S Where NOT EXISTS ( Select sno From SC Where sno=S.sno And cno=C.cno ) );,15,求选修了某个老师所授全部课程的学生姓名和学院名称,学院（学院代码，学院名称）； 学生（学号，姓名，性别，学院代码）； 教师（教师号，教师姓名，学院代码）； 课程（课程号，课程名，学时）； 学习（学号，课程号，成绩）； 开课（教师号，课程号）；,16,求选修了雷老师所授全部课程的学生姓名和学院名称,SQL主体结构： Select 姓名,学院名称 From 学生, 学院 Where (学生.学院代码=学院.学院代码) And (该学生选修了

11、雷老师所授全部课程) 复杂条件：该学生选修了雷老师所授全部课程 数学描述 （1）该学生所选课程 = 集合A （2）雷老师所授全部课程 = 集合B B IN A = True,17,求选修了雷老师所授全部课程的学生姓名和学院名称,Select 姓名,学院名称 From 学生, 学院 Where (学生.学院代码=学院.学院代码) And (B IN A = True) （1）该学生所选课程 = 集合A （2）雷老师所授全部课程 = 集合B 描述转换: NOT EXIST (B-A) B-A：从集合B中去除那些属于集合A的元素 e|eB eA = e|eB e NOT IN A = e|eB m|

12、 mA m=e = ,18,求选修了雷老师所授全部课程的学生姓名和学院名称,（1）该学生所选课程 = 集合A Select 课程号 From 学习 Where 学号=该学生的学号 （2）雷老师所授全部课程 = 集合B Select 课程号 From 开课 Where 教师号=(Select 教师号 From 教师 Where 教师姓名=雷老师),19,求选修了雷老师所授全部课程的学生姓名和学院名称,NOT EXIST (e|eB e NOT IN A) Select 姓名,学院名称 From 学生, 学院 Where (学生.学院代码=学院.学院代码) And NOT EXISTS ( Sel

13、ect 课程号 From 开课 Where 教师号=(Select 教师号 From 教师 Where 教师姓名=雷老师) And 课程号 NOT IN ( Select 课程号 From 学习 Where 学号=学生.学号) ),20,求选修了雷老师所授全部课程的学生姓名和学院名称,NOT EXIST (e|eBm|mAm=e=) Select 姓名,学院名称 From 学生, 学院 Where (学生.学院代码=学院.学院代码) And NOT EXISTS ( Select 课程号 From 开课 Where 教师号=(Select 教师号 From 教师 Where 教师姓名=雷老师)

14、 And NOT EXISTS ( Select 课程号 From 学习 Where 学号=学生.学号 And 课程号=开课.课程号) ),21,求选修了某老师所授全部课程的学生姓名和学院名称,Select 姓名,学院名称 From 学生, 学院 Where (学生.学院代码=学院.学院代码) And (该学生选修了某老师所授全部课程) 等价于： Select 姓名,学院名称 From 学生, 学院 Where (学生.学院代码=学院.学院代码) And (存在一个老师，该学生选修了该老师所授全部课程) 即集合不为空,22,求选修了某老师所授全部课程的学生姓名和学院名称,复杂条件：存在一个老师

15、，该学生选修了该老师所授全部课程课程 （1）全体教师 = T （2）该学生所选课程 = 集合A （3）某老师t所授全部课程 = 集合B （4）EXISTS t|tT B IN A=True （5）B IN A=True NOT EXIST (B-A) （6）B-A = e|eB e NOT IN A= e|eB m| mA m=e = ,23,求选修了某老师所授全部课程的学生姓名和学院名称,集合T：Select 教师号 From 教师; 集合A：Select 课程号 From 学习 Where 学号=该学生的学号 集合B：Select 课程号 From 开课 Where 教师号=教师t的教师号,24,Select