sql经典练习题

网上流传已久的sql语法练习题,找来练练。

相关表结构

course表

CId Cname TId
01 语文 02
02 数学 01
03 英语 03

sc表

skey SId CId score
1 01 01 80.0
2 01 02 90.0
3 01 03 99.0
4 02 01 70.0
5 02 02 60.0
6 02 03 80.0
7 03 01 80.0
8 03 02 80.0
9 03 03 80.0
10 04 01 50.0
11 04 02 30.0
12 04 03 20.0
13 05 01 76.0
14 05 02 87.0
15 06 01 31.0
16 06 03 34.0
17 07 02 89.0
18 07 03 98.0

teacher表

TId Tname
01 张三
02 李四
03 王五

student表

SId Sname Sage Ssex
01 赵雷 1990-01-01 00:00:00
02 钱电 1990-12-21 00:00:00
03 孙风 1990-12-20 00:00:00
04 李云 1990-12-06 00:00:00
05 周梅 1991-12-01 00:00:00
06 吴兰 1992-01-01 00:00:00
07 郑竹 1989-01-01 00:00:00
09 张三 2017-12-20 00:00:00
10 李四 2017-12-25 00:00:00
11 李四 2012-06-06 00:00:00
12 赵六 2013-06-13 00:00:00
13 孙七 2014-06-01 00:00:00

习题

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

1
2
3
4
5
select student.sname,a.sid,a.score
from (select sc.sid,sc.cid,sc.score from sc where cid = '01') a,(select sc.sid,sc.cid,sc.score from sc where cid = '02') b,student

where a.score > b.score and a.sid = b.sid and student.sid = a.sid
;

2、查询同时存在” 01 “课程和” 02 “课程的情况

1
2
3
4
5
6
select student.*
from student,sc
where student.sid = sc.sid and
'01' in (select cid from sc where student.sid = sc.sid group by sid) and '02' in (select cid from sc where student.sid = sc.sid group by sid)
group by sc.sid
;

3、查询存在” 01 “课程但可能不存在” 02 “课程的情况

1
select * from sc where cid = '01';

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

1
2
3
4
5
6
select student.*
from student,sc
where student.sid = sc.sid and
'01' not in (select cid from sc where student.sid = sc.sid group by sid) and '02' in (select cid from sc where student.sid = sc.sid group by sid)
group by sc.sid
;

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

1
2
3
4
5
6
select student.sid,student.sname,avg(sc.score)
from student,sc
where student.sid = sc.sid
group by sc.sid
having avg(sc.score) >= 60
;

首先从sc表中以sid分组,从而找出每位同学的平均成绩,平局成绩大于等于60分的就可以与student表进行连接组成一条新纪录,同时会将该同学的平均成绩求出来。

6、查询在 SC 表存在成绩的学生信息

1
2
3
select distinct student.* 
from student,sc
where student.sid = sc.sid and sc.score is not null;

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

1
2
3
4
5
select student.sid 学号,student.sname 姓名,count(sc.cid) 选课总数,sum(sc.score) 总成绩
from student
left join sc on(student.sid = sc.sid)
group by student.sid
;

左外连接加以sid分组。

8、查有成绩的学生信息

1
2
3
select distinct student.* 
from student,sc
where student.sid = sc.sid and sc.score is not null;

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

1
select count(tid) from teacher where tname like "李%";

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

1
2
3
4
5
6
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 = '张三'

从teacher中获取张三老师的tid,之后再从course中找到他教学的课程id,这样就可以知道cid,借助sc.cid和course.cid将这两张表连接起来,又借助student.sid和sc.sid将它们二者连接起来查询。

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

1
2
3
4
5
6
7
8
9
select student.*
from student,sc
where student.sid = sc.sid
group by sc.sid
having count(sc.cid) < (
select count(distinct cid)
from course
)
;

在sc表中以sid分组,统计出每组选修的课程数,这也就是每个学生选修的课程。判断哪些同学选修的课程少于course表中的课程总数,将student表和sc表连接起来输出即可。

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

1
2
3
4
5
select distinct student.*
from student,sc
where student.sid = sc.sid and
sc.cid in (select cid from sc where sid = '01')
;

13、查询和” 01 “号的同学学习的课程完全相同的其他同学的信息

1
2
3
4
5
6
7
8
9
select student.* from student where student.sid  in
(
select sid from sc where sid not in
(select sid from sc where cid not in
(select cid from sc where sid = '01'))
group by sid
having count(*) = (select count(*) from sc where sid = '01') and sid != '01'
)
;

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

1
2
3
4
5
6
7
8
select student.sname from student where sid in
(
select sid from sc where sid not in
(select sid from sc where cid in
(select cid from course where tid =
(select tid from teacher where tname = '张三')))
)
;

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

1
2
3
4
5
6
select student.sid,student.sname,avg(sc.score)
from student,sc sc1
group by sc1.sid
having count(
select cid from sc sc2 where score < 60) >=2
;

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

1
2
3
4
5
6
7
select student.*
from student,sc
where student.sid = sc.sid and
sc.cid = '01'
and sc.score < 60
order by sc.score desc
;

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

1
2
3
4
5
6
select student.sid 学号,student.sname 姓名,sum(sc.score) 总成绩,avg(sc.score) 平均成绩
from student,sc
where student.sid = sc.sid
group by sc.sid
order by avg(sc.score) desc
;

18、查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格为:>=60,中等为:70-80,优良为:80-90,优秀为:>=90。 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

1
2
3
4
5
6
7
8
9
10
11
select sc.cid ID,course.cname 课程名,max(sc.score) 最高分,min(sc.score) 最低分,avg(sc.score) 平均分,count(sc.sid) 人数,
sum(case when sc.score between 0 and 59 then 1 else 0 end) / count(sc.sid) 不及格率,
sum(case when sc.score between 60 and 69 then 1 else 0 end) / count(sc.sid) 及格率,
sum(case when sc.score between 70 and 79 then 1 else 0 end) / count(sc.sid) 中等率,
sum(case when sc.score between 80 and 89 then 1 else 0 end) / count(sc.sid) 优良率,
sum(case when sc.score between 90 and 100 then 1 else 0 end) / count(sc.sid) 优秀率率
from sc,course
where sc.cid = course.cid
group by sc.cid
order by count(sc.sid)
;

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

1
2
3
4
5
select a.cid,a.sid,a.score,count(b.sid) + 1
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,count(b.sid) + 1
;

sc表自身连接。首先可以明确a表连接b表是以相等的cid为条件,那么得到的新表以相同cid,sid为条件分组,这样每一个分组是一个同学对应多个成绩比他高的同学,统计成绩高的同学的个数即可。

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

1
2


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

1
2
3
4
5
6
select student.*,sum(sc.score)
from student,sc
where student.sid = sc.sid
group by sc.sid
order by sum(sc.score) desc
;

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

1
2


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

1
2
3
4
5
6
7
8
9
select sc.cid,course.cname,count(distinct sc.sid) 人数,
sum(case when sc.score between 85 and 100 then 1 else 0 end)/count(distinct sc.sid) as '[85-100]',
sum(case when sc.score between 70 and 84 then 1 else 0 end)/count(distinct sc.sid) as '[70-84]',
sum(case when sc.score between 60 and 69 then 1 else 0 end)/count(distinct sc.sid) as '[60-69]',
sum(case when sc.score between 0 and 59 then 1 else 0 end)/count(distinct sc.sid) as '[0-59]'
from sc,course
where sc.cid = course.cid
group by sc.cid
;

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

1
2
3
4
5
6
7
8
select a.cid,a.sid,a.score,count(b.sid) + 1
from sc a
left join sc b on (a.cid = b.cid and a.score < b.score)
group by a.sid,a.cid
having count(b.sid) < 3
order by a.cid,count(b.sid) + 1

;

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

1
2
3
4
select course.cname 课程名称,count(sc.cid) 人数 
from course,sc
where sc.cid = course.cid
group by sc.cid;

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

1
2
3
4
5
6
select student.sid,student.sname
from student,sc
where student.sid = sc.sid
group by sc.sid
having count(sc.cid) = 2
;

在sc表中以sid作为分组条件,将每位同学选修的课程数找出来,挑选满足条件的与student表进行连接就可以了。

27、查询男生、女生人数

1
select ssex,count(sid) from student group by ssex;

group by 将查询结果按某一列或多列的值分组,值相等的为一组。在这题中,Ssex就是参照列了。
此外,group by 还可搭配having短语指定筛选条件。

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

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

29、查询同名同性学生名单,并统计同名人数

1
2
3
4
5
select a.sname,count(a.sid)
from student a
group by a.sname
having count(a.sid) >=2
;

以名字分组,同名人数大于2就可以了。

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

1
select * from student where year(Sage) = 1990;

考察标量函数year的用法。意思是返回Sage中的年分值(year)。类似的还有month,day。

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

1
2
3
4
5
select avg(score)
from sc
group by cid
order by avg(score) desc,cid asc
;

按cid分组求平均数。

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

1
2
3
4
5
6
select student.sid,student.sname,avg(sc.score)
from student,sc
where student.sid = sc.sid
group by sc.sid
having avg(sc.score) >= 85
;

有个题目差不多,分组后求平均数,满足条件的就连接。

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

1
2
3
4
5
6
select student.sname,sc.score
from student,sc,course
where course.cname = '数学' and
course.cid = sc.cid and
sc.score < 60 and
student.sid = sc.sid;

34、查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)

1
2
3
4
5
select student.sid 学号,student.sname 姓名,course.cname 课程名称,sc.score 分数
from student
left outer join sc on student.sid = sc.sid
left outer join course on sc.cid = course.cid
;

要使得学生没选课的记录也显示出来,这时候要用到外连接。可以看到,学生的基本信息是已知的,未知的是选课信息,所以student表是主体,要做的是把student的悬浮数组保存起来,可以用左外连接来实现。

拓展:

左外连接 left join table on (连接条件)或 left outer join table on (连接条件)
右外连接 right join table on (连接条件)或 right outer join table on (连接条件)
完全连接 full join 或 full outer join

内连接 join 或 inner join
交叉连接 cross join
多张表的外连接:

1
2
3
from table1 left outer join table2 on(连接条件)
left outer join table3 on(连接条件)
·····

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

1
2
3
4
5
6
7
select student.sname 姓名,course.cname 课程名称,sc.score 分数
from student,sc,course
where student.sid = sc.sid and
sc.cid = course.cid
group by sc.sid
having max(score) > 70
;

36、查询不及格的课程

1
2
3
4
5
select distinct course.cname 课程名称
from sc,course
where sc.cid = course.cid and
sc.score < 60
;

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

1
2
3
4
select student.sid,sname 
from student,sc
where student.sid = sc.sid and
sc.cid = '01' and sc.score > 80;

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

1
2
3
4
select course.cname 课程名称,count(sc.sid) 人数 
from course,sc
where course.cid = sc.cid
group by sc.cid;

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

1
2
3
4
5
6
7
8
9
select student.*,sc.score
from student,sc,course,teacher
where student.sid = sc.sid and
sc.cid = course.cid and
course.tid = teacher.tid and
teacher.tname = '张三'
order by sc.score desc
limit 1
;

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

1
2
3
4
5
6
7
8
9
10
11
12
select student.*,sc.score
from student,sc,course,teacher
where student.sid = sc.sid and
sc.cid = course.cid and
course.tid = teacher.tid and
teacher.tname = '张三' and
sc.score = (select max(score) from sc,course,teacher
where sc.cid = course.cid and
course.tid = teacher.tid and
teacher.tname = '张三'
)
;

可能有多个最高分,要把所有的最高分找出来,那么可以先找出一个最高分,再把所有与最高分相等的分数找出来。

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

1
2
3
4
5
select a.sid,a.cid,a.score
from sc a ,sc b
where (a.sid = b.sid and a.cid != b.cid and a.score = b.score)
group by a.sid,a.cid
;

每个同学有多门课程,找出这些课程中成绩相同的。
题目意思都没理解。。。

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

1
2
3
4
5
6
select a.cid,a.sid,count(b.sid) + 1
from sc a left join sc b on(a.cid = b.cid and a.score < b.score)
group by a.cid,a.sid
having count(b.sid) < 2
order by a.cid
;

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

1
2
3
4
5
6
select course.cname 课程,count(sc.sid) 选修人数
from sc,course
where sc.cid = course.cid
group by sc.cid
having count(sc.sid) >=5
;

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

1
2
3
4
5
select sid
from sc
group by sid
having count(cid) >= 2
;

水题。

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

1
2
3
4
5
6
7
8
9
select student.*
from student,sc
where student.sid = sc.sid
group by sc.sid
having count(sc.cid) >= (
select count(*)
from course
)
;

嵌套一个用来查course表中总课程数。

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

1
select sid 学号,sname 姓名,year(curdate()) - year(sage) 年龄 from student;

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

1
2
3
4
select sid,sname,year(curdate()) - year(sage) -
(case when format(now(),'%m%d') > format(sage,'%m%d') then 0 else 1 end) age
from student
;

条件选择,case when () then num else num end

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

1
2
3
select * from student
where week(curdate()) = week(sage)
;

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

1
2
3
select * from student
where week(curdate()) + 1 = week(sage)
;

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

1
2
3
select * from student
where month(curdate()) = month(sage)
;

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

1
2
3
select * from student
where month(curdate()) + 1= month(sage)
;

时间过的好快啊,不知不觉,半个学期没了。我的学生生涯还有多久呢?

-------------本文结束感谢您的阅读-------------
0%