自学SQL(4) SQL典型题目做题笔记

SQL面试经典50题

题目及数据来源: https://zhuanlan.zhihu.com/p/38354000

以下记录个人认为有所难度的题目和解答

1. 查询两门以上不及格课程的同学的学号及其平均成绩

原回答有误：

select 学号, avg(成绩) as 平均成绩
from score
where 成绩 <60
group by 学号
having count(课程号)>2;

按此语句查询出的仅是符合条件的学生不及格课程的平均成绩，而不是符合条件的学生所有课程的平均成绩。正确写法如下：

select 学号,avg(成绩) 平均成绩
from score 
where 学号 in 
(select 学号 from score where 成绩<60 group by 学号 having count(*) > 2)
group by 学号;

2. 查询所有课程成绩小于60分的学生学号、姓名

原答案有误：

select 学号,姓名
from student
where  学号 in (
select 学号 
from student  #应为score表
where 成绩 < 60);

按此语句查询出的是至少一门课程成绩小于60分的学生学号、姓名，而非所有课程成绩小于60分的学生学号、姓名。以下为正确写法：

select s.学号,姓名
from student s
join score sc
on s.学号=sc.学号
group by s.学号
having max(成绩)<60;

此外，子查询的效率一般低于关联查询，所以尽量用表联接来代替不必要的子查询。

3.查询所有生日在1990年的学生学号、姓名

原答案：

select 学号,姓名
from student
where year(出生日期)=1990;

用Year函数后会对每行数据都进行运算，在数据量大时运算效率会变得很低。所以实际工作中直接设定起止时间点更简单高效。

select 学号,姓名
from student
where 出生日期 between '1990-01-01' and '1990-12-31';

4.查询出每门课程的及格人数和不及格人数

要求输出如下：

select 课程号,
sum(case when 成绩>=60 then 1 
	 else 0 
    end) as 及格人数,
sum(case when 成绩 <  60 then 1 
	 else 0 
    end) as 不及格人数
from score
group by 课程号;

理解过程：
首先，score表的每一行都将执行一遍case函数，得到相应的逻辑判断结果。也即，如果按照以下语句，将会得到如下输出：

select 课程号,
case when 成绩>=60 then 1 else 0 end as 及格人数,
case when 成绩 <  60 then 1 else 0 end as 不及格人数
from score;

然后按照课程号来聚合统计每门课的及格人数与不及格人数。综合就得到了上面的解法

5. 查询各个课程的分数段分布

要求输出如下：

select c.课程号,课程名称,
sum(case when 成绩<=100 and 成绩>=85 then 1 else 0 end) as '[100-85]',
sum(case when 成绩<85 and 成绩>=70 then 1 else 0 end) as '[85-70]',
sum(case when 成绩<70 and 成绩>=60 then 1 else 0 end) as '[70-60]',
sum(case when 成绩<60 then 1 else 0 end) as '[<60]'
from score sc
right join course c
on sc.课程号=c.课程号
group by c.课程号

6. 行列互换

将score表行列互换，转换成以下形式：

select 学号,
max(case 课程号 when '0001' then 成绩 else 0 end) as '课程号0001',
max(case 课程号 when '0002' then 成绩 else 0 end) as '课程号0002',
max(case 课程号 when '0003' then 成绩 else 0 end) as '课程号0003'
from score
group by 学号;

解题思路：
首先，score表的每一行都将执行一遍case函数，得到相应的逻辑判断结果。也即，如果按照以下语句，将会得到如下输出：

select 学号,
(case 课程号 when '0001' then 成绩 else 0 end) as '课程号0001',
(case 课程号 when '0002' then 成绩 else 0 end) as  '课程号0002',
(case 课程号 when '0003' then 成绩 else 0 end) as '课程号0003'
from score;

然后按照如上图所示，按小方块进行分组与max聚合，就将得到最终结果。

7. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select distinct s1.学号,s1.课程号,s1.成绩
from score s1
join score s2
on s1.学号=s2.学号
where s1.课程号!=s2.课程号 and s1.成绩=s2.

正确输出结果如下：

如果不加distinct，那么由于每个课程都与另外两个课程相等，结果就会出现如下重复：

8.查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号

原答案：

select a.学号  
​from 
(select 学号 ,成绩 from score where 课程号=01) as a
inner join 
(select 学号 ,成绩 from score where 课程号=02) as b
on a.学号 =b.学号 
inner join student c on c.学号 =a.学号  #没有必要连接student表
where a.成绩 >b.成绩 ;

也可以通过自连接来实现：

select s1.学号
from score s1
join score s2
on s1.学号=s2.学号
where s1.课程号='0001' and s2.课程号='0002' and s1.成绩>s2.成绩

9. 查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名

如同上题，同样有两种写法：

select s1.学号,姓名
from 
(select 学号 from score where 课程号='0001') s1
join
(select 学号 from score where 课程号='0002') s2
on s1.学号=s2.学号
join student s
on s2.学号=s.学号

select s1.学号,姓名
from score s1
join score s2
on s1.学号=s2.学号
join student s
on s.学号=s2.学号
where s1.课程号='0001' and s2.课程号='0002';
/* 
where条件也可以写为: s1.课程号='0002' and s1.课程号='0002',
但不能写为: (s1.课程号='0001' and s2.课程号='0002) or (s1.课程号='0002' and s1.课程号='0002'),这将会导致重复
*/

10. 查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名

原答案有误：

select s.学号 ,s.姓名
from student as s  
inner join score as a  
​​on s.学号 =a.学号 
inner join  course  b on a.课程号 =b.课程号
inner join  teacher c  on b.教师号 = c.教师号
where c.教师姓名 ='孟扎扎';

按此语句，所有只要上过孟老师一节课的学生，其信息就会被查询出来，不符合题目中要求的，查询上过孟老师所有课的学生的信息。正确的写法应该是，首先查出学过孟老师课程的同学学号(包含只学一门)，同时统计每个同学符合这个条件的有几条记录，也即其上过孟老师的课的数量，然后将这个数量与孟老师的代课数量进行对比，筛选出最后的答案。

#创建孟扎扎所代课程的课程号视图 mc
create view mc as 
(select 课程号
from course c 
join teacher t 
on c.教师号=t.教师号
where 教师姓名='孟扎扎');

#查询选过mc中所有课程的学生学号、姓名
select a.学号,姓名
from 
(select 学号,count(*) 课程数量
from score
where 课程号 in (select 课程号 from mc)
group by 学号
) a
join student s 
on a.学号=s.学号
where a.课程数量=
(select count(*) from mc)

11. 查询没学过“孟扎扎”老师课的学生的学号、姓名

此题先查询出上过孟老师课的学生，再在所有学生中将这部分学生排除掉即可

select 学号,姓名
from student 
where 学号 not in
(
select 学号 from score sc
join course c
on sc.课程号=c.课程号
join teacher t 
on c.教师号=t.教师号
where 教师姓名='孟扎扎'
);

12. 按学号列出每个同学的各科成绩与平均成绩，并按平均成绩从高到低显示

要求输出如下：

select 学号,
max(case 课程名称 when '语文' then 成绩 else 0 end) 语文,
max(case 课程名称 when '数学' then 成绩 else 0 end) 数学,
max(case 课程名称 when '英语' then 成绩 else 0 end) 英语,
avg(成绩) 平均成绩
from score sc 
join course c 
on sc.课程号=c.课程号
group by 学号

13.查询每门课程成绩排名前两位的学生姓名

要求按如下输出：

select 课程号,姓名,成绩,排名
from 
(select 课程号,学号,成绩,
row_number() over (partition by 课程号 order by 成绩 desc) 排名
from score) rk
join student s 
on rk.学号=s.学号
where 排名<3
order by 课程号;

注意窗口函数一般只用于select后，所以以下写法是不支持的：

select 课程号,姓名,成绩,
row_number() over (partition by 课程号 order by 成绩 desc) 排名
from score sc
join student s
on sc.学号=s.学号
where 排名<3

窗口函数的使用参考: 通俗易懂的学会：SQL窗口函数

本文地址：https://blog.csdn.net/Zita_11/article/details/107268016