SQL面试50题

花了四天时间，前期看的是b站视频【数据分析】- SQL面试50题，后面稍微就好多了，基本可以自己写出来，写完可以和SQL面试必会50题 里的SQL及答案比对一下，收获很大。

1.写SQL时，可以将脚本框放大，这样看着舒服点。按住ctrl+alt，然后滑动鼠标即可缩放。

2.如果有group by 的话，select的内容尽量是group by里面的内容。否则可能会报错。如：

[Err] 1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.st.s_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

表名和字段
–1.学生表

Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

–2.课程表

Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

–3.教师表

Teacher(t_id,t_name) –教师编号,教师姓名

–4.成绩表

Score(s_id,c_id,s_score) –学生编号,课程编号,分数

---

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

select a.s_id from
(
select s_id,s_score from Score where c_id="01"
) as a
inner join
(
select s_id,s_score from Score where c_id="02"
) as b
on a.s_id=b.s_id
where a.s_score>b.s_score

 

如果想把名字也输出来，则需要关联第三张表Student。

select a.s_id,c.s_name,a.s_score as "01",b.s_score as "02" from

(
select s_id,s_score from Score where c_id="01"
) as a
inner join
(
select s_id,s_score 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

 

---

 --2、 查询平均成绩大于60分的学生的学号和平均成绩 类似的题目（重点）

select s_id,avg(s_score)
from Score
group by s_id
having avg(s_score)>60

注意这里不能用where，平时多注意having和where的区别。

---

 -- 3、查询所有学生的学号、姓名、选课数、总成绩（不重要）

select a.s_id,a.s_name,count(b.c_id) as "选课数",sum(b.s_score) as "总分"
from 
Student as a
left join Score as b
on a.s_id=b.s_id
group by a.s_id

如果想让这里的Null变为0，则需要修改一下：

select a.s_id,a.s_name,count(b.c_id),
sum(case when b.s_score is null then 0 else b.s_score end)
from Student as a
left join Score as b 
on a.s_id=b.s_id
group by a.s_id

---

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

select count(t_name)
from Teacher
where t_name like "猴%"

---

--5、查询没学过“张三”老师课的学生的学号、姓名（重点）

四张表进行了联合：

select s_id  from
Student where s_id not in
(select f.s_id  from
(select d.s_id from
(select b.c_id from 
(select t_id from Teacher where t_name="张三") as a
inner join Course as b
on a.t_id=b.t_id) as c
inner join Score as d
on c.c_id=d.c_id) as e
inner join Student as f
on e.s_id=f.s_id)

或者 

select s_id,s_name from student 
where s_id not in 
(
select s_id from score as s
inner join course as c on c.c_id=s.c_id
inner join teacher as t on t.t_id=c.t_id
where t.t_name="张三"
)

---

--6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

先想好大概的表结构，然后一次次的联合，最后过滤。

select st.s_id,st.s_name,s.c_id,c.c_name,c.t_id,t.t_name
from student as st
inner join score as s on s.s_id=st.s_id
inner join course as c on c.c_id=s.c_id
inner join teacher as t on t.t_id=c.t_id
where t.t_name="张三"
order by st.s_id

 

---

--7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

in ("01","02")不能解决问题，这里in表示的是或，但是题目要求的是且，所以分开求01和02：

select s_id,s_name from Student 
where s_id in 
(
select a.s_id from 
(select s_id from Score where c_id="01") as a
inner join 
(select s_id from Score where c_id="02") as b
on a.s_id=b.s_id
)

---

 -- 8、查询课程编号为“02”的总成绩（不重点）

select sum(s_score) from Score
where c_id="02"

---

-- 10、查询没有学全所有课的学生的学号、姓名(重点)

三张表关联

select a.s_id,a.c_num,st.s_name from
(
select s_id,count(c_id) as c_num from Score
group by s_id
having count(c_id)<(select count(distinct c_id) from Course)
) as a
inner join Student as st
on a.s_id=st.s_id

---

 --11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）

可以用in，因为in会过滤掉不满足的，也含有or的意思。跟inner join功能有点像，不要一直用inner join。

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"
)

---

 -- 12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)--

思路：先找出修了与01号同学不同课的同学的学号并排除；排除后找出修与01号同学一样多科目的同学。

select s_id from Score
where s_id!="01"
group by s_id 
having count(c_id)=(select count(c_id) from Score where 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"))

---

 -- 13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点）--

先找出上过"张三"老师课的同学，然后排除

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

---

 -- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）--

 先通过Score表得出不及格学生的学号，注意要给临时表命名。

select a.s_id,count(a.c_id),round(avg(a.s_score),2) from 
(
select * from Score
where s_score<60
) as a
group by a.s_id
having count(a.c_id)>=2

获取名字等信息，联合Student表。

select * from 
(select a.s_id,count(a.c_id),round(avg(a.s_score),2) from 
(
select * from Score
where s_score<60
) as a
group by a.s_id
having count(a.c_id)>=2) as b
inner join Student as st
on st.s_id=b.s_id

---

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

select * from 
(select * from Score 
where c_id="01" and s_score<60
order by s_score desc) as a
inner join Student as st 
on a.s_id=st.s_id

---

--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点)

因为后面有group by，所以前面要加上max或者其他聚合函数。关键：利用了case/when进行了行转列

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) "平均成绩"
from score 
group by s_id
order by avg(s_score) desc

 

---

 -- 35、查询所有学生的课程及分数情况（重点）

select sd.s_id,sd.s_name
,max(case when c.c_name="语文" then s.s_score else null end) as "语文"
,max(case when c.c_name="数学" then s.s_score else null end) as "数学"
,max(case when c.c_name="英语" then s.s_score else null end) as "英语"
from score as s
inner join course as c on s.c_id=c.c_id
inner join student as sd on s.s_id=sd.s_id
group by sd.s_id,sd.s_name

 

 

 备注:

1.因为要选出需要的字段用case when 当c.c_name='数学' then 可以得到对应的 s.s_core；

2.因为group by 要与select 列一致，所以case when 加修饰max；

3.因为最后要展现出每个同学的各科成绩为一行，所以用到case。

---

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

--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)

select s.c_id
,c.c_name
,max(s.s_score)
,min(s.s_score)
,avg(s.s_score)
,sum(case when s.s_score>=60 then 1 else 0 end)/count(s_id) "及格"
,sum(case when s.s_score>=70 and s.s_score<80 then 1 else 0 end)/count(s_id) "中等"
,sum(case when s.s_score>=80 and s.s_score<90 then 1 else 0 end)/count(s_id) "优良"
,sum(case when s.s_score>=90 then 1 else 0 end)/count(s_id) "优秀"
from score as s
inner join course as c on s.c_id=c.c_id
group by c_id

---

 -- 20、查询学生的总成绩并进行排名（不重点）

select s_id "学号",sum(s_score) "总分"
from Score
group by s_id
order by "总分" desc

最后放到excel可以添加一列排名。 

---

 -- 21 、查询不同老师所教不同课程平均分从高到低显示(不重点)

select t.t_id,t.t_name,avg(s_score) as avg_score
from Score as Sc
inner join course as c on Sc.c_id=c.c_id
inner join teacher as t on c.t_id=t.t_id
group by t.t_id,t.t_name
order by avg_score desc

---

 -- 23、使用分段[85,100),[70,85),[60,70),<60来统计各科成绩，分别统计各分数段人数、课程ID 和课程名称

select c.c_id,c.c_name
,sum(case when sc.s_score>=85 and sc.s_score<100 then 1 else 0 end) as "[85,100)"
,sum(case when sc.s_score>=70 and sc.s_score<85 then 1 else 0 end) as "[70,85)"
,sum(case when sc.s_score>=60 and sc.s_score<70 then 1 else 0 end) as "[60,70)"
,sum(case when sc.s_score<60 then 1 else 0 end) as "<60"  
from Score as sc
inner join Course as c on c.c_id=sc.c_id
group by c.c_id,c.c_name

---

 -- 26、查询每门课程被选修的学生数(不重点)

select c_id,count(s_id)
from Score
group by c_id

---

-- 27、 查询出只有两门课程的全部学生的学号和姓名(不重点)

select st.s_id,st.s_name 
from Score as sc
inner join Student as st on st.s_id=sc.s_id
group by sc.s_id
having count(sc.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 *,YEAR(s_birth) as year 
from Student
having year =1990

select *
from Student
where year(s_birth)=1990

select *
from Student
where s_birth like "1990%"

 

---

-- 32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要） 

select st.s_id,st.s_name,avg(sc.s_score) as avg_score
from Score as sc
inner join Student as st on sc.s_id=st.s_id
group by sc.s_id,st.s_name
having avg_score>=85

---

-- 33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）

select c_id,avg(s_score) as avg_score
from Score
group by c_id
order by avg_score asc,c_id desc

 

---

 -- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）

select st.s_name,sc.s_score 
from Score as sc
inner join Student as st on st.s_id=sc.s_id
where sc.c_id=(select c_id from Course where c_name="数学")       
and sc.s_score<60

---

-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）

select st.s_name,c.c_name,s.s_score 
from Score as s
inner join Student as st on st.s_id=s.s_id
inner join Course as c on c.c_id=s.c_id
where s.s_score>70

 

---

 -- 37、查询不及格的课程并按课程号从大到小排列(不重点)

select st.*,c.c_name,s.s_score
from Score as s
inner join Student as st on st.s_id=s.s_id
inner join Course as c on c.c_id=s.c_id
where s.s_score<60

---

-- 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）

select st.s_id,st.s_name
from Score as s
inner join Student as st on st.s_id=s.s_id
where s.c_id="03" and s.s_score>80

---

-- 39、求每门课程的学生人数（不重要）

 

select c.c_name,count(s.s_id)
from Score as s
inner join Course as c on c.c_id=s.c_id
group by c.c_id

---

-- 40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）

select st.s_name,s.s_score
from Student as st
inner join Score as s on s.s_id=st.s_id
inner join Course as c on c.c_id=s.c_id
inner join Teacher as t on t.t_id=c.t_id
where t.t_name="张三"
order by s.s_score desc 
limit 1

---

-- 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点） 

select distinct a.s_id,a.c_id,a.s_score
from score as a 
inner join score as b on a.s_id=b.s_id
where a.s_score=b.s_score and a.c_id!=b.c_id

---

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

select c_id,count(c_id)
from Score
group by c_id
having count(c_id)>5
order by count(c_id) desc,c_id

 

---

-- 44、检索至少选修两门课程的学生学号（不重要）

select s_id,count(c_id)
from Score
group by s_id
having count(c_id)>=2

 

---

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

select st.*
from Student as st 
inner join Score as sc on sc.s_id=st.s_id
group by st.s_id
having count(sc.c_id)=(select count(c_id) from Course)

---

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

select s_id,s_name
from Student 
where s_id not in 
(
select s_id
from Course as c 
inner join Score as sc on sc.c_id=c.c_id
inner join Teacher as t on t.t_id=c.t_id
where t.t_name="张三")

---

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

select s_id,avg(s_score)
from Score
where s_id in 
(select s_id from Score
where s_score<60
group by s_id
having count(s_score)>=2)
group by s_id

 

下面的答案是一样的，但是感觉又有点问题，下面求得的平均分应该是不及格成绩的平均分呀？？

---

 - 46、查询各学生的年龄（精确到月份）

select s_id,s_birth
,timestampdiff(year,s_birth,curdate()) as 'age'
from Student

select s_id,s_birth
,timestampdiff(month,s_birth,curdate())/12 as  'age'
from Student

SQLserver里是这样的：

---

-- 47、查询本月过生日的学生

select *,month(s_birth) 
from Student
having month(s_birth)=(select month(curdate()))

select *,month(s_birth) 
from Student
having month(s_birth)=month(date(now()))

 

---

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

week函数里的参数1表示周一为一天的第一天

select *
from Student 
where week(s_birth,1)=week(date(now()),1)+1

-- 50、查询下月过生日的学生

select *,month(s_birth) 
from Student
having month(s_birth)=(select month(curdate())+1)

select *,month(s_birth) 
from Student
having month(s_birth)=month(date(now()))+1