数据库实验三 数据库表的复杂查询

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

SELECT T.Tno, Tname,Tacademicyear, Tterm, Tut.Cno, Cname

FROM Department D

INNER JOIN Teachers T ON T.Dno=D.Dno

LEFT JOIN Tutors Tut ON T.Tno=Tut.Tno

LEFT JOIN Courses C ON Tut.Cno=C.Cno

WHERE Dname='数学与统计学院';

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

SELECT Tno AS "教师编号", Tname AS "教师姓名", Tprof AS "职称", Tsal+Tcomm AS "总工资"

FROM Teachers T, Department D

WHERE T.Dno=D.Dno AND Dname='计算机与信息工程学院'

AND (Tsal+Tcomm)>

( SELECT AVG(Tsal+Tcomm)

  FROM Teachers

  WHERE Dno=

( SELECT Dno

  FROM Department

  WHERE Dname='计算机与信息工程学院'

)

);

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

SELECT Tno AS "教师编号", Tname AS "教师姓名", Dname AS "学院名称", Tprof AS "职称", Tsal+Tcomm AS "总工资"

FROM Teachers T, Department D

WHERE T.Dno=D.Dno 

AND (Tsal+Tcomm)> ALL

( SELECT AVG(Tsal+Tcomm)

  FROM Teachers

  GROUP BY Dno

);

使用EXISTS谓语查询至少选修了学生2014112104选修的全部课程的学生的学号(Sno)、姓名(Sname)

SELECT S.Sno, Sname

FROM Students S

WHERE S.Sno<>'2014112104'

AND NOT EXISTS

(SELECT * FROM Reports Rep

WHERE Sno='2014112104'

AND NOT EXISTS

(SELECT * FROM Reports

WHERE S.Sno=Sno AND Rep.Cno=Cno)

);

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

SELECT Tut.Sclass, Sno, Sname, T.Tno, Tname

FROM Tutors Tut, Teachers T, Courses C,

( SELECT S.Sno, Sname, Sclass 

  FROM Reports R, Courses C, Students S

  WHERE R.Cno=C.Cno AND R.Sno=S.Sno

AND Racademicyear=2015 AND Rterm=1

AND Cname='计算机科学概论'

) R

WHERE Tut.Tno=T.Tno AND Tut.Cno=C.Cno AND R.Sclass=Tut.Sclass

AND Tacademicyear=2015 AND Tterm=1

AND Cname='计算机科学概论' AND Tname='刘伟';

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

SELECT LEFT(S.Sno,4) '年级', S.Sno '学号', Sname '姓名', Grade '成绩'

FROM Students S, Reports R

WHERE S.Sno=R.Sno

AND Cno=

(SELECT Cno

FROM Courses

WHERE Cname='数据结构')

AND S.Sno IN

(SELECT R1.Sno

FROM Reports R1 INNER JOIN

(SELECT LEFT(Sno,4) NJ,AVG(Grade) AvgGrd

FROM Reports

WHERE Cno=

(SELECT Cno

FROM Courses

WHERE Cname='数据结构') 

GROUP BY LEFT(Sno,4)) R2

ON LEFT(R1.Sno,4)=R2.NJ

WHERE R1.Grade>=R2.AvgGrd

AND R1.Cno=

(SELECT Cno

FROM Courses

WHERE Cname='数据结构')

);