知乎sql面试50题

查询课程编号为02的总成绩

select sum(s_score),avg(s_score),count(distinct s_id) from score where c_id = "02"
select sum(s_score),avg(s_score),count(distinct s_id) from score group by c_id
select sum(s_score),avg(s_score),count(distinct s_id) from score group by c_id having c_id="02"

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

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

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

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

查询课程编号为01的课程比02的课程的成绩高的所有学生的学号

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

学过张三老师所教的所有课的同学的学号姓名

select st.st_name,st.st_id
from student as st
inner join score as s
on s.s_id = st.s_id
inner join  course as c
on c.s_id=s.s_id 
inner join teacher as t
on t.t_id = c.t_id
where t.name = "张三"
group by st.s_id

查询学过01和02的课程的学生

select s_id ,s_name from student 
where s_id  in 
(
select 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 
)

查询姓侯的老师的个数

select count(distinct t_id) from teacher where t_name like "候%"

查询没学过张三老师课的学生

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 = "张三")
 )
)

查询所有成绩小于60的学生的学号和姓名

--查学生低于60分的课程数和他学的课程数
select a.s_id
from
(select s_id,count(c_id) from score as a group by s_id where s_scroe<60)
inner join 
(select s_id,count(c_id) from score as b  group by s_id )
on a.s_id = b.s_id

查询没有学全所有课的学生的学号和姓名

select a.*,b.* from student as st
left join score 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)

查询至少有一门课与学号01的学生所学的课程相同的学生的学号与姓名

select s_name,s_id from student  where st_id in (
select distinct s_id from score where c_id in (
(select c_id from score where c_id = "01") and s_id !="01")
)

select s_name,s_id from student as a inner join (
select distinct s_id from score where c_id in (
(select c_id from score where c_id = "01") and s_id !="01") as b on a.s_id=b.s_id
)

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

select a.s_name,a.s_id from student as a
inner join score as b 
on a.s_id=b.s_id
where a.s_id 
in
(select s_id from score where s_score<60 group by s_id having count(distinct c_id)>2)

查询各科成绩的最高分，最低分和平均分

select s.c_id,c.c_name,
max(s.s_score),
min(s.s_core)，
avg(s.s_score),
sum(case when s.s_score>=60 then 1 else 0 End)/count(s.s_id) as "及格",
sum(case when s.s_score>=70 then 1 else 0 End)/count(s.s_id) "良好",
from score as s 
inner join course as c on s.c_id=c.c_id
group by c_id