SQL练习题 51题 一刷

 

Student表：

select * from student;

 

课程表Course：

select * from course;

 

教师表teacher：

select * from teacher;

 

 

成绩表Score：

select * from sc;

 

 

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

select c.*, a.score

from sc a, sc b, student c

where a.sid=b.sid and c.sid=a.sid and a.cid=1 and b.cid=2 and a.score>b.score;  #笛卡尔积形式的连接，不能加上a.cid=b.cid

 

 

 

 2、查询同时选" 01 "课程和" 02 "课程的学生

select a.sid

from sc a, sc b

where a.sid=b.sid and a.cid=1 and b.cid=2;

 

 

 

3、查询选修了" 01 "课程但可能没有选修" 02 "课程的学生情况(不存在时显示为 null )

 方法一：

select *   from 
   (select * from sc where cid=1) a 
left join 
   (select * from sc where cid=2) b
on a.sid=b.sid;

 

 

方法二：

select *
from student a
left join SC b on a.SID = b.SID and b.CID = '01'
left join SC c on a.SID = c.SID and c.CID = '02'
where  b.score is not null; 

运行结果：没有where子句时，中间表的连接结果如下图，题目要求选修了1号课程，可能没有选修2号课程，所以根据下图的结果，可以得知，只要把选修了1号课程的同学查询出来即可，即where子句中的条件为： b.score is not null 

 

 

 

方法三：  这种方法和方法二相同，只是where 子句有所不同，如果想使用方法三，where子句的条件需要修改为b.score>= isnull(c.score)，

原因是b.score>isnull(c.score)，

假定c.score是null，isnull(c.score)返回的结果为1，那么当b.score=1时，此时b.score>isnull(c.score)不成立，那么这一条记录就会被过滤掉

select a.* , b.score  b_score ,c.score  c_score from Student a

left join SC b on a.SID = b.SID and b.CID = '01'

left join SC c on a.SID = c.SID and c.CID = '02'

where  b.score > = isnull(c.score);   #,注意，是>=， 这一句，目的是为了筛选出b表中不为null的成绩，因为isnull(c.score)返回结果只有0和1两种情况

 

思路解析：

select a.* , b.score  b_score ,c.score  c_score from Student a

left join SC b on a.SID = b.SID and b.CID = '01'

left join SC c on a.SID = c.SID and c.CID = '02';

 

 

isnull( )使用说明：

  

 

 

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

 思路：选修2课程的id号课程中，剔除选修了1课程的id 号

select  distinct sid
from sc
where cid=2 and sid not in 
(select  sid
from sc 
where cid=1
);

 

 

 

 

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

select student.sid, student.sname, count(sc.cid) as "选课总数", sum(sc.score) as  "总成绩"

from student left join  sc  on student.sid=sc.sid

group by student.sid  ;

 

 

8、查询「李」姓老师的数量

#（例子：查询以s开头的老师数量）

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

 

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

（例子：查询学过 jane 老师授课的学生信息）

select student.*
from student, sc, course, teacher
where student.sid=sc.sid and sc.cid=course.cid and  course.tid=teacher.tid and teacher.tname='jane';

 

 

中间表：

select *
from student, sc, course, teacher
where student.sid=sc.sid and sc.cid=course.cid and  course.tid=teacher.tid ;

 

 

 

10、查询没有学全所有课程的同学的信息

 

select  student.*
from sc , student
where student.sid=sc.sid
group by  sc.sid
having count(sc.cid)<(select count(*) from course);  # 查询课程表course中有多少门课

 

 

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

ps:把1号同学排除

 

select  distinct student.*

from student, sc
where student.sid=sc.sid and  sc.sid!=1 and  sc.cid in (
select distinct cid from sc where sid=1);

 

 

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

 正确代码：

 此时SC表如图：可以看到，将sid分组的情况下，除6号同学外，每个同学的成绩记录都是按照cid增序的顺序插入的；只有6号同学的课程插入顺序是1,3,2

 

 

 上面这张表不太容易看，对sid按增序排序后，如下图，查看每个同学cid插入的顺序如何，注意：只对sid排序，对cid排序后就不能看出来每个同学cid插入的顺序如何了。

排序后，可以更清楚的看到只有6号同学的课程插入顺序是乱的，不是按照cid增序插入的。

 

 

 

 

 

完全正确的代码：

PS：不仅使用了group_concat( )函数，还对group_concat( )函数中的参数进行了排序。

select student.*
from 
(select sid, group_concat(cid order by cid) as  tt from sc  where sid=1  group by sid  order by cid ) a   #1号同学选修的课程
        #这里对cid进行了排序，因为group_concat()函数连接cid的值时，默认是按照该值在SC表中的插入顺序进行连接的
left join 

( select sid, group_concat(cid order by cid) as rr  from sc  where sid!=1  group by sid order by cid ) b    on a.tt=b.rr  
# #除1号同学外，其他同学选修的课程，将两张表进行连接，按照选修课程相同为条件进行连接

join student on b.sid=student.sid;

运行结果：

 

 

 

下面代码也有漏洞：group _concat( cid)中不对cid排序，则返回结果如下：

select student.*
from 
(select sid, group_concat(cid ) as  tt from sc  where sid=1  group by sid  order by cid ) a 
       
left join 

( select sid, group_concat(cid ) as rr  from sc  where sid!=1  group by sid order by cid ) b    on a.tt=b.rr  

join student on b.sid=student.sid;

运行结果：只查询出了4号同学

 

 

必须要排序的原因： 可以看到，1，4，6号同学选的课程都一样，只是6号同学课程插入时是乱序的，结果造成下面这种情况，连接的结果也是按插入顺序连接的，这样，系统认为1号同学和6号同学的课程是不一样的，所以group_concat( cid )中要对cid排序，即使用group_concat( cid order by cid)

select sid, group_concat(cid) as '选修课程'  from sc  group by sid;

运行结果：

 

 

 

总结：
  按同学分组后，如果每个同学的课程插入记录是顺序增加或顺序降低的，则使用group_concat( )函数时不对参数进行排序，没有问题。

  但如果每个同学的课程插入记录是乱序的，则使用group_concat( )函数时必须对参数进行排序。

 

 

错误代码： 下面的代码看似正确，其实有漏洞，很有迷惑性

 

分析： 假定一共有4门课，课程号cid=1,2,3,4；    SC表如下，其中1同学、4同学、6同学都选修了1,2,3这三门课，不存在选修了全部课程的同学，这种情况下，使用下面代码解决这道题目没有问题，但是

 

 

 假定表的情况按上述情况，运行下面代码，返回的结果看似是正确的：

select  student.*
from   sc ,student
where  student.sid=sc.sid  and sc.cid in   ( select cid from sc where sid=1)  and    student.sid!=1
group by sc.sid
having count(*) in (select count(*) from sc where sid=1);

运行结果：

 

 

 

举一个例子揭示上述代码的漏洞：

在上面的那张表中，插入了一条记录，结果就是6号同学选修了全部课程，那么此时，只有4号同学和1号同学选修的课程完全相同

 

 同样运行上面代码，查看运行结果：

 

 

select  student.*
from   sc ,student
where  student.sid=sc.sid  and sc.cid in   ( select cid from sc where sid=1)  and    student.sid!=1
group by sc.sid
having count(*) in (select count(*) from sc where sid=1);

运行结果： 仍然把6号同学给选了出来，此时，正确的结果应当只有第一条

 

 

 

 

 

 

 

 

 

 

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

（例子：smith老师）

思路：先把学过smith老师课程的学号选出来，然后从student表中剔除这些学号

 

select student.*
from student
where sid not in (
   select sid from sc ,course, teacher  
   where sc.cid=course.cid and course.tid=teacher.tid and  teacher.tname='smith');

 

 

 

 

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

 

A：查询出两门以上不及格的学号

select sid
from sc
where score<60
group by sid
having  count(*)>=2;

 

A：完整结果

 个人理解：我理解的平均成绩某个同学所有选修课程的平均成绩，而不是不及格课程的平均成绩。

例如，2号同学选了3门课，有2门不及格，平均成绩是指3门的平均成绩，而不是2门的。

 

select student.sid, student.sname, avg(sc.score)
from (student join sc on student.sid=sc.sid )  join   
(
select sc.sid as esid
from sc
where sc.score<60
group by sc.sid
having  count(*)>=2) a
on student.sid=a.esid 
group by student.sid
order by student.sid;

运行结果：

 

 

 

 

 

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

 

select student.*
from sc,student
where cid=1 and score<60 and sc.sid=student.sid
order by score desc;

 

 

 

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

select sc.*, a.avgscore
from sc left join (select sid, avg(score) as avgscore from sc group by sid) a on sc.sid=a.sid
order by a.avgscore desc;

 

 

 

 

 

17、查询各科成绩最高分、最低分和平均分

select cid, max(score), min(score), avg(score)
from sc
group by cid;

 

 

 

18、以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select sc.cid, course.cname,count(sc.sid) as '选修人数', max(sc.score), min(score), avg(score),
 (sum(case when score>=60 then 1 else 0 end)/count(sc.sid)) as '及格率',
 (sum(case when score>=70 and score<80 then 1 else 0 end) /count(sc.sid)) as '中等率',
 (sum(case when score>=80 and score<90 then 1 else 0 end)/count(sc.sid)) as '优良率',
 (sum(case when score>=90 then 1 else 0 end)/count(sc.sid)) as '优秀率'
 
from sc, course
where sc.cid=course.cid
group by sc.cid
order by count(sc.sid) desc, cid ; #按选修人数降序排列，如果人数相同，按课程号cid升序排列

 

 

 

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

 思路：这里不能使用a.score<=b.score

select  a.sid ,a.cid, a.score, count(a.score<b.score)+1  as rank
from sc a  left join sc b  on a.cid=b.cid  and a.score<b.score
group by a.cid, a.sid
order by a.cid, a.score desc;

 

 

 

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

 思路：看某个分数在所有分数中，小于等于的有几个

select  a.sid ,a.cid, a.score, count( distinct b.score)+1 as rank
from sc a left join   sc b  on a.cid=b.cid  and a.score<b.score
group by a.cid, a.sid
order by a.cid, a.score desc;

或者   如果是a.score<=b.score，则count( distinct b.score) 即可，不用加1

select  a.sid ,a.cid, a.score, count( distinct b.score) as rank
from sc a left join   sc b  on a.cid=b.cid  and a.score<=b.score
group by a.cid, a.sid
order by a.cid, a.score desc;

 

 

 

 

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

 

select a.* ,count(a.cj<b.cj)+1 as rank

from 
   (select sid , sum(score)  as cj  from sc  group by sid) a
left join 
   ( select sid, sum(score) as cj from sc group by sid) b
on a.cj<b.cj

group by a.sid
order by a.cj desc
;

 

 

 

 

 

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

 

select a.* ,count(distinct b.cj)+1 as rank

from 
   (select sid , sum(score)  as cj  from sc  group by sid) a
left join 
   ( select sid, sum(score) as cj from sc group by sid) b
on a.cj<b.cj

group by a.sid
order by a.cj desc
;

 

 

 

 

 

 23、统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

 

select sc.cid,cname,count(sid) as '总人数',
   concat(round(sum(case when score>=0 and score<60 then 1 else 0 end) /count(sid)*100,2),'%') as '0-60',
   concat(round(sum(case when score>=60 and score<70 then 1 else 0 end)/count(sid)*100,2),"%")  as '60-70',
   concat(round(sum(case when score>=70 and score<85 then 1 else 0 end)/count(sid)*100,2),"%") as '70-85',
   concat(round(sum(case when score<=100 and score>=85 then 1 else 0 end )/count(*)*100,2),'%') as '85-100'


from sc join course on sc.cid=course.cid 
group by cid;

 

 

 

 

 

24、查询各科成绩前三名的记录

 思路：先将各科成绩进行排序（这里应该是不保留名次空缺），然后从成绩表SC中选出排名<=3的记录

select * from 
(
select a.*, count(distinct b.score) +1 as rank
from  sc a
 left join sc b
on a.cid=b.cid and a.score<b.score
group by a.cid, a.sid
order by a.cid, a.score desc) c
where c.rank<=3
;

 

 

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

select cid, count(*) as '选修人数' 
from sc
group by cid;

 

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

select sc.sid, student.sname
from student, sc
where student.sid=sc.sid
group by sc.sid
having count(sc.cid)=2;

 

27、查询男生、女生人数

select ssex as '性别' , count(*) as '人数'
from student
group by ssex;

 

 

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

select * from student  where sname like '%m%'; #这个是查询姓名含有m的学生信息，m在开头或者中间位置都可以

select * from student where sname like 'm%';  #这个是查询姓名以m开头的学生信息

 

 

29、查询同名同姓学生名单，并统计同名人数

 

select sname , count(*) as '人数'
from student
group by sname;

 

 

 

30、查询 1990 年出生的学生名单

 

select * from student where sage like "1990%" ;

 

 

 

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

select cid, avg(score) as '平均成绩'
from sc
group by cid
order by avg(score) desc, cid;

 

 

 

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

 

select student.sid, student.sname, a.pj
from student join 

(select sid, avg(score) as 'pj'
from sc
group by sid
having avg(score)>=85) a

on student.sid=a.sid ;

 

 

 

 

 

 

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

 

select student.sname, score

from student, sc, course
where student.sid=sc.sid and sc.cid=course.cid and score<60 and cname='math';

 

 

 

 

 

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

select  student.sid,student.sname, sc.cid,sc.score
from student left join sc on student.sid=sc.sid
order by student.sid;

 

1	mark	1	80
1	mark	3	95
1	mark	2	90
2	james	2	39
2	james	3	39
2	james	4	94
3	michael	1	93
3	michael	3	87
4	jack	2	74
4	jack	1	89
4	jack	3	83
5	mei	1	76
5	mei	2	47
6	lily	4	44
6	lily	1	41
6	lily	3	83
7	nana	3	83
7	nana	2	89
8	mifei	NULL	NULL
9	cry	NULL	NULL

 

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

select sname, cname ,score
from (sc join student on sc.sid=student.sid) join course on sc.cid=course.cid
where  score>70
order by sc.sid;

 

 

 

 

36、查询不及格的课程

select  course.cname, sc.score
from sc join course on sc.cid=course.cid
where sc.score<60;

 

 

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

 

select student.sid, student.sname
from student ,sc
where student.sid=sc.sid  and sc.cid=1 and sc.score>80
order by cid ;

 

 

 

 

 

38、求每门课程的学生人数

select cid, count(*) as '该课程的选修人数'
from sc
group by cid;

 

 

 

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

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

PS：这两道题，使用下面同样的代码都能运行出正确结果

 

 

select student.*, a.score
from sc a left join sc b on a.cid=b.cid and a.score<b.score   #对每门课程下的学生成绩排名

join course on a.cid=course.cid   
join teacher on course.tid=teacher.tid and tname='张三'  #这一行和上一行，是为了筛选张三老师教的课
join student on a.sid=student.sid  # 获取学生信息

group by a.cid, a.sid  
having count( a.score< b.score)=0   #筛选名次rank=1, count(a.score<b.score)+1=1，两端减去1，简化为count(a.score<b.score)=0

#上面这一行having 子句，这种格式也可以：having count( distinct b.score)=0 ,使用distinct
;

 

 

 

 

 

 

 

 

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

 

select sc.cid , sc.sid, sc.score
from sc join  (select cid, score from sc  group by cid, score having count(sid)>=2) a 
        on sc.cid=a.cid and sc.score=a.score
order by cid ;

 

或

select    sc.sid, sc.cid , sc.score
from sc join  (select sid, score from sc  group by sid, score having count(cid)>=2) a 
        on sc.sid=a.sid and sc.score=a.score
order by sid;

 

 

 

 

 

42、查询每门功成绩最好的前两名

思路：先对每门课程下的成绩排序，然后筛选排名<=2的

select a.cid, a.sid, sname, a.score,  count(distinct b.score)+1 as 'rank'
from ( sc a left join sc b on a.cid=b.cid and a.score<b.score) join student on a.sid=student.sid

group by a.cid, a.sid
having rank<=2
order by cid
;

 

 

 

43、统计每门课程的学生选修人数（超过 5 人的课程才统计）

select sc.cid,cname,count(sid) as '选修人数'
from sc join course on sc.cid=course.cid
group by sc.cid
having count(sid)>4;

 

 

 

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

select sid
from sc
group by sid
having count(cid)>=2;

 

 

 

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

select student.*
from sc join student on sc.sid=student.sid
group by sid 
having count(*) in (

select count(*)  from course);

 

 

计算年龄，只按年份来算和按月日来算的区别

46、查询各学生的年龄，只按年份来算

select student.sid, student.sname,student.ssex, year(now())-year(student.sage)  as 'age'
from student;

 

47、按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

 

select student.sid, student.sname,student.ssex, sage,

timestampdiff(year,sage,now()) as '按月日计算',  # 出生月日< 当前日期的月日时，年龄会减一  ,该题目功能是通过这一句实现的，下一句只是为了对比说明两者之间的差别
year(now())-year(sage) as '按年份计算'  
from student; 

 

 

 

 

48、查询本周过生日的学生

 

select * 
from student 
where week(concat_ws('-',year(now()),date_format(sborn,'%m-%d')))=week(now());

 

 

 

 

49、查询下周过生日的学生

 

select * 
from student 
where week(concat_ws('-',year(now()),date_format(sborn,'%m-%d')))=week(now())+1;

 

 

 

50、查询本月过生日的学生

select *
from student
where month(student.sage)=month(now());

 

 

51、查询下月过生日的学生

select *
from student
where month(student.sage)=month(now())+1;