数据库实验二及其答案

实验名称 实验二：使用分组，排序，汇总

课程名称 数据库原理与设计 成绩 学院（系）

软件学院 专业 计算机软件工程 班级 学生姓名

学号 实验地点 实验日期

实验报告答案如下：

一.实验内容：

1. 完成在在Recruitment，GlobalToyz和Student数据库基础上的查询，

按要求完成给出的15道题目，要求写出相应数据库的查询语句（SELECT语句）。

二.实验目的：

1.掌握通配符的用法

2.掌握 GROUP BY 子句的使用

3.掌握 ORDER BY子句的使用

4.掌握 TOP和DISTINCT关键字的使用

5.掌握 COMPUTE和COMPUTE BY子句的使用

6.掌握聚集函数的使用

三.实验原理：

本次实验主要通过根据题目要求完成对数据库的查询，加深对sql语言的印象。主要的原理就是SQL语言基本语句及语法。

四.实验过程及编写代码：

1.显示以‘S’开头，并且玩具名称不少于7个字符的玩具名称vToyName。

SELECT vToyName

FROM Toys

WHERE vToyName like'S______%'

2.显示名称里包含字母‘u’或‘x’的玩具ID和名称以及价格。

SELECT cToyId, vToyName, mToyRate

FROM Toys

WHERE vToyName like'u%'or vToyName like'x%'

3.查询信用卡号（cCreditCardNo）中包含4个8的订购者（Shopper）的详细信息。

SELECT*

FROM Shopper

WHERE cCreditCardNo like'%8%8%8%8%'

4.统计订单号为‘000001’的订单订购的玩具的数量和玩具的总花费（mToyCost）。

SELECT cOrderNo, mTotalCost

FROM Orders

WHERE cCartId ='000001'

5.统计每份提单订购的玩具数量和玩具花费。

SELECT cOrderNo, mToyCost

FROM OrderDetail

6.对于GlobalToyz数据库的Country表，统计国家名称只由六个字母构成的国家的数目。

SELECT count(cCountry)'国家数量'

FROM Country

WHERE cCountry like'______'

7.显示价格最高的五种玩具的名称和价格（vToyName，mToyRate）。

SELECT TOP 5 vToyName, mToyRate

FROM Toys

8.输出Shopper表的‘Texas’州的前10%的订购者的详细信息。

SELECT TOP 10 PERCENT*

FROM Shopper

WHERE cCity ='Texas'

9.统计被人订购过的玩具的数量，相同的玩具只统计一次。（OrderDetail表）

SELECT count(cOrderNo)'订购数量'

FROM OrderDetail

GROUP BY cToyId

10.toys表中，按照玩具的类别（cCategoryId）统计每类玩具的数量和平均价格（mToyrate），只输出平均价格大于20$的玩具的信息。思考：能输出的列可以有哪些？请写出相应的查询。

SELECT count(cCategoryId)'玩具类别',count(mToyRate)'平均价格'

FROM Toys

group by cCategoryId

having avg(mToyRate)> 20

11.对于PickofMonth表，打印出该表的详细信息，并在底部汇总出‘2000’年销售数量的总和。

SELECT*,SUM(iTotalSold)'销售数量总和'

FROM PickOfMonth

GROUP BY cToyId, siMonth, iYear, iTotalSold

COMPUTE SUM(iTotalSold)

12.对于OrderDetail表，请输出订单号，cWrapperId，vMessage和mToyCost，并根据cWrapperId小计玩具花销的平均值和总值，在底部总计玩具花销的平均值和总值。

SELECT cToyId, cWrapperId, vMessage, mToyCost,avg(mToyCost)'小计平均值',sum(mToyCost)'小计总值'

FROM OrderDetail

GROUP BY cToyId, cWrapperId, vMessage, mToyCost

ORDER BY cWrapperId

COMPUTE avg(mToyCost),sum(mToyCost)

13.打印出玩具花费最高的三份订单的详情。

SELECT TOP 3 *

FROM Toys

14.以下这段代码将会输出什么：

SELECT cOrderNo,cToyId,SUM(mToyCost)

FROM OrderDetail

GROUP BY cOrderNo

请写出正确的语句。

选择列表中的列'OrderDetail.cToyId' 无效，因为该列没有包含在聚合函数或GROUP BY 子句中。

SELECT cOrderNo,cToyId,SUM(mToyCost)'小计总值'

FROM OrderDetail

GROUP BY cOrderNo, cToyId

15.下面包含COMPUTE BY子句的代码都会产生错误，请写出产生错误的原因，并将其改为正确的带COMPUTE BY子句的SQL语句。

SELECT cToyId,mToyCost

FROM OrderDetail

COMPUTE SUM(mToyCost) BY cToyId

在排序依据列表中没有找到COMPUTE BY 子句中的某一项。计算依据列表中的

所有表达式也必须同时出现在排序依据列表中。

compute ？？？by 子句中？？？出的列必须出现在选择列表中

以cToyId没有包含在聚合函数或GROUP BY 子句中。

应改成如下所示：

SELECT cToyId,mToyCost,SUM(mToyCost)'小计总值'

FROM OrderDetail

GROUP BY cToyId,mToyCost

ORDER BY cToyId

COMPUTE SUM(mToyCost)BY cToyId

SELECT cCartId

FROM Orders

ORDER BY cCartId

COMPUTE AVG(mTotalCost) BY cCartId

compute ？？？by 子句中？？？出的列必须出现在选择列表中

SELECT 之后如果用了聚集函数和某一属性列则该属性列必须用GROUP分组

应改为：

SELECT cCartId,AVG(mTotalCost)

FROM Orders

GROUP BY cCartId

ORDER BY cCartId

COMPUTE AVG(mTotalCost)BY cCartId

五、实验问题：

1、第十五题的第二问从理论上说是没问题，但是还是出现如下错误提示：COMPUTE 子句#1、聚合表达式#1 不在选择列表中。

已解决：

改成SELECT cCartId, mTotalCost,AVG(mTotalCost)

FROM Orders

GROUP BY cCartId, mTotalCost

ORDER BY cCartId

COMPUTE AVG(mTotalCost)BY cCartId

六、实验总结：

1.使用COMPUTE 子句的注意事项和原则

1.1.在一条语句中，不能同时使用多个COMPUTE 和COMPUTE BY 子句

1.2.SQL Server 中要求选择列表中的字段和COMPUTE 子句中的字段相同

1.3.不能同时使用SELECT INTO 和COMPUTE，因为COMPUTE 不会生成关系型的输出结果

2.使用COMPUTE BY子句的注意事项和原则

2.1.应当同时使用ORDER BY 子句和COMPUTE BY 子句，这样记录就会被分组显示

2.2.应在COMPUTE BY 子句后指定字段名，使SQL Server 能够决定要生成的汇总值是什么

2.3.出现在COMPUTE BY 子句后的字段必须出现在ORDER BY 子句后，且顺序相同，始于同一表达式，不能略过任一表达式

3.SELECT 之后如果用了聚集函数和某一属性列

则该属性列必须用GROUP分组

如：

SELECT cToyId, SUM(mToyCost)

FROM OrderDetail

会提示错误：

选择列表中的列'OrderDetail.cToyId' 无效，因为该列没有包含在聚合函数或GROUP BY 子句中。

改为如下所示：

SELECT cToyId, SUM(mToyCost)

FROM OrderDetail

GROUP BY cToyId

则能成功查询

指导教师日期

注：请用A4纸书写，不够另附纸。第

页，共页