数据库实验—复杂查询

• 查询20161151班的学生在大学一年级选修的课程情况，查询结果要显示学号(Sno)、姓名(Sname)、专业名(Mname)、选课的课程号(Cno)、选课的课程名称(Cname)及成绩（Grade)，并按照学号、课程号升序排序

  select Sno, Sname,Mname, Cno,Cname,Grade
  from Students S, Courses C, Reports R, Major M
  where S.Sno = R.Sno and C.Cno = R.Cno 
  and M.Mno = S.Mno and S.Sclass = '20161151'
  and R.Racademicyear = 2016
  order by S.Sno, C.Cno -- 默认升序，可以不写asc
  

• 查询教师编号为T002的教师的授课信息，查询结果显示教师编号(Tno)、姓名(Tname）、授课学年(Tacademicyear)、授课学期(Tterm)、授课的课程编号(Cno)、课程名(Cname)以及授课班级(Sclass)，结果按授课学年、授课学期升序排序

  select T.Tno, T.Tname, Tu.Tacademicyear, Tu.Tterm, 
  Tu.Cno, C.Cname, Tu.Sclass
  from Teachers T, Tutors Tu, Courses C
  where T.Tno = Tu.Tno, C.Cno = Tu.Cno and Tno = 'T002'
  order by Tu.Tacademicyear, Tu.Tterm
  

• 查询计算机与信息工程学院的各个专业的学生数，查询结果以中文名显示专业号(Mno)、专业名称(Mname）、学生人数，并按专业号升序排序

  select M.Mno '专业号', 
  M.Mname '专业名称', count(*) '学生人数'
  from Major M, Students S, Department D
  where M.Mno = S.Mno and M.Dno = D.Dno 
  and D.Dname = '计算机与信息工程学院'
  group by M.Mno, M.Mname 
  order by M.Mno
  

• 查询和“王一凡”在同一个学院，且总工资（基本工资Tsal+岗位津贴Tcomm）比“王一凡”高的教师的信息，查询结果以中文显示教师编号(Tno)、姓名(Tname)和总工资

  select T2.Tno '教师编号', T2.Tname '姓名',
  T2.Tsal+T2.Tcomm '总工资' 
  from Teachers T1, Teachers T2 
  where T1.Tname = ' 王一凡' and T1.Tno <> T2.Tno
  and (T1.Tsal+T1.Tcomm < T2.Tsal+T2.Tcomm)
  and T1.Dno = T2.Dno
  

• 查询前序课程是数据结构的课程的信息，查询结果显示课程编号（Cno）、课程名称（Cname）和学分（Ccredit）

  select C1.Cno, C1.Cname,C1.Ccredit
  from Courses C1, Courses C2
  where C1.Cno = C2.Cno and C1.Cname = '数据结构'
  

• 查询既给20161121班上过课，也给20161151班上过课的教师信息，查询结果显示教师编号(Tno)、教师姓名(Tname)

  select  distinct T1.Tno, T.Tname
  from Tutors T1, Tutors T2,Teachers T
  where T1.Tno = T2.Tno and T1.Tno = T.Tno
  and T1.Sclass = '20161121'
  and T2.Sclass = '20161151'
  

• 查询课程表中的所有课程在每个学期的选课情况，查询结果以中文名显示课程编号(Cno)、课程名称(Cname)、选课学年(Racademicyear)、选课学期(Rterm)及该学年该学期的选课人数(SelNumber)，按课程编号、选课学年、选课学期升序排序

  • 从这一题中学到，分组的对象就是最后select的那个表的主键，否则如果select元组主键没有进行分组的就可能会报错，因为用集函数就是希望能够计算多个元组的数据
    • 比如：我要计算成绩，这时候我多个表拼起来了，那么最后select出来的多个元组组合起来就是属于一个表，那么这个select出来的表选一个主键，那么这个主键就应该是你分组的属性。
    • 一般比较霸道的做法就是你select什么就将其放进分组中
    • 但有种属于比较大白话的，这种就属于你能读懂就可以知道怎么写，如下就是，典型的直接使用大白话说明了按照学院分组，然后计算每个学院的平均薪水

      select avg((T2.Tsal + T2.Tcomm))
      				from Teachers T2
      				where T1.Tno <>T2.Tno 
      				group by T2.Dno
      

  • 因为查找的是所有课程，为了防止会出现Reports表中没有对应课程记录，所以将Courses表设置为主表进行查询。（left join表示以左边的表为主表，如果你把Courses表放在右边就要用right join）
  • left join 或者right join后不能接where，两个表之间的条件筛选变成了on，只有当两个表连接完成后你还想继续筛选的时候还是可以继续使用where的，同样后面如果有分组的还是继续使用having

  select R.Cno '课程编号' , C.Cname '课程名',
  R.Racademicyear '选课学年', R.Rterm, count(*) '选课人数'
  from Courses C left join Reports R
  on C.Cno = R.Cno
  group by R.Cno, C.Cname,R.Rterm,R.Racademicyear
  order by R.Cno, R.Racademicyear, R.Rterm
  

• 查询教师表中的每一位数学与统计学院的教师的授课课情况，查询结果显示教师编号(Tno)、教师姓名(Tname)、授课学年(Tacademicyear)、授课学期(Tterm)、课程编号(Cno)及课程名称(Cname)，按教师编号、开课学年、授课学期升序排序

  select 
  Tu.Tno, T.Tname,Tu.Tacademicyear, 
  Tu.Tterm,C.Cno,C.Cname
  from 
  Department D inner join Teachers T on T.Dno = D.Dno
  left join Tutors Tu on T.Tno = Tu.Tno
  left join Courses C on Tu.Cno = C.Cno
  where D.Dname = '数学与统计学院'
  order by T.Tno, Tu.Tacademicyear, Tu.Tterm
  

• 使用IN谓词查询有课程成绩不及格的学生的信息，结果显示学号(Sno)、姓名(Sname)及所在班级(Sclass)

  select Sno, Sname, Sclass
  from Students S
  where S.Sno in (select Sno
  				from Reports 
  				where Grade < 60)
  

• 使用嵌套WHERE子查询，查询在2014学年有授课的教师的信息，结果显示教师编号(Tno)、教师姓名(Tname)及职称(Tprof)

  select Tno,Tname,Tprof
  from Teachers
  where Tno in (select Tno
  				from Tutors
  				where Tacademicyear = 2014)
  

• 使用嵌套WHERE子查询，查询计算机与信息工程学院中总工资高于本学院的平均总工资的教师信息，结果以中文名称显示教师编号(Tno)、教师姓名(Tname)、职称（Tprof）及总工资

  select T1.Tno, T1.Tname, T1.Tprof,(T1.Tsal+T1.Tcomm)
  from Teachers T1
  where (T1.Tsal+T1.Tcomm) > 
  		(select avg((T2.Tsal+T2.Tcomm)) 
  		from Teachers T2
  		where T1.Dno = T2.Dno 
  		and T2.Dno = (select Dno
  					from Department
  					where Dname = '计算机与信息工程学院'))
  

• 使用嵌套WHERE子查询，查询总工资高于所有学院的平均总工资的教师信息，结果显示教师编号(Tno)、教师姓名(Tname)、学院名称(Dname)、职称(Tprof)及总工资

  select T1.Tno, T1.Tname, D.Dname, 
  T1.Tprof, (T1.Tsal+T1.Tcomm)
  from Teachers T1,Department D
  where T1.Dno = D.Dno and 
  (T1.Tsal+T1.Tcomm) > all (
  				select avg((T2.Tsal + T2.Tcomm))
  				from Teachers T2
  				where T1.Tno <>T2.Tno 
  				group by T2.Dno)
  				
  

• 使用嵌套WHERE子查询，查询已获学分比2014级所有计算机与信息工程学院学生的获得的学分都高的学生的学号(Sno)、姓名(Sname)及已获得的总学分（提示：① 已获学分是指已有成绩且成绩大于等于60分的课程的学分；② 先统计每位学生获得的学分，再筛选出满足条件的学生）

  select S.Sno, S.Sname, sum(C.Ccredit) '总学分'
  from Reports R, Courses C,Students S
  where C.Cno = R.Cno and R.Sno = S.Sno
  and R.Grade > 60
  group by R.Sno,S.Sname
  having sum(C.Ccredit) > all (
  	select sum(Ccredit)
  	from Reports R,Department D,Courses C
  	where R.Grade > 60 and C.Cno = R.Cno 
  	and left(R.Sno,4) = '2014'
  	and D.Dno=(
  		select Dno
  		from Department
  		where Dname = '计算机与信息工程学院')
  	group by R.Sno)
  

• 使用EXISTS谓词查询计算机与信息工程学院中没有讲授过课程号为112p0054课程的教师编号(Tno)、姓名(Tname)和职称(Tprof)

  select T.Tno, T.Tname, T.Tprof
  from Teachers T
  where 
  not exists (select * 
  			from Tutors Tu
  			where Cno = '112p0054' 
  			and Tu.Tno = T.Tno)
  and 
  T.Dno = (select Dno
  			from Department
  			where Dname = '计算机与信息工程学院')
  

• 使用EXISTS谓语查询至少选修了学生2014112104选修的全部课程的学生的学号(Sno)、姓名(Sname)及其所选修的课程号(Cno)、课程名(Cname)和成绩(Grade)

  select S.Sno, C.Cno, C.Cname, R.Grade
  from Students S, Courses C, Reports R
  where S.Sno = R.Sno and C.Cno = R.Cno
  and S.Sno<>'2014112104'
  and not exists (select *
  			from Reports R_
  			where R_.Sno = '2014112104'
  			not exists (select *
  						from Reports 
  						where S.Sno = Sno 
  						and Cno = R_.Cno))
  

• 使用嵌套子关系查询，查询课程表中每一门课程的选课情况，查询结果要显示课程编号(Cno)、课程名称(Cname)、选课人数、平均分、最高分、最低分。若某门课程还没有被选，则要选课人数显示为‘尚未选’,平均分等显示为NULL；若某门课程有人选，但平均分等结果为NULL，则显示为’尚无成绩’；非空的数据结果保留两位小数。

  • convert：第一个参数是类型，decimal(4,2)表示最后数据出来的长度是4是包含小数位的总长，保留2位小数。
    点击学习SQL数据类型讲解
  • case when then 必须加上end表示结束，否则会报错。

  select C.Cname, 
  case when count(*) is null then '尚未选' 
  else count(*) end,
  case when avg(R.Grade) is null then '尚无成绩' 
  else convert(decimal(4,2),avg(R.Grade)) end,
  case when max(R.Grade) is null then '尚无成绩' 
  else convert(decimal(4,2),max(R.Grade)) end,
  case when min(R.Grade) is null then '尚无成绩' 
  else convert(decimal(4,2),min(R.Grade)) end
  from Courses C left join Reports R on C.Cno = R.Cno 
  group by C.Cname, C.Cno
  

• 使用嵌套子关系查询，查询在2015年的第1学期，选修了刘伟老师上的计算机科学概论课程的学生的基本信息，查询结果显示所在班级(Sclass)、学号(Sno)、姓名(Sname）、选修的课程编号(Cno)、课程名(Cname)以及授课的教师(Tname)

  • 这里采用了先在Reports表中选出所有选择了计算机科学概论的同学信息，然后再进一步筛选是刘伟老师授课的同学

  select S.Sclass,S.Sno, S.Sname,C.Cno,C.Cname,T.Tname
  from 
  (select R.Sno Sno,R.Cno Cno
  from Reports R
  where R.Cno = (select Cno
  				from Courses
  				where Cname ='计算机科学概论')
  and R.Racademicyear = 2015 and R.Rterm = 1) as new_R,
  Teachers T,Students S, Courses C
  where
  Tname = '刘伟'
  and new_R.Cno = C.Cno and new_R.Sno = S.Sno
  

• 使用嵌套子关系查询，查询选修了数据结构，且其成绩不低于本年级所有选修了该门课程的学生的平均成绩的学生信息，查询结果以中文名称显示学生所在年级、学号(Sno)、姓名(Sname)及其选修的该门课程的成绩(Grade)

select S.Sno, S.Sname,R.Grade
from Students S,Reports R
where R.Sno = S.Sno 
and R.Grade >= (select avg(Grade)
			from Reports
			where R.Cno = Cno and left(S.Sno,4) = left(Sno,4)
			and Cno = (select Cno
						from Courses
						where Cname = '数据结构'))