从小白到小黑 python学习之旅 日常总结 48（补充练习）

补充练习

#class

#course

#score

#student

#teacher

 

 

-- 1、查询所有的课程的名称以及对应的任课老师姓名
-- SELECT
--     course.cname,
--     teacher.tname 
-- FROM
--     course
--     INNER JOIN teacher ON course.teacher_id = teacher.tid;

-- 2、查询平均成绩大于八十分的同学的姓名和平均成绩
-- SELECT
--     student.sname,
--     t1.avg_num 
-- FROM
--     student
--     INNER JOIN (
--     SELECT
--         score.student_id,
--         avg( num ) AS avg_num 
--     FROM
--         score
--         INNER JOIN student ON score.student_id = student.sid 
--     GROUP BY
--         score.student_id 
--     HAVING
--         AVG( num ) > 80 
--     ) AS t1 ON student.sid = t1.student_id;

SELECT
    student.sname AS '姓名',
    avg( score.num ) AS '平均成绩' 
FROM
    score
    INNER JOIN student ON score.student_id = student.sid 
GROUP BY
    score.student_id 
HAVING
    AVG( num )> 80;


-- 3、 查询没有报李平老师课的学生姓名
# 分步操作
# 1 先找到李平老师教授的课程id
# 2 再找所有报了李平老师课程的学生id
# 3 之后去学生表里面取反 就可以获取到没有报李平老师课程的学生姓名
-- SELECT
--     student.sname 
-- FROM
--     student 
-- WHERE
--     sid NOT IN (
--     SELECT DISTINCT
--         score.student_id 
--     FROM
--         score 
--     WHERE
--         score.course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' ) 
--     );

-- 4、 查询没有同时选修物理课程和体育课程的学生姓名
--     (只要选了一门的 选了两门和没有选的都不要)
# 1 先查物理和体育课程的id
# 2 再去获取所有选了物理和体育的学生数据
# 3 按照学生分组 利用聚合函数count筛选出只选了一门的学生id
# 4 依旧id获取学生姓名
-- SELECT
--     student.sname 
-- FROM
--     student 
-- WHERE
--     student.sid IN (
--     SELECT
--         score.student_id 
--     FROM
--         score 
--     WHERE
--         score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( '物理', '体育' ) ) 
--     GROUP BY
--         score.student_id 
--     HAVING
--         COUNT( score.course_id ) = 1 
--     );

-- 5、 查询挂科超过两门(包括两门)的学生姓名和班级
# 1 先筛选出所有分数小于60的数据
# 2 按照学生分组 对数据进行计数获取大于等于2的数据
SELECT
    class.caption,
    student.sname 
FROM
    class
    INNER JOIN student ON class.cid = student.class_id 
WHERE
    student.sid IN (
    SELECT
        score.student_id 
    FROM
        score 
    WHERE
        score.num < 60 GROUP BY score.student_id HAVING COUNT( score.course_id ) >= 2 
    );