【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 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,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