【MySQL】多表联合查询常见练习题
数据库表如下：
teacher：老师表
course：课程表
student：学生表
class：班级表
sc：成绩表
一、根据上面5张表写sql语句
1. 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
select student.*,t1.score 课程1分数,t2.score 课程2分数 from student
inner join (select * from sc where cid=1) t1 on student.sid=t1.sid
inner join (select * from sc where cid=2) t2 on t1.sid=t2.sid
where t1.score>t2.score;2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select student.sid,sname,avg(score) from student
left join sc on student.sid=sc.sid
group by student.sid
having avg(score)>=60;select student.sid,sname,avg(score) from sc,student
where student.sid=sc.sid
group by student.sid
having avg(score)>=60;3. 查询在 SC 表存在成绩的学生信息
select * from student where sid in
(select sid from sc);select distinct student.* from student
inner join sc on student.sid=sc.sid ;4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select student.sid,sname,count(cid),sum(score) from student
left join sc on student.sid=sc.sid
group by student.sid;5. 查询「李」姓老师的数量
select count(*) from teacher where tname like '李%';6. 查询学过「张三」老师授课的同学的信息
select * from student where student.sid in(
select sid from sc where sc.cid =(
select cid from course where course.tid=(
select tid from teacher where tname='张三'
)
)
);7. 查询没有学全所有课程的同学的信息
select student.*,count(cid) from student
left join sc on student.sid=sc.sid
group by student.sid
having count(cid)<(select count(*) from course);8. 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息
select distinct student.* from student
left join sc on student.sid=sc.sid
where cid in(
select cid from sc where sid=1 and student.sid != 1
);9. 查询和” 01 “号的同学学习的课程完全相同的其他同学的
注意：一模一样 1.范围相同 2.个数相同
select student.* from student
inner join sc on student.sid=sc.sid
where student.sid not in -- 范围（逆向）
(select sid from sc where cid not in
(select cid from sc where sid=1)
)
and student.sid != 1
group by student.sid
having count(cid)=(select count(*) from sc where sid=1); -- 个数相同10. 查询没学过”张三”老师讲授的任一门课程的学生姓名
select sname from student where student.sid not in
(select sid from sc where sc.cid =
(select cid from course where course.tid =
(select tid from teacher where tname='张三')
)
)11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.sid,sname,avg(score) from sc,student
where score<60 and sc.sid=student.sid
group by student.sid
having count(cid)>=2;12. 检索” 01 “课程分数小于 60，按分数降序排列的学生信息
select * from student,sc
where student.sid=sc.sid and cid=1 and score<60
order by score desc13. 查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）
select sc.cid,cname,max(score),min(score),avg(score),
count(case when score >=60 then score end)/count(*) * 100 及格率,
count(case when score >=70 and score <80 then score end)/count(*) * 100 中等率,
count(case when score >=80 and score <90 then score end)/count(*) * 100 优良率,
count(case when score >90 then score end)/count(*) * 100 优秀率
from course,sc
where course.cid=sc.cid
group by course.cid
-- 各科成绩最高分、最低分和平均分
select sc.cid,cname,max(score),min(score),avg(score) from course,sc
where course.cid=sc.cid
group by course.cid
-- 及格率，中等率，优良率，优秀率
select cid,
count(case when score >=60 then score end)/count(*) * 100,
count(case when score >=70 and score <80 then score end)/count(*) * 100,
count(case when score >=80 and score <90 then score end)/count(*) * 100,
count(case when score >90 then score end)/count(*) * 100
from sc
group by cid