-- 7. 查询分数大于整体平均分的学生信息
SELECT s.student_id, s.name, s.gender
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
WHERE sc.score > (SELECT AVG(score) FROM student_courses);
-- 8. 查询学过的科目数跟学号为02一模一样的学生
SELECT s.student_id, s.name
FROM students s
JOIN (SELECT student_id, COUNT() AS course_count
FROM student_courses
WHERE student_id = '02'
GROUP BY student_id) AS ref
ON (SELECT COUNT() FROM student_courses sc WHERE sc.student_id = s.student_id) = ref.course_count;
-- 9. 查询销售额大于平均销售额的订单ID，销售额
SELECT order_id, sales_amount
FROM orders
WHERE sales_amount > (SELECT AVG(sales_amount) FROM orders);
-- 10. 查询出只选修两门课程的学生学号和姓名
SELECT s.student_id, s.name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING COUNT(sc.course_id) = 2;
-- 11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.student_id, s.name, AVG(sc.score) AS average_score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
WHERE sc.score < 60
GROUP BY s.student_id
HAVING COUNT(sc.course_id) >= 2;
-- 12. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT c.course_id, c.course_name, AVG(sc.score) AS average_score
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id
ORDER BY average_score DESC, c.course_id ASC;
-- 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s.student_id, s.name, sc.course_id, sc.score, AVG(sc.score) OVER (PARTITION BY s.student_id) AS average_score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
ORDER BY average_score DESC;
-- 14. 查询所教课程平均分大于30分的教师姓名、课程名、平均成绩、和课程的学习人数
SELECT t.teacher_name, c.course_name, AVG(sc.score) AS average_score, COUNT(sc.student_id) AS student_count
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY t.teacher_name, c.course_name
HAVING AVG(sc.score) > 30;
-- 15. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT s.student_id, s.name, sc.score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
WHERE sc.course_id = '01' AND sc.score < 60
ORDER BY sc.score DESC;
-- 16. 查有成绩的学生信息
SELECT DISTINCT s.student_id, s.name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
WHERE sc.score IS NOT NULL;
-- 17. 检索学过语文课程的同学信息，按分数降序排列，若分数相同则按照学号排序，取结果的前三条
SELECT s.student_id, s.name, sc.score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '语文'
ORDER BY sc.score DESC, s.student_id
LIMIT 3;
-- 18. 查询既学过01课程又学过02课程的学号和姓名
SELECT s.student_id, s.name
FROM students s
WHERE EXISTS (SELECT 1 FROM student_courses sc WHERE sc.student_id = s.student_id AND sc.course_id = '01')
AND EXISTS (SELECT 1 FROM student_courses sc WHERE sc.student_id = s.student_id AND sc.course_id = '02');
-- 19. 查询01课程比02课程成绩高的所有学生的学号
SELECT s.student_id
FROM students s
JOIN student_courses sc1 ON s.student_id = sc1.student_id AND sc1.course_id = '01'
JOIN student_courses sc2 ON s.student_id = sc2.student_id AND sc2.course_id = '02'
WHERE sc1.score > sc2.score;
-- 20. 查询没有学全所有课程的同学的信息
SELECT s.student_id, s.name
FROM students s
WHERE (SELECT COUNT() FROM courses) > (SELECT COUNT() FROM student_courses sc WHERE sc.student_id = s.student_id);
-- 21. 查询没有学全所有课程的同学的姓名、学号、性别、选修课程名称、授课教师名称以及该课程的分数
SELECT s.name, s.student_id, s.gender, c.course_name, t.teacher_name, sc.score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
JOIN teachers t ON c.teacher_id = t.teacher_id
WHERE (SELECT COUNT() FROM courses) > (SELECT COUNT() FROM student_courses WHERE student_id = s.student_id);
-- 22. 查询只学过01和02这两门课程的学号和姓名
SELECT s.student_id, s.name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING COUNT(DISTINCT sc.course_id) = 2 AND SUM(CASE WHEN sc.course_id IN ('01', '02') THEN 1 ELSE 0 END) = 2;
-- 23. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 0 )
SELECT s.student_id, s.name, COUNT(sc.course_id) AS course_count, COALESCE(SUM(sc.score), 0) AS total_score
FROM students s
LEFT JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id;
-- 24. 查询分数低于60的记录，统计这些记录各科课程的平均成绩，并且输出各科课程的教师
SELECT c.course_id, c.course_name, t.teacher_name, AVG(sc.score) AS average_score
FROM courses c
JOIN teachers t ON c.teacher_id = t.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
WHERE sc.score < 60
GROUP BY c.course_id, t.teacher_name;
-- 25. 查询任何一门课程成绩在 70 分以上的学生的姓名、所学所有课程名称和该课程的分数
SELECT s.name, c.course_name, sc.score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE s.student_id IN (SELECT DISTINCT student_id FROM student_courses WHERE score > 70);
-- 26. 查询只有02课程没学过，其他课程均学过的学生信息
SELECT s.student_id, s.name
FROM students s
WHERE NOT EXISTS (SELECT 1 FROM student_courses sc WHERE sc.student_id = s.student_id AND sc.course_id = '02')
AND (SELECT COUNT() FROM courses) - 1 = (SELECT COUNT() FROM student_courses sc WHERE sc.student_id = s.student_id);
-- 27. 查询学过01课程但没学过02课程的情况
SELECT s.student_id, s.name
FROM students s
WHERE EXISTS (SELECT 1 FROM student_courses sc WHERE sc.student_id = s.student_id AND sc.course_id = '01')
AND NOT EXISTS (SELECT 1 FROM student_courses sc WHERE sc.student_id = s.student_id AND sc.course_id = '02');
-- 28. 查询和01号的同学学习的课程完全相同的其他同学的信息
SELECT DISTINCT s2.student_id, s2.name
FROM students s1
JOIN student_courses sc1 ON s1.student_id = sc1.student_id
JOIN students s2 ON s1.student_id <> s2.student_id
JOIN student_courses sc2 ON s2.student_id = sc2.student_id
WHERE sc1.course_id = sc2.course_id
GROUP BY s2.student_id
HAVING COUNT(DISTINCT sc1.course_id) = (SELECT COUNT(DISTINCT course_id) FROM student_courses WHERE student_id = s1.student_id);
-- 29. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s.student_id, s.name, AVG(sc.score) AS average_score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING AVG(sc.score) >= 60;
-- 30. 查询「李」姓老师所教学生的总人数和总成绩
SELECT COUNT(DISTINCT s.student_id) AS total_students, SUM(sc.score) AS total_score
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
JOIN students s ON sc.student_id = s.student_id
