一、目标
完成这个实验后，你将能够：
1. 使用GROUP BY和HAVING子句对数据进行分组，并对组中的数据汇总。 2. 使用ROLLUP和CUBE操作符及GROUPING函数生成汇总数据。 3. 使用 PIVOT运算符格式化数据
4. 使用不同的连接进行表的连接操作。 5. 用UNION操作符合并结果集。 6. 把子查询用做派生表。 7. 把子查询用做表达式。 8. 用子查询关联数据。 二、实验内容
实验开始前，请执行脚本instnwnd.sql，安装Northwind数据库
练习1： 数据分组与汇总
任务1、使用TOP n关键字
在该练习中，将使用TOP n和WITH TIES子句返回结果集中的前几个或前百分之几的记录。
1、打开脚本文件TopN.sql，并查看此文件，文件中的查询将统计表order details中每次订购的总销售数量，然后将结果集按数量降序排列。
结果：
2、修改第一步中的查询，使其只返回前10条记录。执行此查询并查看结果。 代码：USE northwind
SELECT TOP 10 orderid,(unitprice * quantity) AS totalsale
FROM [order details]
ORDER BY (unitprice * quantity) DESC
GO
结果：
3、修改步骤2，使用WITH TIES子句列出前10个数量最多的记录。执行此查询并查看结果。
代码：USE northwind
SELECT TOP 10 WITH TIES orderid,(unitprice * quantity) AS totalsale
FROM [order details]
ORDER BY (unitprice * quantity) DESC
GO 结果：
回答问题：为什么该查询使用了WITH TIES子句后，返回的结果集中含有多于10条的记录？
返回结果之所以是多于10条，正是因为使用了WITH TIES选项，把经过ORDER BY子句排序后的最后一行添加上的结果。
任务2、使用GROUP BY和HAVING子句
在该练习中，将使用GROUP BY和HAVING子句对数据库Northwind中的数据进行分组和汇总。
1、编写脚本，按类别统计所有产品的订购数量。结果如下所示 categoryid total_quantity ----------- -------------- 1 9532
2 5298 3 7906 4 9149 5 4562 6 4199 7 2990 8 7681
（所影响的行数为 8 行）
脚本：SELECT Categories.categoryid,SUM(Quantity) AS total_quantity
FROM Categories
INNER JOIN Products ON Products.CategoryID=Categories.CategoryID INNER JOIN [Order Details] ON Products.ProductID=[Order Details].ProductID
GROUP BY Categories.categoryid
2、编写脚本，统计每次订购时的订购总数量。结果如下 orderid total_quantity ----------- -------------- 10248 27 10249 49 10250 60 10251 41 ……
11075 42 11076 50 11077 72
（所影响的行数为 830 行）
脚本：SELECT orderid, SUM(Quantity) AS total_quantity
FROM [Order Details] GROUP BY orderid
3、编写脚本，统计订购数量大于250的订购。结果如下 orderid total_quantity ----------- -------------- 10515 286 10612 263 10658 255 10678 280 10847 288 105 346 10990 256 11030 330
（所影响的行数为 8 行）
脚本：SELECT orderid, SUM(Quantity) AS total_quantity
FROM [Order Details] GROUP BY orderid
HAVING SUM(Quantity)>250
任务3、使用ROLLUP和CUBE操作符
本练习中，将使用ROLLUP和CUBE操作符生成汇总数据，用GROUPING函数决定哪一条记录是这些操作符生成的汇总记录。
1、编写脚本，统计订购的每种产品的数量，按照productid和orderid排序，并执行rollup计算。结果如下：
productid orderid total_quantity ----------- ----------- --------------
NULL NULL 51317 1 NULL 828 1 10285 45 1 10294 18 1 10317 20 ……
77 11060 10 77 11068 28 77 11077 2
（所影响的行数为 2233 行）
脚本：SELECT productid,orderid, SUM(quantity) AS total_quantity
FROM [Order Details] GROUP BY productid,orderid WITH ROLLUP
ORDER BY productid,orderid
2、修改步骤1中的脚本，结果集返回产品号为50的记录。执行该查询。 修改后的脚本：
脚本：SELECT productid,orderid,total_quantity
FROM ( SELECT productid,orderid, SUM(quantity) AS total_quantity
FROM [Order Details] GROUP BY productid,orderid WITH ROLLUP ) pro_view
WHERE productid=50
问题：结果集中的productid和orderid字段中的空值有何含义？
productid字段中的空值表示某一订单号的所有的产品；orderid字段中的空值表示某
一产品编号的所有的订单
3、修改步骤2中的脚本，使用操作符CUBE而不是ROLLUP，并对productid和orderid使用GROUPING函数，可以用来区分结果集中的细节记录和汇总记录。执行该查询。
SELECT * FROM [Order Details] SELECT
productid,grouping(productid),orderid,grouping(orderid),sum(quantity)as total_quantity
from [Order Details] group by productid,orderid with cube
order by productid,orderid
问题1：哪些记录是汇总记录？
grouping(productid)和grouping(orderid)值为1的表示是汇总记录
问题2：哪些记录是按产品类别而汇总的记录，哪些是按订单而汇总的记录？
grouping(productid)为0、grouping(orderid)为1表示是按产品类别而汇总的记录；grouping(orderid)为0、grouping(productid)为1表示是按订单而汇总的记录
4、执行下面查询，学习CASE及GROUPING的用法。
SELECT CASE WHEN (GROUPING(productid) = 1) THEN 'ALL' ELSE convert(varchar,productid) END AS productid,
CASE WHEN (GROUPING(orderid) = 1) THEN 'ALL' ELSE convert(varchar,orderid) END AS orderid,
SUM(Quantity) AS QtySum FROM [order details]
GROUP BY productid,orderid WITH CUBE
ORDER BY productid
结果：
任务4、使用 PIVOT运算符格式化数据
1、 执行下面查询，学习PIVOT运算符的用法
USE AdventureWorks; GO
SELECT VendorID, [1] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT (
COUNT (PurchaseOrderID) FOR EmployeeID IN
( [1], [198], [223], [231], [233] ) ) AS pvt
ORDER BY VendorID
结果为：
2、 编写脚本，输出每个雇员处理1—5号供应商的采购订单的数量 USE AdventureWorks;
GO
SELECT EmployeeID, [1] AS Ven1, [2] AS Ven2, [3] AS Ven3,Ven4, [5] AS Ven5
AS [4] FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT (
COUNT (PurchaseOrderID) FOR VendorID IN
( [1], [2], [3], [4], [5] ) ) AS pvt
ORDER BY EmployeeID
结果：
练习2：查询多个表
任务1、表连接
在该练习中将对数据库library中的表进行连接操作。安装library方法： 1）将library.exe解压缩至文件夹d:\\ 2）执行Bldlib.cmd
若不成功，点击Bldlib.cmd，右键->编辑，按照文件中的顺序执行脚本文件。 1、编写查询member和adult中数据的语句，返回字段firstname, ,lastname, street, city, state和zip的值。把firstname, middleinitial,lastname合并到一个字符串中，中间空格分隔，并起别名name。
SELECT firstname+' '+middleinitial+' '+lastname [name], street,city, [state],zip FROM member INNER JOIN adult
ON adult.member_no=member.member_no
结果：
2、查询表title，item和copy中数据，结果集返回字段isbn、copy_no，on_loan,title,translation和cover，所返回的记录满足ISBN为1、500或1000，按照字段ISBN的值排序。
SELECT copy.isbn,copy_no,on_loan,title,translation,cover FROM copy
INNER JOIN item
ON copy.isbn=item.isbn and copy.title_no=item.title_no INNER JOIN title
ON item.title_no=title.title_no WHERE copy.isbn in(1,500,1000) ORDER BY copy.isbn
结果：
3、查询成员编号为250、341或1675的成员的编号及成员全名，以及这些成员预约reservation图书的信息（isbn号及预约日期），结果按照成员号码排序，结果如下所示：
member_no name isbn date --------- ---------------------------------- ----------- --------
250 Hightower, Michael A NULL NULL
341 Martin, Brian A 43 04/07/08 341 Martin, Brian A 330 04/07/08 341 Martin, Brian A 617 04/07/08 341 Martin, Brian A 904 04/07/08 1675 LaBrie, Joshua B NULL NULL
（所影响的行数为 6 行）
SELECT member.member_no,lastname+','+firstname+' '+middleinitial [name],isbn,log_date as date FROM member
LEFT OUTER JOIN reservation
ON member.member_no=reservation.member_no WHERE member.member_no in(250,341,1675)
ORDER BY member.member_no
问题：第1行和第6行的NULL表示什么含义？
null表示member_no为250和1675的记录没出现在reservation中。 任务2、用操作符UNION合并结果集
本练习中，用两个相似的SELECT语句生成结果集，并用操作符UNION合并这两个结果集。
说明：memeber为成员表（主表），adult（成人）和juvenile（青年）为子表，memeber与adult为一对一的联系，adult和juvenile为一对多的联系（家长与子女）。
1、查询住在亚利桑那州（state = 'AZ'）的成员中，其两个以上孩子同时拥有借书卡的成员编号及孩子数量。执行查询，观察返回记录数量。
SELECT adult_member_no,count(juvenile.member_no)as childQuantity FROM juvenile INNER JOIN adult
ON adult.member_no=juvenile.adult_member_no WHERE [state]='AZ' GROUP BY adult_member_no
HAVING count(juvenile.member_no)>=2 结果：
2、查询住在加利福尼亚州（state = 'CA'）的成员中，其三个以上孩子同时拥有借书卡
的成员编号及孩子数量。执行查询，观察返回记录数量。
SELECT adult_member_no,count(juvenile.member_no)as childQuantity FROM juvenile INNER JOIN adult
ON adult.member_no=juvenile.adult_member_no WHERE [state]='CA' GROUP BY adult_member_no
HAVING count(juvenile.member_no)>=3 结果：
3、 合并步骤1和步骤2的结果集，执行查询，观察一共返回记录数量。
SELECT adult_member_no,count(juvenile.member_no)as childQuantity
FROM juvenile INNER JOIN adult
ON adult.member_no=juvenile.adult_member_no WHERE [state]='AZ' GROUP BY adult_member_no
HAVING count(juvenile.member_no)>=2 UNION
SELECT adult_member_no,count(juvenile.member_no)as childQuantity FROM juvenile INNER JOIN adult
ON adult.member_no=juvenile.adult_member_no WHERE [state]='CA' GROUP BY adult_member_no
HAVING count(juvenile.member_no)>=3 结果：
练习3：使用子查询
本实验使用Library数据库。 任务1、把子查询用做派生表
1、编写使用派生表的查询，返回字段juvenile.adult_member_no、到期日期expr_date和每个具有三个以上孩子成员的成年人的孩子的个数。
SELECT DISTINCT juvenile.adult_member_no,expr_date,cView.childQuantity FROM (SELECT adult_member_no,count(juvenile.member_no)AS childQuantity FROM juvenile
GROUP BY adult_member_no
HAVING count(juvenile.member_no)>=3 )AS cView INNER JOIN juvenile
ON juvenile.adult_member_no=cView.adult_member_no INNER JOIN adult
ON adult.member_no=juvenile.adult_member_no 结果：
2、编写下列查询语句，返回字段adult_member_no，并计算每个成年人成员的孩子个数，然后返回表juvenile中有三个以上孩子成员的成员。执行查询并分析结果。与步骤1比较结果集的相似之处。
SELECT adult_member_no,count(juvenile.member_no)AS childQuantity FROM juvenile
GROUP BY adult_member_no
HAVING count(juvenile.member_no)>=3 结果：
3、编写查询，从表adult中返回字段expr_date的值。与步骤1比较结果集的相同之处与不同之处。
SELECT member_no,expr_date FROM adult
结果：
4、 用连接语句重写步骤1中的查询。
SELECT cView.adult_member_no,expr_date,cView.childQuantity FROM
(SELECT adult_member_no,count(juvenile.member_no)AS childQuantity
FROM juvenile
GROUP BY adult_member_no
HAVING count(juvenile.member_no)>=3)AS cView inner join
(SELECT member_no,expr_date FROM adult)AS sl
ON cView.adult_member_no=sl.member_no 结果：
任务2、把子查询用做表达式
1、 编写查询返回字段loanhist.fine_paid最大的记录。
SELECT max(fine_paid)AS MP FROM loanhist
2、 编写查询，连接表member和loanhist，返回字段firstname、lastname、isbn和fine_paid。
SELECT firstname,lastname,isbn,fine_paid FROM member
INNER JOIN loanhist
ON loanhist.member_no=member.member_no
3、 编写查询返回罚款（fine_paid）最多的成员如下信息：member.firstname,
member.lastname,loanhist.isbn和loanhist.fine_paid。请消除结果集中的重复记录。
SELECT DISTINCT firstname,lastname,isbn FROM member
INNER JOIN loanhist
ON loanhist.member_no=member.member_no
WHERER fine_paid in(SELECT max(fine_paid)AS MP
FROM loanhist)
4、 编写查询检索少于5本且预约次数大于50次的书。返回title_no,Title,isbn和预约次
数。
SELECT title.title_no,title,RRT.isbn,RRT.Rt FROM title
inner join (SELECT c1.isbn,title_no FROM copy c1
inner join (SELECT isbn,count(copy_no)AS Ct
FROM copy c2
GROUP BY isbn
HAVING count(copy_no)<5) AS CCT
ON c1.isbn=CCT.isbn) AS CCCt ON CCCt.title_no=title.title_no
inner join(SELECT isbn,count(member_no)AS Rt
FROM reservation GROUP BY isbn
HAVING count(member_no)>50)AS RRT
ON CCCt.isbn=RRT.isbn
任务3、用子查询关联数据
1、 编写一个查询，从member表中返回字段member_no和lastname，给member表使
用别名。
select member_no,lastname from member Class 2、 编写一个查询，从loanhist表中计算并返回每个成员的总罚款数。
select member_no,sum(fine_paid)as Fine from loanhistS group by member_no
3、 使用关联子查询，查询表member和loanhist，返回图书馆中罚款数大于5美元的成
员其member_no及lastname
select member.member_no,lastname from member where exists(
select * from (select member_no,sum(fine_paid)AS class
from loanhist group by member_no
having sum(fine_paid)>5)FK where member.member_no=FK.member_no)
4、 要求使用GROUP BY和HAVING子句的连接完成步骤3。
因篇幅问题不能全部显示，请点此查看更多更全内容