SQL练习题

面试SQL题：

来自SQL题目，自己sql使用的例子
有关学校老师和学生考试成绩的几张表格

---

问题描述：

-- 5、没有学过张三老师教的课的学生
select s_id,s_name from student 
where s_id not in(
select sc.s_id
from teacher as t 
inner join course as c on t.t_id=c.t_id
inner join score as sc on c.c_id=sc.c_id
where t_name = "张三"
)

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

select * 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
)

-- 9、查询所有课程成绩小于60分的学生的学号、姓名
select a.s_id from 
(select s_id,count(c_id) as cnt from 
score where s_score<60 group by s_id) as a
inner join 
(select s_id,count(c_id) as cnt from score group by s_id) as b
on a.s_id=b.s_id
where a.cnt=b.cnt

-- 10.查询没有学全所有课的学生的学号、姓名(重点)
select st.s_id,st.s_name,count(s.c_id) cnt from student as st
left join score as s on st.s_id=s.s_id
group by s.s_id,st.s_name
having cnt<(
select count(c_id) from course)

-- 11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）
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”号同学所学课程完全相同的其他同学的学号(重点)

select a.s_id from 
(select * from score where s_id<>01) as a inner join 
(select c_id from score where s_id=01) as b on a.c_id=b.c_id
group by a.s_id having count(a.c_id) = (select count(c_id) from score where s_id=01)

-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）
select s_id,count(c_id)as cnt,avg(s_score)  from score where s_score<60 group by s_id having cnt>=2

-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）
select s_id,s_score from score where c_id=01 and  s_score<60 
order by s_score desc

-- 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 = 03 THEN s_score
        ELSE NULL
    END) '英语',
    AVG(s_score) AS avg_score
FROM
    score
GROUP BY s_id
ORDER BY avg_score DESC

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

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

SELECT 
    sc.c_id,c.c_name, MAX(sc.s_score) as "最高分", MIN(sc.s_score) as "最低分", AVG(sc.s_score) as "平均分",
    sum(case when sc.s_score>=60 then 1 else 0 end)/count(sc.s_id) as "及格率",
    sum(case when sc.s_score<80 and sc.s_score>=70 then 1 else 0 end)/count(sc.s_id) as "中等率",
    sum(case when sc.s_score>=80 and sc.s_score<90 then 1 else 0 end)/count(sc.s_id) as "优良率",
    sum(case when sc.s_score>=90 then 1 else 0 end)/count(sc.s_id) as "优秀率"
FROM
    score AS sc
        INNER JOIN
    course AS c ON sc.c_id = c.c_id
GROUP BY sc.c_id,c.c_name

-- 19、按各科成绩进行排序，并显示排名(重点row_number)

select c_id,s_score,ROW_NUMBER() over(PARTITION BY c_id order by s_score desc) from score

使用排序：ROW_NUMBER() over(partition by … order by …) ,rank

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）
select * from student as st inner join (
select c_id,s_id,s_score,ROW_NUMBER() over(PARTITION BY c_id order by s_score DESC) as m from score) as dd
on st.s_id=dd.s_id where m in(1,2,3)

case when 的使用：case when … then … else … end

-- 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>=0 and s_score<60 then 1.0 else 0.0 end) '不及格率',
avg(case when s_score>=60 and s_score<70 then 1.0 else 0.0 end) '2',
avg(case when s_score>=70 and s_score<80 then 1.0 else 0.0 end) '3',
avg(case when s_score>=80 and s_score<90 then 1.0 else 0.0 end) '4',
avg(case when s_score>=90 and s_score<100 then 1.0 else 0.0 end) '5'
from score 
group by c_id

-- 31、查询1990年出生的学生名单（重点year）
select * from student where year(s_birth)=1990;
select * from student where s_birth like '1990%';