一个数据库题目理解所有的sql语句
数据库表
学生表 (students)
| id | name | age |
| 1 | 张三 | 20 |
| 2 | 李四 | 21 |
| 3 | 王五 | 22 |
课程表 (courses)
| id | name | credit |
| 1 | 数学 | 3 |
| 2 | 英语 | 4 |
| 3 | 物理 | 3 |
成绩表 (scores)
| student_id | course_id | score |
| 1 | 1 | 85 |
| 1 | 2 | 78 |
| 2 | 1 | 92 |
| 2 | 2 | 85 |
| 2 | 3 | 89 |
| 3 | 2 | 90 |
| 3 | 3 | 75 |
sql题目
- 从学生表中选择所有学生的姓名和年龄。
SELECT name, age FROM students;
- 从课程表中选择所有课程的名称和学分。
SELECT name, credit FROM courses;
- 从成绩表中选择所有学生的姓名、课程名称和分数。
SELECT s.name AS student_name, c.name AS course_name, sc.score FROM students s JOIN scores sc ON s.id = sc.student_id JOIN courses c ON sc.course_id = c.id;
- 计算每个学生的平均成绩。
SELECT student_id, AVG(score) AS avg_score FROM scores GROUP BY student_id;
- 找出选修了所有课程的学生姓名。
SELECT s.name AS student_name FROM students s WHERE NOT EXISTS ( SELECT * FROM courses WHERE NOT EXISTS ( SELECT * FROM scores WHERE scores.student_id = s.id AND scores.course_id = courses.id ) );
- 找出没有选修任何课程的学生姓名。
SELECT s.name AS student_name FROM students s LEFT JOIN scores sc ON s.id = sc.student_id WHERE sc.student_id IS NULL;
- 计算每门课程的平均分数。
SELECT course_id, AVG(score) AS avg_score FROM scores GROUP BY course_id;
- 找出每个老师教授的课程数量。
SELECT t.name AS teacher_name, COUNT(c.id) AS course_count FROM teachers t JOIN courses c ON t.id = c.teacher_id GROUP BY t.name;
- 找出选修了课程编号为1和2的学生姓名。
SELECT s.name AS student_name FROM students s JOIN scores sc ON s.id = sc.student_id WHERE sc.course_id IN (1, 2) GROUP BY s.name HAVING COUNT(DISTINCT sc.course_id) = 2;
- 计算每个学生选修的课程数量。
SELECT student_id, COUNT(course_id) AS course_count FROM scores GROUP BY student_id;
