实验3

1. (简答题, 2分) 本实验的实验目的是：
   (1)掌握设计一个以上数据表的查询方法；
   (2)掌握嵌套查询，使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。

2. (简答题, 4分)
   请写出下列查询操作的SQL语句："查询2022030381班的学生在大学一年级选修的课程情况，查询结果要显示学号(Sno)、姓名(Sname)、专业名(Mname)、学年(Racademicyear)、学期(Rterm)、选课的课程号(Cno)、选课的课程名称(Cname)及成绩（Grade)，并按照学号、学年、学期升序排序"

   USE jxdb59;
   SELECT report.Sno, s.Sname, m.Mname, report.Racademicyear, report.Rterm, report.Cno, C.Cname, report.Grade
   FROM reports59 report
   JOIN students59 s ON s.Sno = report.Sno
   JOIN major59 m ON m.Mno = s.Mno
   JOIN courses59 c ON c.Cno = report.Cno
   WHERE s.Sclass = '2022030381' AND Racademicyear = 2022
   ORDER BY Sno, Racademicyear, Rterm;

   

3. (简答题, 4分)
   请写出下列查询操作的SQL语句："查询教师张雪的授课信息，查询结果显示教师编号(Tno)、姓名(Tname）、授课学年(Tacademicyear)、授课学期(Tterm)、授课的课程编号(Cno)、课程名(Cname)以及授课班级(Sclass)，结果按授课学年、授课学期升序排序"

   USE jxdb59;
   SELECT t.Tno, t.Tname, tc.Tacademicyear, tc.Tterm, tc.Cno, C.Cname, tc.Sclass
   FROM tutors59 tc
   JOIN teachers59 t ON t.Tno = tc.Tno
   JOIN courses59 c ON c.Cno = tc.Cno
   WHERE t.Tname = '张雪'
   ORDER BY Tacademicyear, Tterm;

   

4. (简答题, 4分)
   请写出下列查询操作的SQL语句："查询计算机与信息工程学院的各个专业的学生数，查询结果显示专业号(Mno)、专业名称(Mname）、学生人数，按专业号升序排序，并用中文名显示属性列"

   USE jxdb59;
   SELECT m.Mno AS '专业号',
      m.Mname AS '专业名称',
      COUNT(*) AS '学生人数'
   FROM major59 m
   JOIN students59 s ON m.Mno = s.Mno
   JOIN department59 d ON m.Dno = d.Dno
   WHERE d.Dname = '计算机与信息工程学院'
   GROUP BY m.Mno, m.Mname
   ORDER BY m.Mno;

   

5. (简答题, 4分)
   请写出下列查询操作的SQL语句："用自身连接查询和'王一凡'在同一个学院，且总工资（基本工资Tsal+岗位津贴Tcomm）比'王一凡'高的教师的信息，结果显示教师编号(Tno)、姓名(Tname)和总工资，并用中文显示属性列名"

   USE jxdb59;
   SELECT t1.Tno AS '教师编号',
      t1.Tname AS '姓名',
      (t1.Tsal + t1.Tcomm) AS '总工资'
   FROM teachers59 t1, teachers59 t2
   WHERE t1.Dno = t2.Dno
   AND t2.Tname = '王一凡'
   AND (t1.Tsal + t1.Tcomm) > (t2.Tsal + t2.Tcomm);

   

6. (简答题, 4分)
   请写出下列查询操作的SQL语句："用自身连接查询前序课程是数据结构的课程的信息，查询结果显示课程编号（Cno）、课程名称（Cname）和学分（Ccredit）"

   USE jxdb59;
   SELECT c1.Cno, c1.Cname, c1.Ccredit
   FROM courses59 c1, courses59 c2
   WHERE c1.Pre_Cno = c2.Cno AND c2.Cname = '数据结构';

   

7. (简答题, 5分)
   请写出下列查询操作的SQL语句："用自身连接查询既给2022030051班上过课，也给2022030381班上过课的教师信息，查询结果显示教师编号(Tno)、教师姓名(Tname)"

   USE jxdb59;
   SELECT DISTINCT t.Tno, t.Tname
   FROM tutors59 t1, tutors59 t2
   JOIN teachers59 t
   WHERE t1.Tno = t2.Tno
   AND t1.Sclass = '2022030051'
   AND t2.Sclass = '2022030381';

   

8. (简答题, 5分)
   请写出下列查询操作的SQL语句："查询教师表中的每一位数学与统计学院的教师的授课课情况，查询结果显示教师编号(Tno)、教师姓名(Tname)、授课学年(Tacademicyear)、授课学期(Tterm)、课程编号(Cno)及课程名称(Cname)，按教师编号、开课学年、授课学期升序排序"

   USE jxdb59;
   SELECT t.Tno, t.Tname, tc.Tacademicyear, tc.Tterm, tc.Cno, c.Cname, d.Dname
   FROM tutors59 tc
   INNER JOIN teachers59 t ON tc.Tno = t.Tno
   INNER JOIN courses59 c ON c.Cno = tc.Cno
   JOIN department59 d ON d.Dno = t.Dno
   WHERE d.Dname = '数学与统计学院'
   ORDER BY t.Tno, tc.Tacademicyear, tc.Tterm;

   

9. (简答题, 4分)
   请写出下列查询操作的SQL语句："使用谓词IN嵌套查询，查询有课程成绩不及格的学生的信息，结果显示学号(Sno)、姓名(Sname)及所在班级(Sclass)"

   USE jxdb59;
   SELECT DISTINCT s.Sno, s.Sname, s.Sclass
   FROM students59 s
   WHERE s.Sno IN (
   SELECT Sno
   FROM reports59
   WHERE Grade < 60
   );

   

10. (简答题, 4分)
    请写出下列查询操作的SQL语句："使用谓词IN嵌套查询，查询在2020学年有授课的教师的信息，结果显示教师编号(Tno)、教师姓名(Tname)及职称(Tprof)"

    USE jxdb59;
    SELECT t.Tno, t.Tname, t.Tprof
    FROM teachers59 t
    WHERE t.Tno IN (
    SELECT Tno
    FROM tutors59
    WHERE Tacademicyear = '2020'
    );

    

11. (简答题, 5分)
    请写出下列查询操作的SQL语句："使用嵌套查询，查询计算机与信息工程学院中总工资高于本学院的平均总工资的教师信息，结果以中文名称显示教师编号(Tno)、教师姓名(Tname)、职称（Tprof）及总工资" （总工资=工资Tsal+岗位津贴Tcomm）

    USE jxdb59;
    SELECT T.Tno AS '教师编号',
       T.Tname AS '教师姓名',
       T.Tprof AS '职称',
       (T.Tsal + T.Tcomm) AS '总工资'
    FROM Teachers59 T
    JOIN Department59 D ON T.Dno = D.Dno
    WHERE D.Dname = '计算机与信息工程学院'
    AND (T.Tsal + T.Tcomm) > (
      SELECT AVG(Tsal + Tcomm)
      FROM Teachers59 T2
      WHERE T2.Dno = T.Dno
    );

    

12. (简答题, 5分)
    请写出下列查询操作的SQL语句："使用嵌套查询，查询总工资高于所有学院的平均总工资的教师信息，结果显示教师编号(Tno)、教师姓名(Tname)、学院名称(Dname)、职称(Tprof)及总工资，结果按教师编号升序排序，并用中文显示属性列名" （总工资=工资Tsal+岗位津贴Tcomm）

    USE jxdb59;
    SELECT T.Tno AS '教师编号', T.Tname AS '教师姓名', D.Dname AS '学院名称',
       T.Tprof AS '职称', (T.Tsal + T.Tcomm) AS '总工资'
    FROM Teachers59 T
    JOIN Department59 D ON T.Dno = D.Dno
    WHERE (T.Tsal + T.Tcomm) > ALL (
    SELECT AVG(Tsal + Tcomm)
    FROM Teachers59
    GROUP BY Dno
    )
    ORDER BY T.Tno;

    

13. (简答题, 7分)
    请写出下列查询操作的SQL语句："使用嵌套查询，查询已获学分高于2020级所有计算机与信息工程学院的学生获得的学分的学生学号(Sno)、姓名(Sname)及已获得的总学分，并用中文显示属性列名"

（提示：① 已获学分是指已有成绩且成绩大于等于60分的课程的学分；② 先统计出计算机与信息工程学院的每位学生获得的学分；③ 统计的每位学生获得的学分，再筛选出满足条件的学生）

14. (简答题, 5分)
    请写出下列查询操作的SQL语句："使用EXISTS谓词查询计算机与信息工程学院中没有讲授过课程号为112p0054课程的教师编号(Tno)、姓名(Tname)和职称(Tprof)"

15. (简答题, 6分)
请写出下列查询操作的SQL语句："使用EXISTS谓语查询至少选修了学生202003005104选修的全部课程的学生的学号(Sno)、姓名(Sname)"
```sql

16. (简答题, 5分)
    请写出下列查询操作的SQL语句："使用嵌套子关系查询，查询课程表中的所有课程在2023学年的两个学期的选课情况，查询结果以中文名显示课程编号(Cno)、课程名称(Cname)、选课学年(Racademicyear)、选课学期(Rterm)及该学年该学期的选课人数(SelNumber)，按课程编号、选课学年、选课学期升序排序"

17. (简答题, 9分)
请写出下列查询操作的SQL语句："使用嵌套子关系查询，查询课程表中每一门课程的选课情况，查询结果要显示课程编号(Cno)、课程名称(Cname)、选课人数、平均分、最高分、最低分。若某门课程还没有被选，则要选课人数显示为‘尚无人选’,平均分等显示为NULL；若某门课程有人选，但平均分等结果为NULL，则显示为’尚无成绩’；非空的数据结果保留两位小数。结果按课程编号升序排序，并用中文显示属性列名"

提示：① 本查询会使用到COALESCE()函数、FORMAT()函数；② 本查询需要使用CASE WHEN语句；③先统计出每门课程的选修情况，再与课程表建立左连接

另：在执行此语句前，先在课程表中加入一门新的课程信息（Cno: 112p1025，Cname: Java_面向对象程序设计，Pre_Cno: 112p0055, Chour: 64, Ccredit: 4）。
```sql

18. (简答题, 9分)
    请写出下列查询操作的SQL语句："使用嵌套子关系查询，查询在2021学年的第1学期，选修了刘伟老师上的计算机科学概论课程的学生的基本信息，查询结果显示所在班级(Sclass)、学号(Sno)、姓名(Sname）、选修的课程编号(Cno)、课程名(Cname)以及授课的教师(Tname)"

19. (简答题, 9分)
请写出下列查询操作的SQL语句："使用嵌套子关系查询，查询选修了数据结构，且其成绩不低于本年级所有选修了该门课程的学生的平均成绩的学生信息，查询结果以中文名称显示学生所在年级、学号(Sno)、姓名(Sname)及其选修的该门课程的成绩(Grade)"
```sql