实验三 数据查询.doc

实验三 数据查询一、目标完成这个实验后，你将能够：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 northwindSELECT TOP 10 orderid,(unitprice * quantity) AS totalsale FROM order details ORDER BY (unitprice * quantity) DESCGO 结果：3、修改步骤2，使用WITH TIES子句列出前10个数量最多的记录。执行此查询并查看结果。代码：USE northwindSELECT TOP 10 WITH TIES orderid,(unitprice * quantity) AS totalsale FROM order details ORDER BY (unitprice * quantity) DESCGO结果： 回答问题：为什么该查询使用了WITH TIES子句后，返回的结果集中含有多于10条的记录？返回结果之所以是多于10条，正是因为使用了WITH TIES选项，把经过ORDER BY子句排序后的最后一行添加上的结果。任务2、使用GROUP BY和HAVING子句在该练习中，将使用GROUP BY和HAVING子句对数据库Northwind中的数据进行分组和汇总。1、编写脚本，按类别统计所有产品的订购数量。结果如下所示categoryid total_quantity - - 1 95322 52983 79064 91495 45626 41997 29908 7681（所影响的行数为 8 行）脚本：SELECT Categories.categoryid,SUM(Quantity) AS total_quantityFROM CategoriesINNER JOIN Products ON Products.CategoryID=Categories.CategoryIDINNER JOIN Order Details ON Products.ProductID=Order Details.ProductIDGROUP BY Categories.categoryid2、编写脚本，统计每次订购时的订购总数量。结果如下orderid total_quantity - - 10248 2710249 4910250 6010251 4111075 4211076 5011077 72（所影响的行数为 830 行）脚本：SELECT orderid, SUM(Quantity) AS total_quantityFROM Order DetailsGROUP BY orderid3、编写脚本，统计订购数量大于250的订购。结果如下orderid total_quantity - - 10515 28610612 26310658 25510678 28010847 28810895 34610990 25611030 330（所影响的行数为 8 行）脚本：SELECT orderid, SUM(Quantity) AS total_quantityFROM Order DetailsGROUP BY orderidHAVING SUM(Quantity)250任务3、使用ROLLUP和CUBE操作符本练习中，将使用ROLLUP和CUBE操作符生成汇总数据，用GROUPING函数决定哪一条记录是这些操作符生成的汇总记录。1、编写脚本，统计订购的每种产品的数量，按照productid和orderid排序，并执行rollup计算。结果如下：productid orderid total_quantity - - - NULL NULL 513171 NULL 8281 10285 451 10294 181 10317 2077 11060 1077 11068 2877 11077 2（所影响的行数为 2233 行）脚本：SELECT productid,orderid, SUM(quantity) AS total_quantityFROM Order DetailsGROUP BY productid,orderidWITH ROLLUPORDER BY productid,orderid2、修改步骤1中的脚本，限制结果集返回产品号为50的记录。执行该查询。修改后的脚本：脚本：SELECT productid,orderid,total_quantityFROM ( SELECT productid,orderid, SUM(quantity) AS total_quantity FROM Order Details GROUP BY productid,orderid WITH ROLLUP ) pro_viewWHERE productid=50问题：结果集中的productid和orderid字段中的空值有何含义？productid字段中的空值表示某一订单号的所有的产品；orderid字段中的空值表示某一产品编号的所有的订单3、修改步骤2中的脚本，使用操作符CUBE而不是ROLLUP，并对productid和orderid使用GROUPING函数，可以用来区分结果集中的细节记录和汇总记录。执行该查询。SELECT * FROM Order DetailsSELECT productid,grouping(productid),orderid,grouping(orderid),sum(quantity)as total_quantityfrom Order Detailsgroup by productid,orderidwith cubeorder 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 QtySumFROM order detailsGROUP BY productid,orderid WITH CUBEORDER BY productid结果： 任务4、使用 PIVOT运算符格式化数据1、 执行下面查询，学习PIVOT运算符的用法USE AdventureWorks;GOSELECT VendorID, 164 AS Emp1, 198 AS Emp2, 223 AS Emp3, 231 AS Emp4, 233 AS Emp5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) pPIVOT(COUNT (PurchaseOrderID)FOR EmployeeID IN( 164, 198, 223, 231, 233 ) AS pvtORDER BY VendorID结果为： 2、 编写脚本，输出每个雇员处理15号供应商的采购订单的数量 USE AdventureWorks;GOSELECT EmployeeID, 1 AS Ven1, 2 AS Ven2, 3 AS Ven3, 4 AS Ven4, 5 AS Ven5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) pPIVOT(COUNT (PurchaseOrderID)FOR VendorID IN( 1, 2, 3, 4, 5 ) AS pvtORDER 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,zipFROM memberINNER JOIN adultON 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,coverFROM copyINNER JOIN item ON copy.isbn=item.isbn and copy.title_no=item.title_noINNER JOIN titleON item.title_no=title.title_noWHERE 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 NULL341 Martin, Brian A 43 04/07/08341 Martin, Brian A 330 04/07/08341 Martin, Brian A 617 04/07/08341 Martin, Brian A 904 04/07/081675 LaBrie, Joshua B NULL NULL（所影响的行数为 6 行）SELECT member.member_no,lastname+,+firstname+ +middleinitial name,isbn,log_date as dateFROM memberLEFT OUTER JOIN reservationON member.member_no=reservation.member_noWHERE 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 childQuantityFROM juvenileINNER JOIN adultON adult.member_no=juvenile.adult_member_noWHERE state=AZGROUP BY adult_member_noHAVING count(juvenile.member_no)=2结果： 2、查询住在加利福尼亚州（state = CA）的成员中，其三个以上孩子同时拥有借书卡的成员编号及孩子数量。执行查询，观察返回记录数量。SELECT adult_member_no,count(juvenile.member_no)as childQuantityFROM juvenileINNER JOIN adultON adult.member_no=juvenile.adult_member_noWHERE state=CAGROUP BY adult_member_noHAVING count(juvenile.member_no)=3结果： 3、 合并步骤1和步骤2的结果集，执行查询，观察一共返回记录数量。 SELECT adult_member_no,count(juvenile.member_no)as childQuantityFROM juvenileINNER JOIN adultON adult.member_no=juvenile.adult_member_noWHERE state=AZGROUP BY adult_member_noHAVING count(juvenile.member_no)=2UNIONSELECT adult_member_no,count(juvenile.member_no)as childQuantityFROM juvenileINNER JOIN adultON adult.member_no=juvenile.adult_member_noWHERE state=CAGROUP BY adult_member_noHAVING count(juvenile.member_no)=3结果： 练习3：使用子查询本实验使用Library数据库。任务1、把子查询用做派生表1、编写使用派生表的查询，返回字段juvenile.adult_member_no、到期日期expr_date和每个具有三个以上孩子成员的成年人的孩子的个数。SELECT DISTINCT juvenile.adult_member_no,expr_date,cView.childQuantityFROM (SELECT adult_member_no,count(juvenile.member_no)AS childQuantity FROM juvenile GROUP BY adult_member_no HAVING count(juvenile.member_no)=3 )AS cViewINNER JOIN juvenileON juvenile.adult_member_no=cView.adult_member_noINNER JOIN adultON 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.childQuantityFROM(SELECT adult_member_no,count(juvenile.member_no)AS childQuantity FROM juvenile GROUP BY adult_member_no HAVING count(juvenile.member_no)=3)AS cViewinner join (SELECT member_no,expr_date FROM adult)AS slON cView.adult_member_no=sl.member_no结果： 任务2、把子查询用做表达式1、 编写查询返回字段loanhist.fine_paid最大的记录。SELECT max(fine_paid)AS MP FROM loanhist2、 编写查询，连接表member和loanhist，返回字段firstname、lastname、isbn和fine_paid。SELECT firstname,lastname,isbn,fine_paidFROM memberINNER JOIN loanhistON loanhist.member_no=member.member_no3、 编写查询返回罚款（fine_paid）最多的成员如下信息：member.firstname, member.lastname,loanhist.isbn和loanhist.fine_paid。请消除结果集中的重复记录。SELECT DISTINCT firstname,lastname,isbnFROM memberINNER JOIN loanhis