SQL查询练习(高级)(附答案)
1. 目标
熟悉常用的DQL语言
熟练常用的多表查询
2. 创建数据表
-- 1.学生表
-- sid 学生编号,sname 学生姓名,sage 出生年月,ssex 学生性别
create table if not exists student
(
sid varchar(10),
sname varchar(50),
sage datetime,
ssex char(2)
);
-- 2.课程表
-- cid -- 课程编号,cname 课程名称,tid 教师编号
create table if not exists course
(
cid varchar(10),
cname varchar(50),
tid varchar(10)
);
-- 3.教师表
-- tid 教师编号,tname 教师姓名
create table if not exists teacher
(
tid varchar(10),
tname varchar(50)
);
-- 4.成绩表
-- sid 学生编号,cid 课程编号,score 分数
create table if not exists sc
(
sid varchar(10),
cid varchar(10),
score double
);
-- 创建测试数据
insert into student
values ('01', '赵雷', '1990-01-01', '男'),('02', '钱电', '1990-12-21', '男'),('03',
'孙风', '1990-05-20', '男'),('04', '李云', '1990-08-06', '男'),('05', '周梅',
'1991-12-01', '女'),('06', '吴兰', '1992-03-01', '女'),('07', '郑竹', '1989-07-01',
'女'),('08', '王菊', '1990-01-20', '女');
insert into course
values ('01', '语文', '02'),('02', '数学', '01'),('03', '英语', '03');3. 分析表结构
4. DQL**数据查询**
insert into teacher
values ('01', '张三'),('02', '李四'),('03', '王五');
insert into sc
values ('01', '01', 80),('01', '02', 90),('01', '03', 99),('02', '01', 70),
('02', '02', 60),('02', '03', 80),('03', '01', 80),('03', '02', 80),('03', '03',
80),('04', '01', 50),('04', '02', 30),('04', '03', 20),('05', '01', 76),('05',
'02', 87),('06', '01', 31),('06', '03', 34),('07', '02', 89),('07', '03', 98);
\1. 学生表
\2. 课程表
\3. 成绩表
\4. 教师表
结论： 学生表和课程表之间是多对多的关系，中间表就是成绩表， 教师表和课程表之间是一对一的关系。
# 1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
## 分析：需要几张表 学生表，成绩表这两张表之间有直接关系，所以可以直接关联
# 2.查询同时存在"01"课程和"02"课程的情况，有可能存在"01"课程但可能不存在"02"课程的学生，查
询"01"课程比"02"课程成绩高的学生的信息及课程分数。
# 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
# 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
# 5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
# 6.查询"李"姓老师的数量（两种方法）
# 7.查询没学过"张三"老师授课的同学的信息
# 8.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息# 9. 查询没学过"张三"老师讲授的任一门课程的学生姓名
# 10.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
# 11.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均
分，及格率，中等率，优良率，优秀率。及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
# 12.查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
# 13.查询不及格的课程
# 14.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
# 15.检索至少选修两门课程的学生学号
# 参考答案
create database day08;
use day08;
-- 1.学生表
-- sid 学生编号,sname 学生姓名,sage 出生年月,ssex 学生性别
create table if not exists student
(
sid varchar(10),
sname varchar(50),
sage datetime,
ssex char(2)
);
-- 2.课程表
-- cid -- 课程编号,cname 课程名称,tid 教师编号
create table if not exists course
(
cid varchar(10),
cname varchar(50),
tid varchar(10)
);
-- 3.教师表
-- tid 教师编号,tname 教师姓名
create table if not exists teacher
(
tid varchar(10),
tname varchar(50)
);
-- 4.成绩表
-- sid 学生编号,cid 课程编号,score 分数
create table if not exists sc
(
sid varchar(10),
cid varchar(10),
score double
);
-- 创建测试数据
insert into studentvalues ('01', '赵雷', '1990-01-01', '男'),('02', '钱电', '1990-12-21', '男'),('03',
'孙风', '1990-05-20', '男'),('04', '李云', '1990-08-06', '男'),('05', '周梅',
'1991-12-01', '女'),('06', '吴兰', '1992-03-01', '女'),('07', '郑竹', '1989-07-01',
'女'),('08', '王菊', '1990-01-20', '女');
insert into course
values ('01', '语文', '02'),('02', '数学', '01'),('03', '英语', '03');
insert into teacher
values ('01', '张三'),('02', '李四'),('03', '王五');
insert into sc
values ('01', '01', 80),('01', '02', 90),('01', '03', 99),('02', '01', 70),
('02', '02', 60),('02', '03', 80),('03', '01', 80),('03', '02', 80),('03', '03',
80),('04', '01', 50),('04', '02', 30),('04', '03', 20),('05', '01', 76),('05',
'02', 87),('06', '01', 31),('06', '03', 34),('07', '02', 89),('07', '03', 98);
# 1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select s.*,one.score,two.score
from student s
left join sc one on s.sid=one.sid and one.cid='01'
left join sc two on s.sid=two.sid and two.cid='02'
where one.score>two.score;
# select s.*,one.score,two.score
from student s
left join(select sid from sc one where cid='01')tmp on tmp.sid=s.sid
# 2.查询同时存在"01"课程和"02"课程的情况，有可能存在"01"课程但可能不存在"02"课程的学生，查
询"01"课程比"02"课程成绩高的学生的信息及课程分数。
select s.*,one.score,two.score
from student s
left join sc one on s.sid=one.sid and one.cid='01'
left join sc two on s.sid=two.sid and two.cid='02'
where one.score>ifnull(two.score,0);
# 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.sid,s.sname,round(avg(s2.score),1)
from student s
left join sc s2 on s.sid = s2.sid
group by s.sid,s.sname
having avg(s2.score)>=60
order by s.sid;
SELECT s.sid,s.sname,round(avg(s2.score),2)
FROM student s
left join sc s2 on s.sid = s2.sid
group by s.sid,s.sname
having avg(s2.score)>=60;
# 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select s.sid,s.sname,round(avg(s2.score),1)
from student s
left join sc s2 on s.sid = s2.sid
group by s.sid,s.sname
having avg(s2.score)<60;# 5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.sid,s.sname,count(s2.cid),sum(s2.score)
from student s
left join sc s2 on s.sid = s2.sid
group by s.sid, s.sname
order by s.sid;
# 6.查询"李"姓老师的数量（两种方法）
select count(tname)
from teacher
where tname like '李%';
-- 方法2
select count(tname)
from teacher
where left(tname,1)='李';
-- 方法3
select count(tname)
from teacher
where substring(tname,1,1)='李';
# 7.查询没学过"张三"老师授课的同学的信息
# 7-1 查询所有同学的信息 student
# 7-2 张三教过所有同学信息
# 7-3 过滤
select *
from student where sid not in(
select sc.sid
from sc join course c on sc.cid = c.cid
join teacher t on c.tid=t.tid and t.tname='张三'
group by sc.sid
);
select *
from student s
left join sc s2 on s.sid = s2.sid
left join course c on s2.cid = c.cid
left join teacher t on c.tid = t.tid
where s.sname='吴兰';
# 8.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select s.*,one.score,two.score
from student s
left join sc one on s.sid=one.sid and one.cid='01'
left join sc two on s.sid=two.sid and two.cid='02'
where one.score is not null and two.score is not null;
# 9. 查询没学过"张三"老师讲授的任一门课程的学生姓名
select *
from day05.student s
where s.sid not in(
select ss.sid
from day05.student ss
join sc s2 on ss.sid = s2.sid
join course c on s2.cid = c.cid
join teacher t on c.tid = t.tidwhere t.tname='张三'
);
# 10.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.sid,s.sname,truncate(avg(score),2) from student s
left join sc s2 on s.sid = s2.sid
where s.sid in(
select sid
from sc
where score <60
group by sid
having count(1)>=2
)
group by s.sid, s.sname;
# student sc
# sc score <60 group by sid count>=2
# 过滤
select s.sid,s.sname, truncate(avg(s2.score),2) as avg_score
from student s
left join sc s2 on s.sid = s2.sid
where s.sid in(
select sid from sc
where s2.score<60
group by sid
having count(sid)>=2
)
group by s.sid, s.sname;
# 11.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均
分，及格率，中等率，优良率，优秀率。及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select m.cid 课程编号 , m.cname 课程名称 ,
max(n.score) 最高分 ,
min(n.score) 最低分 ,
TRUNCATE(avg(n.score) ,2) 平均分 ,
TRUNCATE((select count(1) from sc where cid = m.cid and score >= 60)*100.0 /
(select count(1) from sc where cid = m.cid) ,2) 及格率 ,
TRUNCATE((select count(1) from sc where cid = m.cid and score >= 70 and score
< 80 )*100.0 / (select count(1) from sc where cid = m.cid) ,2) 中等率 ,
TRUNCATE((select count(1) from sc where cid = m.cid and score >= 80 and score
< 90 )*100.0 / (select count(1) from sc where cid = m.cid),2) 优良率 ,
TRUNCATE((select count(1) from sc where cid = m.cid and score >= 90)*100.0 /
(select count(1) from sc where cid = m.cid),2) 优秀率
from course m , sc n
where m.cid = n.cid
group by m.cid , m.cname
order by m.cid;
# 12.查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select s.sname,c.cname,s2.score
from student s
left join sc s2 on s.sid = s2.sid
left join course c on s2.cid = c.cid
where s2.score>=70
order by s2.score;
# student sc courseselect s.sname,c.cname,s2.score
from student s
left join sc s2 on s.sid = s2.sid
left join course c on s2.cid = c.cid
where s2.score>=70;
# 13.查询不及格的课程
select s.*,c.cname
from student s
left join sc s2 on s.sid = s2.sid
left join course c on s2.cid = c.cid
where s2.score<60;
# 14.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select s.sid,s.sname,s2.cid,s2.score,c.cname
from student s
left join sc s2 on s.sid = s2.sid
left join course c on s2.cid = c.cid
where s2.score>=80
and s2.cid='01';
select s.sid,s.sname
from student s
left join sc s2 on s.sid = s2.sid
where s2.score>=80
and s2.cid='01';
# 15.检索至少选修两门课程的学生学号
select s.sid,s.sname
from student s,sc
where s.sid=sc.sid
group by sid,sname
