一些数据库习题以及答案

1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT s.s_id, s.s_name, sc1.s_score AS score_01, sc2.s_score AS score_02
FROM Student s
JOIN Score sc1 ON s.s_id = sc1.s_id AND sc1.c_id = '01'
JOIN Score sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02'
WHERE sc1.s_score > sc2.s_score;

2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT s.s_id, s.s_name, sc1.s_score AS score_01, sc2.s_score AS score_02
FROM Student s
JOIN Score sc1 ON s.s_id = sc1.s_id AND sc1.c_id = '01'
JOIN Score sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02'
WHERE sc1.s_score < sc2.s_score;

3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT s.s_id, s.s_name, AVG(sc.s_score) AS average_score
FROM Student s
JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
HAVING AVG(sc.s_score) >= 60;

4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（包括有成绩的和无成绩的）

SELECT s.s_id, s.s_name, AVG(sc.s_score) AS average_score
FROM Student s
LEFT JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
HAVING AVG(sc.s_score) < 60 OR AVG(sc.s_score) IS NULL;

5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT s.s_id, s.s_name, COUNT(sc.c_id) AS course_count, SUM(sc.s_score) AS total_score
FROM Student s
LEFT JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name;

6. 查询"李"姓老师的数量

SELECT COUNT(*) AS teacher_count
FROM Teacher
WHERE t_name LIKE '李%';

7. 查询学过"张三"老师授课的同学的信息

SELECT s.*
FROM Student s
JOIN Score sc ON s.s_id = sc.s_id
JOIN Course c ON sc.c_id = c.c_id
JOIN Teacher t ON c.t_id = t.t_id
WHERE t.t_name = '张三';

8. 查询没学过"张三"老师授课的同学的信息

SELECT s.*
FROM Student s
WHERE s.s_id NOT IN (
  SELECT DISTINCT s.s_id
  FROM Student s
  JOIN Score sc ON s.s_id = sc.s_id
  JOIN Course c ON sc.c_id = c.c_id
  JOIN Teacher t ON c.t