# 准备表、记录
CREATE DATABASE db1;
USE db1;
SOURCE /root/init.sql;
练习
1. 查询所有的课程的名称以及对应的任课老师姓名
SELECT course.cname, teacher.tname
FROM course
JOIN teacher ON course.teacher_id = teacher.tid;
2. 查询学生表中男女生各有多少人
SELECT gender, COUNT(*)
FROM student
GROUP BY gender;
3. 查询物理成绩等于100的学生的姓名
SELECT student.sname
FROM score
JOIN student ON score.student_id = student.sid
WHERE score.course_id = (SELECT cid FROM course WHERE cname = '物理')
AND score.num = 100;
4. 查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT student.sname, AVG(score.num) as avg_score
FROM score
JOIN student ON score.student_id = student.sid
GROUP BY student.sid
HAVING avg_score > 80;
5. 查询所有学生的学号，姓名，选课数，总成绩
SELECT student.sid, student.sname, COUNT(score.course_id) as course_count, SUM(score.num) as total_score
FROM student
LEFT JOIN score ON student.sid = score.student_id
GROUP BY student.sid;
6. 查询姓李老师的个数
SELECT COUNT(*)
FROM teacher
WHERE tname LIKE '李%';
7. 查询没有报李平老师课的学生姓名
SELECT sname
FROM student
WHERE sid NOT IN (
SELECT student_id
FROM score
WHERE course_id IN (
SELECT cid
FROM course
WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '李平老师')
)
);
8. 查询物理课程比生物课程高的学生的学号
SELECT s1.student_id
FROM score s1
JOIN score s2 ON s1.student_id = s2.student_id
WHERE s1.course_id = (SELECT cid FROM course WHERE cname = '物理')
AND s2.course_id = (SELECT cid FROM course WHERE cname = '生物')
AND s1.num > s2.num;
9. 查询没有同时选修物理课程和体育课程的学生姓名
SELECT sname
FROM student
WHERE sid NOT IN (
SELECT student_id
FROM score
WHERE course_id IN (
SELECT cid
FROM course
WHERE cname IN ('物理', '体育')
)
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) = 2
);
10. 查询挂科超过两门(包括两门)的学生姓名和班级
SELECT student.sname, class.caption
FROM student
JOIN class ON student.class_id = class.cid
WHERE student.sid IN (
SELECT student_id
FROM score
WHERE num < 60
GROUP BY student_id
HAVING COUNT(*) >= 2
);
11. 查询选修了所有课程的学生姓名
SELECT sname
FROM student
WHERE sid IN (
SELECT student_id
FROM score
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) = (SELECT COUNT(*) FROM course)
);
12. 查询李平老师教的课程的所有成绩记录
SELECT *
FROM score
WHERE course_id IN (
SELECT cid
FROM course
WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '李平老师')
);
13. 查询全部学生都选修了的课程号和课程名
SELECT course.cid, course.cname
FROM course
WHERE NOT EXISTS (
SELECT *
FROM student
WHERE NOT EXISTS (
SELECT *
FROM score
WHERE course.cid = score.course_id
AND student.sid = score.student_id
)
);
14. 查询每门课程被选修的次数
SELECT course.cname, COUNT(score.course_id) as selection_count
FROM course
LEFT JOIN score ON course.cid = score.course_id
GROUP BY course.cid;
15. 查询只选修了一门课程的学生姓名和学号
SELECT student.sid, student.sname
FROM student
WHERE sid IN (
SELECT student_id
FROM score
GROUP BY student_id
HAVING COUNT(course_id) = 1
);
16. 查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT num
FROM score
ORDER BY num DESC;
17. 查询平均成绩大于85的学生姓名和平均成绩
SELECT student.sname, AVG(score.num) as avg_score
FROM score
JOIN student ON score.student_id = student.sid
GROUP BY student.sid
HAVING avg_score > 85;
18. 查询生物成绩不及格的学生姓名和对应生物分数
SELECT student.sname, score.num
FROM score
JOIN student ON score.student_id = student.sid
WHERE score.course_id = (SELECT cid FROM course WHERE cname = '生物')
AND score.num < 60;
SELECT student.sname
FROM student
WHERE sid IN (
SELECT student_id
FROM score
WHERE course_id IN (
SELECT cid
FROM course
WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '李平老师')
)
GROUP BY student_id
ORDER BY AVG(num) DESC
LIMIT 1
);
20. 查询每门课程成绩最好的前两名学生姓名
SELECT sname
FROM (
SELECT student.sname, score.course_id, score.num,
ROW_NUMBER() OVER(PARTITION BY score.course_id ORDER BY score.num DESC) as rank
FROM score
JOIN student ON score.student_id = student.sid
) as ranked
WHERE rank <= 2;
21. 查询不同课程但成绩相同的学号，课程号，成绩
SELECT s1.student_id, s1.course_id, s1.num
FROM score s1
JOIN score s2 ON s1.num = s2.num
AND s1.course_id != s2.course_id
AND s1.student_id = s2.student_id;
22. 查询没学过“叶平”老师课程的学生姓名以及选修的课程名称
SELECT sname, cname
FROM student
JOIN score ON student.sid = score.student_id
JOIN course ON score.course_id = course.cid
WHERE student.sid NOT IN (
SELECT student_id
FROM score
WHERE course_id IN (
SELECT cid
FROM course
WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '叶平')
)
);
23. 查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名
SELECT DISTINCT s2.sid, s2.sname
FROM score s1
JOIN score s2 ON s1.course_id = s2.course_id
JOIN student s3 ON s2.student_id = s3.sid
WHERE s1.student_id = 1 AND s2.student_id != 1;
24. 任课最多的老师中学生单科成绩最高的学生姓名
SELECT sname
FROM student
WHERE sid IN (
SELECT student_id
FROM score
WHERE course_id IN (
SELECT cid
FROM course
WHERE teacher_id = (
SELECT teacher_id
FROM (
SELECT teacher_id, COUNT(*) as course_count
FROM course
GROUP BY teacher_id
ORDER BY course_count DESC
LIMIT 1
) as most_courses
)
)
ORDER BY num DESC
LIMIT 1
);
