掌握这些SQL面试题再也不怕面试懵逼了

select *
from student
where name like "猴%"

select *
from student
where name like "%猴"

select *
from student
where name like "%猴%"

select sum(成绩)
from score
where 课程号=’0002‘

select count(distinct 学号) as 学生人数
from score

select 课程号,max(成绩) as 最高分,min(最低分) as 最低分
from score
group by 课程号

select 课程,count(学号)
from score
group by 课程号;

select 性别,count(*)
from student
group by 性别

select 学号,avg(成绩)
from score
where 成绩>60
group by 学号

select by 学号,count(课程号) as 选修科目数
from score
group by 学号
having count(课程号)>=2;

select 姓名,count(姓名) as 人数
from student
group by 姓名
having count(姓名)>=2

select 课程
from score
where 成绩<60
order by 成绩 desc；

select 课程号,avg(成绩) as 平均成绩
from score
group by 课程号
order by 平均成绩 asc,课程号 desc;

select 学号 
from score
where 课程号='0004' and 成绩<60
order by 成绩 desc;

select 课程号,count(学号)
from score
group by 课程号
having count(课程号)>2
order by count(学号) desc,课程号 asc;

select 学号,avg(成绩)
from score 
where 成绩<60
group by 学号
having count(课程号)>2

select score.学号,student.姓名
from score INNER JOIN student on student.学号=score.学号
group by 学号 
having max(成绩)<60
select 学号,姓名
from student
where in (
select 学号
from score
group by 学号
having max(成绩)<60
)

select 学号,姓名
from student
where 学号 in(
select 学号
from score
group by 学号
having count(课程号)< (select count(课程号) from course)
);

select 学号,姓名
from student
where 学号 in(
select 学号
from score
group by 学号
having count(课程号) =2);
)

select 学号,姓名
from student
where year (出生日期)=1990；

select 课程号,max(成绩) as 最大成绩
from score
group by 课程号;

select * 
from score as a
where 成绩 =(
select max(成绩)
from score b
where a.课程号=b.课程号
group by 课程号
)

select *
from score as a 
where 成绩={
select min(成绩)
from score as b
where b.课程号=a.课程号
group by 课程号);

案例：查询各科成绩前两名的记录
(select * from score where 课程号 = '0001' order by 成绩  desc limit 2)
union all
(select * from score where 课程号 = '0002' order by 成绩 desc limit 2)
union all
(select * from score where 课程号 = '0003' order by 成绩 desc limit 2);

select 学号,姓名
from 学生表
where 学号 not in (
select 学号
from 近视学生表
)

select a.学号,a.姓名 as 不近视学生名单
from 学生表 a 
left join 近视学生表 b
on a.学号=b.学生学号
where b.学生学号 is null

select a.id,a.name
from customer c
left join order o on o.id=c.id
where o.customerid=null

select a.学号,a.姓名,count(c.学号),sum(c.成绩)
from student a
left join score c
on a.学号=c.学号
group by a.学号

select a.学号,a.姓名,avg(c.成绩) as 平均成绩
from student a
left join score c
on a.学号=c.学号 
group by a.学号
having avg(成绩)>85

select a.学号,a.姓名,c.课程号,c.课程名称
from studnet a 
inner join score b on b.学号=a.学号
inner join course c on c=课程号=b.课程号

select 课程号,
sum(case when 成绩>=60 then 1 else 0 end) as  及格人数
sum(case when 成绩<60 then 1 else 0 end) as 不及格人数
from score
group by 课程号

select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100 then 1 else 0 end ) as '[100-85]' 
sum(case when 成绩>=70 and 成绩<85 then 1 else 0 end ) as '[70-85]'
sum(case when 成绩›=60 and 成绩‹70 then 1 else 0 end) as '[70-60]',
sum(case when 成绩‹60 then 1 else 0 end) as '[‹60]'
from score as a right join course as b 
on a.课程号=b.课程号 
group by a.课程号

select a.学号,a.姓名
from student as a inner join score as b on a.学号=b.学号
where b.课程号=’0003‘ and b.成绩>80;

select 学号,
max(case 课程号 when '0001' then 成绩 else 0 end) as '课程号0001',
max(case 课程号 when '0002' then 成绩 else 0 end) as  '课程号0002',
max(case 课程号 when '0003' then 成绩 else 0 end) as '课程号0003'
from score
GROUP BY 学号;

select 学号, 成绩 
from 成绩表 
where 学号 in (8, 9)

select 学号, 成绩 
from 成绩表 
where 学号 between 8 and 9

select 学号 
from 成绩表 
where 成绩 = 88 or 成绩 = 89
优化后：
select 学号 from 成绩表 where 成绩 = 88
union
select 学号 from 成绩表 where 成绩 = 89