欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-03-09 09:13:12
...

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

学生信息表:

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

课程信息:

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

选课记录:

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

教师信息:

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

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:aaa@qq.com+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:aaa@qq.com+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(aaa@qq.com,@rank:aaa@qq.com+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.文章不妥之处还望指正















相关标签: 数据库 sql