数据库作业5（上）

习题解答

---

1. 查询选课表中的全部数据

SELECT * FROM SC;

---

2. 查询计算机系学生的姓名和年龄

SELECT Sname, Sage
FROM Student
WHERE Sdept = '计算机系';

---

3. 查询考试成绩在70~80分之间的学生学号、课程号和成绩

SELECT Sno, Cno, Grade
FROM SC
WHERE Grade BETWEEN 70 AND 80;

---

4. 查询计算机系年龄在18~20岁之间且性别为“男”的学生姓名、年龄

SELECT Sname, Sage
FROM Student
WHERE Sdept = '计算机系'
  AND Sage BETWEEN 18 AND 20
  AND Ssex = '男';

---

5. 查询c001课程的考试最高分

SELECT MAX(Grade) AS 最高分
FROM SC
WHERE Cno = 'c001';

---

6. 查询计算机系学生的最大年龄和最小年龄

SELECT MAX(Sage) AS 最大年龄, MIN(Sage) AS 最小年龄
FROM Student
WHERE Sdept = '计算机系';

---

7. 统计每个系的学生人数

SELECT Sdept, COUNT(*) AS 学生人数
FROM Student
GROUP BY Sdept;

---

8. 统计每门课程的选课人数和考试最高分

SELECT Cno, COUNT(Sno) AS 选课人数, MAX(Grade) AS 最高分
FROM SC
GROUP BY Cno;

---

9. 统计每个学生的选课数和考试总成绩，并按选课门数升序显示结果

SELECT Sno, COUNT(Cno) AS 选课数, SUM(Grade) AS 总成绩
FROM SC
GROUP BY Sno
ORDER BY 选课数 ASC;

---

10. 查询总成绩超过200分的学生，列出学号和总成绩

SELECT Sno, SUM(Grade) AS 总成绩
FROM SC
GROUP BY Sno
HAVING SUM(Grade) > 200;

---

11. 查询选了c002课程的学生姓名和所在系

SELECT Sname, Sdept
FROM Student
WHERE Sno IN (
    SELECT Sno
    FROM SC
    WHERE Cno = 'c002'
);

---

12. 查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果

SELECT Student.Sname, SC.Cno, SC.Grade
FROM Student
JOIN SC ON Student.Sno = SC.Sno
WHERE SC.Grade > 80
ORDER BY SC.Grade DESC;

---

13. 查询哪些学生没选课，列出学号、姓名和所在系

SELECT Student.Sno, Sname, Sdept
FROM Student
LEFT JOIN SC ON Student.Sno = SC.Sno
WHERE SC.Sno IS NULL;

---

14. 查询与Java课程在同一学期开设的课程的课程名和开课学期

假设“Java”课程的开课学期为Semester字段：

SELECT Cname, Semester
FROM Course
WHERE Semester = (
    SELECT Semester
    FROM Course
    WHERE Cname = 'Java'
);

---

15. 查询与李勇年龄相同的学生姓名、所在系和年龄

SELECT Sname, Sdept, Sage
FROM Student
WHERE Sage = (
    SELECT Sage
    FROM Student
    WHERE Sname = '李勇'
);

---

16. 用子查询实现以下查询

（1）查询选了c001课程的学生姓名和所在系

SELECT Sname, Sdept
FROM Student
WHERE Sno IN (
    SELECT Sno
    FROM SC
    WHERE Cno = 'c001'
);

（2）查询数学系成绩80分以上的学生学号、姓名、课程号和成绩

SELECT Student.Sno, Sname, SC.Cno, SC.Grade
FROM Student
JOIN SC ON Student.Sno = SC.Sno
WHERE Sdept = '数学系' AND SC.Grade > 80;

（3）查询计算机系考试成绩最高的学生的姓名

SELECT Sname
FROM Student
WHERE Sno = (
    SELECT SC.Sno
    FROM SC
    JOIN Student ON SC.Sno = Student.Sno
    WHERE Sdept = '计算机系'
    ORDER BY Grade DESC
    LIMIT 1
);

（4）查询数据结构考试成绩最高的学生的姓名、所在系、性别和成绩

SELECT Sname, Sdept, Ssex, Grade
FROM Student
JOIN SC ON Student.Sno = SC.Sno
JOIN Course ON SC.Cno = Course.Cno
WHERE Cname = '数据结构'
ORDER BY Grade DESC
LIMIT 1;

---

17. 查询选修Java课程的学生姓名和所在系

SELECT Sname, Sdept
FROM Student
WHERE Sno IN (
    SELECT Sno
    FROM SC
    WHERE Cno = (
        SELECT Cno
        FROM Course
        WHERE Cname = 'Java'
    )
);

---

18. 查询计算机系选课的学生姓名和性别

SELECT DISTINCT Sname, Ssex
FROM Student
JOIN SC ON Student.Sno = SC.Sno
WHERE Sdept = '计算机系';

---

19. 创建新表test_t并插入数据

创建表结构

CREATE TABLE test_t (
    COL1 INT NULL,
    COL2 CHAR(10) NOT NULL,
    COL3 CHAR(10) NULL
);

插入数据

INSERT INTO test_t (COL2) VALUES ('B1');
INSERT INTO test_t (COL1, COL2, COL3) VALUES (1, 'B2', 'C2');
INSERT INTO test_t (COL1, COL2) VALUES (2, 'B3');

---

注意事项

1. 表名与字段名需与实际数据库一致，如“计算机系”可能需替换为实际字段值。
2. 部分数据库语法差异：如LIMIT 1在MySQL中适用，SQL Server中需改为TOP 1。
3. 空值处理：插入空值时需明确指定字段或省略对应列。
4. 性能优化：复杂查询可通过索引或临时表优化执行效率。