随笔十六 ：5大SQL练习题

郑统京

# 1、查询所有课程的名称以及对应的任课老师的名字
第一步，需要连表
select * from course inner join teacher on course.teacher_id=teacher.id;
第二步，需要查询什么条件
select course.cname,teacher.tname from course inner join teacher on course.teacher_id=teacher.id;

# 2、查询平均成绩大于80分的同学姓名和平均成绩
连表:
第一步
select * from student inner join score on student.sid=score.student_id
第二步，分组获取平均成绩进行过滤
select student.sid,avg(num)as avg_num from student inner join score on student.sid=score.student_id group by student.sid having avg(num)>80
第三步，拿到平均成绩在和学生表做内连接
SELECT
        student.sname,
        t1.avg_num
FROM
        student
        INNER JOIN （ SELECT
        student.sid,
        avg( num ) AS avg_num
FROM
        student
        INNER JOIN score ON student.sid = score.student_id
GROUP BY
        student.sid
HAVING
        avg( num ) > 80
        ) AS t1 ON student.sid = t1.student_id
子查询：
第一步先通过成绩表获取大于85分的同学id和平均成绩
select student_id,avg(num) from score group by student_id having avg(num)>80;
第二步通过内连接连表
select student.sname,avg(num) from student inner join
        (select student_id,avg(num) from score group by student_id having avg(num)>85) as sco
on student.sid=sco.student_id
# 3、查询没有报李平老师课程的学生姓名
分步操作:
        方法一:
        1、查询出李平老师的课程id
        select course.cid from course inner join teacher on course.teacher_id = teacher.id where tname=('李平')
        2、通过课程id查询学生id
        select distinct student_id score where course_id in (select course.cid from course inner join teacher on course.teacher_id = teacher.id where tname not in ('李平'))
        3、然后连表对于学生id取反
        SELECT
        student.sname
    FROM
        student
        INNER JOIN score ON student.sid = score.sutdent_id
    WHERE
        score.student_id in
        (select distinct student_id score where course_id in (select course.cid from course inner join teacher on course.teacher_id = teacher.id where tname not in ('李平')))
    方法二:
1、course和teacher连表，过滤出没有没有李平老师的cid
select course.cid from course inner join teacher on course.teacher_id = teacher.id where tname not in ('李平')
2、student和score连表，条件等与上表的course.cid
SELECT
        student.sname
FROM
        student
        INNER JOIN score ON student.sid = score.sutdent_id
WHERE
        course_id IN ( SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.id WHERE tname NOT IN ( '李平' ) )
# 4、查询没有同时选修物理课程和体育课程的学生姓名，没有选的也不要
老师思路:
        1、先查询出物理和体育的课程id
        select cid from course where cname in（'物理'，'体育'）
        2、通过成绩表进行过滤课程id当条件，在进行分组过滤，拿到count为1的学生id
        select sid from score where cid in (select cid from course where cname in（'物理'，'体育'）) group by sid having count(*)=1
        3、通过学生表通过上面的学生id获取学生信息
        SELECT
                sname
    FROM
        student
    WHERE
        sid IN (
    SELECT
        sid
    FROM
        score
    WHERE
        cid IN ( SELECT cid FROM course WHERE cname IN（ '物理'，'体育'） GROUP BY sid HAVING count( * ) = 1 )
        )
自己思路：
    1、课程与成绩连表，选出学生id
    select score.sid from score inner join coures on score.course_id = course.id where course.cname in（'物理'，'体育'） group by score.sid having count(*)=1;
    2、学生表直接用sid当条件
    SELECT
        sname
    FROM
        student
    WHERE
        sid IN (
    SELECT
        score.sid
    FROM
        score
        INNER JOIN coures ON score.course_id = course.id
    where
        course.cname in（'物理'，'体育'）        
    GROUP BY
        score.sid
    HAVING
        count( * ) = 1
        )
# 5、查询挂科成绩超过2门（包括2门）的学生成绩和班级(学生姓名)
1、在成绩表中对于学生分组，通过where score<60,having>=2,拿到学生的sid，score
select sid,score from score where score<60 group by sid having count(*)>=2

2、与学生表进行连表取出score,class_id,再去和班级表连表
select student.class_id,t1.score from student inner join (select sid,score from score group by sid having count(*)>=2) t1
on student.id=t1.sid
3、在和班级连表
SELECT
        t2.score,
        calss.captian
FROM
        class
        INNER JOIN (
select student.class_id,t1.score from student inner join (select sid,score from score group by sid having count(*)>=2) t1
on student.id=t1.sid
        ) t2 ON class.id = t2.class_id