最新国开电大《数据库应用技术》第四次形考作业答案

实验目的：通过使用SSMS工具，练习对数据库中的数据表进行单表查询、多表连接查询、子查询。对数据表中的数据进行更改和删除等操作。

实验要求：将相关的SQL 语句和运行结果的截屏保存在文件中，或填写在下面的实验报告中，并通过网络提交。

实验内容：

使用SSMS工具，在“可用数据库”中选中Students数据库，完成如下实验。

1.
单表查询。写出实现如下查询的SQL语句。

(1)  查询学生选课表中的全部数据。

(2)  查询计算机系的学生的姓名、年龄。

(3)  查询成绩在70～80分的学生的学号、课程号和成绩。

(4)  查询计算机系年龄在18～20岁男生的姓名、年龄。

(5)  查询C001课程的考试成绩最高分。

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

(7)  统计各系的学生人数。

(8)  统计每门课程的选课人数和考试成绩最高分。

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

(10)查询总成绩超过200分的学生，要求列出其学号和总成绩。

(11)查询选课门数超过2门的学生的学号、平均成绩和选课门数。
简答题 (20 分)
(1) 查询学生选课表中的全部数据：

SELECT * FROM 学生选课表;

(2) 查询计算机系的学生的姓名、年龄：SELECT 姓名, 年龄 FROM 学生选课表 WHERE 专业 = ‘计算机系’;

(3) 查询成绩在70～80分的学生的学号、课程号和成绩：SELECT 学号, 课程号, 成绩 FROM 学生选课表 WHERE 成绩 BETWEEN 70 AND 80;

(4) 查询计算机系年龄在18～20岁男生的姓名、年龄：SELECT 姓名, 年龄 FROM 学生选课表 WHERE 专业 = ‘计算机系’ AND 年龄 BETWEEN 18 AND 20 AND 性别 = ‘男’;

(5) 查询C001课程的考试成绩最高分：

SELECT MAX(成绩) AS 最高分 FROM 学生选课表 WHERE 课程号 = ‘C001’;

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

SELECT MAX(年龄) AS 最大年龄, MIN(年龄) AS 最小年龄 FROM 学生选课表 WHERE 专业 = ‘计算机系’;

(7) 统计各系的学生人数：SELECT 专业, COUNT(学号) AS 学生人数 FROM 学生选课表 GROUP BY 专业;

(8) 统计每门课程的选课人数和考试成绩最高分：SELECT 课程号, COUNT(学号) AS 选课人数, MAX(成绩) AS 最高分 FROM 学生选课表 GROUP BY 课程号;

(9) 统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果：SELECT 学号, COUNT(课程号) AS 选课门数, SUM(成绩) AS 考试总成绩

FROM 学生选课表 GROUP BY 学号 ORDER BY 选课门数 ASC;

(10) 查询总成绩超过200分的学生，要求列出其学号和总成绩：SELECT 学号, SUM(成绩) AS 总成绩 FROM 学生选课表

GROUP BY 学号 HAVING SUM(成绩) > 200;

(11) 查询选课门数超过2门的学生的学号、平均成绩和选课门数：SELECT 学号, AVG(成绩) AS 平均成绩, COUNT(课程号) AS 选课门数

FROM 学生选课表 GROUP BY 学号 HAVING COUNT(课程号) > 2;

2.
多表连接查询。写出实现如下查询的SQL语句。

(12)  查询选了C002课程的学生的姓名和所在系。

(13)  查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩降序排列结果。

(14)  查询计算机系男生选修了“数据库基础”的学生的姓名和成绩。

(15)  查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、课程号和考试成绩。

(16)  查询哪些课程没有人选修，要求列出课程号和课程名。

(17)  查询计算机系没有选课的学生，列出学生的姓名。
简答题 (15 分)
(12) 查询选了C002课程的学生的姓名和所在系：

SELECT 学生表.姓名, 学生表.所在系

FROM 学生表

JOIN 选课表 ON 学生表.学号 = 选课表.学号

WHERE 选课表.课程号 = ‘C002’;

(13) 查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩降序排列结果：

SELECT 学生表.姓名, 选课表.课程号, 选课表.成绩

FROM 学生表

JOIN 选课表 ON 学生表.学号 = 选课表.学号

WHERE 选课表.成绩 > 80

ORDER BY 选课表.成绩 DESC;

(14) 查询计算机系男生选修了“数据库基础”的学生的姓名和成绩：SELECT 学生表.姓名, 选课表.成绩

FROM 学生表

JOIN 选课表 ON 学生表.学号 = 选课表.学号

WHERE 学生表.所在系 = ‘计算机系’ AND 学生表.性别 = ‘男’ AND 选课表.课程名 = ‘数据库基础’;

(15) 查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、课程号和考试成绩：SELECT 学生表.学号, 学生表.姓名, 选课表.课程号, 选课表.成绩

FROM 学生表 LEFT JOIN 选课表 ON 学生表.学号 = 选课表.学号;

(16) 查询哪些课程没有人选修，要求列出课程号和课程名：SELECT 课程表.课程号, 课程表.课程名

FROM 课程表 LEFT JOIN 选课表 ON 课程表.课程号 = 选课表.课程号 WHERE 选课表.课程号 IS NULL;

(17) 查询计算机系没有选课的学生，列出学生的姓名：SELECT 学生表.姓名

FROM 学生表 LEFT JOIN 选课表 ON 学生表.学号 = 选课表.学号 WHERE 学生表.所在系 = ‘计算机系’ AND 选课表.学号 IS NULL;

3.
使用TOP和CASE的查询。写出实现如下查询的SQL语句。

(18)  列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。

(19)  查询Java考试成绩最低的学生的姓名、所在系和Java成绩。

(20)  查询选修了Java的学生学号、姓名、所在系和成绩，并对所在系进行如下处理：

当所在系为“计算机系”时，显示“CS”；

当所在系为“信息管理系”时，显示“IS”；

当所在系为“通信工程系”时，显示“CO”；

对其他系，均显示“OTHER”。
简答题 (15 分)
(18) 列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩：

SELECT TOP 3 学生表.学号, 学生表.姓名, 学生表.所在系, 选课表.成绩

FROM 学生表

JOIN 选课表 ON 学生表.学号 = 选课表.学号

WHERE 选课表.课程名 = ‘数据库基础’

ORDER BY 选课表.成绩 DESC;

(19) 查询Java考试成绩最低的学生的姓名、所在系和Java成绩：SELECT TOP 1 学生表.姓名, 学生表.所在系, 选课表.成绩

FROM 学生表

JOIN 选课表 ON 学生表.学号 = 选课表.学号

WHERE 选课表.课程名 = ‘Java’

ORDER BY 选课表.成绩 ASC;

(20) 查询选修了Java的学生学号、姓名、所在系和成绩，并对所在系进行如下处理：

SELECT 学生表.学号, 学生表.姓名,

CASE 学生表.所在系

WHEN ‘计算机系’ THEN ‘CS’

WHEN ‘信息管理系’ THEN ‘IS’

WHEN ‘通信工程系’ THEN ‘CO’

ELSE ‘OTHER’

END AS 所在系,

选课表.成绩

FROM 学生表

JOIN 选课表 ON 学生表.学号 = 选课表.学号

WHERE 选课表.课程名 = ‘Java’;

4.
子查询。写出实现如下查询的SQL语句。

(21)  查询选修了C001课程的学生姓名和所在系。

(22)  查询计算机文化学考试成绩在80分以上的学生的学号和姓名。

(23)  查询计算机文化学考试成绩最高的学生姓名和所在系。

(24)  查询年龄最大的男生的姓名和年龄。

(25)  查询C001课程的考试成绩高于C001课程的平均成绩的学生的学号及其C001课程考试成绩。
简答题 (20 分)
(21) 查询选修了C001课程的学生姓名和所在系：

SELECT 姓名, 所在系

FROM 学生表

WHERE 学号 IN (

SELECT 学号 FROM 选课表 WHERE 课程号 = ‘C001’

);

(22) 查询计算机文化学考试成绩在80分以上的学生的学号和姓名：

SELECT 学号, 姓名

FROM 学生表

WHERE 学号 IN (

SELECT 学号 FROM 选课表 WHERE 课程名 = ‘计算机文化学’ AND 成绩 > 80

);

(23) 查询计算机文化学考试成绩最高的学生姓名和所在系：

SELECT 姓名, 所在系

FROM 学生表

WHERE 学号 = (

SELECT TOP 1 学号 FROM 选课表 WHERE 课程名 = ‘计算机文化学’ ORDER BY 成绩 DESC

);

(24) 查询年龄最大的男生的姓名和年龄：

SELECT TOP 1 姓名, 年龄

FROM 学生表

WHERE 性别 = ‘男’

ORDER BY 年龄 DESC;

(25) 查询C001课程的考试成绩高于C001课程的平均成绩的学生的学号及其C001课程考试成绩：

SELECT 学号, 成绩

FROM 选课表

WHERE 课程号 = ‘C001’ AND 成绩 > (

SELECT AVG(成绩) FROM 选课表 WHERE 课程号 = ‘C001’

);

5.
数据更改。写出实现如下操作的SQL语句。

(26)  将C001课程的考试成绩加10分。

(27)  将计算机系所有选修了“计算机文化学”课程的学生考试成绩加10分，分别用子查询和多表连接形式实现。
简答题 (15 分)
(26) 将C001课程的考试成绩加10分：

UPDATE 选课表

SET 成绩 = 成绩 + 10

WHERE 课程号 = ‘C001’;

(27) 将计算机系所有选修了“计算机文化学”课程的学生考试成绩加10分，分别用子查询和多表连接形式实现。

使用子查询：

UPDATE 选课表

SET 成绩 = 成绩 + 10

WHERE 学号 IN (

SELECT 学号

FROM 学生表

WHERE 所在系 = ‘计算机系’

) AND 课程号 = (

SELECT 课程号

FROM 课程表

WHERE 课程名 = ‘计算机文化学’

);

使用多表连接：UPDATE 选课表

SET 选课表.成绩 = 选课表.成绩 + 10

FROM 选课表

JOIN 学生表 ON 选课表.学号 = 学生表.学号

JOIN 课程表 ON 选课表.课程号 = 课程表.课程号

WHERE 学生表.所在系 = ‘计算机系’ AND 课程表.课程名 = ‘计算机文化学’;

6.
数据删除。写出实现如下操作的SQL语句。

(28)  删除考试成绩低于50分的学生的选课记录。

(29)  删除信息管理系考试成绩低于50分的学生的该门课程的选课记录，分别用子查询和多表连接形式实现。

(30)  删除Java考试成绩最低的学生的Java选课记录。
简答题 (15 分)
(28) 删除考试成绩低于50分的学生的选课记录：

DELETE FROM 选课表

WHERE 学号 IN (

SELECT 学号 FROM 选课表 WHERE 成绩 < 50

);

 

(29) 删除信息管理系考试成绩低于50分的学生的该门课程的选课记录，分别用子查询和多表连接形式实现。

使用子查询：

DELETE FROM 选课表

WHERE 学号 IN (

SELECT 学号

FROM 学生表

WHERE 所在系 = ‘信息管理系’

) AND 课程号 = (

SELECT 课程号

FROM 课程表

WHERE 课程名 = ‘计算机文化学’

) AND 成绩 < 50;

使用多表连接：

DELETE 选课表

FROM 选课表

JOIN 学生表 ON 选课表.学号 = 学生表.学号

JOIN 课程表 ON 选课表.课程号 = 课程表.课程号

WHERE 学生表.所在系 = ‘信息管理系’ AND 课程表.课程名 = ‘计算机文化学’ AND 选课表.成绩 < 50;

(30) 删除Java考试成绩最低的学生的Java选课记录：

DELETE FROM 选课表

WHERE 学号 = (

SELECT TOP 1 学号

FROM 选课表

WHERE 课程名 = ‘Java’

ORDER BY 成绩 ASC

) AND 课程名 = ‘Java’;