SQL面试50题

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

select a.s_id, a.s_score '01', b.s_score '02' from 
(SELECT s_id,c_id,s_score from Score where c_id = '01') a 
inner join 
(SELECT s_id,c_id,s_score from Score where c_id = '02') b 
on a.s_id = b.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

3.查询所有学生的学号、姓名、选课数、总成绩(不重要)

SELECT A.s_id, A.s_name, count(B.s_id),sum(B.s_score)

from Student A

inner join Score B

on A.s_id = B.s_id

GROUP BY A.s_id,A.s_name

4.查询姓“猴”的老师的个数(不重要)

select count(*) from Teacher where t_name like '猴%'

5.查询没学过“张三”老师课的学生的学号、姓名(重点,很容易出错!)

SELECT * from Student where s_id not in

(

SELECT s_id from Score

inner join Course on Score.c_id = Course.c_id

inner join Teacher on Course.t_id = Teacher.t_id

where Teacher.t_name = '张三'

)

错误答案:

https://i-blog.csdnimg.cn/blog_migrate/63c4bfd4c532b269dadef61680e463f9.png

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

SELECT st.s_id ,st.s_name from Student st where st.s_id in (

SELECT s_id from Score s

inner join Course c on s.c_id = c.c_id

inner join Teacher t on t.t_id = c.t_id

WHERE t.t_name = '张三')

7.查询学过编号为“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') a

inner join (SELECT s_id from Score WHERE c_id = '02') b

on a.s_id = b.s_id)

8、查询课程编号为“02”的总成绩(不重点)

SELECT sum(s_score) from Score where c_id='02'

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

SELECT stu.s_id,stu.s_name from Student stu where s_id in(
SELECT DISTINCT s_id from Score where s_id not in (SELECT distinct s_id from Score where s_score > 60))

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

select stu.s_id,stu.s_name,s.num from Student stu 
left join
(select s_id,count(s_id) num from Score Score GROUP BY s_id) s 
on stu.s_id = s.s_id 
WHERE num != 3 or num is null;

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

SELECT stu.s_id,stu.s_name from Student stu 
inner join (
select distinct s_id from Score 
where c_id = any(SELECT distinct c_id from Score where s_id = '01') and s_id != '01' ) B 
on stu.s_id = B.s_id

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

SELECT stu.s_id,stu.s_name, s.num from Student stu 
inner join (select s_id, count(*) as num from Score GROUP BY s_id) s 
on stu.s_id = s.s_id 
WHERE s.num = 3 and stu.s_id != '01'

13.查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样(重点,能做出来)

select * from Student WHERE s_id not in (
SELECT s.s_id from Score s
inner join Course c on s.c_id = c.c_id
inner join Teacher t on c.t_id = t.t_id
where t_name = '张三')

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

SELECT stu.s_id,stu.s_name,s.avg_score from Student stu
inner join (
select s_id, count(s_id) num, avg(s_score) avg_score from Score where s_score < 60 GROUP BY s_id HAVING num >=2) s on stu.s_id = s.s_id;

16.检索"01"课程分数小于60,按分数降序排列的学生信息(和34题重复,不重点)

select stu.s_id, stu.s_name from Student stu 
inner join(
select * from Score where c_id = '01' and s_score<60 ORDER BY s_score desc) s 
on stu.s_id = s.s_id 

17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)

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 = '02' then s_score else null end ) '英语',
avg(s_score) "平均成绩" from Score GROUP BY s_id ORDER BY avg(s_score) desc 
选择max是因为,每个学生每一科目只有一个成绩,max即使取最大,也是取值本身。

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

select s.c_id,c.c_name, max(s_score),min(s_score),avg(s_score),
avg(case when s_score >= 60 then 1 else 0 end ) '及格率',
avg(case when s_score>= 70 and s_score<80 then 1 else 0 end ) '中等率',
avg(case when s_score>= 80 and s_score <90 then 1 else 0 end ) '优良率',
avg(case when s_score>=90 then 1 else 0 end ) '优秀率'
from Score as s 
inner join Course c on s.c_id = c.c_id
GROUP BY s.c_id

19、按各科成绩进行排序,并显示排名(重点row_number)  row_number()over (order by 列)

SELECT *, row_number() over(partition by c_id order by s_score desc) from Score
row_number()没有重复值的排序 1 2 3 4 5
rank() 跳跃排序 1 2 2 4 
dense_rank() 连续排序 1 2 2 3 4 

 20.查询学生的总成绩并进行排名(不重点)

select s_id, sum(s_score) sum_score from Score GROUP BY s_id ORDER BY sum_score desc

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

select c.t_id,t.t_name, c_name,s.avg from Course c 
inner join 
(select c_id, avg(s_score) avg from Score group by c_id) s on c.c_id = s.c_id 
inner join Teacher t on c.t_id = t.t_id 
order by s.avg desc

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

select st.s_id,st.s_name,st.s_birth,st.s_sex,sc.排名 from Student
inner join 
(select *, row_number() over(partition by c_id order by s_score) 排名 from Score) sc on st.s_id = sc.s_id 
where sc.排名 in (2,3)
不一定对 不能运行

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


select c_id,max(s_score),min(s_score),avg(s_score),
avg(case when s_score >=85 then 1 else 0 end ) '优秀率',
sum(case when s_score >=85 then 1 else 0 end ) '优秀人数',
avg(case when s_score >=70 and s_score < 85 then 1 else 0 end ) '优良率',
sum(case when s_score >=70 and s_score < 85 then 1 else 0 end ) '优良人数',
avg(case when s_score >=60 and s_score < 70 then 1 else 0 end ) '中等率',
sum(case when s_score >=60 and s_score < 70 then 1 else 0 end ) '中等人数',
avg(case when s_score <60 then 1 else 0 end ) '及格率',
sum(case when s_score <60 then 1 else 0 end ) '及格人数' 
from Score 
GROUP BY c_id 

24.查询学生平均成绩及其名次(同19题,重点)

select s_id,avg(s_score) row_number() '排名' over(order by avg(s_score) desc)from Score group by s_id 

25.查询各科成绩前三名的记录(不考虑成绩并列情况)(重点 与22题类似)

select c_id,
max(case when '排名'=1 then s_score else null end) '第一',
max(case when '排名'=2 then s_score else null end) '第二',
max(case when '排名'=3 then s_score else null end) '第三' 
from (
select st.s_id,st.s_name,st.s_birth,st.s_sex,sc.c_id,sc.score from Student inner join 
(select c_id,row_number() over(partition by c_id order by s_score desc) '排名' from Score) sc 
on st.s_id = sc.c_id )
where '排名' = any(1,2,3)
group by c_id 
不一定对

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

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

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

select st.s_id,st.s_name from Student st inner join 
(select s_id,count(s_id) from Score group by s_id having count(s_id)=2) B on st.s_id = B.s_id 

28.查询男生、女生人数(不重点)

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

29. 查询名字中含有"风"字的学生信息(不重点)

select s_id,s_name,s_birth,s_sex from Student st where s_name like '%风%'

31.查询1990年出生的学生名单(重点year)

select s_id,s_name,s_birth,s_sex from Student st where Year(st.s_birth) = '1990'

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

select st.s_id, st.s_name,sc.`平均成绩` from Student st 
inner join (select s_id, avg(s_score) '平均成绩' from Score group by s_id having 平均成绩 >= 85) sc 
on st.s_id = sc.s_id 

33.查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列(不重要)

select c_id, avg(s_score) '平均成绩' from Score GROUP BY c_id order by 平均成绩 asc,c_id desc 

34.查询课程名称为"数学",且分数低于60的学生姓名和分数(不重点)

select s.s_id, st.s_name, s.s_score from Score s 
inner join Course c on s.c_id = c.c_id 
inner join Student st on s.s_id = st.s_id
where c.c_name = '数学' and s.s_score < 60

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

select s.s_id,
max(case when s.c_id = '01' then s_score else null end) '语文',
max(case when s.c_id = '02' then s_score else null end) '数学',
max(case when s.c_id = '03' then s_score else null end) '外语'
from Score s 
inner join Student st on s.s_id = st.s_id 
group by s.s_id 

36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数(重点)  注:不用group by

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

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

select s.s_id,st.s_name,s.c_id,c.c_name,s.s_score from Score s
inner join Student st on  s.s_id = st.s_id 
inner join Course c on s.c_id = c.c_id 
where s.s_score < 60 order by s.c_id desc;

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

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

39.求每门课程的学生人数(不重要)

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

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

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

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

select a.s_id,a.s_score from Score1 a
inner join (select s_id, count(*) from Score group by s_id having count(*) > 1) b on a.s_id = b.s_id 
group by a.s_id,a.s_score
having count(a.s_id) = (select count(*) from Course);

42、查询每门功成绩最好的前两名(同22和25题)

select c_id, s_id, s_score, row_number() over(partition by c_id order by s_score desc) '排名' from Score 
where 排名 in (1,2)

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

select c_id, count(*) 人数 from Score group by c_id having 人数 > 5 
order by 人数 desc,c_id asc 

44、检索至少选修两门课程的学生学号(不重要)

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

45、 查询选修了全部课程的学生信息(不能直接写=3)

select s_id, count(*) from Score group by s_id having count(*) = (select count(c_id) from Course)

46、查询各学生的年龄(精确到月份)

select s_name, round(DATEDIFF(Date(now()),s_birth)/365) from Student

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

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

48、查询两门以上不及格课程的同学的学号及其平均成绩(重点 没写出来 )

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

47、查询本月、下月、本周、下周过生日的学生(无法使用week、date(now())

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值