50题出自知乎专栏：

图解SQL面试题：经典50题

SQL面试必会50题

-- 1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
select a.s_id from (select s_id, c_id, s_score as score01 from score where c_id = "01") a
INNER join (select s_id, c_id, s_score as score02 from score where c_id = "02") b 
on a.s_id = b.s_id
where score01 > score02;

-- 2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）
select s_id, avg(s_score) as avgs from score
group by s_id
having avgs > 60;

-- 3、查询所有学生的学号、姓名、选课数、总成绩（不重要）
select s_id, count(c_id) as 选课数, sum(s_score) as 总成绩 from score GROUP BY s_id;

select s1.s_id as 学号, s_name as 姓名, count(c_id) as 选课数, sum(s_score) as 总成绩 from student s1
inner join score s2 
on s1.s_id = s2.s_id
group by s2.s_id;

-- 4、查询姓“猴”的老师的个数（不重要）
SELECT count(t_id) from teacher where t_name like "猴%";

-- 5、查询没学过“张三”老师课的学生的学号、姓名（重点）
-- 学过张三课程的学生id
select s1.s_id from score s1
INNER join course c on s1.c_id = c.c_id
INNER join teacher t on t.t_id = c.t_id
where t_name = "张三";

select * from student where s_id not in (
	select s1.s_id from score s1
	INNER join course c on s1.c_id = c.c_id
	INNER join teacher t on t.t_id = c.t_id
	where t_name = "张三"
);

-- 6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
select * from student where s_id in (
	select s1.s_id from score s1
	INNER join course c on s1.c_id = c.c_id
	INNER join teacher t on t.t_id = c.t_id
	where t_name = "张三"
);

-- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
select s1.s_id, s_name from score s1
INNER join score s2 on s1.s_id = s2.s_id
INNER join student s on s1.s_id = s.s_id
where s1.c_id = "01" and s2.c_id = "02";

-- 8、查询课程编号为“02”的总成绩（不重点）
select sum(s_score) from score where c_id = "02";

-- 9、查询所有课程成绩小于60分的学生的学号、姓名
-- 考虑 所有课程成绩中的最大值也小于60分
select score.s_id, s_name from score 
INNER join student on score.s_id = student.s_id
GROUP BY s_id having max(s_score) < 60;

-- 10.查询没有学全所有课的学生的学号、姓名(重点)
-- 考虑 课程数小于课程总数的s_id
select s1.s_id, s_name from score s1
INNER join student s2
on s1.s_id = s2.s_id
group by s_id 
HAVING count(c_id) < (select count(c_id) from course); 

-- 11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）
select DISTINCT s1.s_id, s_name from student s1 
INNER join score s2 on s1.s_id = s2.s_id
where c_id in 
(select c_id from score where s_id = "01")
and s1.s_id != "01";

-- 12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)
-- 需要确保该同学没有学过“01”号同学没学的课程，还需保证课程数相等
select s_id from score 
where s_id != "01" 
and s_id not in (
	select s_id from score where c_id not in (
		select c_id from score where s_id = "01"
    )
)
group by s_id
having count(*) = (
	select count(*) from score where s_id = "01"
);


-- 13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点，能做出来）
select s_id, s_name from student where s_id not in (
	select s.s_id from course c
	INNER join score s on c.c_id = s.c_id
	INNER join teacher t on c.t_id = t.t_id 
	where t.t_name = "张三"
);


-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）
select s1.s_id, s2.s_name, avg(s_score) as 平均成绩 from score s1 
join student s2 on s1.s_id = s2.s_id
where s_score < 60 
GROUP BY s_id
having count(s_score) >= 2;

-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）
select st.* from student st 
INNER join score sc on st.s_id = sc.s_id
where c_id = "01" and s_score < 60
ORDER BY s_score DESC;

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)
-- 有参考
select s_id "学号",
max(case when c_id = '01' then s_score else null end) as '语文',
max(case when c_id = '02' then s_score else null end) as '数学',
max(case when c_id = '03' then s_score else null end) as '英语',
avg(s_score) as 平均成绩 from score GROUP BY s_id
order by 平均成绩 desc;

-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：
-- 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)

select sc.c_id 课程ID, c_name 课程名称, max(s_score) 最高分, min(s_score) 最低分, avg(s_score) 平均分,
avg(case when s_score >= 60 then 1.0 else 0.0 end) as 及格率, 
avg(case when s_score >= 70 and s_score < 80 then 1.0 else 0.0 end) as 中等率,
avg(case when s_score >= 80 and s_score < 90 then 1.0 else 0.0 end) as 优良率,
avg(case when s_score >= 90 then 1.0 else 0.0 end) as 优秀率
from score sc 
inner join course c on sc.c_id = c.c_id
group by sc.c_id;

-- 19、按各科成绩进行排序，并显示排名(重点row_number)
select *, ROW_NUMBER() over (ORDER BY s_score DESC) as 排名
from score;

-- 20、查询学生的总成绩并进行排名（不重点）
select s_id, sum(s_score) 总成绩
from score
GROUP BY s_id
ORDER BY 总成绩 DESC;

-- 21 、查询不同老师所教不同课程平均分从高到低显示(不重点)
select c.t_id, t.t_name, avg(s_score) 平均分
from course c 
INNER join score s on c.c_id = s.c_id
INNER join teacher t on c.t_id = t.t_id
GROUP BY s.c_id
ORDER BY 平均分 DESC;

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）
select st.*, sc.s_score, ROW_NUMBER() over (PARTITION BY c_id ORDER BY s_score desc) as ranking
from score sc INNER join student st on sc.s_id = st.s_id;
-- 窗口函数限制不能直接使用ranking列？
select * from (
	select st.*, sc.s_score, ROW_NUMBER() over (PARTITION BY c_id ORDER BY s_score desc) as ranking
	from score sc INNER join student st on sc.s_id = st.s_id
) r
where ranking between 2 and 3;

-- 23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计:各分数段人数、课程ID和课程名称(重点和18题类似)
select s.c_id, c.c_name, 
sum(case when s_score <= 100 and s_score >= 85 then 1 else 0 end) "100-85",
sum(case when s_score < 85 and s_score >= 70 then 1 else 0 end) "85-70",
sum(case when s_score < 70 and s_score >= 60 then 1 else 0 end) "70-60",
sum(case when s_score < 60 then 1 else 0 end) "<60"
from score s
INNER join course c on s.c_id = c.c_id
group by s.c_id;

-- 24、查询学生平均成绩及其名次（同19题，重点）
select s_id, avg(s_score) avgs, ROW_NUMBER() over (order by avg(s_score) desc) ranking
from score group by s_id;

-- 25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）
select c_id, st.s_name, s_score from
(select s_id, c_id, s_score, row_number() over (partition by c_id order by s_score desc) ranking
from score) s
INNER join student st on s.s_id = st.s_id
where ranking < 4; 

-- 26、查询每门课程被选修的学生数(不重点)
select c_id, count(c_id) from score
group by c_id;

-- 27、 查询出只有两门课程的全部学生的学号和姓名(不重点)
select st.s_id, st.s_name, count(c_id) from student st
INNER join score sc on st.s_id = sc.s_id
group by sc.s_id
having count(c_id) = 2;

-- 28、查询男生、女生人数(不重点)
select s_sex, count(s_sex) from student group by s_sex;

-- 29 查询名字中含有"风"字的学生信息（不重点）
select * from student 
where s_name like "%风%";

-- 31、查询1990年出生的学生名单（重点year）
select * from student 
where year(s_birth) = 1990;

-- 32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）
select st.s_id, st.s_name, avg(s_score) 平均成绩 from student st 
INNER join score sc on st.s_id = sc.s_id
GROUP BY st.s_id
having 平均成绩 >= 85;

-- 33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）
select c_id, avg(s_score) avgs from score
group by c_id
order by avgs asc, c_id desc;

-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）
select s_name, s_score from score sc 
inner join student st on sc.s_id = st.s_id 
inner join course c on sc.c_id = c.c_id 
where c.c_name = "数学" and s_score < 60;

-- 35、查询所有学生的课程及分数情况（重点）
select s_id, 
max(case when c_name = '数学' then s_score else null end) 数学, 
max(case when c_name = '语文' then s_score else null end) 语文, 
max(case when c_name = '英语' then s_score else null end) 英语 
from score s
INNER join course c on s.c_id = c.c_id
group by s_id;

-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）
select st.s_id, s_name, c_name, s_score from student st 
INNER join score sc on st.s_id = sc.s_id
inner join course c on sc.c_id = c.c_id
where s_score > 70;

-- 37、查询不及格的课程并按课程号从大到小排列(不重点)
select st.s_id, s_name, c_name, sc.c_id, s_score from score sc 
inner join student st on sc.s_id = st.s_id
inner join course c on sc.c_id = c.c_id
where s_score < 60 
order by sc.c_id desc;

-- 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）
select sc.s_id, st.s_name
from score sc 
inner join student st on sc.s_id = st.s_id
where sc.c_id = "03" and s_score > 80;

-- 39、求每门课程的学生人数（不重要）
select c_id, count(s_id) from score
group by c_id;

-- 40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）
select s_name, s_score from student st 
inner join score sc on st.s_id = sc.s_id
inner join course c on sc.c_id = c.c_id
inner join teacher t on c.t_id = t.t_id
where t.t_name = "张三"
order by s_score desc
limit 1;

-- 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
-- 题意有点模糊，有参考
select distinct s1.s_id, s1.s_score, s1.c_id
from score s1
inner join score s2 
on s1.s_id = s2.s_id
where s1.s_score = s2.s_score and s1.c_id != s2.c_id;


-- 42、查询每门功课成绩最好的前两名（同22和25题）
select * from (
select s_id, c_id, s_score, ROW_NUMBER() over (PARTITION BY c_id order by s_score desc) ranking
from score) a
where ranking < 3;

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）
select c_id, count(s_id) counts from score
group by c_id
having counts > 5
order by counts desc, c_id asc;

-- 44、检索至少选修两门课程的学生学号（不重要）
select s_id, count(c_id) from score 
group by s_id
having count(c_id) >= 2;

-- 45、 查询选修了全部课程的学生信息（重点划红线地方）
select st.* from student st 
inner join score sc on st.s_id = sc.s_id
group by s_id
having count(c_id) = (select count(*) from course);

-- 46、查询各学生的年龄（精确到月份）
/* 时间戳 select from_unixtime(1645707893);
select unix_timestamp(now());*/
select s_name, timestampdiff(month, s_birth, now()) / 12 年龄
from student;


/*
47、48 这两个周数问题很多，week(day)获得的是day在该年度的第几周，不能与其他年份的第几周比较；
参考 select week("2020-01-05"), week("2018-01-05"); 结果一个为第一周，一个为第0周；
那么判断生日所在周数需要将年份替换为当前日期的年份，再判断该日期在now()对应年份的周数； 
关于datediff差值小于7的判断方法，完全不能保证两个周数相同，比如周日和周一；
还需要考虑一年中最后一周的情况，这一系列都过于复杂，就不写了。
*/
-- 47、查询本周过生日的学生

-- 48、查询下周过生日的学生

-- 49、查询本月过生日的学生
select s_name, s_birth from student 
where month(s_birth)  = month(now());

-- 50、查询下月过生日的学生
select s_name, s_birth from student 
where month(s_birth)  = (month("2020-12-01") + 1) % 12;