SQL面试五十题 + 基本知识

Day 1

1.查询课程编号为"01"的课程比“02”的课程成绩高的所有学生学号（重点）

select a.s_id, c.s_name, a.s_score, b.s_score from (
select * from Score where c_id ='01'
) as a
inner join (
select * from Score where c_id ='02') as b on a.s_id = b.s_id
inner join Student as c on c.s_id = a.s_id
where a.s_score > b.s_score

思路：分别找出01，02课程学生的成绩形成两个表，通过inner join连接，01的分数大于02的分数作为筛选
顺便再inner join拿到学生姓名

2.查询平均成绩大于60的学生学号和学生成绩

select s_id, AVG(s_score)
from Score
GROUP BY s_id 
HAVING AVG(s_score) >60

3.查询所有学生的学号、姓名、选课数、总成绩

SELECT st.s_id, st.s_name, (select DISTINCT count(c_id)), 
SUM(case when s_score is NULL THEN 0 else s_score END)from Student st
left JOIN Score sc on st.s_id = sc.s_id
GROUP BY s_id,s_name

4.查询姓“猴”的老师的个数

select COUNT(t_id) from Teacher
where t_name like '猴%'

5.查询没学过“张三”老师课的学生的学号、姓名

select s_name from Student where s_id not in (
select s_id from Score where c_id=(
select c_id from Course where t_id=
(select t_id from Teacher
where t_name = '张三'))
)

Day 2

6.查询学过“张三”老师所教的所有课的同学的学号、姓名

select * from Student WHERE
s_id in (
select s_id from score 
where c_id =(select c_id from course where t_id = 
(select t_id from teacher where t_name ='张三')));

7.查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

select s_id, s_name from Student WHERE
s_id in (
select DISTINCT s1.s_id from score s1
inner join score s2 on s1.s_id = s2.s_id
where (s1.c_id = '01' and s2.c_id='02') or (s1.c_id ='02' and s2.c_id='01'))

8.查询课程编号为“02”的总成绩

select sum(s_score) from Score sc 
left join Course co on sc.c_id =co.c_id
where sc.c_id = '02'
或
select DISTINCT sum(s_score) over( PARTITION BY sc.c_id 
)
from Score sc left join Course co on sc.c_id =co.c_id
where sc.c_id = '02'
或
select sum(s_score) from Score where c_id = '02'

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

/** 查询所有课程成绩小于60分的学生的学号、姓名*/
select DISTINCT st.s_id,st.s_name from Student st
left join Score sc on st.s_id =sc.s_id
where 60 > all(select sc.s_score from Score)

10.查询没有学全所有课的学生的学号、姓名

select st.s_id, st.s_name 
from Student st
left JOIN Score sc on st.s_id = sc.s_id
GROUP BY st.s_id
having count(DISTINCT sc.c_id) < (select count(DISTINCT c_id) from Course)

Day 3

11.查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

select s_id, s_name from student 
where s_id in (
SELECT DISTINCT s_id from Score 
where c_id IN (
select c_id from Score where s_id = '01') AND s_id <> '01');
或
select distinct st.s_id, st.s_name from student st
inner join Score sc on st.s_id = sc.s_id
where sc.c_id in (select c_id from Score where s_id = '01') and st.s_id <> '01';

12.查询和“01”号同学所学课程完全相同的其他同学的学号 （不能直接按照数量一样，因为可能选择的课程是不同的）

select * from student
where s_id in (
select s_id from score
where s_id !='01'
GROUP BY s_id 
HAVING COUNT(DISTINCT c_id) =(select DISTINCT count(c_id) from Score where s_id = '01')
) 
-- 数量与01相等且s_id不等于01的
# 下面的AND连接的条件是： s_id不在 存在有与01课程不同的课程学生中(可能选课数目一样的）
and s_id not in (
select distinct s_id from score
where c_id not in (
select c_id from score
where s_id='01'
)
)

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

select DISTINCT st.s_id, st.s_name, avg(sc.s_score) over(PARTITION by st.s_id) from score sc
left join student st on sc.s_id =st.s_id
where st.s_id in 
(select sc.s_id from score
where sc.s_score < 60
GROUP BY sc.s_id
HAVING count(sc.s_score) >= 2)

16.检索"01"课程分数小于60，按分数降序排列的学生信息

select * from student a
left join Score b on a.s_id = b.s_id
where c_id = '01' and s_score < 60
ORDER BY s_score desc

17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select s_id, max(CASE WHEN c_id = '01' THEN s_score ELSE null END) '语文', 
max(CASE WHEN c_id = '02' THEN s_score ELSE null END) '数学', 
max(CASE WHEN c_id = '03' THEN s_score ELSE null END) '英语', 
avg(s_score) as AVGG
from score 
GROUP BY s_id
order by AVGG desc

select s_id, c_id, s_score, avg(s_score) over(PARTITION by s_id) as AVGG
from score
ORDER BY AVGG desc

这个每一条都会出来，但是不能做到上图效果

Day 4

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

select sc.c_id, co.c_name, max(sc.s_score), min(sc.s_score), avg(sc.s_score),
sum(case when sc.s_score >=60 then 1 else 0 end)/count(sc.s_id) '及格率', #还可以sum/count=avg(）
sum(case when sc.s_score <=80 and sc.s_score >=70 then 1 else 0 end)/count(sc.s_id) '中等率',
sum(case when sc.s_score <=90 then 1 else 0 end)/count(sc.s_id) '优良率',
sum(case when sc.s_score >=90 then 1 else 0 end)/count(sc.s_id) '优秀率'
from score sc
inner join Course co on sc.c_id = co.c_id
GROUP BY sc.c_id

19.按各科成绩进行排序，并显示排名

#没要求排序方式
select *, RANK() over(PARTITION by c_id order by s_score desc) from score

20.查询学生的总成绩并进行排名

select stemp.s_id,stemp.total, rank() over(ORDER BY stemp.total DESC ) from (
select s_id, sum(s_score) as total from Score
GROUP BY s_id ) stemp

21.查询不同老师所教不同课程平均分从高到低显示

SELECT sc.c_id , co.c_name, avg(sc.s_score) as AVGG, te.t_name from Score sc
inner join Course co on co.c_id = sc.c_id
inner join Teacher te on co.t_id = te.t_id
GROUP BY sc.c_id
ORDER BY AVGG DESC

22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select st.*, temp.c_id, temp.s_score from (
select *, ROW_NUMBER() over(PARTITION by c_id ORDER BY s_score desc) rn from Score) temp
inner join Student st on temp.s_id = st.s_id
where rn = 2 or rn = 3

Day 5

23.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)

select c.c_id, c.c_name,
sum(case when sc.s_score >=85 and sc.s_score <=100 then 1 else 0 end) "85-100",
sum(case when sc.s_score >=70 and sc.s_score <85 then 1 else 0 end) "70-85",
sum(case when sc.s_score >=60 and sc.s_score <70 then 1 else 0 end) "60-70",
sum(case when sc.s_score < 60 then 1 end) "<60"
from Score sc
INNER JOIN Course c on sc.c_id = c.c_id
GROUP BY c.c_id,c.c_name

#或者
count(case when sc.s_core >=60 and sc.sc_score <70 then 1 else NULL)

select s_id, avg(s_score) , rank() over(ORDER BY avg(s_score) desc) 
from Score
GROUP BY s_id

25.查询各科成绩前三名的记录（不考虑成绩并列情况）

select sc.s_id, sc.c_id, 
(case when sc.rn = 1 then sc.s_score else null end ) "No.1",
(case when sc.rn = 2 then sc.s_score else null end ) "No.2",
(case when sc.rn = 3 then sc.s_score else null end ) "No.3"
from (
SELECT s_id,c_id, s_score, ROW_NUMBER() over(PARTITION by c_id ORDER BY s_score desc) rn from Score) sc
where sc.rn in (1,2,3)

26.查询每门课程被选修的学生数

select c_id, count(DISTINCT s_id) from Score
GROUP BY c_id

27.查询出只有两门课程的全部学生的学号和姓名

select st.s_id, st.s_name from Score sc
left join Student st on sc.s_id = st.s_id
group by sc.s_id
having count(DISTINCT sc.c_id) = 2

Day 6

28.查询男生、女生人数

select s_sex, count(s_id) from Student
GROUP BY s_sex

29.查询名字中含有"风"字的学生信息

select * from Student
where s_name like "%风%"

31.查询1990年出生的学生名单

SELECT * FROM Student
where YEAR(s_birth) = '1990'

32.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select st.s_id, st.s_name, avg(DISTINCT sc.s_score) from student st
LEFT JOIN Score sc on st.s_id = sc.s_id
GROUP BY st.s_id
having AVG(DISTINCT s_score) >=85

33.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select sc.c_id, co.c_name,avg(sc.s_score) from Score sc 
INNER JOIN Course co on sc.c_id = co.c_id
 GROUP BY sc.c_id
ORDER BY avg(sc.s_score) asc,sc.c_id desc

34.查询课程名称为"数学"，且分数低于60的学生姓名和分数

select score.s_id, Student.s_name,score.s_score from score 
INNER JOIN Student on score.s_id = Student.s_id
where score.c_id =(
select c_id from Course where c_name = '数学') and s_score <60

35.查询所有学生的课程及分数情况

select a.s_id, b.s_name,
max(case when a.c_id='01' then a.s_score else 0 end) '语文',
max(case when a.c_id='02'  then a.s_score else 0 end) '数学',
max(case when a.c_id='03'  then a.s_score else 0 end) '英语',
max(case when a.c_id='04'  then a.s_score else 0 end) '化学'
from Score a
INNER JOIN Student b on a.s_id=b.s_id
GROUP BY a.s_id

这几道题都比较简单，刷了一会儿leetcode，作业比较多，所以最近刷sql和python的时间不是很多

Day 7

36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select a.s_id, b.s_name, c.c_name,a.s_score from 
Score a
INNER JOIN Student b on a.s_id =b.s_id
INNER JOIN Course c on a.c_id = c.c_id
where s_score >70

37.查询不及格的课程并按课程号从大到小排列

select st.s_id, st.s_name, sc.c_id,co.c_name, sc.s_score
FROM Score sc
INNER JOIN Student st on sc.s_id = st.s_id
INNER JOIN Course co on co.c_id = sc.c_id
where s_score < 60
ORDER BY sc.c_id Desc

38.查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

select st.s_id, st.s_name from Score sc
LEFT JOIN Student st on sc.s_id=st.s_id
where sc.c_id = '03' and sc.s_score >=80

39.求每门课程的学生人数

select DISTINCT c_id, count(s_id) over(PARTITION by c_id)
from Score

40.查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

select sc.s_id, st.s_name, sc.s_score from Score sc
RIGHT JOIN Student st on sc.s_id =st.s_id
where c_id in (
select c_id from Course where t_id in (
select t_id FROM Teacher
where t_name = "张三")) 
ORDER BY sc.s_score DESC
LIMIT 1 
-- LIMIT 1 OFFSET 0/ LIMIT 0,1

Day 8

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

select distinct a.s_id,a.c_id,a.s_score from Score a
left join Score b on a.s_id = b.s_id
where a.s_score=b.s_score and a.c_id != b.c_id;

42.查询每门功成绩最好的前两名

select s_id,c_id , s_score from 
(select *, ROW_NUMBER() over(PARTITION by c_id ORDER BY s_score desc) rn
from Score) Sc
where Sc.rn in (1,2)

43.统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c_id, count(s_id) from Score
GROUP BY c_id
HAVING count(s_id) > 5
ORDER BY count(s_id) desc, c_id asc

44.检索至少选修两门课程的学生学号

select s_id, count(DISTINCT c_id) from Score
GROUP BY s_id
HAVING count(DISTINCT c_id) >= 2

45.查询选修了全部课程的学生信息

select * FROM Student
where s_id in (
select s_id from Score 
GROUP BY s_id
having COUNT(DISTINCT c_id) = (select COUNT(distinct c_id) from Course) )

Day 9

46.查询各学生的年龄

SELECT *,(year(CURRENT_DATE)-YEAR(s_birth)) as age from Student

47.查询没学过“张三”老师讲授的任一门课程的学生姓名

select s_name from Student 
where  s_name not in (
select s_name FROM Student st
left JOIN Score sc on st.s_id = sc.s_id
where sc.c_id in (
select c_id from Course where t_id =(
select t_id from Teacher
where t_name ='张三')))

48.查询本周过生日的学生

-- 查询本月过生日
select * from Student
where MONTH(s_birth) = month(NOW())

-- 查询下周
换算成当前年
select * FROM student 
where week(CONCAT('2023-',SUBSTRING('1990-01-20',6,5)),1) = WEEK('2023-3-7',1)+1

49.查询本月过生日

select * from student
where MONTH(NOW())=MONTH(s_birth)

50.查询下个月过生日

select * from student
where 
case when month(current_date)=12 then month(s_birth) =1 
else MONTH(CURRENT_DATE)=MONTH(s_birth)+1 END

查漏补缺

1. Drop， delete， truncate的区别
   ANS：
   
   DDL【Data Definition Language】数据定义语言，用来维护存储数据的结构代表指令: create、drop、alter、truncate。
   DML【Data Manipulation Language】数据操纵语言，用来对数据进行操作代表指令：insert，delete，update，DML 中又单独分了一个 DQL，数据查询语言，代表指令是 select。
   DCL【Data Control Language】数据控制语言，主要负责权限管理和事务代表指令：grant，revoke，commit。

2. in 和 ‘=’的区别
   ANS：in针对多个数据，’ = '只能单个数据

3. rank(), dense_rank(), row_number()区别
   都是开窗函数，rank()存在并列情况，但是并列后不连续排名，比如有2个并列第一，那么前面两个分别为序号1，第三个为序号3
   而dense_rank()也存在并列，但是后续排名连续，比如有2个并列第一，那么前面两个分别为序号1，第三个为序号2
   row_number()不存在并列，按照1，2，3，…排列

percent_rank(): 0~1之间，可以等于0，可以获得高位、低位百分点
cume_dist():0～1之间，不会为0，它返回一组值中值的累积分布。它表示值小于或等于行的值除以总行数的行数。

4. sql中过滤条件放在on和where的区别
   inner join没区别：笛卡尔积再过滤
   left join/right join： where在join之后，on只针对右/左表生效（左表全部保留）， where对全部过滤

5. Limit 2,1和limit 2 offset 1的区别
   limit 2,1为从index=2开始（index=0开始）读取一条数据，则输出第三条信息
   limit 2 offset 1从第二条（index=1）数据开始取2条(limit 2)，limit后面跟2条数据，offset后面是从第一条开始读，则输出为第2，3条

limit 1,1 从1开始（第二条数据)读1条，输出第二条信息
limit 1 offset 1，从1开始（第二条数据）读1条，输出第二条信息

6. Datediff(datepart,被减数_start，减数_end)， 开始-结束一般为负值
   具体

7. Length(NULL) =NULL

8. if跟Excel一样

Update Salary
set sex = if(sex='m','f','m')

9. 首字母大写,之后小写

#先用left函数取值，跟Excel用法一样
# 然后upper,lower
# 最后concat
select user_id,
concat(upper(left(name,1)),lower(right(name,length(name)-1))) as name. #跟EXCEL一模一样
from Users
order by user_id

10. union all (leetcode 1795. Rearrange Products Table)
    

select product_id, 'store1' as store, store1 as price from Products where store1 is not null
union all
select product_id, 'store2' as store, store2 as price from Products where store2 is not null
union all
select product_id, 'store3' as store, store3 as price from Products where store3 is not null

11. sql中 where 与on的区别

on和where在内连接时没有区别，在left join,right join时对右表/左表进行条件过滤

12. 177.Nth Highest Salary (不太懂原理，内部查询是对的，但是外面少前套一个select,不知道那个select的意义，以及我原本是e.dr=N）

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN

  RETURN (
      # Write your MySQL query statement below.
      select (
          select distinct salary from (
              select salary,dense_rank() over(order by salary desc) dr from Employee
          ) e 
           where e.dr= N
      ) getNthHighestSalary
  );
END

13. 连续三个相同值查找——三表按照a.id = b.id-1, a.id = c.id-2连接，根据条件进行查找
14. 找节点

select id,
case when p_id is null then "Root"
when id in (select p_id from tree) then "Inner"
else "Leaf" end as "type"
from tree

15.SQL查询来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换

select rank() over(
(if id %2=0, id =id-1,id=id+1)
) as id, student
from seat 

15. 不使用order输出第二高薪水

select e.emp_no, s.salary as salary,e.last_name,e.first_name from employees e
inner join salaries s on e.emp_no=s.emp_no
where
s.salary =(select max(salary)
              from salaries
              where salary<(select max(salary)
              from salaries)
             )

16. 平均播放进度大于60%的视频类别 时间不能直接相减，要用timestampdiff(时/分/秒，起始时间，终止时间）

select tag, concat(round(avg(
    case when timestampdiff(second,start_time,end_time)/duration>=1 then 1
    else timestampdiff(second,start_time,end_time)/duration end )*100 ,2),'%') as avg_play_progress 
from tb_user_video_log vl
inner join tb_video_info vi on vl.video_id = vi.video_id
group by tag
having avg_play_progress > 60
order by avg_play_progress desc

17. 基本插入语句

insert into XXXX values(),();

insert into XXX1 (某些属性) select 某些属性 from XXXX2

replace into XXX values()

18. 时间函数

timestampdiff(参数，时间小的，时间大的）
有year，month，day,minute,second	

19. ALTER

在某列前插入列： alter table table_name add column column_name 数据类型   AFTER 列名
alter table table_name add [索引类型] index 索引名(列名);
修改某列名字和数据类型：alter table table_name change 原来的列名 新列名 数据类型
为某列添加默认值：alter table table_name modify coulmn 列名 数据类型（必须要写！不然报错） default 默认值

alter table examination_info add index idx_duration(duration);
alter table examination_info add unique index uniq_idx_exam_id(exam_id);
alter table examination_info add fulltext index full_idx_tag(tag);--全文索引

alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;

20. DELETE

delete from exam_record
where timestampdiff(minute,start_time,submit_time) < 5 or submit_time is null
order by start_time asc
limit 3

truncate 删除表数据，保留结构，只清除数据，保留表结构，列，权限，索引，视图，关系等等，相当于清零数据

21. 去掉一个最高分，去掉一个最低分，求剩下的平均值

select ei.tag, difficulty, round((sum(score)-min(score)-max(score))/(count(er.score)-2),1) from exam_record er
left join examination_info ei on er.exam_id = ei.exam_id
where ei.tag="SQL" and ei.difficulty = "hard"
group by er.exam_id

开窗函数学习

1. 基本语法

function (experssion)
OVER (partition by column #partition与group by很像
         order by column ASC/DESC
         rows [...]) as 别称

函数名（[expr]） over(partition by <要分列的组> order by <要排序的列> rows between <数据范围> AND <范围>)
OVER 必须有，其他不一定有
如果不限定窗口大小，就默认整张表

2. As 后面Sqlzoo的别名是英文的单引号，但是在Navicat是``，而在windowfunctions.com中是" "
3. PARTITION BY

• 每个部门的 —— PARTITION BY department
• 每个部门及小组的 —— PARTITION By department, group
  如果Group名字唯一的话，可以省略department

4. ROWS 默认 ROW Between n行 Preceding AND CURRENT ROW ：前N行到现在行
   前五行的后五行数据: rows between 5 preceding and 5 following

ROWS BETWEEN unbounded preceding AND current row 包括本行在内的所有行
ROWS BETWEEN 2 preceding AND current row 当前行和它的前两行
ROWS BETWEEN current row AND unbounded following 本行和本行之后的所有行
ROWS BETWEEN 3 preceding AND 1 following 本行、本行的前三行、本行的后一行
AND unbounded following/ x preceding/ x following/ current

常用函数（然后后面跟over）
avg()
count(*)
dense_rank()
lag(colum [,offset])/ lead(…)
max(), min(), median()

partition by 很像将一个大类细化
比如这里猫可能重名，如果加上partition by name输出的是每一个叫某个名字的猫的体重，这里没有partition，意味着是所有叫这个名字猫的体重

索引

视图