SQL综合查询下

18、查询所有人都选修了的课程号与课程名

题目

有课程表，学生表，成绩表如下，查询所有人都选修了的课程号与课程名。

course
列名数据类型约束说明cnochar(4)主键非空课程号cnamevarchar(40)非空课程名cpnochar(4)参照course(cno)先修课ccredittinyint学分
student
列名数据类型约束说明snochar(7)主键非空学号snamechar(10)非空学生姓名ssexenum(‘男’,‘女’)默认‘男’性别sagetinyint年龄sdeptchar(20)默认’计算机系’系别
sc
列名数据类型约束说明snochar(7)主键非空，参照student(sno)学号cnochar(4)主键非空，参照course(cno)课程号gradedecimal(5,1)成绩ccredittinyint

代码

# 查询所有人都选修了的课程号与课程名select c.cno,c.cname from course c wherenotexists(select s.sno from student s wherenotexists(select a.cno from sc a where a.sno=s.sno and a.cno=c.cno))

题解

该语句中，使用了嵌套的子查询来查找所有人都选修了的课程。
首先，在外层查询中，从课程表中选择本门课程的编号和名称。
然后，在内层查询中，对于每个学生，我们检查是否存在没有修读该门课程的情况。
如果不存在这样的情况，则意味着所有学生都修读了该门课程，因此将该门课程包含在结果中。

19、SQL查询：查询没有参加选课的学生。

题目

#查询没有参加选课的学生，输出系名，学号，姓名，性别，按系名（升序），学号（升序）排序。

[注意：SQL表名请用小写]

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 绩 SMALLINT

代码

#查询没有参加选课的学生，输出系名，学号，姓名，性别，按系名（升序），学号（升序）排序。select s.sdept ,s.sno ,s.sname,s.ssex
from student s
where s.sno notin(selectdistinct sno from sc )orderby s.sdept asc,s.sno asc;

20、SQL查询：统计各门课程选修人数，要求输出课程代号，课程名，有成绩人数（grade不为NULL），最高分，最低分，平均分，按课程号排序。

题目

#统计各门课程选修人数，要求输出课程代号，课程名，有成绩人数（grade不为NULL），最高分，最低分，平均分(取整round函数)，按课程号排序。

[注意：SQL表名请用小写]

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

#统计各门课程选修人数，要求输出课程代号，课程名，有成绩人数（grade不为NULL），最高分，最低分，平均分(取整round函数)，按课程号排序。select c.cno,c.cname,count(sc.sno),max(sc.grade),min(sc.grade),round(avg(sc.grade))from course c 
join sc on c.cno=sc.cno
where sc.grade isnotnullgroupby c.cno
orderby c.cno;

题解

从课程表(course)和选课表(sc)中查询课程代号(cno)，课程名(cname)，成绩不为NULL的学生人数(count(sc.sno))，最高分(max(sc.grade))，最低分(min(sc.grade))和平均分(round(avg(sc.grade)))这些字段；
通过INNER JOIN关键字将课程表和选课表连接起来，连接条件为课程代号(cno)相等；
使用WHERE子句过滤掉成绩为NULL的记录，只统计有成绩的学生人数；
使用GROUP BY子句对每门课程进行分组，统计每门课程的选修人数及成绩相关指标；
使用ORDER BY子句按照课程代号(cno)升序排序。

21、查询计算机系平均成绩高于70分的成绩信息。

题目

有课程表，学生表，成绩表如下，查询计算机系平均成绩高于70分的学号，姓名、平均成绩，并按平均成绩降序排列。

代码

#查询计算机系平均成绩高于70分的成绩信息SELECT sc.sno, s.sname,AVG(sc.grade)FROM sc
JOIN student s ON sc.sno = s.sno
WHERE s.sdept ='计算机系'GROUPBY sc.sno
HAVINGAVG(sc.grade)>70ORDERBYAVG(sc.grade)DESC

22、查询同时选修了“数据库基础”和“计算机网络”两门课的学生的学号，姓名。

题目

在这里插入图片描述

代码

# 查询同时选修了“数据库基础”和“计算机网络”两门课的学生的学号，姓名。select sno,sname
from student
where sno in(select sno
 from course,sc
 where course.cno=sc.cno and cname='数据库基础'and sno in(select sno
 from course,sc
 where course.cno=sc.cno and cname='计算机网络'));

not exists写法

select sno,sname from student 
wherenotexists(select*from course where cname in('数据库基础','计算机网络')andnotexists(select*from sc where sno=student.sno and cno=course.cno))

在主查询中，我们选择输出学生的学号和姓名，因此使用了 SELECT sno, sname。
在 FROM 子句中，我们选择了 student 表，表示我们要查询学生的信息。
在 WHERE 子句中，我们使用了一个子查询来筛选同时选修了“数据库基础”和“计算机网络”两门课程的学生。具体来说，子查询中的第一个 NOT EXISTS 子句用于筛选选修了至少一门不是这两门课程的学生，第二个 NOT EXISTS 子句用于筛选未选修这两门课程的学生。通过使用两个 NOT EXISTS 子句的嵌套，我们筛选出了选修了这两门课程的学生。
在子查询中，我们使用了 SELECT * FROM course WHERE cname IN ('数据库基础', '计算机网络') 来选择选修了这两门课程的课程记录。然后，我们使用 NOT EXISTS 子句来筛选未选修这些课程的学生。具体来说，NOT EXISTS 子句中的子查询 SELECT * FROM sc WHERE sno = student.sno AND cno = course.cno 用于查找学生选课表中是否存在选修了这些课程的记录。如果不存在这样的记录，那么学生就未选修这些课程。

📌如果对not exists用法不熟悉建议用两次子查询

23、SQL查询：查询选修了课程的学生的学号和姓名。

题目

#查询选修了课程的学生的学号和姓名，按学号排序。

[注意：SQL表名请用小写]

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

#查询选修了课程的学生的学号和姓名，按学号排序。selectdistinct s.sno,s.sname from student s,sc
where s.sno = sc.sno
orderby s.sno;

24、SQL查询：查询学生的选课情况，要求输出学号，姓名，课程门数，课程名列表（按课程名升序排列，用下划线_分隔），按照学号升序排序。

题目

#查询学生的选课情况，要求输出学号，姓名，课程门数，课程名列表（按课程名升序排列，用下划线_分隔），按照学号升序排序。

[注意：SQL表名请用小写]

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

#查询学生的选课情况，要求输出学号，姓名，课程门数，课程名列表（按课程名升序排列，用下划线_分隔），按照学号升序排序。SELECT s.sno,sname,COUNT(*),
GROUP_CONCAT(cname ORDERBY cname SEPARATOR '_')'课程名列表'FROM student s,sc,course cs
WHERE s.sno=sc.sno AND sc.cno=cs.cno
GROUPBY s.sno
ORDERBY s.sno;

题解

从学生表(student)、选课表(sc)和课程表(course)中查询学号(sno)、姓名(sname)、课程数量(COUNT( *))和课程名列表(GROUP_CONCAT())这些字段；
使用WHERE子句将学生表、选课表和课程表连接起来，连接条件为学号(sno)和课程号(cno)相等；
使用GROUP BY子句对每个学生进行分组，统计每个学生所选课程的数量和课程名列表；
使用GROUP_CONCAT函数将每个学生所选课程的课程名按照字母表顺序排序并用下划线连接起来，形成课程名列表；
使用ORDER BY子句按照学号(sno)升序排序。

25、查询每个男生的选课门数（要求用嵌套查询实现）

题目

#查询每个男生的选课门数（要求用嵌套查询实现）要求输出学号、选课门数，并按序号升序排序。

[注意：SQL表名请用小写]

在这里插入图片描述

代码

#查询每个男生的选课门数（要求用嵌套查询实现）要求输出学号、选课门数，并按学号升序排序。select sc.sno,count(*)from sc
where sno in(select sno from student where ssex='男')groupby sno;

26、SQL查询：查询没有同时选修“计算机导论”和“计算机网络”两门课的学生的学号，姓名

题目

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

select sno,sname from student where sno notin(select sno from sc,course where sc.cno=course.cno and cname='计算机导论'and sno in(select sno from sc,course where sc.cno=course.cno and cname='计算机网络'));

27、查询选修了学号为9521102同学选修的全部课程的学生学号和姓名

题目一

有课程表，学生表，成绩表如下，查询选修了学号为9521102同学选修的全部课程的学生学号和姓名。

代码

# 查询选修了学号为9521102同学选修的全部课程的学生学号和姓名SELECTDISTINCT s.sno, s.sname
FROM student s
WHERENOTEXISTS(SELECT*FROM sc b
  WHERE b.sno ='9521102'ANDNOTEXISTS(SELECT*FROM sc c
    WHERE s.sno = c.sno AND c.cno = b.cno
  ));

题解

首先，我们使用SELECT DISTINCT关键字指定要查询的结果集中的每个记录都是唯一的，即去除重复记录。
接下来，我们使用FROM子句指定要查询的表是学生表(student)，使用别名s来代替学生表(student)。
然后，我们使用WHERE子句对查询结果进行筛选。在这个查询中，我们使用了NOT EXISTS子查询，表示查找不存在满足特定条件的记录。
在NOT EXISTS子查询中，我们使用了一个子子查询，其中先查询出学号为9521102的学生选修的全部课程，然后再查询出除该学生外，选修了这些全部课程的学生。如果存在这样的学生，则该学生不符合条件，被排除在查询结果之外。
最后，我们使用SELECT子句指定要查询的结果集包括每个学生的学号(sno)和姓名(sname)。这些字段分别来自于学生表(student)。

28、查询95级学生(学号前两位)的选课情况，输出学号、姓名、课程号、成绩（包括未选课的学生信息）

题目

查询95级学生(学号前两位)的选课情况，输出学号、姓名、课程号、成绩（包括未选课的学生信息）。

有课程表，学生表，成绩表如下，请完成查询。

代码

# 查询95级学生(学号前两位)的选课情况，输出学号、姓名、课程号、成绩（包括未选课的学生信息）。select s.sno,s.sname,sc.cno,sc.grade 
from student s
leftjoin sc on sc.sno = s.sno
where s.sno like'95%';

29、查询选课门数最多的学生的学号和姓名

题目

有课程表，学生表，成绩表如下，查询选课门数最多的学生的学号和姓名，结果可能不只一行。

代码

#查询选课门数最多的学生的学号和姓名select s.sno,s.sname from student s,sc 
where s.sno=sc.sno 
groupby s.sno
havingcount(*)=(selectcount(*)from sc 
                   groupby sno 
                   orderbycount(*)desclimit1);

题解

首先，使用SELECT关键字指定要查询的结果集包含学生的学号(sno)和姓名(sname)字段。
接着，使用FROM子句指定要查询的表包括学生表(student)和选课表(sc)。
然后，使用WHERE子句指定学生表(student)和选课表(sc)之间的连接条件为学号(sno)相等。
接下来，使用GROUP BY子句按照学生的学号(sno)进行分组，统计每个学生选修的课程数量。
然后，使用HAVING子句过滤掉选修课程数量不是最多的学生记录。在这个查询中，我们使用COUNT( *)函数统计每个学生选修的课程数量，然后将这个数量与所有学生选修课程数量的最大值进行比较，如果相等，则表示该学生选修课程数量最多，满足条件。
最后，使用子查询获取所有学生的选修课程数量，按照数量从大到小排序，并只返回数量最大的那个值，作为判断条件。

30、查询每个学生成绩最高的成绩信息

题目

有课程表，学生表，成绩表如下，请完成查询，输出成绩信息学号、课程号及成绩，最高分可能有多门成绩。

代码

# 输出成绩信息学号、课程号及成绩，最高分可能有多门成绩。select*from sc student 
where grade=(selectmax(grade)from sc where sc.sno=student.sno);

31、SQL查询：查询同时选修了c01,c02,c03课程学生，输出学号，姓名

题目

#查询同时选修了c01,c02,c03课程学生，输出学号，姓名

在这里插入图片描述

代码

#查询同时选修了c01,c02,c03课程学生，输出学号，姓名select sno,sname from student 
wherenotexists(select*from course where cno in('c01','c02','c03')#筛选出选修了这几门课程中任意一门或多门的学生记录andnotexists(select*from sc where sno=student.sno and cno=course.cno))

题解

首先，使用SELECT关键字指定要查询的结果集包含学生的学号(sno)和姓名(sname)字段。
接着，使用FROM子句指定要查询的表为学生表(student)。
然后，使用WHERE子句对查询结果进行筛选。在这个查询中，我们使用了NOT EXISTS子查询，表示查找不存在满足特定条件的记录。
在NOT EXISTS子查询中，我们使用了一个子子查询，其中先查询出所有指定课程（c01、c02和c03），然后再查询出选修了这些课程的学生。如果存在这样的学生，则该学生符合条件，被包含在查询结果之中。
在子子查询中，我们使用了另一个子查询，查询选修了指定课程的学生和课程的关系表(sc)。如果存在这样的关系记录，则表示该学生选修了指定的课程。
最后，使用sno = student.sno和cno = course.cno这两个条件将学生表(student)和课程表(course)之间的关系进行连接。

32、查询每门课程被选修的情况，输出课程号，课程名，被选修次数

题目

查询每门课程被选修的情况，输出课程号，课程名，被选修次数，结果按选修次数降序课程号降序排列。

其中课程表course结构为

在这里插入图片描述

选修表sc结构为
在这里插入图片描述

代码

#查询每门课程被选修的情况，输出课程号，课程名，被选修次数，结果按选修次数降序课程号降序排列。select c.cno, c.cname,count(*)from course c ,sc
where c.cno = sc.cno
groupby c.cno
ORDERBYcount(*)DESC, c.cno DESC;

33、查询每门课程被选修的情况(包括从未被选修过的课程)

题目

查询每门课程被选修的情况(包括从未被选修过的课程)，输出课程号，课程名，被选修次数，结果按选修次数升序课程号升序排列。

其中课程表course结构为
在这里插入图片描述

选修表sc结构为

在这里插入图片描述

代码

# 查询每门课程被选修的情况(包括从未被选修过的课程)select course.cno,cname,count(sno)from course 
leftjoin sc on  course.cno=sc.cno 
groupby course.cno
orderbycount(sno)asc,course.cno asc

34、查询选修了c03课程的学生学号和姓名

题目

有课程表，学生表，成绩表如下，查询选修了c03课程的学生学号和姓名。

代码

# 查询选修了c03课程的学生学号和姓名select s.sno,s.sname from student s,sc
where sc.sno  = s.sno 
and cno ='c03';

35、查询实验题-查询考试不及格的学生的学号（包括没有成绩的）

题目

查询考试不及格的学生的学号（包括没有成绩的）。选课表(sc)表结构如下：
在这里插入图片描述

代码

# 查询考试不及格的学生的学号（包括没有成绩的）selectdistinct sno from sc
where grade <60or grade isnull;# 别忘了学号的去重

标签： sql 数据库 mysql

本文转载自: https://blog.csdn.net/m0_60496161/article/details/130183503
版权归原作者 不会喷火的小火龙 所有，如有侵权，请联系我们删除。

SQL综合查询下