【SQL Server数据库】带函数查询和综合查询（2）

目录

1．检索至少选修课程“数据结构”和“C语言”的学生学号。

2．列出所有班名、班主任、班长、系名。

3.没有选修以“01”开头的课程的学生学号，姓名，选课的课程号。

4. 统计各门课程的选修人数，并按人数降序排列，找出排名前三位的课程。

5. 统计各门课程的选修人数，并按人数降序排列，找出排名前三位的课程。

6. 检索选修了‘0103’和‘0105’两门课程，并且‘0103’这门课程成绩高于‘0105’的学生的学号。

7. 检索选修了课程“数据结构”和“C语言”两门课程并且“数据结构”分数高于“C语言”的学生学号和姓名。

小结

---

1．检索至少选修课程“数据结构”和“C语言”的学生学号。

-- 交集

select Stu_id

from StudentGrade

where Course_id in (select Course_id from Course where Course_name = '数据结构')

intersect

select Stu_id

from StudentGrade

where Course_id in (select Course_id from Course where Course_name = 'C语言');



-- 自连接：同一个学生，第一列科目是数据结构，第二列是c语言

select sg1.Stu_id

from StudentGrade sg1,

     StudentGrade sg2

where sg1.Course_id = (select Course_id from Course where Course_name = '数据结构')

  and sg2.Course_id = (select Course_id from Course where Course_name = 'C语言')

  and sg1.Stu_id = sg2.Stu_id;

2．列出所有班名、班主任、班长、系名。

（请使用连接查询；进一步考虑使用外连接，因为很多班级可能是没有班长的，考虑需要显示所有班级的信息）

select c.Class_name, t.Teac_name, s.Stu_name, d.Depar_name

from Class c,

     Teacher t,

     Student s,

     Deparment d

where c.Depar_id = d.Depar_id

  and c.Director = t.Teac_id

  and c.Monitor = s.Stu_id;

-- 左外连接（显示没有班长的班级）

select c.Class_name, t.Teac_name, s.Stu_name, d.Depar_name

from Class c

         left join Deparment d on c.Depar_id = d.Depar_id

         left join Teacher t on c.Director = t.Teac_id

         left join Student s on c.Monitor = s.Stu_id;

3.没有选修以“01”开头的课程的学生学号，姓名，选课的课程号。

（用子查询完成，提示not in或not exists。需考虑没选课的学生，仔细对比上一篇博客第5题）

【SQL Server数据库】带函数查询和综合查询（1）-CSDN博客

-- not exists

select s.Stu_id, s.Stu_name, sg.Course_id

from Student s

         left join StudentGrade sg on s.Stu_id = sg.Stu_id

where not exists(select sg.Stu_id

                 from StudentGrade sg

                 where sg.Course_id like '01%'

                   and s.Stu_id = sg.Stu_id)

-- not in

select s.Stu_id, s.Stu_name, sg.Course_id

from Student s

         left join StudentGrade sg on s.Stu_id = sg.Stu_id

where s.Stu_id not in (select sg.Stu_id

                       from StudentGrade sg

                       where sg.Course_id like '01%')

4. 统计各门课程的选修人数，并按人数降序排列，找出排名前三位的课程。

（提示：可以使用TOP 3）

select TOP 3 c.Course_name, count(sg.Course_id) '选修人数'

from Course c

         left join StudentGrade sg on

    c.Course_id = sg.Course_id

group by c.Course_name

ORDER BY count(c.Course_id) desc;

5. 统计各门课程的选修人数，并按人数降序排列，找出排名前三位的课程。

（上述使用TOP 3，却不能处理人数并列的情况。试考虑一种方法能处理人数并列的情况。）

select c.Course_name, count(sg.Course_id) '选修人数'

from Course c

         left join StudentGrade sg on

    c.Course_id = sg.Course_id

group by c.Course_name

having count(sg.Course_id) in (select distinct TOP 3 count(sg.Course_id) '选修人数'

                                     from Course c

                                              left join StudentGrade sg on

                                         c.Course_id = sg.Course_id

                                     group by c.Course_name

                                     ORDER BY count(sg.Course_id) desc

                               )

order by count(sg.Course_id) desc;

6. 检索选修了‘0103’和‘0105’两门课程，并且‘0103’这门课程成绩高于‘0105’的学生的学号。

select sg1.Stu_id

from StudentGrade sg1,

     StudentGrade sg2

where sg1.Course_id = '0103'

  and sg2.Course_id = '0105'

  and sg1.Stu_id = sg2.Stu_id

  and sg1.Grade > sg2.Grade;

7. 检索选修了课程“数据结构”和“C语言”两门课程并且“数据结构”分数高于“C语言”的学生学号和姓名。

select distinct sg1.Stu_id, s.Stu_name

from StudentGrade sg1,

     StudentGrade sg2,

     Student s

where sg1.Course_id = (select Course_id from Course where Course_name = '数据结构')

  and sg2.Course_id = (select Course_id from Course where Course_name = 'C语言')

  and sg1.Grade > sg2.Grade

  and sg1.Stu_id = sg2.Stu_id

  and sg1.Stu_id = s.Stu_id;

小结

在查询的过程需注意表格之间的关系，是否需要外连接，显示一些其他信息。

通过第四题和第五题对TOP 3的含义更加清晰了，返回的是按某个条件排序后的前三条结果。在 SQL Server 中，可以使用 TOP 子句来限制返回的行数，并使用 ORDER BY 子句进行排序。

通过第六题和第七题，我更加深刻的理解了表的自连接查询。表的自连接查询是指在同一张表中进行连接操作，将表中的不同行作为两个独立的实例进行比较和匹配。这种技术常用于处理具有层级结构的数据或者在一个表中存储了相关的信息。要进行自连接查询，可以使用表的别名来创建两个表的副本，并通过别名进行连接。