[数据库与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;
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
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");
学生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;
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;
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;
select s.sname,s.sid from student s,sc
where s.sid = sc.sid
group by sc.sid
having count(sc.cid) = 1;
select (select count(*) from student s where s.ssex = "男") as "男",
(select count(*) from student s where s.ssex = "女") as "女";
同4题
30. 查询同名同姓的学生名单,并统计同名人数
select s.sname,count(*) as cnt from student s
group by s.sname
having cnt >1;
select s.sname from student s where s.sbirth = "1991";
降序排列
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;
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;
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;
select s.sid,s.sname,c.cname from sc,student s ,course c
where s.sid = sc.sid and c.cid = sc.cid ;
select s.sname,sc.cid,sc.score from sc,student s
where s.sid = sc.sid and sc.score>=70;
select sc.cid,sc.score from sc where sc.score <60
order by sc.cid desc;
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;
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;
select sc.cid,c.cname,count(sc.sid) from sc,course c where c.cid = sc.cid
group by sc.cid;
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;
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 )
select * from student s where s.sname like "王%"
使用like 和%符号,比使用正则表达式更为简单。%表示0个或多个任意符号
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 = "女");
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 |
由于null 和 80相比结果为false,所以not exists 后边的select子查询没有返回结果,所以not exists 为真,返回了王二这一条记录
而我们不需要王二的记录,所以添加上is null
P.S.文章不妥之处还望指正
上一篇: Docker cpu限制分析
下一篇: PHP中的魔术常量是什么