sql50题

https://zhuanlan.zhihu.com/p/43289968

建表：（成绩表中的score字段应该是int类型的，在建表语句中是varchar类型的了）

create table student(
    s_id varchar(10),
    s_name varchar(20),
    s_age date,
    s_sex varchar(10)
);

create table course(
    c_id varchar(10),
    c_name varchar(20),
    t_id varchar(10)
);


create table teacher (
t_id varchar(10),
t_name varchar(20)
);

create table score (
    s_id varchar(10),
    c_id varchar(10),
    score varchar(10)
);

向表中填充数据：

insert into student (s_id, s_name, s_age, s_sex)
values  ('01' , '赵雷' , '1990-01-01' , '男'),
        ('02' , '钱电' , '1990-12-21' , '男'),
        ('03' , '孙风' , '1990-05-20' , '男'),
        ('04' , '李云' , '1990-08-06' , '男'),
        ('05' , '周梅' , '1991-12-01' , '女'),
        ('06' , '吴兰' , '1992-03-01' , '女'),
        ('07' , '郑竹' , '1989-07-01' , '女'),
        ('08' , '王菊' , '1990-01-20' , '女');

insert into course (c_id, c_name, t_id)
values  ('01' , '语文' , '02'),
        ('02' , '数学' , '01'),
        ('03' , '英语' , '03');

insert into teacher (t_id, t_name)
values  ('01' , '张三'),
        ('02' , '李四'),
        ('03' , '王五');

insert into score (s_id, c_id, score)
values  ('01' , '01' , 80),
        ('01' , '02' , 90),
        ('01' , '03' , 99),
        ('02' , '01' , 70),
        ('02' , '02' , 60),
        ('02' , '03' , 80),
        ('03' , '01' , 80),
        ('03' , '02' , 80),
        ('03' , '03' , 80),
        ('04' , '01' , 50),
        ('04' , '02' , 30),
        ('04' , '03' , 20),
        ('05' , '01' , 76),
        ('05' , '02' , 87),
        ('06' , '01' , 31),
        ('06' , '03' , 34),
        ('07' , '02' , 89),
        ('07' , '03' , 98);

 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数。

select*from(
    select d.*,c.score from
        (select a.s_id,b.score chinese from 
            (select distinct s_id from score) a
        left join score b
            on a.s_id=b.s_id
        where b.c_id='01') d
    left join score c
        on c.s_id=d.s_id
        where c.c_id='02'    
    )e 
where e.chinese>e.score    

 

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

使用inner join的性能要比left join的性能要好很多！

2、查询平均成绩大于等于60分的同学的学生编号和平均成绩。

select * from(
    select s_id,avg(score) avgScore from score group by s_id)a 
where a.avgScore>60

 

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

使用having条件语句，一条sql就可以搞定了！不需要写子查询了。

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

select c.*,d.num from
     (    
        select a.s_id,a.s_name,b.score from
            (select s_id,s_name from vichin_student) a
                inner join
            (select s_id,SUM(score) score from vichin_score group by s_id) b
        on a.s_id=b.s_id
     ) c
     inner join
     (select s_id,count(s_id) num from vichin_score group by s_id) d
     on c.s_id=d.s_id

 

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

审题出现偏差,题目中要求选出所有学生的信息,而我写的是:将所有有成绩的学员信息给选出来。因为题目是查询出所有学生，所以此处应该使用left join，并以学生表student为主表进行关联查询。

4、查询姓“侯”的老师的个数。

select  COUNT(t_id) from vichin_teacher where  t_name like '侯%'

select  COUNT(distinct t_id) from vichin_teacher where  t_name like '侯%'

5、★查询没有学过张三老师课的学生的学号。

 这道题写出来的sql语句是错的。

Sql语句中的子查询，可以使用inner join来替换。

select distinct s_id,s_name from vichin_student  where s_id not in
(
    select s_id from vichin_score 
        where c_id =(
            select c_id from
                (select t_id from vichin_teacher where t_name='张三') a
                inner join
                vichin_course b
                on a.t_id=b.t_id
        )
)

思路：先写查询出所有参加过张三老师课程的学生，最后在not in 一下就能得出没参加过张三老师课程的学生了。

6、查询出学过张三老师所教授课程的所有学生的学号和姓名。

    select e.s_id,e.s_name from
        (select t_id from Vichin_teacher where t_name='张三')a
        inner join
        Vichin_course b
        on a.t_id=b.t_id
        inner join
        Vichin_score d
        on d.c_id=b.c_id
        inner join Vichin_student e
        on d.s_id=e.s_id

思路与视频中的一致。

7、★查询学过编号为01的课程，并且也学过编号为02的课程的学生的学号和姓名。

select  a.s_id,b.s_name from
    (select distinct s_id from Vichin_score where c_id='02' or c_id='01') a
    inner join
    Vichin_student b
    on a.s_id=b.s_id

写法有问题，审题有误。我的写法求出的结果是学过01或者是学过02课程的学生。

    select c.s_id,d.s_name from(
         select a.s_id from 
         (select distinct s_id,c_id from Vichin_score where c_id='02')a
          inner join 
          (select distinct s_id,c_id from Vichin_score where c_id='01')b
          on a.s_id=b.s_id
    ) c 
        inner join
    Vichin_student d
    on c.s_id=d.s_id

思路：先使用inner join查询出学过01、02课程的学生之后，既可以使用子查询，也可以使用连接查询。因为连接查询的性能优于子查询，所以使用了连接查询。

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

select c_id,sum(score) totalScore,AVG(score) avgScore,COUNT(s_id) totalStudent from vichin_score where c_id='02'

select c_id,sum(score) totalScore,AVG(score) avgScore,COUNT(s_id) totalStudent from vichin_score group by c_id

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

 没有写出Sql语句。

select c.s_id,d.s_name from
    (
    select distinct a.s_id from    
        (select s_id,COUNT(c_id) cls from Vichin_score 
            where score<60
            group by s_id)a
            inner join 
            (select s_id,COUNT(c_id) cls from Vichin_score
            group by s_id)b
            on a.cls=b.cls and a.s_id=b.s_id
    )c
    inner join
    Vichin_student d
    on c.s_id=d.s_id

思路：

　　1、先得出成绩小于60分的同学

select s_id,COUNT(c_id) cls from Vichin_score 
where score<60
group by s_id

　　2、统计该同学总共学了几门课

select s_id,COUNT(c_id) cls from Vichin_score
group by s_id

　　3、如果第一条sql语句取出的cls（课程数）数量等于第二条sql语句取出的cls（课程数），就说明这个同学所有的课程都不及格。

select * from
(select s_id,COUNT(c_id) cls from Vichin_score 
            where score<60
            group by s_id)a
inner join
(select s_id,COUNT(c_id) cls from Vichin_score
            group by s_id)b
on a.cls=b.cls and a.s_id=b.s_id

 10、★查询没有学全所有课程的学生的学号、姓名。

    select * from(
        select s_id,COUNT(c_id) cls from Vichin_score group by s_id
        )a where a.cls<3

 

--1、先使用左连接查询，得出所有学生的考试信息
--2、再获取课程数量，并且使用统计函数count，统计所有学生的报考科目的数量，再比较学生报考科目数量与课程数量即可。
select a.s_id,a.s_name from
    (
    select st.s_id,st.s_name,sc.c_id from Vichin_student as st
    left join Vichin_score sc 
    on st.s_id=sc.s_id
    ) a  group by a.s_id,a.s_name having COUNT(c_id)<(select COUNT(distinct(c_id))from Vichin_course)

思路：不应该固定写死3那个数字，因为当后面课程数量发生变化的时候，sql语句就无效了。还有、王菊同学一门课都没有考，他的名字就完全不会出现在score表，在统计的时候就会漏掉。

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

select distinct s_id from Vichin_score where c_id  in(
select c_id from Vichin_score where s_id='01') and s_id !='01'

12、查询和“01”号同学所学课程完全相同的其他同学的学号。

--先找出01所选修的课程01，02，03，再使用in找出选修了这3门课程的同学。其中07同学之选修了2门，在选修课程的数量上是有问题的。
select s_id from Vichin_score where c_id in(
    select c_id from Vichin_score where s_id='01'
) and s_id!='01' group by s_id having COUNT(c_id)=(select COUNT(distinct c_id) from Vichin_score where s_id='01')

 

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

感觉我的写法不比视频中的写法差。

15、查询两门及以上不合格课程的同学的学号、姓名及其平均成绩。

select a.s_id,c.s_name,AVG(score) score from 
        (select s_id from Vichin_score where score<60 group by s_id having COUNT(c_id)>=2)a
        inner join
        Vichin_score b
        on a.s_id=b.s_id
        inner join
        Vichin_student c
        on c.s_id=a.s_id
        group by a.s_id,c.s_name

视频中的写法与我的写法大体相同。

16、检索“01”课程分数小于60，按分数降序排列的学生信息。

select b.s_id,score from Vichin_score a 
inner join
Vichin_student b
on a.s_id=b.s_id
where c_id='01' and score<60 
order by a.score desc

17、★按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。

 审题有误，所以就不展示我的写法。

select s_id,
MAX(case when c_id='01' then score else null end) '语文',
MAX(case when c_id='02' then score else null end) '数学',
MAX(case when c_id='03' then score else null end) '英语',
AVG(score) '平均成绩'
from Vichin_score
group by s_id
order by AVG(score) desc

思路：

　　　因为group by后面的字段需要与select后面的字段一样，所以将MAX函数去掉，group by就需要加上case when这段语句。很明显在group by 后面加上case语句去分组，是不符合逻辑的（不可能根据score去分组）。但是如果select的字段加上了聚合函数，那么就不需要出现在group by后面了。因为每个学生 “01”的课程只有1们，所以使用MAX函数也不会错。所以这里就使用MAX函数来解决问题。null值代表没有考过。

18、★★查询各科最高分、最低分和平均分：以如下形式显示：课程ID，课程名称、最高分、最低分、平均分，及格率，中等率，优良率，优秀率，及格为<=60,中等为70-80，优良为80-90，优秀为<=90。

select a.c_id,b.c_name,max(score) maxS ,min(score) minS,AVG(score) avgS, 
CONVERT(decimal,SUM(case when score>60 then 1 else 0 end))/COUNT(score) '及格率',
CONVERT(decimal,SUM(case when score>=70 and score<80 then 1 else 0 end))/COUNT(score) '中等率',
CONVERT(decimal,SUM(case when score>=80 and score<90 then 1 else 0 end))/COUNT(score) '优良率',
CONVERT(decimal,SUM(case when score>=90 then 1 else 0 end))/COUNT(score) '优秀率'
from Vichin_score a
inner join
Vichin_course b
on a.c_id=b.c_id
group by a.c_id,b.c_name

19、★按各科成绩进行排序，并显示排名（重点row_number）。

select ROW_NUMBER() over(order by num desc) num,a.s_id,a.c_id,a.score from(
select s_id,c_id,score,num=1 from Vichin_score
group by c_id,s_id,score)a
order by c_id,score desc

视频中没有讲解（my Sql中没有ROW_NUMBER 函数）。

20、查询学生的总成绩，并进行排名。

select ROW_NUMBER()over(order by a.totalScore desc) num,a.s_id,a.totalScore from(
select s_id,sum(score) totalScore from Vichin_score 
group by s_id)a
order by a.totalScore desc

与视频中的写法一致。（视频中没有使用ROW_NUMBER函数进行排名）。

21、查询不同老师所教不同课程的平均分，从高到低排序。

select a.c_id,b.c_name,AVG(score) avgScore from Vichin_score a
inner join
vichin_course b
on a.c_id=b.c_id
group by a.c_id,b.c_name
order by avgScore

与视频中的思路差不多。

22、★★查询所课程的成绩 第2名到第3名的学生信息以及课程成绩。

select * from
(
    select a.s_id,a.s_name,a.s_age,a.s_sex,b.c_id,score,ROW_NUMBER() over(partition by b.c_id order by b.score desc) num from Vichin_score b
    inner join
    Vichin_student a
    on a.s_id=b.s_id
)c
where c.num in(2,3)

我的思路是错的，所以就没贴出来。

思路：使用ROW_NUMBER() over(partition by b.c_id order by b.score desc) 给分数进行分组排序，分组字段是partition后面跟着的c_id。然后再使用where条件语句 in(2,3)就能得出结果了。

很久没有用到partition进行分组了，所以忘记了这个知识点。

23、★★使用分段（100-85）、（85-70）、（70-60），（<60） 来统计各科成绩，分别统计各分数段人数，课程ID和课程名称。

select b.c_id,b.c_name,
SUM(case when score<=100 and score>85 then 1 else 0 end) '85以上',
SUM(case when score<=85 and score>70 then 1 else 0 end) '85~70',
SUM(case when score<=70 and score>=60 then 1 else 0 end) '70~60',
SUM(case when score<60 then 1 else 0 end) '60以下'
from Vichin_score a
inner join
Vichin_course b
on a.c_id=b.c_id
group by b.c_id,b.c_name

感觉跟前面的一题思路相似，都是使用case when。

24、查询学生的平均成绩及其名次。

select s_id,AVG(score) avgScore,ROW_NUMBER() over(order by AVG(score) desc) num from Vichin_score
group by s_id

我的写法与视频中的写法一致。

25、★★查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）

select a.c_id,
Max(case when a.num=1 then a.score else null end) as '第一名',
Max(case when a.num=2 then a.score else null end) as '第二名',
Max(case when a.num=3 then a.score else null end) as '第三名'
 from
    (
    --下面这条子句就已经能够查询出结果了，但是横置过来的数据，增加上面那部分语句是将整个结果进行了转置
    select c_id,score,num,s_name from(
    select c_id,s_id,score,ROW_NUMBER() over(partition by c_id order by score) as num from Vichin_score)s
    inner join --这里可以用子查询,去将num为1，2，3的给过滤出来，但是使用关联查询，性能更好。where num in(1,2,3) 
    Vichin_student st
    on st.s_id=s.s_id    
    where s.num in(1,2,3)
)a
group by c_id

26、查询每门课程被选修的学生数。

select COUNT(s_id) '学生数量',c_id from Vichin_score group by c_id

视频中的写法是将课程的名称也显示出来，只要去inner join course表就能出来了。

27、查询出只有两门课程的全部学生的学号和姓名。

select COUNT(s.c_id) num ,s.s_id,st.s_name from Vichin_score s 
inner join 
vichin_student st
on st.s_id=s.s_id
group by s.s_id,st.s_name
having COUNT(s.c_id)=2

 28、查询男生、女生人数。

我的想法是使用 Sum(case when) 来实现，感觉没有视频中的写法好。

select COUNT(s_id),s_sex from vichin_student group by s_sex

29、查询名字中含有“风”字学生的信息。

select *from vichin_student where s_name like '%风%'

31、查询1990年出生的学生名单。

select * from vichin_student where s_age like '1990%'

--视频中写法
select * from Vichin_student where YEAR(s_age)=1990

32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。

select st.s_id,st.s_name,AVG(score) avgScore from Vichin_score s  
inner join
Vichin_student st
on s.s_id=st.s_id
group by st.s_id,st.s_name 
having AVG(score)>85

写法一致

33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列。

select c_id,AVG(score) from Vichin_score 
group by c_id
order by AVG(score),c_id desc

写法一致

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数。

select s.s_id,st.s_name,score from Vichin_course c
inner join
Vichin_score s
on c.c_id=s.c_id
inner join
Vichin_student st
on st.s_id=s.s_id
where c_name='数学' and s.score<60

思路：这道题目有两种实现方法。

　　我的想法是先对course 和 student 表使用inner join筛选一下，然后对筛选的结果使用子查询。

　　视频中的写法是对3个表直接使用inner join，这里因为数据量不是很大，所以直接对3个表使用inner join 是可以的。如果表中的数据量很大的时候，使用临时表或者子查询效果会好一些，特别是临时表。

35、★查询所有学生的课程及分数情况。

下面这是我写的，视频中的sql语句还将学生的姓名也查询了出来

select s_id, 
max(case when c_id='01' then score else null end)'语文',
max(case when c_id='02' then score else null end)'数学',
max(case when c_id='03' then score else null end)'英语'
from Vichin_score
group by s_id

1	01	90
	02	67
	03	46

如果不使用max函数，不使用group by，那么得到的结果是上面这样的一个表格。

当对课程编号01这一列进行判断的时候，第1行01符合条件，那么结果就是90，当进行到第2行的时候，发现值是02不是，那么结果会是null，第3行也是null。这些null值会呈现在查询结果中，是不符合预期的。

加了max之后，在查询结果中只会去取90这个数据，另外的2个null值是不会被选取的。

同理 当对编号02进行判断的时候，01和03是不会选取的。最终的结果是正确的，即：在判断01的时候，我只会取01所对应的值，02只会取02的值，03只会取03的值。

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数。

select st.s_name,s.score,c.c_name from Vichin_score s
inner join
Vichin_student st
on s.s_id=st.s_id
inner join
Vichin_course c
on c.c_id=s.c_id
where score>70

与视频中一致

37、查询不及格的课程并按课程号从大到小排列。

select * from Vichin_score where score<60 order by c_id

与视频一致

38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名。

select st.s_id,st.s_name,score from Vichin_score s
inner join
Vichin_student st
on s.s_id=st.s_id
where c_id='03' and score>=80

与视频一致

39、求每门课程的学生人数。

select c_id,COUNT(s_id)'人数' from Vichin_score
group by c_id

与视频一致

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

select top 1 st.s_name,s.s_id,score from Vichin_teacher t
inner join
Vichin_course c
on t.t_id=c.t_id
inner join 
Vichin_score s
on s.c_id=c.c_id
inner join
Vichin_student st
on st.s_id=s.s_id
where t_name='张三'
order by s.score desc

与视频一致

41、★查询不同课程（语、数、英）成绩相同的学生的学生编号、课程编号、学生成绩。

select s.s_id,s.c_id,s.score from
    ( select  s_id,score from Vichin_score
      group by s_id,score
      having count(c_id)=3
    )a
inner join
Vichin_score s
on a.s_id=s.s_id

不适用聚合函数，也能查询到相应结果。

select distinct a.s_id,a.c_id,a.score
from Vichin_score a
inner join
Vichin_score b
on a.s_id=b.s_id
where a.score=b.score and a.c_id!=b.c_id

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

select c_id,COUNT(s_id)'人数' from Vichin_score
group by c_id
having COUNT(s_id)>5
order by COUNT(s_id),c_id

与视频一致

44、检索至少选修两门课程的学生学号。

select s_id,count(c_id)'课程数' from Vichin_score
group by s_id
having count(c_id)>=2

45、查询选修了全部课程的学生信息。

select s_id,COUNT(c_id) from Vichin_score
group by s_id
having COUNT(c_id)=(select COUNT(c_id) from Vichin_course)

46、查询各学生的年龄。

select s_name,DATEDIFF(YY,s_age,GETDATE()) '年龄' from Vichin_student

47、查询本月过生日的学生。

select s_name from Vichin_student
where month(s_age)=datepart(month,getdate())

48、查询两门以上不及格课程的同学的学号及其平均成绩。

select s_id,AVG(score)'平均成绩' from Vichin_score
where score<60
group by s_id
having COUNT(c_id)>=2

49、查询下月过生日的同学。

select s_name from Vichin_student
where month(s_age)=datepart(month,getdate())+1

50、查询本周过生日的同学。

 不会...Sql中没有week函数，mySql中有这个函数。