20道版本

1 SELECT s.name AS student_name, c.course_name, g.score
FROM students s
JOIN grades g ON s.student_id = g.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE c.teacher_name = '王刚' AND g.score > 90;

2 SELECT DISTINCT s.name AS student_name, s.college
FROM students s
JOIN grades g ON s.student_id = g.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE c.teacher_name = '王刚';

3 SELECT s.student_id, s.name
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id AND c.course_name = '软件工程'
WHERE c.course_name IS NULL;

4 SELECT student_id
FROM enrollments
GROUP BY student_id
HAVING COUNT(course_id) >= 2;

5 SELECT s.name AS student_name, g.score
FROM students s
JOIN grades g ON s.student_id = g.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE c.course_name = '经济学' AND g.score < 60;

6 SELECT DISTINCT e1.student_id
FROM enrollments e1
JOIN enrollments e2 ON e1.course_id = e2.course_id
WHERE e2.student_id = 'T06' AND e1.student_id <> 'T06';

7 SELECT s.name AS student_name, s.college
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_id IN ('C3', 'C4')
GROUP BY s.student_id, s.name, s.college
HAVING COUNT(DISTINCT e.course_id) = 2;

8 SELECT c.course_id, c.course_name
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id AND e.student_id = (
SELECT student_id FROM students WHERE name = '王石'
)
WHERE e.student_id IS NULL;

9 SELECT c.course_id
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.course_id IS NULL;

10 SELECT s.name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.name
HAVING COUNT(DISTINCT e.course_id) = (SELECT COUNT(*) FROM courses);

11 SELECT s.college AS 学院名称, AVG(g.score) AS 平均分
FROM grades g
JOIN students s ON g.student_id = s.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE c.course_name = '经济学'
GROUP BY s.college
ORDER BY 平均分 DESC;

12 SELECT s.name AS 姓名, s.college AS 学院名称, g.score AS 成绩
FROM grades g
JOIN students s ON g.student_id = s.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE c.course_name = '经济学'
ORDER BY s.college, g.score DESC;

13 SELECT c.course_id AS 课程号, c.course_name AS 课程名, t.teacher_name AS 教师姓名
FROM courses c
JOIN course_teachers ct ON c.course_id = ct.course_id
JOIN teachers t ON ct.teacher_id = t.teacher_id
WHERE c.credit_hours BETWEEN 30 AND 45;

14 SELECT s.name AS 姓名
FROM grades g
JOIN students s ON g.student_id = s.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE c.course_name = '经济学'
AND g.score = (SELECT MAX(score) FROM grades WHERE course_id = (SELECT course_id FROM courses WHERE course_name = '经济学'));

15 SELECT c.course_id AS 课程号, c.course_name AS 课程名
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name
HAVING COUNT(e.student_id) > 5;

16 SELECT s.student_id AS 学号, s.name AS 姓名, c.course_name AS 课程名, g.score AS 成绩
FROM grades g
JOIN students s ON g.student_id = s.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE g.semester = '2024-1'
AND g.score > (
SELECT AVG(g2.score)
FROM grades g2
JOIN students s2 ON g2.student_id = s2.student_id
WHERE g2.semester = '2024-1' AND s2.class_id = s.class_id
)
ORDER BY s.student_id; -- Optional ordering by student_id

17 SELECT t.teacher_id AS 教师号, t.teacher_name AS 教师姓名, t.college AS 学院名称
FROM teachers t
JOIN teaching_records tr ON t.teacher_id = tr.teacher_id
WHERE tr.semester = '2024-2'
AND tr.credit_hours > (
SELECT AVG(tr2.credit_hours)
FROM teaching_records tr2
JOIN teachers t2 ON tr2.teacher_id = t2.teacher_id
WHERE tr2.semester = '2024-2' AND t2.college = t.college
)
GROUP BY t.teacher_id, t.teacher_name, t.college;

18 SELECT s.student_id AS 学号, s.name AS 姓名, SUM(c.credit_hours * (g.score / 10 - 5)) AS 绩点总和 FROM students s JOIN grades g ON s.student_id = g.student_id JOIN courses c ON g.course_id = c.course_id WHERE s.college = '计算机学院' AND g.semester = '2024-1' GROUP BY s.student_id, s.name ORDER BY 绩点总和 DESC LIMIT 3;

19 SELECT s.student_id AS 学号, s.name AS 姓名, t.teacher_name AS 授课教师姓名, g.score AS 成绩, CASE WHEN g.score >= 85 THEN '优秀' WHEN g.score >= 75 THEN '良好' WHEN g.score >= 60 THEN '中等' ELSE '及格' END AS 成绩等级 FROM grades g JOIN students s ON g.student_id = s.student_id JOIN courses c ON g.course_id = c.course_id JOIN teaching_records tr ON c.course_id = tr.course_id JOIN teachers t ON tr.teacher_id = t.teacher_id WHERE g.semester = '2024-2' AND c.course_name = '数据库';

20 SELECT c.college AS 学院名称,
COUNT(c.course_id) AS 未开课数量
FROM courses c
LEFT JOIN (
SELECT course_id, COUNT(student_id) AS student_count
FROM enrollments
GROUP BY course_id
) e ON c.course_id = e.course_id
WHERE e.student_count <= 30 OR e.student_count IS NULL
GROUP BY c.college;