【MySQL】多表联合查询常见练习题

数据库表如下：   

        teacher：老师表

        course：课程表

        student：学生表

        class：班级表

        sc：成绩表

一、根据上面5张表写sql语句

1. 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数

select student.*,t1.score 课程1分数,t2.score 课程2分数 from studentinner join (select * from sc where cid=1) t1 on student.sid=t1.sidinner join (select * from sc where cid=2) t2 on t1.sid=t2.sidwhere t1.score>t2.score;

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select student.sid,sname,avg(score) from studentleft join sc on student.sid=sc.sidgroup by student.sidhaving avg(score)>=60;

select student.sid,sname,avg(score) from sc,studentwhere student.sid=sc.sidgroup by student.sidhaving avg(score)>=60;

3. 查询在 SC 表存在成绩的学生信息

select * from student where sid in(select sid from sc);

select distinct student.* from studentinner join sc on student.sid=sc.sid ;

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

select student.sid,sname,count(cid),sum(score) from studentleft join sc on student.sid=sc.sidgroup 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 studentleft join sc on student.sid=sc.sidgroup by student.sidhaving count(cid)<(select count(*) from course);

8. 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息

select distinct student.* from studentleft join sc on student.sid=sc.sidwhere cid in(select cid from sc where sid=1 and student.sid != 1);

9. 查询和” 01 “号的同学学习的课程完全相同的其他同学的

    注意：一模一样 1.范围相同 2.个数相同

select student.* from  studentinner join sc on student.sid=sc.sidwhere student.sid not in                          -- 范围（逆向）(select sid from sc where cid not in(select cid from sc where sid=1))and student.sid != 1group by student.sidhaving 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,studentwhere score<60 and sc.sid=student.sidgroup by student.sidhaving count(cid)>=2;

12. 检索” 01 “课程分数小于 60，按分数降序排列的学生信息

select * from student,scwhere student.sid=sc.sid and cid=1 and score<60order by score desc

13. 查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 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,scwhere course.cid=sc.cidgroup by course.cid

-- 各科成绩最高分、最低分和平均分

select sc.cid,cname,max(score),min(score),avg(score) from course,scwhere course.cid=sc.cidgroup 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(*) * 100from scgroup by cid