SQL SERVER2005上机实验指导参考答案

实验三数据查询

二、实验内容及步骤

2．在studentsdb数据库中使用SELECT语句进行基本查询。

（1）在student_info表中，查询每个学生的学号、姓名、出生日期信息。

SELECT 学号, 姓名, 出生日期FROM student_info

（2）查询学号为0002 的学生的姓名和家庭住址。

SELECT 姓名, 家庭住址FROM student_info WHERE 学号= '0002'

（3）找出所有男同学的学号和姓名。

SELECT 学号, 姓名FROM student_info WHERE 性别= '男'

3．使用SELECT语句进行条件查询

（1）在grade表中查找分数在80~90范围内的学生的学号和分数。

SELECT 学号, 分数FROM grade WHERE (分数BETWEEN 80 AND 90)

（2）在grade表中查询课程编号为0003的学生的平均分。

SELECT SUM(分数) / COUNT(*) AS 平均分FROM grade WHERE (课程编号= '0003') （3）在grade表中查询学习各门课程的人数。

SELECT 课程编号, COUNT(*) AS 人数FROM grade GROUP BY 课程编号

（4）将学生按出生日期由大到小排序。

SELECT * FROM student_info ORDER BY 出生日期DESC

（5）查询所有姓“张”的学生的学号和姓名。

SELECT * FROM student_info WHERE 姓名LIKE '张%'

4．对student_info表，按性别顺序列出学生的学号、姓名、性别、出生日期及家庭住址，性别相同的按学号由小到大排序。

SELECT 学号, 姓名, 性别, 出生日期, 家庭住址

FROM student_info

ORDER BY 性别, 学号

5．使用GROUP BY查询子句列出各个学生的平均成绩。

SELECT 学号, SUM(分数) / COUNT(*) AS 平均成绩

FROM grade

GROUP BY 学号

6．使用UNION运算符将student_info表中姓“张”的学生的学号、姓名与curriculum 表的课程编号、课程名称返回在一个表中，且列名为u_编号、u_名称，如图1-8所示。

图1-8 联合查询结果集

SELECT 学号AS u_编号, 姓名AS u_名称

FROM student_info

WHERE (姓名LIKE '张%')

UNION

SELECT 课程编号AS u_编号, 课程名称AS u_名称

FROM curriculum

7．嵌套查询

（1）在student_info表中查找与“刘卫平”性别相同的所有学生的姓名、出生日期。

SELECT 姓名，出生日期

FROM student_info

WHERE 性别=

(SELECT 性别

FROM student_info

WHERE 姓名='刘卫平')

（2）使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。

SELECT 学号，姓名，性别

FROM student_info

WHERE student_info.学号IN

(SELECT 学号

FROM grade

WHERE 课程编号IN ('0002'，'0005'))

（3）列出学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。

SELECT 课程编号，分数

FROM grade

WHERE 学号='0001' AND 分数>ANY

(SELECT 分数FROM grade

WHERE 学号='0002')

（4）列出学号为0001的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。

SELECT 课程编号，分数

FROM grade

WHERE 学号='0001' AND 分数>

(SELECT MAX(分数) FROM grade

WHERE 学号='0002')

8．连接查询

（1）查询分数在80~90范围内的学生的学号、姓名、分数。

SELECT student_info.学号，姓名，分数

FROM student_info，grade

WHERE student_info.学号=grade.学号AND 分数BETWEEN 80 AND 90

（2）查询学习“C语言程序设计”课程的学生的学号、姓名、分数。

SELECT student_info.学号，姓名，分数

FROM student_info

INNER JOIN grade ON student_info.学号=grade.学号

INNER JOIN curriculum ON 课程名称='C语言程序设计'

（3）查询所有男同学的选课情况，要求列出学号、姓名、课程名称、分数。

SELECT a.学号, a.姓名, c.课程名称, b.分数

FROM student_info a INNER JOIN

grade b ON a.学号= b.学号INNER JOIN

curriculum c ON b.课程编号= c.课程编号

WHERE (a.性别= '男')

（4）查询每个学生的所选课程的最高成绩，要求列出学号、姓名、课程编号、分数。

SELECT a.学号, c.姓名, a.课程编号, a.分数

FROM grade a INNER JOIN

student_info c ON a.学号= c.学号

WHERE (a.分数=

(SELECT MAX(b.分数)

FROM grade b

WHERE b.学号= a.学号))

（5）查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。

提示：使用左外连接。

SELECT a.学号, a.姓名, b.总成绩

FROM student_info a LEFT OUTER JOIN

(SELECT 学号, SUM(分数) AS 总成绩

FROM grade

GROUP BY 学号) b ON a.学号= b.学号

（6）为grade表添加数据行：学号0004、课程编号为0006、分数为76。查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum表中没有的课程列值为空。

提示：使用右外连接。

INSERT INTO grade (学号, 课程编号, 分数) VALUES ('0004', '0006', 76)

SELECT a.课程编号, a.课程名称, b.选修人数

FROM curriculum a RIGHT OUTER JOIN

(SELECT 课程编号, COUNT(*) AS 选修人数

FROM grade

GROUP BY 课程编号) b ON a.课程编号= b.课程编号

三、实验思考

1．查询所有没有选修课程的学生信息，返回结果包括学号、姓名、性别。

SELECT 学号, 姓名, 性别

FROM student_info

WHERE (学号NOT IN

(SELECT 学号

FROM grade))

2．在student_info表和grade表之间实现交叉连接。

SELECT a.学号, a.姓名, a.性别, b.课程编号, b.分数

FROM student_info a CROSS JOIN

grade b

3．查询每个学生的所选课程的成绩，并列出学号生成分组汇总行（总成绩）和明细行（各课成绩）。提示：使用SELECT语句的COMPUTE选项。

SELECT 学号, 分数

FROM grade

ORDER BY 学号

COMPUTE SUM(分数) BY 学号

4．在查询语句中SELECT、FROM和WHERE选项分别实现什么运算？

投影、选择、自然连接

5．在查询的FROM子句中实现表与表之间的连接有哪几种方式？对应的关键字分别是什么？

有三种：内连接（INNER JOIN）、外连接（LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN）和交叉连接（CROSS JOIN）。