SQL基础二刷(练习)

2. 练习

image.png

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

image.png

我的方法

select s_id,s_name from student c where c.s_id in 
 ( 
 select a.s_id from (
 select s_id,c_id,s_score from score where c_id='01') as a 
 join 
(select s_id,c_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
)

• 正确方法1-从条件出发(01>02)

--4.最后，在选择需要的条件在过滤过后的表
select * from
--1.先产出01课程和02课程同时满足条件的成绩
(select s_id,s_score from score where c_id='01' ) as a01 
inner join (select s_id,s_score from score where c_id='02') as b02 on a01.s_id=b02.s_id
--2.在将这些满足条件的id去匹配学生信息表
inner join student as c on a01.s_id = c.s_id
--3.在从这张总表里面筛选条件
where a01.s_score>b02.s_score

• 正确方法2-从结果出发(所有学生信息)

--1.要的是学生的信息,那么student表是必须的
select * from student as a
--2. 在查询出来考了课程01的学生信息和成绩
inner join (select s_id,s_score from score where c_id = '01') b01 on a.s_id = b01.s_id 
--3. 在用上面的结果去匹配考了课程02的成绩，得出最后的表就是考了01课程和02课程的所有学生信息
inner join (select s_id,s_score from score where c_id = '02') c02 on a.s_id =c02.s_id
--4. 通过上面的总表来比较01>02的
where b01.s_score>c02.s_score

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

image.png

select s_id,CONVERT(float,CONVERT(decimal(10,1),AVG(s_score))) from score 
group by s_id having AVG(s_score)>60

注意： 使用CONVERT(float,CONVERT(decimal(10,1),AVG(s_score)))可以保留两位小数

• 还可以使用CAST(ROUND(AVG(s_score),2)as float)

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

image.png

• 这里需要注意的是：有些学生缺考了，成绩表里没有他的学号，所有不能使用inner join，因为inner join会过滤掉不符合条件的学生王菊，错

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,a.s_name

4.查询没学过“张三”老师课的学生的学号、姓名（重点2）

select * 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 = '张三')))

5.查询学过“张三”老师所教的所有课的同学的学号、姓名

select a.s_id,a.s_name,b.c_id,c.c_name,d.t_name from student as a 
inner join score as b on a.s_id=b.s_id
inner join course as c on b.c_id = c.c_id
inner join teacher as d on d.t_id = c.t_id
where t_name = '张三'

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

image.png

select a.s_id,d.s_name from 
(select * from score where c_id = '01' ) as a 
inner join (select * from score where c_id = '02') as b
on a.s_id =b.s_id
inner join student as d on a.s_id = d.s_id

• 注意：遇到这种同时满足的情况，应该第一时间想到inner join

7.查询所有课程成绩都小于60分的学生的学号、姓名（重点）

• 错误：这种方法会查出来多门课程中只要有一门不及格的学生，并不是所有课都是小于60的学生

select distinct( a.s_id),a.s_name from student 
as a left join score 
as b on a.s_id = b.s_id where b.s_score<60

• 正确：使用max(s_score)筛选所有课程都不大于60的学生后（使用max，就可以得到最大成绩，所有课程足最大的分数不大于60就是满足条件），在进行筛选

select a.s_id,b.s_name from score a
left join student b on a.s_id = b.s_id
group by a.s_id,b.s_name
having MAX(s_score)<60

注意： max min这种聚合函数在判断条件时候的思维转换

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

image.png

• 旧版

select a.s_id,a.s_name,COUNT(b.c_id) from student as a
left join score as b on a.s_id = b.s_id 
group by a.s_id,a.s_name having COUNT(b.c_id)<3

• 新版

select a.s_id,a.s_name,count(b.c_id) from student a
left join score b on a.s_id = b.s_id
group by a.s_id,a.s_name having COUNT(b.c_id)<(select COUNT(*) from course )

注意：这里一定要用学生去left join 成绩 而不能用成绩去left join学生，因为学生里面会有所有考试都将没去参加的学生。

9.查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

image.png

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

• 注意：可以将子查询换成exsists这样查询效率会提高

select b.s_id,a.s_name  from student as a 
inner join 
(select distinct(s_id)  from score 
where exists
(select c_id from score where s_id = '01')) as b
 on a.s_id  = b.s_id

10.查询和“01”号同学所学课程完全相同的其他同学的学号(争议)

image.png

• 错误

select s_id from score 
where c_id in (
select c_id from score where s_id = '01'
) and s_id !='01' group by s_id having COUNT(distinct c_id)
= (select COUNT(distinct c_id) from score where s_id= '01')

• 正确

select * from student where s_id in(
--2.再选出所有人与01选课数量相同的人
select s_id from score 
where s_id!='01'
group by s_id having COUNT(distinct c_id) = (select COUNT(distinct c_id) from score where s_id ='01')
)
-- 3.在排除与01数量相同，但是科目不同的
and s_id not in (
--1.先选出来所有 没有01同学的课的人
select distinct s_id from score where c_id not in(
select c_id from score where s_id ='01')
)

• 注意：可以直接通过后面的条件来筛选

where s_id!='01'
group by s_id having COUNT(distinct c_id) = (select COUNT(distinct c_id) from score where s_id ='01')

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）

image.png

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

12.行转列

image.png

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

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

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

  
  image.png

select a.c_id,b.c_name,MAX(a.s_score)s_max,MIN(a.s_score)s_min,AVG(a.s_score)s_avg,
SUM(case when a.s_score<70.0 then 1.0 else 0.0 end)/COUNT(a.s_id) "及格率",
SUM(case when a.s_score>=70.0 and a.s_score<80.0 then 1.0 else 0.0 end)/COUNT(a.s_id) "中等率",
SUM(case when a.s_score>=90.0 then 1.0 else 0.0 end)/COUNT(a.s_id) "优秀率"
from score as a
inner join course as  b on
a.c_id =b.c_id
group by a.c_id,b.c_name

• 注意:case when 里的数字一定要是decimal，不然无法计算出结果

14.按各科成绩进行排序，并显示排名(重点窗口函数)

• 语法row_number() over(partition by 分组 order by 字段)
  image.png

select a.s_id,a.c_id,s_score,
ROW_NUMBER() over (partition by a.c_id order by a.s_score desc),b.c_name,c.s_name 
from score a
inner join course b on a.c_id= b.c_id
inner join student c on a.s_id = c.s_id

• 注意：这里的意思是，按照c_id分组，然后按照分数从高到低

15.查询不同老师所教不同课程平均分从高到低显示

image.png

select t.t_name,c.c_name,AVG(s.s_score)s_avg 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
group by t.t_name,c.c_name
order by s_avg desc

16.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩(窗口)

image.png

select * from
(
select sc.s_id,st.s_name,s_score,
ROW_NUMBER() over(partition by c_id order by s_score desc )num 
from score sc
inner join student st on sc.s_id = st.s_id
) as temp
where num in (2,3)

• 注意：在查询出来row_number()后不可以直接，通过where in (2,3)直接筛选，而是需要将查询出来的整个结果当作一个临时表temp，然后select * from temp才可以使用where

17.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

image.png

select sc.c_id,c.c_name,
sum(case when sc.s_score<60 then 1 else 0 end)"[<60]",
sum(case when sc.s_score>=60 and sc.s_score<80 then 1 else 0 end)"[60-80]",
sum(case when sc.s_score>=80 then 1 else 0 end)"[>80]"
from score as sc
inner join course c on sc.c_id=c.c_id
group by sc.c_id,c.c_name 

18.查询学生平均成绩及其名次

image.png

select s_id,AVG(s_score)s_avg,ROW_NUMBER() over(order by AVG(s_score) desc) from score  group by s_id

19. 查询各科成绩前三名的记录

image.png

select * from 
(
select c_id,s_score,ROW_NUMBER() over(partition by c_id order by s_score desc) rank from score 
) as temp
where temp.rank<=3

• 注意：在创建临时表temp的时候，所有字段都需要有名字，不然无法创建

20. 查询1990年出生的学生名单

image.png

select * from student where YEAR(s_age)=1990

21.查询所有学生的课程及分数情况（重点）

image.png

select st.s_id,st.s_name,
SUM(case when sc.c_id = '01' then sc.s_score else null end) '数学',
SUM(case when sc.c_id = '01' then sc.s_score else null end) '语文',
SUM(case when sc.c_id = '01' then sc.s_score else null end) '英语'
from student st left join score sc
inner join course c on c.c_id = sc.c_id
on st.s_id=sc.s_id
group by st.s_id,st.s_name

22.查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

image.png

select top 1 a.s_id,d.s_name,a.s_score,b.c_name from score a 
left join course b on a.c_id = b.c_id
left join teacher c on b.c_id = c.t_id
inner join student d on d.s_id =a.s_id
where c.t_name= '张三'
order by s_score desc