[数据库与SQL] - No.3 SQL结构化查询训练

最近在复习数据库SQL，所以开始在网上找了一些SQL的训练。完整的50个题目及答案：

学生信息表：

课程信息：

选课记录：

教师信息：

1. 查询01课程比"02"成绩高的同学学号

select sc1.sid,sc1.score,sc2.score from sc sc1,sc sc2 where sc1.sid = sc2.sid and 
sc1.cid = "01" and sc2.cid = "02" and sc1.score>sc2.score; 

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

select sc.sid ,avg(sc.score) as avg from sc
group by sc.sid
having avg>60;

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

select s.sid,s.sname,(select count(*) from sc where sc.sid = s.sid) as "选课数",(select sum(sc.score) from sc where sc.sid = s.sid) as "总成绩" 
from student s,sc
group by s.sid

4. 查询姓李的老师个数

select count(*)from teacher t where t.tname like "李%";

5. 查询没有学过张三老师课的同学

写法1：（使用in）

select s.sname from student s where s.sid not in
(select distinct sc.sid from sc,course c,teacher t where t.tname = "张三"
and t.tid = c.tid and c.cid = sc.cid);

写法2：（使用exists）

select s.sname from student s where not exists
(select * from sc,course c,teacher t where t.tname = "张三" 
and t.tid = c.tid and c.cid = sc.cid and sc.sid = s.sid);

6. 查询学过01也学过02的同学编号

写法1：使用SQL除法

select s.sid,s.sname from student s where not exists(
	select distinct cid from (select c.cid from course c where c.cid = "01" or c.cid = "02") tmp where not exists(
		select * from sc where sc.sid = s.sid and sc.cid = tmp.cid));

写法2：使用计数

select sc.sid,s.sname from student s ,sc where sc.cid in (select c.cid from course c where c.cid = "01" or c.cid = "02")
and s.sid = sc.sid
group by sc.sid
having count(*) = (select count(*) from course c where c.cid = "01" or c.cid = "02");

这里解释一下这段SQL：

首先我们在选课记录中，选择所有选中了01或者02的课程记录

然后，我们进行分组，按照学号来分组，这样每个组里面的记录都是某某同学选了01或者某某同学选了02

我们选择这样的分组，该分组的个数是01,02号课程的总和（当然就是2了）

7. 查询学过“张三老师”所有课程的同学

同上，也分为两种写法。把6中的01,02课程的集合改为张老师教授的课程集合

8. 查询课程“02”比“01”分数低同学学号

写法同1题

9. 查询所有课程成绩小于60分的同学学号，姓名

select distinct sc.sid,s.sname from student s,sc where sc.sid not in 
(select distinct sc.sid from sc where sc.score>=60)
and sc.sid = s.sid ;

首先找出成绩大于60分的选课记录。该同学的学号不在集合中

10. 查询没有选全所有课的同学

select s.sid,(select count(*) from sc where sc.sid = s.sid) as cnt from student s,sc t
group by s.sid
having cnt != (select count(*) from course);

计数法

11. 查询至少一门课和01学号同学相同的其他同学学号和姓名

select distinct sc.sid,s.sname from sc,student s where sc.cid in (select cid from sc where sc.sid = "01") and 
sc.sid != "01" and s.sid = sc.sid;

12.题干和11相同

13. 把sc表中“张三”老师教的课的成绩改为此课程平均数

update sc set score = (select avg(sc1.score) from sc sc1 where sc1.cid = sc.cid)
where sc.cid in (select sc2.cid from sc sc2,course c,teacher t where sc2.cid = c.cid and c.tid = t.tid and t.tname = "张三" )

mysql会报报错，还没找到解决办法

14. 查询和“01”号同学学习的课程完全相同的其他同学学号和姓名

select sc.sid from sc where sc.sid!="01" and sc.cid in (select cid from sc where sc.sid = "01")
group by sc.sid
having count(*) = (select count(*) from sc where sc.sid = "01");

15. 删除学习“王五”老师课程的SC记录

delete sc from sc,course c,teacher t 
where sc.cid = c.cid and c.tid = t.tid and t.tname = "王五";

16. 向SC表插入一些记录，这些记录符合：没有上过“02”课程的同学学号，“02”，2号课平均成绩

insert into sc
select distinct sc.sid,"02",(select avg(sc1.score) from sc sc1 where sc1.cid = "02")from sc where sc.sid not in (select sid from sc where cid = "02");

17. 按照平均成绩显示所有学生“01”，“02”，“03”三门课程的成绩，按如下形式显示：

学生ID，“01”，“02”，“03”，有效课程数，有效平均分

select t.sid,(select sc.score from sc where sc.cid = "01" and t.sid = sc.sid)
from sc t
group by t.sid;

18. 查询各科成绩最高和最低的分数

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

19.按各科平均成绩从低到高和及格率的百分数从高到低排列.（不包含没有选课记录的课程）

select t.cid,avg(t.score) as avg, ((select count(*) from sc where sc.cid = t.cid and sc.score>=60 )/(select count(*) from sc where sc.cid = t.cid ))as rate
from sc t
group by t.cid
order by avg desc,rate asc;

20. 查询如下课程的平均成绩及合格率:01,02,03(一行表示)

select 
	(select avg(sc.score) from sc where sc.cid = "01") as avg,(select count(*) from sc where sc.cid = "01" and sc.score<60)/(select count(*) from sc where sc.sid = "01")*100 as rate,
	(select avg(sc.score) from sc where sc.cid = "02") as avg,(select count(*) from sc where sc.cid = "02" and sc.score<60)/(select count(*) from sc where sc.sid = "02")*100 as rate,
	(select avg(sc.score) from sc where sc.cid = "03") as avg,(select count(*) from sc where sc.cid = "03" and sc.score<60)/(select count(*) from sc where sc.sid = "03")*100 as rate;

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

select sc.cid,t.tname as tname,avg(sc.score) as avg from teacher t,course c,sc
where t.tid = c.tid and c.cid = sc.cid
group by sc.cid
order by avg desc;

22. 查询如下课程第三名到第六名的同学：01,02,03

select cid,score,rank from
(select tmp.sid,tmp.cid,tmp.score,if(tmp.cid = @tmid,@rank:=@rank+1,@rank:=1) as rank,@tmid:= tmp.cid
from (select sc.sid,sc.cid,sc.score from sc group by sc.cid,sc.score order by sc.cid asc,sc.score desc,sc.sid) tmp,(select @rank:=0,@tmid=null) t
)r
where r.cid = "01" or r.cid = "02" or r.cid = "03"
having rank>=3 and rank<=6;

23. 统计列印各科成绩，各分数段人数，课程id，课程名称，[100-85],[85-70],[70-60],[<60]

select r.cid,cname,`[100-85]`,`[85-70]`,`[70-60]`,`[<=60]` from
(select t.cid,
(select count(*) from sc where sc.cid = t.cid and sc.score<=100 and sc.score>85) as "[100-85]",
(select count(*) from sc where sc.cid = t.cid and sc.score<=85 and sc.score>70) as "[85-70]",
(select count(*) from sc where sc.cid = t.cid and sc.score<=70 and sc.score>60) as "[70-60]",
(select count(*) from sc where sc.cid = t.cid and sc.score<=60) as "[<=60]"
from sc t
group by t.cid) as r,course c
where c.cid = r.cid;

这个题目要注意的地方是，当列名中含有[]、（）的时候，在查询时要加上` `，即反引号（tab上边）

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

select sid,sname,avg,@rownum:=@rownum+1 as rank from
(select tmp.sid,sname,avg from
(select sid ,avg(sc.score) as avg from sc group by sc.sid order by avg desc)tmp,student s
where tmp.sid = s.sid
order by avg desc)
r,(select @rownum:=0)t

这个题目，解释了如何在 Mysql中实现rank函数的实现

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

解法同22题

26. 查询每门课程被选的人数

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

27. 查询只选修了一门课程的学生姓名学号

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

28. 查询男女生人数

select (select count(*) from student s where s.ssex = "男") as "男",
(select count(*) from student s where s.ssex = "女") as "女";

29. 查询姓王的同学信息

同4题

30. 查询同名同姓的学生名单，并统计同名人数

select s.sname,count(*) as cnt from student s
group by s.sname
having cnt >1;

31. 查询1991年出生的学生

select s.sname from student s where s.sbirth = "1991";

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

select sc.cid,avg(sc.score) as avg
from sc
group by sc.cid
having avg(sc.score) is not null
order by avg asc,sc.cid desc;

33. 查询平均分数大于85的同学学号，姓名和平均成绩

select s.sid,s.sname,avg(sc.score) as “Avg” from student s,sc
where s.sid = sc.sid
group by sc.sid
having  avg(sc.score) > 85;

34. 查询课程名称为“语文”，且分数低于60分的学生姓名和分数

select s.sname,sc.score from student s,sc,course c
where s.sid = sc.sid and sc.cid = c.cid and c.cname = "语文"
and sc.score <60;

35. 查询所有学生的选课情况

select s.sid,s.sname,c.cname from sc,student s ,course c
where s.sid = sc.sid and c.cid = sc.cid ; 

36. 查询任何一门成绩在70分以上的姓名，课程编号和分数

select s.sname,sc.cid,sc.score from sc,student s
where s.sid = sc.sid and sc.score>=70;

37. 查询不及格的课程成绩并且按照课程号从小到大排列

select sc.cid,sc.score from sc where sc.score <60
order by sc.cid desc;

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

select s.sid,s.sname from student s,sc where s.sid = sc.sid and
sc.cid = "03" and sc.score >=80;

39. 求选了课程的人

select count(*) from sc;

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

select cid,tmp.sid,sname,score from
(select s.sname,sc.cid as cid,sc.sid as sid,sc.score as score from
sc,course c,teacher t,student s where 
t.tname = "张三" and 
t.tid = c.tid and 
sc.cid = c.cid and 
sc.sid = s.sid order by sc.score desc) tmp
limit 1;

41.查询各个课程及相应选修人数

select sc.cid,c.cname,count(sc.sid) from sc,course c where c.cid = sc.cid
group by sc.cid;

42. 查询不同课程成绩相同的学生的学号，课程号，学生成绩

select sc.sid,sc.cid,sc.score from sc,sc sc1 where sc.sid != sc1.sid and sc.score = sc1.score;

43.查询每门课成绩最好的前两名：

select r.cid,c.cname,score,rank from
(select tmp.cid,tmp.score,if(tmp.cid=@tmid,@rank:=@rank+1,@rank:=1) as rank,@tmid:=tmp.cid
from (select cid,score from sc group by cid,score order by cid asc,score desc) tmp,
(select @rank:=0,@tmid:=null)tmp1
) r,course c
where r.cid = c.cid and score is not null and rank <=2;

同样也是rank的实现

44. 统计每门课学生选修人数(超过三个人才统计)。要求输出课程号和选修人数，平均分
查询结果按照人数降序排列，查询结果按照人数降序排列。若人数相同按照课程号升序排列

select sc.cid,c.cname,count(sc.sid) as cnt ,avg(sc.score) from sc,course c
where c.cid = sc.cid
group by sc.cid
having count(sc.sid)>3
order by cnt desc,sc.sid asc;

45. 检索至少选修三门课的学生学号

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

46.查询全部学生都选修的课程课程号和课程名

select c.cid,c.cname from course c where not exists
(select s.sid from student s where not exists
(select sc.sid from sc where sc.cid = c.cid and sc.sid = s.sid));

使用SQL除法，找出覆盖了所有学生集合的课程

47.查询没有学过“王五”老师任一门课的学生姓名

同5题

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

select sc.sid,avg(sc.score) as avg from sc where exists 
(select * from sc sc1 where sc1.sid = sc.sid and sc1.score < 60 
having count(*) >= 2)
group by sc.sid;

49. 检索04课程分数大于60的同学学号，按照分数降序排列

select sc.sid from sc  where sc.cid = "04" and sc.score > 60
order by sc.score desc;

50. 删除“02”同学“01”课程成绩

delete from sc where sc.sid = "02" and sc.cid = "01";

=======================================================================================================================

整理的其他题目

=======================================================================================================================

1. 统计选课但是没有选02课程的学生姓名、学号

select distinct s.sid,s.sname from student s,sc where s.sid = sc.sid and not exists
(select sc.sid from sc where sc.cid = '02');

挑选这样的学生，选课记录中不存在02课程

2. 统计各个学科的平均分

select sc.cid,c.cname,avg(sc.score) from course c,sc where sc.cid = c.cid 
group by sc.cid

3. 挑选年龄比平均年龄大的同学信息

select s.sid,s.sname,s.sage from student s where s.sage >
(select avg(s1.sage) from student s1)

首先在子查询计算学生平均年龄，然后检索

4.查询至少选修了语文数学的学生学号

select distinct sc1.sid from student sc1
	where not exists
		(select distinct c.cid from (select * from course t where t.cid = "01" or t.cid = "02" ) as c
			where not exists 
					(select * from sc sc2
						where sc1.sid = sc2.sid
						and   c.cid = sc2.cid))

使用SQL除法表示，详细查看 SQL除法这篇文章

5.选修了所有课程的同学学号（非计数写法）

select distinct sc1.sid from student sc1  
	where not exists    
		(select distinct c.cid from course c   
			where not exists      
					(select * from sc sc2        
						where sc1.sid = sc2.sid
						and   c.cid = sc2.cid))

解释同上。同时也可以用计数的方法，就是看同学选课的个数。也有很多习题使用这种简单的写法

6.王菊同学不学的课程名

select cid from course where cid not in (select sc.cid from sc ,student s where sc.sid = s.sid and s.sname = "王菊")

在子查询中选出王菊的课程，然后减去该集合

7.至少选了两门课的同学信息

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

按照学号分组，使用聚集函数计算每一组学生选课数目，检索大于2的学生信息

8.所有学生都选的课

select cid,cname from course c 
	where not exists
		(select sid from student s 
			where not exists 
				(select * from sc where sc.cid = c.cid and sc.sid = s.sid))

使用SQL除法，选出这样一门课，不存在一个学生没有选该课

9.选修了语文的同学的平均年龄

select avg(s.sage) from student s,sc where s.sid in (select distinct sc.sid from sc,course c where sc.cid = c.cid and c.cname = "语文")

首先选出选修语文的同学学号，计算平均年龄

10.各科选修同学的平均年龄

select c.cname,avg(s.sage) from course c,student s,sc where sc.sid =s.sid and sc.cid = c.cid
group by sc.cid;

按照课程号分组，计算每一组的学生平均年龄

11.求张三教授的每门课的学生平均成绩

select c.tid,c.cname,avg(sc.score)
from course c,teacher t,sc
where c.tid = t.tid and t.tname = "张三" and sc.cid = c.cid
group by sc.cid

和10很相似，添加课程老师是张三这个条件

12.统计每门课的选课人数（超过3人），要求输出课程号和选课人数，查询结果按照人数降序排列，若人数相同按照课程号排列

select c.cname,sc.cid,count(sc.sid) as cnt from sc,course c where c.cid = sc.cid
group by sc.cid having count(sc.sid) > 2 order by cnt desc,sc.cid

首先按照课程号分组，选择人数在三个以上，计算课程选课人数

13.学号比赵雷大，年龄比赵雷小的学生信息

select * from student s
	where exists 
		(select * from student s1 where  s1.sname = "赵雷" and s1.sage>s.sage and s1.sid<s.sid )

14.所有姓王的同学信息

select * from student s where s.sname like "王%"

使用like 和%符号，比使用正则表达式更为简单。%表示0个或多个任意符号

15.sc表中成绩为空的学生学号和课号

select sc.sid,sc.cid from sc where sc.score is null;

使用isnull判断空值

16.年龄大于女同学平均年龄的男同学姓名年龄

select s.sname,s.sage from student s where s.ssex = "男" and s.sage > 
	(select avg(s1.sage) from student s1 where s1.ssex = "女");

17.年龄大于所有男同学年龄的女同学姓名年龄

select s.sname,s.sage from student s where s.ssex = "女" and 
s.sage > all (select s.sage from student s where s.ssex = "男");

18.在student 中检索所选选课程每一门都大于等于80的学生的姓名学号性别，存储到新的表youxiu中

首先创建表youxiu：

create table youxiu(
sid char(10),
sname char(10),
ssex char(10),
primary key(sid)
)

将select语句直接放在insert之后（没有添加values）

insert into youxiu(sid,sname,ssex)
select s.sid,s.sname,s.ssex from student s
where not exists
(select sc.cid from sc
where sc.sid = s.sid and (sc.score <80 or sc.score is null))

注意，这里为什么要加上 isnull 呢？

主要 我们有一条记录 王二

09

05

所以，王二同学在选课表中05课程没有成绩，在我们的select语句中，当检索到王二的时候：

由于null 和 80相比结果为false，所以not exists 后边的select子查询没有返回结果，所以not exists 为真，返回了王二这一条记录

而我们不需要王二的记录，所以添加上is null

P.S.文章不妥之处还望指正