SQL45题

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

解题思路：重点找SID， 使用自连接的方式将成绩做比较得到SID后，
放入子查询中

select * 
from student
where Sid in (
    select a.SID 
    from SC a inner join SC b 
    on A.SID =B.SID where b.CID= 01 AND A.CID=02 AND B.SCORE> a.score);

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

解题思路：和第一题的不同是不需要比较01 和02 课程得分情况，但是具体得分都要列出来

select * 
from student s
inner join (
    select A.*,b.cid cid_01,b.score score_01
    from SC a inner join SC b 
    on A.SID =B.SID where b.CID= 01 AND A.CID=02) c
on s.SID= C.SID;

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

解题思路：显示null值 考察外连接 left/right join

select * 
from student s
inner join (
    select A.SID,b.cid cid_all,b.score score_all
    from SC a left join SC b 
    on A.SID =B.SID and b.CID= 02 
    where a.CID=01
) c
on s.SID= C.SID;

1.3查询不存在" 01 "课程但存在" 02 "课程的情况

解题思路：不要考虑太复杂的情况，cid=01的SID剔除掉 即可

select * 
from sc a 
where sid not in(select sid from sc where cid='01')
and cid='02'

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

解题思路：按照学生编号分组后，求均值，having语句过滤>=60分的学生ID 同学生表innerjoin

select a.sid,a.sname,b.avg_score from student a inner join 
(select SID, avg(score) as avg_score 
from sc a 
group by Sid having avg(score) >=60) b
on a.sid = b.sid;

3.查询在 SC 表存在成绩的学生信息

解题思路：distinct SC表的SID innerjoin表student，得到学生信息

select a.* from student a inner join 
(select distinct sid 
from sc ) b
on a.sid = b.sid;

4、询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

解题思路：表student left join SC表（sc表 按照SID聚合，得到选课门数和总成绩），得到学生信息

select a.* ,b.course_num,b.sum_score  from student a left join 
(select  sid,count(*) course_num, sum(score) sum_score 
from sc group by sid) b
on a.sid = b.sid;

4.1 查有成绩的学生信息

解题思路：表student inner join SC表（sc表 按照SID聚合，得到选课门数和总成绩），得到学生信息

select a.* ,b.course_num,b.sum_score  from student a inner join 
(select  sid,count(*) course_num, sum(score) sum_score 
from sc group by sid) b
on a.sid = b.sid;

5.查询「李」姓老师的数量

解题思路：通配符的使用 teacher表

select count(*) from teacher where tname like '李%';

6.查询学过「张三」老师授课的同学的信息

解题思路：张三老师授课ID —— 选学张三老师课的学生ID SID——inner join 学生表

select * from student
where sid 
    in (
    select SID 
    from sc 
    where cid in (
                select cid
                from course 
                where tid 
                    in (select tid 
                        from teacher where tname='张三')));

7.查询没有学全所有课程的同学的信息

解题思路：所有课程数，SID聚合后count数量<课程数的SID与student表innerjoin

select * from student s 
inner join (
            select SID from sc group by sid having count(*)<(select count(*) from course)) b
on s.sid= b.sid

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

解题思路： 01号同学的cid，与 01号同学的cid 有相同的学生的SID（distinct），与学生表 inner join

select s.* from student s inner join 
(select distinct sid from sc where cid in (select cid from sc where sid=01)) a
 on s.sid= a.sid;

9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

解题思路： not in的用法！！ 倒推！！
选取学生ID 条件：CID 不等于sid=01 的任一个CID的学生ID不选，
得到的SID的课程数量要和SID=01的学生的课程数量相等

select b.* from (
                    select sc.* from sc inner join 
                                          (select sid from sc
                                           group by sid 
                                           having count(*)= (select count(*) from sc where sid=01)) a
                   on a. sid = sc.sid) b  
inner join 
(select distinct sid from sc where sid not in 
 (select sid from sc where cid not in (select cid from sc where sid='01')) and sid!='01') c
 on b.sid=c.sid;

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

解题思路： not in的用法！！ 倒推！！
选取学生ID 条件：只要学过张三老师的任意一门课的学生，全都不选

 select sname 
 from student 
 where sid not in(
    select sid 
    from sc
    where cid in(
        select cid 
        from course
        where tid = (select tid from teacher where tname='张三')));;

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

解题思路： 选取学生ID 条件：两门不及格

 select * from student s
inner join (select SId,
                    sum(case when score-60<0 then 1 end)  as num ,   
                    avg(score) as avg_score 
              from sc group by sid having num>=2) a 
on s.SID= A.SID:

12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

解题思路： 选取学生ID 条件：课程01，分数<60

 select * from student s
inner join (select * from sc where cid = 01 and score<60) b 
on s.SID= b.SID
order by score DESC;

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

解题思路：

select * from sc inner join (select sid,
 avg(score) avg_score from sc group by sid) a 
 on sc.SID= A.SID 
 order by a.avg_score DESC;

14.查询各科成绩最高分、最低分和平均分： 以如下形式显示：

课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率；
优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select cid ,max(score) max_score,min(score) min_score,avg(score) avg_score,
sum(case when score>60 then 1 end)/count(*) '及格率',
sum(case when score>=70  and score<80 then 1 end)/count(*) '中等率',
sum(case when score>=80  and score<90 then 1 end)/count(*) '优良率',
sum(case when score>=90 then 1 end)/count(*) '优秀率'
from sc group by cid
order by count(*) DESC, cid;

15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select sc.*, @rank:=@rank+1 as ord 
from sc, (select @rank:=0) a
order by score;

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

select sc.*, case when @score=score then @rank
                when @score:=score then @rank:=@rank+1 end
         as ord 
from sc, (select @rank:=0 , @score:=null) a
order by score;

16、查询学生的总成绩，并进行排名，总分重复时保留名次空缺 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

select a.*,@rank:=@rank+1 as ord 
from (select SID, sum(score) as sum_score
        from sc group by sid
        order by sum_score DESC) a ,
(select @rank:=0 ) b;

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

select a.*, case when @score=sum_score then @rank
                when @score:=sum_score then @rank:=@rank+1 end
                as ord from (select SID, sum(score) as sum_score 
from sc group by sid
order by sum_score DESC) a ,(select @rank:=0 , @score:=null) b;

17、统计各科成绩各分数段人数：

课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

select cname,a.* from course c
inner join 
(select cid ,sum(case when score>85  and score<=100 then 1 end) '[100-85]人数',
sum(case when score>85  and score<=100 then 1 end)/count(*) '[100-85]人数占比',
sum(case when score>70 and score<=85 then 1 end) '[85-70]人数',
sum(case when score>70 and score<=85 then 1 end)/count(*) '[85-70]人数占比',
sum(case when score>60 and score<=70 then 1 end) '[70-60]人数',
sum(case when score>60 and score<=70 then 1 end)/count(*) '[70-60]人数占比',
sum(case when score<60 then 1 end) '[60-0]人数',
sum(case when score<60 then 1 end)/count(*) '[60-0]人数占比'
from sc group by cid) a on c.cid=a.cid;

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

解题思路：关联子查询 !!
a和B相关联，当b.score>a.score时 count计数，但是count不能大于3，只能<=3

select 
*
from sc a
where (select count(1) from sc b 
        where a.cid=b.cid and b.score>a.score)<3 
order by cid, score DESC;

19 .查询每门课程被选修的学生数

select cid, count(*) from sc group by cid;

20 查询出只选修两门课程的学生学号和姓名

select s.sname,s.sid from student s 
inner join (select sid from sc group by sid having count(*)=2) a
on s.sid =a.sid;

21 查询男生、女生人数

select ssex, count(*) from student group by ssex ;

22 查询名字中含有「风」字的学生信息

select * from student where sname like '%风%';
select * from student where sname regexp '风';

23 查询同名同性学生名单，并统计同名人数

select a.sname ,count(1) from student a where sname in (
  select sname from student b where b.sid!=a.sid and 
  a.sname=b.sname ) 
group by a.sname ;

24 查询 1990 年出生的学生名单

select * from student where year(sage)=1990;

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

select cid,avg(score) avg_score from sc 
group by cid order by avg_score DESC,cid;

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

select a.*,s.sname from student s inner join 
(select sid,avg(score) avg_score from sc group by sid having 
avg(score)>85) a
on s.sid=a.sid;

27 .查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

select a.*,s.sname from student s inner join 
  (select sid,score from sc where cid in
   (select cid from course where cname='数学') 
  and score<60) a
on s.sid=a.sid;

28 .查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select s.sid,s.sname,sc.* from student s left join sc
on s.sid =sc.sid;

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

select a.*,s.sname from student s inner join 
(select * from sc where sid not in (select sid from sc where score <70)) a
on s.sid=a.sid;

30 30.查询不及格的课程

select* from course where cid in (select distinct cid from sc where 
score<60);

31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

select a.sid,s.sname from student s inner join 
(select sid from sc where cid=01 and score>80) a
on s.sid=a.sid;
 select * from sc;

32 假设成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select sid,score from sc where cid in 
(select cid from course where tid in 
  (select tid from teacher where tname='张三'))
order by score desc limit 1;

34 假设成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select a.* from 
(select * from sc where cid in 
  (select cid from course where tid in 
    (select tid from teacher where tname='张三'))) a
where
 (select count(1) from sc b
    where a.cid= b.cid and b.score > a.score)<1;

35 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select s1.*,s.sname from student s inner join 
(select distinct a.sid,a.cid,a.score from sc a 
inner join sc b 
on a.sid!=b.sid and a.cid!=b.cid and a.score =b.score) s1
on s1.sid =s.sid;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容