多表查询习题参考答案。
-- ***首先设置sql_mode，不要 ONLY_FULL_GROUP_BY模式（偶尔会用到，十分方便！）
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
#1.自行创建测试数据
-- 创建数据库homework
create database homework charset="utf8";
use homework;
-- 创建班级表
create table class(
cid int primary key auto_increment,
caption char(4),
grade_id int
);
-- 创建学生表
create table student(
sid int primary key auto_increment,
sname char(4),
gender enum("男","女") default "女",
class_id int
);
-- 创建老师表
create table teacher(
tid int primary key auto_increment,
tname char(4)
);
-- 创建课程表
create table course(
cid int primary key auto_increment,
cname char(2),
teacher_id int
);
-- 创建班级任职表
create table teach2cls(
tcid int primary key auto_increment,
tid int,
cid int
);
-- 创建成绩表
create table score(
sid int primary key auto_increment,
student_id int,
course_id int,
score float(5,2) unsigned
);
-- 创建年级表
create table class_grade(
gid int primary key auto_increment,
gname char(3)
);
-- 插入班级表数据
insert into class values
(1,"一年一班",1),
(2,"二年一班",2),
(3,"三年二班",3);
-- 插入学生表数据
insert into student values
(1,"乔丹","女",1),
(2,"艾弗森","女",1),
(3,"科比","男",2);
-- 插入老师表数据
insert into teacher values
(1,"张三"),
(2,"李四"),
(3,"王五");
-- 插入课程表数据
insert into course values
(1,"生物",1),
(2,"体育",1),
(3,"物理",2);
-- 插入班级任职表数据
insert into teach2cls values
(1,1,1),
(2,1,2),
(3,2,1),
(4,3,2);
-- 插入成绩表数据
insert into score values
(1,1,1,60),
(2,1,2,59),
(3,2,2,99);
-- 插入年级表数据
insert into class_grade values
(1,"一年级"),
(2,"二年级"),
(3,"三年级");
-- 2查询学生总人数
select
count(sid) total_number
from
student
;
-- 3查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；
select
student.sid,student.sname
from
(
select
student_id,score
from
score
where
score >= 60
) a1
inner join
student
on
student.sid = a1.student_id
;
-- 4查询每个年级的班级数，取出班级数最多的钱三个年级
select
grade_id,count(caption) count_cap
from
class
group by
grade_id
limit
3
;
-- 5查询平均成绩最高和最低的学生的id和姓名以及平均成绩（严格模式）
select
student.sid,student.sname,a2.avg_score
from
(
select -- 最高成绩与最低成绩的学生信息
min(a1.student_id) student_id,a1.avg_score avg_score
from
(
select -- 平均成绩表
student_id,avg(score) avg_score
from
score
group by
student_id
) a1
group by
a1.avg_score
having
a1.avg_score = max(a1.avg_score) or
a1.avg_score = min(a1.avg_score)
) a2
inner join
student
on
student.sid = a2.student_id
;
-- 6查询每个年级的学生人数
select
class_grade.gname,
a2.count_grade_id
from
(
select
a1.grade_id grade_id,
count(grade_id) count_grade_id
from
(
select
student.sid sid,
student.class_id class_id,
class.grade_id grade_id
from
student
inner join
class
where
student.class_id = class.cid
) a1
group by
a1.grade_id
) a2
inner join
class_grade
on
class_grade.gid = a2.grade_id
;
-- 7查询每位学生的学号，姓名，选课数，平均成绩
select
a1.sid sid,
a1.sname sname,
count(a1.course_id) count_course,
avg(a1.score) avg_score
from
(
select distinct
student.sid sid,
student.sname sname,
score.course_id course_id,
score.score score
from
student
inner join
score
on
student.sid = score.student_id
) a1
group by
a1.sid
;
-- 8查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、
-- 成绩最低的课程名及分数。
select
student.sname sname,
a3.course_cname course_cname,
a3.score score
from
(
select
a2.student_id student_id,
a2.course_id course_id,
a2.score score,
course.cname course_cname
from
(
select
a1.student_id student_id,
a1.course_id course_id,
a1.score score
from
( -- 2号学生的所有成绩
select
student_id,
course_id,
score
from
score
where
student_id = 2
) a1
having
a1.score = max(a1.score) or
a1.score = min(a1.score)
)a2
inner join
course
on
course.cid = a2.course_id
) a3
inner join
student
on
student.sid = a3.student_id
;
-- 9查询姓“李”的老师的个数和所带的班级数
select
tid teacher_id,
count(tid) teacher_number,
count(cid) class_number
from
teach2cls
where
tid in
( -- 李 的tid
select
tid
from
teacher
where
tname like "李%"
)
group by
tid
;
-- 10 查询班级数小于5的年级id和年级名
select
a1.gid gid,
a1.gname gname
from
(
select
class_grade.gid gid,
class_grade.gname gname,
class.cid cid
from
class_grade
inner join
class
on
class.grade_id = class_grade.gid
)a1
group by
a1.gid
having
count(a1.cid) < 5
;
-- 11 查询班级信息，包括班级id,班级名称，年级，年级级别（12低，34中，56高）
create table changeMsg(
id int primary key auto_increment,
xxxxx char(3),
grade_id int
);
insert into changeMsg values
(1,"低",1),
(2,"低",2),
(3,"中",3),
(4,"中",4),
(5,"高",5),
(6,"高",6);
select
a1.cid 班级id,
a1.gname 班级名称,
a1.caption 年级,
changeMsg.xxxxx 年级级别
from
(
select
class.cid cid,
class.caption caption,
class_grade.gname gname,
class_grade.gid gid
from
class
inner join
class_grade
on
class.grade_id = class_grade.gid
) a1
inner join
changeMsg
on
a1.gid = changeMsg.grade_id
;
-- 12、查询学过“张三”老师2门课以上的同学的学号、姓名
select
student.sid,
student.sname
from
student
inner join
(
select
score.student_id student_id
from
score
where
course_id in
( -- 张三老师上课的id
select
cid
from
course
where
teacher_id =
(
select
tid
from
teacher
where
tname = "张三"
)
)
group by
score.student_id
having
count(score.course_id) >= 2
) a1
on
student.sid = a1.student_id
;
-- 13查询教授课程超过2门的老师的id和姓名
select
tid,
tname
from
teacher
where
tid in
(
select
teacher_id
from
course
group by
teacher_id
having
count(cid) >= 2
)
;
-- 14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；
select
sid,
sname
from
student
where
sid in
(
select
student_id
from
score
where
course_id in (1,2)
)
;
-- 15、查询没有带过高年级的老师id和姓名；
select
tid,
tname
from
teacher
where
tid in
(select
tid
from
teach2cls
where
tid not in
( -- 带过高年级的老师 tid
select
tid
from
teach2cls
group by
tid
having
cid in
( -- 高年级的班级cid列表
select
cid
from
class
where
grade_id in
( -- 高年级的年级
select
grade_id
from
changemsg
where
xxxxx = "高"
)
)
)
)
;
-- 16、查询学过“张三”老师所教的所有课的同学的学号、姓名；
select
student.sid,
student.sname
from
student
where
sid in
(
select
student_id
from
score
where
course_id in
(
select
cid
from
course
where
teacher_id =
(select
tid
from
teacher
where
tname = "张三"
)
)
group by
student_id
having
count(course_id) =
(
select
count(cid)
from
course
where
teacher_id =
(select
tid
from
teacher
where
tname = "张三"
)
)
)
;
-- 17、查询带过超过2个班级的老师的id和姓名；
select
tid,
tname
from
teacher
where
tid in
(
select
tid
from
teach2cls
group by
tid
having
count(cid) >= 2
)
;
-- 18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
select
sid,
sname
from
student
where
sid in
(
select
c1.student_id student_id
from
(
select
a1.student_id student_id,
a1.course_id_1 course_id_1,
a1.score_1 score_1,
b1.course_id_2 course_id_2,
b1.score_2 score_2
from
(
select
student_id,
course_id course_id_1,
score score_1
from
score
where
course_id = 1
)a1
inner join
(
select
student_id,
course_id course_id_2,
score score_2
from
score
where
course_id = 2
)b1
on
a1.student_id = b1.student_id
) c1
where
c1.score_1 > c1.score_2
)
;
-- 19、查询所带班级数最多的老师id和姓名；
select
a2.tid,
teacher.tname
from
teacher
inner join
(select
a1.tid tid,
max(a1.count_cid)
from
(
select
tid,
count(cid) count_cid
from
teach2cls
group by
tid
) a1
)a2
on teacher.tid = a2.tid
;
-- 20、查询有课程成绩小于60分的同学的学号、姓名；
select
sid,
sname
from
student
where
sid in
(select
student_id
from
score
where
score < 60
)
;
-- 21、查询没有学全所有课的同学的学号、姓名；
select
sid,
sname
from
student
left join
(
select
a1.student_id student_id
from
(
select
student_id,
count(course_id) count_course_id
from
score
group by
student_id
)a1
where
a1.count_course_id <
(select
count(cid)
from
course
)
)a2
on
student.sid = a2.student_id
;
-- 22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
select
sid,
sname
from
student
where
sid in
(
select
student_id
from
score
where
course_id in
( -- 1号所学的课course_id
select
course_id
from
score
where
student_id = 1
)
and student_id != 1
)
;
-- 23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；
select
sid,
sname
from
student
where
sid in
(
select
student_id
from
score
where
course_id in
( -- 1号所学的课course_id
select
course_id
from
score
where
student_id = 1
)
and student_id != 1
)
;
-- 24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；
select
sid,
sname
from
student
where
sid in
(
select
student_id
from
score
where
course_id in
( -- 课程号
select
course_id
from
score
where
student_id = 2
)
and
student_id != 2
group by
student_id
having
count(course_id) =
( -- 课程总个数
select
count(course_id)
from
score
where
student_id = 2
)
)
;
-- 25、删除学习“张三”老师课的score表记录；
delete from
score
where
course_id in
(select
cid
from
course
where
teacher_id in
(
select
tid
from
teacher
where
tname="张三"
)
)
-- 26、向score表中插入一些记录，这些记录要求符合以下条件：
①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；
select
student_id
from
score
where
course_id != 2
;
select
avg(score)
from
score
where
course_id = 2
;
insert into score values
(4,3,3,79)
;
-- 27、****按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，
按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
select
c1.student_id,
c1.Chinese_score 语文,
c2.score 数学,
c1.English_score 英语,
c1.count_course 有效课程数,
c1.avg_score 有效平均分
from
(select
b1.student_id,
b1.count_course count_course,
b1.avg_score avg_score,
b1.Chinese_score Chinese_score,
b2.score English_score
from
(select
a1.student_id,
a1.count_course count_course,
a1.avg_score avg_score,
a2.score Chinese_score
from
(select -- 学生id 有效课程数，有效平均分
student_id,
count(course_id) count_course,
avg(score) avg_score
from
score
where
course_id in
(select -- 三门课的id
cid
from
course
where
cname = "语文"
or cname = "数学"
or cname = "英语"
)
group by
student_id
) a1
left join
(select -- 语文成绩
student_id,
score
from
score
where
course_id =
(select -- 语文课的cid
cid
from
course
where
cname = "语文"
)
)a2
on
a1.student_id = a2.student_id
) b1
left join
(select -- 英语成绩
student_id,
score
from
score
where
course_id =
(select -- 英语课的cid
cid
from
course
where
cname = "英语"
)
)b2
on
b1.student_id = b2.student_id
)c1
left join
(select -- 数学成绩
student_id,
score
from
score
where
course_id =
(select -- 数学课的cid
cid
from
course
where
cname = "数学"
)
)c2
on
c1.student_id = c2.student_id
order by
有效平均分
;
-- 28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select
a1.course_id 课程ID,
a1.max_score 最高分,
a2.min_score 最低分
from
(select
course_id,
max(score) max_score
from
score
group by
course_id
)a1
inner join
(select
course_id,
min(score) min_score
from
score
group by
course_id
)a2
on
a1.course_id = a2.course_id
;
-- 29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select
b1.course_id course_id,
b1.avg_score avg_score,
b2.pass_score / b1.sum_score * 100 pass_rate
from
(select
a1.course_id course_id,
a1.avg_score avg_score,
a2.sum_score sum_score
from
(select -- 各科的平均成绩
course_id,
avg(score) avg_score
from
score
group by
course_id
)a1
left join
(select -- 各科的总成绩
course_id,
sum(score) sum_score
from
score
group by
course_id
)a2
on
a1.course_id = a2.course_id
)b1
left join
(select -- 及格总成绩表
course_id,
sum(score) pass_score
from
score
where
score >=60
group by
course_id
)b2
on
b1.course_id = b2.course_id
order by
avg_score,
pass_rate desc
;
-- 30、课程平均分从高到低显示（现实任课老师）；
select distinct
a2.cid course_id,
teacher.tname teacher_name,
a2.avg_score avg_score
from
teacher
inner join
(
select
course.cid cid,
course.teacher_id teacher_id,
a1.avg_score avg_score
from
(
select
course_id,
avg(score) avg_score
from
score
group by
course_id
)a1
inner join
course
on
a1.course_id = course.cid
)a2
on
a2.teacher_id = teacher.tid
order by
avg_score
;
-- 31、查询各科成绩前三名的记录(考虑成绩并列情况)
select
a6.sid sid,
a6.student_id student_id,
a6.course_id course_id,
a6.score score
from
(select
score.sid sid,
score.student_id student_id,
score.course_id course_id,
score.score score,
a5.max_three_score max_three_score
from
score
left join
(select
a4.course_id course_id,
max(a4.score) max_three_score
from
(select
score.course_id course_id,
score.score score,
a3.max_two_score max_two_score
from
score
left join
(select
a2.course_id course_id,
max(a2.score) max_two_score
from
(select
score.course_id course_id,
score.score score,
a1.max_score max_score
from
score
left join
(select -- 最大
course_id,
max(score) max_score
from
score
group by
course_id
) a1
on
score.course_id = a1.course_id
) a2
where
a2.score != max_score
group by
a2.course_id
) a3
on
score.course_id = a3.course_id
) a4
where
a4.max_two_score != null
or
a4.score < a4.max_two_score
group by
course_id
) a5
on
score.course_id = a5.course_id
)a6
where
a6.max_three_score is NULL
or
a6.score > max_three_score
;
-- 32、查询每门课程被选修的学生数；
select
course_id,
count(student_id)
from
score
group by
course_id
;
-- 33、查询选修了2门以上课程的全部学生的学号和姓名；
select
sid,
sname
from
student
where
sid in
(
select
student_id
from
(
select
student_id,
count(course_id) count_course
from
score
group by
student_id
)a1
where
a1.count_course >= 2
)
;
-- 34、查询男生、女生的人数，按倒序排列；
select
gender,
count(gender)
from
student
group by
gender
order by
count(gender) desc
;
-- 35、查询姓“张”的学生名单；
select
*
from
student
where
sname like "张%"
;
-- 36、查询同名同姓学生名单，并统计同名人数；
select
sname,
count(sname)
from
student
group by
sname
;
-- 37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select
course_id,
avg(score) avg_score
from
score
group by
course_id
order by
avg_score,
course_id desc
;
-- 38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select
student.sname,
a1.score
from
student
inner join
(select
student_id,
score
from
score
where
course_id =
(
select
cid
from
course
where
cname = "数学"
)
and
score < 60
)a1
on
student.sid = a1.student_id
;
-- 39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；
select
sid,
sname
from
student
where
sid in
(select
student_id
from
score
where
course_id = 3
and
score >= 80
)
;
-- 40、求选修了课程的学生人数
select
count(a1.student_id)
from
(select distinct
student_id
from
score
group by
student_id
)a1
;
-- 41、查询选修“王五”(用张三代替)老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；
select
student.sname sname,
d1.score
from
student
inner join
(select
c1.student_id student_id,
c1.score score
from
(select
b1.student_id student_id,
b1.score score,
b1.max_score max_score,
a3.min_score min_score
from
(select
a1.student_id student_id,
a1.score score,
a2.max_score max_score
from
(select -- 所有学过王老师课的学生，分数
student_id,
score
from
score
where
course_id in
(select -- 课程表
cid
from
course
where
teacher_id =
(select
tid
from
teacher
where
tname = "张三"
)
)
)a1
left join
(select -- 所有学过王老师课的学生，分数
max(score) max_score
from
score
where
course_id in
(select -- 课程表
cid
from
course
where
teacher_id =
(select
tid
from
teacher
where
tname = "张三"
)
)
)a2
on
a1.score = a2.max_score
)b1
left join
(select -- 所有学过王老师课的学生，分数
student_id,
min(score) min_score
from
score
where
course_id in
(select -- 课程表
cid
from
course
where
teacher_id =
(select
tid
from
teacher
where
tname = "张三"
)
)
)a3
on
b1.score = a3.min_score
)c1
where
c1.score = max_score
or
c1.score = min_score
)d1
on
student.sid = d1.student_id
;
-- 42、查询各个课程及相应的选修人数；
select
course.cname course_name,
a1.count_student count_student
from
course
left join
(select
course_id,
count(student_id) count_student
from
score
group by
course_id
)a1
on
course.cid = a1.course_id
;
-- 43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select distinct
a1.student_id,
a1.course_id,
a1.score a1_score,
b1.score b1_score
from
score a1
inner join
score b1
on
a1.course_id != b1.course_id
and
a1.score = b1.score
;
-- 44、查询每门课程成绩最好的前两名学生id和姓名；
select
c2.student_id student_id,
student.sname name,
c2.course_id course_id,
c2.score score
from
student
inner join
(select
c1.student_id student_id,
c1.course_id course_id,
c1.score score
from
(select
a2.student_id student_id,
a2.course_id course_id,
a2.score score,
a2.max_score max_score,
b2.max_two_score max_two_score
from
(select -- 伴随最大分值
score.student_id student_id,
score.course_id course_id,
score.score score,
a1.max_score max_score
from
score
inner join
(select -- 每门课程最高分
course_id,
max(score) max_score
from
score
group by
course_id
)a1
on
score.course_id = a1.course_id
)a2
left join
(select -- 第二大分值
b1.course_id course_id,
max(b1.score) max_two_score
from
(select -- 伴随最大分值
score.course_id course_id,
score.score score,
a1.max_score max_score
from
score
inner join
(select -- 每门课程最高分
course_id,
max(score) max_score
from
score
group by
course_id
)a1
on
score.course_id = a1.course_id
)b1
where
b1.score != b1.max_score
group by
b1.course_id
) b2
on
a2.course_id = b2.course_id
)c1
where
c1.score = c1.max_score
or
c1.score = c1.max_two_score
or
c1.score is null
)c2
on
c2.student_id = student.sid
;
-- 45、检索至少选修两门课程的学生学号；
select
student_id
from
score
group by
course_id
having
count(course_id) >= 2
;
-- 46、查询没有学生选修的课程的课程号和课程名；
select
cid,
cname
from
course
where
cid not in
(
select distinct
course_id
from
score
)
;
-- 47、查询没带过任何班级的老师id和姓名；
select
tid
from
teacher
where
tid not in
(
select distinct
tid
from
teach2cls
)
;
-- 48、查询有两门以上课程超过80分的学生id及其平均成绩；
select
student_id,
avg(score) avg_score
from
score
where
student_id in
(select
student_id
from
score
where
score >= 80
group by
student_id
having
count(score) >= 2
)
group by
student_id
;
-- 49、检索“3”课程分数小于60，按分数降序排列的同学学号；
select
student_id
from
score
where
course_id = 3
and
score < 60
order by
score
;
-- 50、删除编号为“2”的同学的“1”课程的成绩；
delete from
score
where
student_id = 2
and
course_id = 1
;
-- 51、查询同时选修了物理课和生物课的学生id和姓名；
select
sid,
sname
from
student
where
sid in
(select
student_id
from
score
where
course_id in
(select
cid
from
course
where
cname = "物理"
or
cname = "生物"
)
group by
student_id
having
count(course_id) = 2
)
;
"""
