SQL综合查询上

目录


在这里插入图片描述

1、查询输出“高等数学”课程成绩前三名(不考虑成绩有重复值的情况)的学生的学号,姓名,课程名,系名,成绩。

题目

在这里插入图片描述

代码

#查询输出“高等数学”课程成绩前三名(不考虑成绩有重复值的情况)的学生的学号,姓名,课程名,系名(sdept),成绩。
select s.sno,s.sname,s.sdept,sc.grade,c.cname from student s 
join sc on s.sno=sc.sno join course c on sc.cno=c.cno
where c.cname="高等数学"
order by sc.grade desc
limit 3;


# 或者:两种都是多表连接的表示方式
select s.sno,s.sname,s.sdept,sc.grade,c.cname from student s,course c,sc 
where s.sno=sc.sno and sc.cno=c.cno
and c.cname="高等数学"
order by sc.grade desc
limit 3;


2、统计各门课程的重修人数(包括grade为NULL),要求输出课程代号,课程名及重修人数。

题目

在这里插入图片描述

代码

#统计各门课程的重修人数(包括grade为NULL),要求输出课程代号,课程名及重修人数。
select c.cno,c.cname,count(*) from course c,sc
where c.cno = sc.cno and (grade<60 or grade is null)
group by c.cno;

题解

讲一下为什么需要group by操作

在这个SQL查询中,group by语句的作用是将结果按照指定的列进行分组,然后对每个分组进行聚合计算。在这个问题中,我们需要统计每门课程的重修人数,这就需要将选修了同一门课程的学生分成一组,然后计算这个组内选修该课程的人数。

在SQL中,group by语句用于指定分组的列,count(*)函数用于统计每个分组的行数,也就是该门课程的重修人数。因此,在这个查询中,group by语句的作用就是将选修了同一门课程的学生分组,然后对每个分组计算该门课程的重修人数。

如果不使用group by语句,那么查询的结果将会是所有选修了该门课程的学生的总人数,而不是每门课程的重修人数。因此,group by语句是必须的,它可以将结果按照课程分组,然后对每个分组计算该门课程的重修人数,从而得到正确的结果。

3、查询输出平均成绩在2-5名的学生,输出学号、姓名和平均成绩(取整),平均成绩降序。【不用考虑空值】

题目

学生数据库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

代码

#查询输出平均成绩在2-5名的学生,输出学号、姓名和平均成绩(取整),平均成绩降序。【不用考虑空值】
select s.sno,s.sname,round(avg(sc.grade)) from student s,sc
where sc.sno = s.sno
group by sc.sno
order by round(avg(grade)) DESC
limit 1,4;

题解

本题讲解一下limit 的用法:

限制返回的行数。可以有两个参数,第一个参数为起始行,从 0 开始;第二个参数为返回的总行数。

因此输出2-5名的写法也显而易见了。

4、查询超过该课程平均分的成绩信息

题目

有课程表,学生表,成绩表如下,查询超过该课程平均分的成绩信息,输出学号,课程号及成绩。

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 a.sno,a.cno,a.grade from sc a 
where a.grade>(select avg(sc.grade) from sc where sc.cno=a.cno);

题解

  1. 首先,从 sc 表中选取所有记录,并给它们起了一个别名 a
  2. where 子句中,我们使用了一个子查询:select avg(sc.grade) from sc where sc.cno=a.cno。这个子查询的作用是计算每个课程的平均分数,其中 a.cno 表示当前记录所属的课程号。因此,子查询的结果就是当前记录所属课程的平均分数。
  3. 然后,我们使用 a.grade 来筛选出所有分数高于该门课程平均分的学生成绩信息,即 a.grade > (select avg(sc.grade) from sc where sc.cno=a.cno)
  4. 最后,我们选择输出学生学号、课程号和成绩信息,即 select a.sno, a.cno, a.grade from sc a

5、查询选修平均分为60分(包括60分)以上的学生的各门课成绩,要求输出学号,姓名,课程名和成绩。

题目

代码

#查询选修平均分为60分(包括60分)以上的学生的各门课成绩,要求输出学号,姓名,课程名和成绩,并按学号升序排序。
select s.sno,s.sname,c.cname,a.grade 
from student s, course c, sc a
where s.sno=a.sno 
and c.cno=a.cno 
and s.sno in (select sno from sc group by sno having avg(grade)>=60)
order by s.sno;

题解

子查询的作用:s.sno in (select sno from sc group by sno having avg(grade)>=60)

具体来说,这个子查询分为三个部分:

  1. SELECT sno FROM sc:从 sc 表中选择学生学号(sno)。
  2. GROUP BY sno:按学号进行分组。
  3. HAVING AVG(grade) >= 60:筛选出平均成绩大于等于60分的学生。在这里,AVG(grade) 计算分组后的平均成绩,HAVING 子句用于筛选满足条件的分组,即平均成绩大于等于60分的学生。

然后,这个子查询会返回一个学生学号的列表,这个列表中包含平均成绩大于等于60分的学生。接着,主查询中使用 s.sno IN 将这个列表与 student 表连接起来,从而筛选出相应的学生信息。换句话说,主查询中只会返回在子查询返回的学生学号列表中的学生信息。

6、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 * from student s
where s.sname !='王大力' and  s.sdept = (select sdept from student where sname = '王大力');
# 本题需要注意先判断不是王大力本人再做子查询

7、查询每门课的先修课,输出课程号、课程名和先修课程名。

题目

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

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 a.cno,a.cname,b.cname
from course a,course b
where a.cpno=b.cno;

题解

  1. SELECT 子句中,我们选择输出三个字段:课程号 (cno)、课程名 (cname) 和先修课程名 (b.cname),因此使用了 SELECT a.cno, a.cname, b.cname
  2. FROM 子句中,我们选择了两次 course 表,分别表示当前课程和先修课程。
  3. WHERE 子句中,我们使用了条件 a.cpno = b.cno 来连接当前课程和先修课程。具体来说,a.cpno 表示当前课程的先修课程号,b.cno 表示先修课程的课程号。通过这个条件,我们可以找到每门课程的先修课程。

8、查询选修“高等数学”课程的成绩不为空的学生学号、姓名和成绩。

题目

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

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 s.sno,sname,grade
from student s,sc,course c
where s.sno = sc.sno and c.cno = sc.cno
and (grade is not null) and cname='高等数学';

9、查询学生有效成绩信息

题目

有课程表,学生表,成绩表如下,请完成查询,查询学生成绩单,要求输出有有效成绩的学号,姓名,课程名,成绩,按学号升序课程名降序排列。

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 student.sno,sname,cname,grade
from student,sc,course
where grade is not null and student.sno=sc.sno and course.cno=sc.cno
order by sno asc,cname desc;

10、查询学生成绩单,要求输出学号,姓名,平均分,选修门数,按平均分降序排序

题目

表结构如下:

在这里插入图片描述

代码

select s.sno,s.sname,avg(sc.grade),count(*) from student s,sc,course c
where c.cno = sc.cno and s.sno = sc.sno
group by s.sno
order by avg(sc.grade) desc;

11、查询选修通过2门(包括2门)以上的学生的信息,输出学号、选修通过门数、平均成绩,按门数降序排序,若门数相同,按照成绩降序。

题目

学生数据库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

代码

# 查询选修通过2门(包括2门)以上的学生的信息,输出学号、选修通过门数、平均成绩,按门数降序排序,若门数相同,按照成绩降序。
select sno,count(*),round(avg(grade)) from sc 
where grade>=60 
group by sno 
having count(*)>=2
order by 2 desc,3 desc;

12、SQL查询:统计各门课程的重修人数(包括grade为NULL),要求输出课程代号,重修人数。

题目

学生数据库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 cno,count(*) from sc where grade<=60 or grade is null
group by cno order by cno;

13、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

代码

#查询统计学生不及格(低于60分)门数大于等于2门的信息,输出系名,学号,姓名,不及格门数,按照系(升序)排序,不及格门数(降序)排序。
select s.sdept,s.sno,s.sname,count(*) from student s ,sc 
where s.sno=sc.sno
and sc.grade<60
group by s.sno
having count(*)>=2
order by s.sdept,count(*) desc;

14、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, s.sname
FROM student s
JOIN sc ON s.sno = sc.sno
JOIN course c ON sc.cno = c.cno
WHERE c.cname IN ('计算机网络', '数据库基础')
GROUP BY s.sno
ORDER BY s.sno ASC;

15、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, s.SNAME, s.SDEPT
FROM student s
WHERE NOT EXISTS (
  SELECT c.CNO
  FROM course c
  WHERE NOT EXISTS (
    SELECT *
    FROM sc
    WHERE sc.SNO = s.SNO AND sc.CNO = c.CNO
  )
)

题解

这个SQL查询语句可以分为两部分:外部查询和内部查询。

外部查询:

SELECT s.SNO, s.SNAME, s.SDEPT

FROM student s

WHERE NOT EXISTS (

  ...

)

在外部查询中,我们从学生表student中选择学号(SNO)、姓名(SNAME)和系名(SDEPT)。接下来,我们使用NOT EXISTS子句来筛选掉那些没有选修全部课程的学生。NOT EXISTS子句的作用是,对于学生表中的每一行,检查内部查询是否返回了任何匹配的课程。如果内部查询没有返回任何匹配的课程,那么NOT EXISTS子句返回TRUE,这意味着该学生选修了所有课程。反之,如果内部查询返回了任何匹配的课程,那么NOT EXISTS子句返回FALSE,这意味着该学生没有选修全部课程。

内部查询:

SELECT c.CNO

FROM course c

WHERE NOT EXISTS (

  SELECT *

  FROM sc

  WHERE sc.SNO = s.SNO AND sc.CNO = c.CNO

)

在内部查询中,我们从课程表course中选择课程号(CNO)。接下来,我们使用NOT EXISTS子句来查找没有选修过这门课程的学生。NOT EXISTS子句的作用是,对于课程表中的每一行,检查子查询是否返回了任何匹配的选课记录。如果子查询没有返回任何匹配的选课记录,那么NOT EXISTS子句返回TRUE,这意味着没有学生选修这门课程。反之,如果子查询返回了任何匹配的选课记录,那么NOT EXISTS子句返回FALSE,这意味着有学生选修了这门课程。

16、查询实验题-查询不姓张的学生的基本信息

题目

查询不姓张的学生的基本信息。学生(student)表结构如下:

在这里插入图片描述

代码

select * from student where sname not like '张%'

17、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 sc.sno,cno, grade,avggrade 
from sc,(select sno,round(avg(grade)) as avggrade from sc group by sno) as avg_sc
where sc.sno = avg_sc.sno and sc.grade>avg_sc.avggrade
order by sc.sno;

题解

具体来说,这个查询语句中的派生表部分如下:

(SELECT sno, ROUND(AVG(grade)) AS avggrade FROM sc GROUP BY sno) AS avg_sc

在这个派生表中,我们使用GROUP BY子句按照学生号(sno)分组,并计算每个学生的平均成绩(AVG(grade)),并使用ROUND函数将平均成绩四舍五入为整数。最终,我们将学生号(sno)和平均成绩(avggrade)作为结果集返回,并将其命名为avg_sc。然后,在外部查询中,我们使用WHERE子句将选课表sc和派生表avg_sc联接起来,并筛选出成绩高于平均成绩的选课记录。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

不会喷火的小火龙

你的鼓励是我最大的创作动力!

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值