花了四天时间,前期看的是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