第六章习题答案
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
--表的结构和表间联系请参见书上第109页
--1.在产品表中检索所有产品名称以字符串’en’或’ton’结尾的产品，并按单价降序排序。select*from Products where ProductName like'%en'or ProductName like'%ton' order by UnitPrice desc
--2.根据产品表，在单价$15~$25之间的产品中随机检索5个产品。
--利用随机函数NewID()
select top 5 *from Products where UnitPrice between 15 and 25
order by NewID()
--3.在客户表中检索所有美国客户来自于哪些城市。
--使用distinct去掉重复记录
select distinct City from Customers where Country='USA'
--4.在供应商表中检索所有邮政编码（Postalcode）是字母开头的而且传真号（Fax）为非空
（NULL）的供应商信息。
--使用like和is not null
select*from Suppliers where postalcode like'[A-Z]%'and Fax is not null --5.在员工表中检索所有备注为Sales Representative的这些员工的主管（ReportsTo）的
编码。
--使用distinct去掉重复记录
select distinct ReportsTo from Employees where notes='Sales Representative'
--6.在订单表中检索所有在2009年6月30日之前需要发货但还没有发货的订单信息。
--注：不能省略ShippedDate这个条件，它的含义为：在2009年6月30日之后发货的订单在当时（2009年6月30日之前）等同于还没有发货
select*from Orders where RequiredDate<='2009-06-30'and(ShippedDate is null or ShippedDate>='2009-06-30')
--7.按产品类别编码对产品表进行分组汇总，检索平均单价$20元以上的所有产品类别。
--使用group by和having
select CategoryID,AVG(UnitPrice)from Products
group by CategoryID
having AVG(UnitPrice)>20
--8.按供应商和产品类别进行分组汇总，检索每个供应商提供的每类产品的平均单价。
--使用带两个关键字的group by
select SupplierID,CategoryID,AVG(UnitPrice)from Products
group by SupplierID,CategoryID
order by SupplierID,CategoryID
--9.按供应商编码对产品表进行分组汇总，检索哪些供应商至少提供了两个单价在$20以下的产品。
--在使用group by的查询语句中，注意where和having的出现顺序位置
select SupplierID from Products
where UnitPrice<20
group by SupplierID
having count(*)>=2
--10.按客户和月份对订单表进行分组汇总，统计检索2009年度每个客户每月的订单数量。
--使用带两个关键字的group by
select CustomerID,Month(OrderDate)as'Month',COUNT(*)as'NumberofOrders'
from Orders
where OrderDate between'2009-01-01'and'2009-12-31'
group by CustomerID,MONTH(OrderDate)
order by CustomerID,MONTH(OrderDate)
--11.统计检索2009年度每个产品的订单数和订单金额。
--使用带where的group by
select ProductID,COUNT(*)as'NumberofOrders',SUM(Amount)as'Amount' from Orders as a
join OrderItems as b on a.OrderID=b.OrderID
where OrderDate between'2009-01-01'and'2009-12-31'
group by ProductID
order by ProductID
--12.统计检索2009年销售额大于$150万的员工姓名。
--使用带where、having和多表连接的group by
select Firstname+' '+Lastname as EmployeeName from Orders as a
join OrderItems as b on a.OrderID=b.OrderID
join Employees as c on a.EmployeeID=c.EmployeeID
where OrderDate between'2009-01-01'and'2009-12-31'
group by Firstname+' '+Lastname
having SUM(Amount)>1500000
order by EmployeeName
--13.统计检索与Tofu同一类别的产品中，哪些产品的单价比Tofu的单价两倍还高。
--在where子句中使用子查询
select ProductID,Unitprice from Products
where UnitPrice>(select UnitPrice*2 from Products where ProductName='tofu') and CategoryID=(select CategoryID from Products where ProductName='tofu')
--14.统计检索哪几类产品的平均单价大于Beverages类产品的平均单价。
--修改题目：统计检索哪几类产品的平均单价大于Confections类产品的平均单价。
--在having子句中使用子查询和多表连接
select CategoryID,AVG(UnitPrice)from Products
group by CategoryID having AVG(UnitPrice)>
(select AVG(UnitPrice)from Products as a
join categories as b on a.CategoryID=b.CategoryID where
CategoryName='Confections')
--15.统计检索订单表中订单数量在20张以上的这些客户的名称。
--在group by中使用多表连接
select CompanyName,COUNT(*)from Customers as a
join Orders as b on a.CustomerID=b.CustomerID
group by CompanyName
having COUNT(*)>=20
--19.分别使用EXISTS、IN和ANY这3个子句检索美国供应商提供的所有产品名称。
--使用exists
select ProductName from Products as a where exists
(select 1 from Suppliers as b where a.SupplierID=b.SupplierID and
Country='USA')
--使用IN
select ProductName from Products where SupplierID in
(select SupplierID from Suppliers where Country='USA')