数据库习题及答案(查询语句)

1、-1.在产品表中检索所有产品名称以字符串en或ton结尾的产品，并按单价降序排序。select * from Products where ProductName like '%en%' or ProductName like '%ton%'order by UnitPrice desc-2.根据产品表，在单价$15$25之间的产品中随机检索个产品。-利用随机函数NewID()select top 5 * from Products where UnitPrice between 15 and 25order by NewID()-3.在客户表中检索所有美国客户

2、来自于哪些城市。-使用distinct去掉重复记录select distinct City from Customers where Country='USA'-4.在供应商表中检索所有邮政编码（Postalcode）是字母开头的而且传真号（Fax）为非空（NULL）的供应商信息。-使用like和is not nullselect * from Suppliers where postalcode like 'A-Z%' and Fax is not null-5.在员工表中检索所有职位为Sales Representative的这些员工的主管（ReportsT

3、o）的编码。-使用distinct去掉重复记录select distinct ReportsTo from Employees where title='Sales Representative'-6.在订单表中检索所有在年月日之前需要发货但还没有发货的订单信息。-注：不能省略ShippedDate这个条件，它的含义为：在年月日之后发货的订单在当时（年月日之前）等同于还没有发货select * from Orders where RequiredDate<='2009-06-30' and (ShippedDate is null or ShippedDa

4、te>='2009-06-30')-7.按产品类别编码对产品表进行分组汇总，检索平均单价$30元以上的所有产品类别。-使用group by和havingselect CategoryID,AVG(UnitPrice) from Productsgroup by CategoryIDhaving AVG(UnitPrice)>=30-8.按供应商和产品类别进行分组汇总，检索每个供应商提供的每类产品的平均单价。-使用带两个关键字的group byselect SupplierID,CategoryID,AVG(UnitPrice) from Productsgroup

5、by SupplierID,CategoryIDorder by SupplierID,CategoryID-9.按供应商编码对产品表进行分组汇总，检索哪些供应商至少提供了两个单价在$20以下的产品。-在使用group by的查询语句中，注意where和having的出现顺序位置select SupplierID from Productswhere UnitPrice<20group by SupplierIDhaving count(*)>=2-10.按客户和月份对订单表进行分组汇总，统计检索年度每个客户每月的订单数量。-使用带两个关键字的group byselect Cust

6、omerID,Month(OrderDate) as 'Month',COUNT(*) as 'NumberofOrders' from Orderswhere OrderDate between '2009-01-01' and '2009-12-31'group by CustomerID,MONTH(OrderDate)order by CustomerID,MONTH(OrderDate)-11.统计检索年度每个产品的订单数和订单额。-使用带where的group byselect ProductID,COUNT(*) a

7、s 'NumberofOrders',SUM(Amount) as 'Amount'from Orders as ajoin OrderItems as b on a.OrderID=b.OrderIDwhere OrderDate between '2009-01-01' and '2009-12-31'group by ProductIDorder by ProductID-12.统计检索年销售额大于$150万的员工姓名。-使用带where、having和多表连接的group byselect Firstname+'

8、'+Lastname as EmployeeName from Orders as ajoin OrderItems as b on a.OrderID=b.OrderIDjoin Employees as c on a.EmployeeID=c.EmployeeIDwhere OrderDate between '2009-01-01' and '2009-12-31'group by Firstname+' '+Lastnamehaving SUM(Amount)>1500000order by EmployeeName-13.

9、统计检索与Tofu同一类别的产品中，哪些产品的单价比Tofu的单价两倍还大。-在where子句中使用子查询select ProductID,Unitprice from Productswhere UnitPrice>(select UnitPrice*2 from Products where ProductName='tofu')and CategoryID=(select CategoryID from Products where ProductName='tofu')-14.统计检索哪几类产品的平均单价大于Beverages类产品的平均单价。-修

10、改题目：统计检索哪几类产品的平均单价大于Confections类产品的平均单价。-在having子句中使用子查询和多表连接select CategoryID,AVG(UnitPrice) from Productsgroup 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.统计检索订单表中订单数

11、量在张以上的这些客户的名称。-在group by中使用多表连接select CompanyName,COUNT(*) from Customers as ajoin Orders as b on a.CustomerID=b.CustomerIDgroup by CompanyNamehaving COUNT(b.CustomerID)>=20-16.统计检索哪些客户的订单数量最多。-使用临时表和子查询if OBJECT_ID('tmp') is not null drop table tmpgoselect CustomerID,COUNT(*) as 'Num

12、' into tmp from Ordersgroup by CustomerIDselect * from tmp where num=(select MAX(num) from tmp)-不使用子查询，而使用变量declare x moneyselect x=MAX(num) from tmpselect * from tmp where num=x-17.统计检索哪些订单所包含的产品个数最多。-与上题相似if OBJECT_ID('tmp') is not null drop table tmpgoselect OrderID,COUNT(*) as 'N

13、um' into tmp from OrderItemsgroup by OrderIDgoselect * from tmp where Num=(select MAX(Num) from tmp)-18.统计检索哪几类产品其所属的产品个数最多、平均单价最高。-使用表变量和insert.select语句declare tmp table(CategoryID int,num int,avgprice money)insert into tmpselect CategoryID,COUNT(*),AVG(UnitPrice) from Products group by Categor

14、yID-产品个数最多select * from tmp where num=(select MAX(num) from tmp)-平均单价最高select * from tmp where avgprice=(select MAX(avgprice) from tmp)-19.分别使用EXISTS、IN和ANY这个子句检索美国供应商提供的所有产品名称。-使用existsselect ProductName from Products as a where exists(select 1 from Suppliers as b where a.SupplierID=b.SupplierID an

15、d Country='USA')-使用INselect ProductName from Products where SupplierID in (select SupplierID from Suppliers where Country='USA')-使用ANYselect ProductName from Products where SupplierID=any (select SupplierID from Suppliers where Country='USA')-20.利用随机函数，从产品表单价排名最低的前个产品中随机取出个产品

16、。-使用IN，注意order by NEWID()出现在主查询中select top 5 * from Products where ProductID in (select top 20 ProductID from Products order by UnitPrice)order by NEWID()-使用existsselect top 5 * from Products as a where exists (select top 20 * from Products as b where a.ProductID=b.ProductID order by UnitPrice)order

17、 by NEWID()-21.统计检索Confections这类产品中单价最便宜的产品名称。-使用排名函数，先将排名结果放在一个临时表tmp中if OBJECT_ID('tmp') is not null drop table tmpgoSELECT ROW_NUMBER() OVER (ORDER BY UnitPrice) AS 'PriceRank',ProductName,UnitPrice,a.CategoryIDinto tmp from Products as ajoin Categories as b on a.CategoryID=b.Cate

18、goryIDwhere CategoryName='Confections'-从tmp表中检索排名第一的产品select * from tmp where PriceRank=1-不使用排名函数if OBJECT_ID('tmp') is not null drop table tmpgoSELECT ProductID,ProductName,UnitPrice,a.CategoryID into tmp from Products as ajoin Categories as b on a.CategoryID=b.CategoryIDwhere Categ

19、oryName='Confections'order by UnitPriceselect * from tmp where UnitPrice=(select min(UnitPrice) from tmp)-22.统计检索Confections这类产品中每个产品单价与平均单价的差额-在查询列表中使用子查询SELECT ProductID,ProductName,UnitPrice,UnitPrice-(select AVG(UnitPrice) from Products as ajoin categories b on a.CategoryID=b.CategoryID

20、and categoryname='Confections') from Products as ajoin categories b on a.CategoryID=b.CategoryID and categoryname='Confections'-23.统计检索Chef Anton's Gumbo Mix产品的单价在所有产品中的排名名次。-假设从大到小排序-使用变量分步实现，先求出这个产品的单价declare x moneyselect x=UnitPrice from Products where ProductName='Chef A

21、nton''s Gumbo Mix'-统计比这个产品单价大的其他产品的个数，这个个数加就是这个产品的排名。注意这个算法。select count(*)+1 as 'PriceRank' from Products where UnitPrice>x-或不使用变量，直接使用子查询select count(*)+1 as 'PriceRank' from Products where UnitPrice>(select UnitPrice from Products where ProductName='Chef Anto

22、n''s Gumbo Mix')-使用排名函数，注意要使用临时表if OBJECT_ID('tmp') is not null drop table tmpgoSELECT ROW_NUMBER() OVER (ORDER BY UnitPrice desc) AS 'PriceRank',* into tmp from Products as aselect * from tmp where ProductName='Chef Anton''s Gumbo Mix'-24.统计检索Chef Anton&#

23、39;s Gumbo Mix产品的单价在它所属的那类产品中的排名名次。-假设从小大到大排序-不使用变量，直接使用子查询select count(*)+1 as 'PriceRank' from Products as a where UnitPrice<(select UnitPrice from Products a where ProductName='Chef Anton''s Gumbo Mix')and CategoryID in (select CategoryID from Products where ProductName

24、='Chef Anton''s Gumbo Mix')-使用排名函数，并使用partition by子句if OBJECT_ID('tmp') is not null drop table tmpgoSELECT rank() OVER (partition by CategoryID ORDER BY UnitPrice) AS 'PriceRank',* into tmp from Products as aselect * from tmp where ProductName='Chef Anton''

25、;s Gumbo Mix'-25.统计检索价格最低的前%的产品是由哪些供应商提供的。-使用IN连接子查询select * from Suppliers where SupplierID in (select top 10 percent SupplierID from Products order by UnitPrice)-26.统计检索年上半年哪些客户没有销售订单记录。-使用not in连接子查询。先找到年上半年有销售记录的那些客户，再使用排除法select * from Customers where CustomerID not in(select CustomerID fro

26、m Orders where OrderDate between '2008-01-01' and '2008-06-30')-27.统计检索哪些产品的所有销售单价都大于成本单价。-使用not in。先在子查询中找到销售单价小于成本单价的那些产品销售记录，再使用排除法select * from Products where ProductID not in(select a.ProductID from OrderItems as ajoin Products as b on a.ProductID=b.ProductID and a.UnitPrice<

27、=b.UnitPrice)-28.统计检索哪些产品的平均销售单价大于成本单价。-修改题目：统计检索哪些产品的平均销售单价大于成本单价的.2倍。-不能使用avg(UnitPrice)函数。要先求出总销售额和销售量，然后求出平均单价，计算公式如下：sum(Amount)/sum(quantity)if OBJECT_ID('tmp') is not null drop table tmpgoselect ProductID,sum(Amount) as amt,sum(quantity) as qty into tmp from OrderItems as agroup by Pr

28、oductIDselect * from tmp as awhere amt/qty>1.2*(select UnitPrice from Products as b where b.ProductID=a.ProductID)-或不使用临时表，直接在having中使用子查询。select ProductID,sum(Amount) as amt,sum(quantity) as qty from OrderItems as agroup by ProductIDhaving sum(Amount)/sum(quantity)>1.2*(select UnitPrice from

29、Products as b where b.ProductID=a.ProductID)-29.统计检索平均单价小于元的这些产品的销售订单信息。-修改题目：统计检索平均销售单价小于元的这些产品的销售订单信息。-使用IN连接子查询，在子查询中检索平均销售单价小于元的产品select * from Orders where OrderID in (select OrderID from OrderItems where ProductID in (select ProductID from OrderItems group by ProductID having sum(Amount)/sum(q

30、uantity)<30)-30.根据订单明细表中销售单价与成本单价之间的差，计算汇总每笔订单的盈利额，并按降序排序。-使用衍生表，也可以使用临时表或with asselect a.*,fit from Orders as ajoin (select OrderID,SUM(Amount-quantity*b.UnitPrice) as 'profit' from OrderItems as ajoin Products as b on a.ProductID=b.ProductID group by OrderID ) as p on a.OrderID=p.O

31、rderIDorder by profit desc-31.统计检索哪些产品与Chocolate这个产品的单价最接近。-先使用临时表tmp求出所有产品与Chocolate这个产品单价之差的绝对值，然后求绝对值最小的产品（Chocolate本身除外）。if OBJECT_ID('tmp') is not null drop table tmpgoselect *,abs(UnitPrice-(select UnitPrice from Products where ProductName='Chocolate') as 'Difference' i

32、nto tmp from Products where ProductName<>'Chocolate'order by differencegoselect ProductID,Productname,Unitprice,Difference from tmp where Difference=(select min(difference) from tmp)-32.分别使用排名函数和其他方法，统计检索哪些产品的价格是相同的。-使用排名函数rank()，求出单价排名相同的产品存放到临时表tmp中if OBJECT_ID('tmp') is not

33、null drop table tmpgoSELECT Rank() OVER (ORDER BY UnitPrice ) AS 'PriceRank',* into tmp from Products as a-使用自连接找到单价相同的产品select a.ProductID,a.ProductName,a.UnitPrice,a.pricerank from tmp as a,tmp as bwhere a.pricerank=b.pricerank and a.ProductID<>b.ProductID-或直接使用自连接，而不使用排名函数select a.P

34、roductID,a.ProductName,a.UnitPrice from Products as a,Products as bwhere a.ProductID<>b.ProductID and a.UnitPrice=b.UnitPriceOrder by a.UnitPrice,a.ProductID-33.使用自连接，检索员工表中每个员工的直接主管（ReportsTo）的姓名和职务。select a.EmployeeID,a.Firstname,a.Lastname,a.Title,a.ReportsTo,b.FirstName+' '+b.LastN

35、ame as 'LeaderName',b.Title as 'LeaderTitle'from Employees as a,Employees as bwhere a.ReportsTo=b.EmployeeID-注意select列表和where条件中的a和b不能混乱-34.检索哪几张订单至少购买了订单所含的全部产品。-使用exists实现蕴含计算（即关系代数中的除法运算）SELECT * FROM Orders as p Where NOT EXISTS(SELECT 1 FROM OrderItems as a Where a.OrderID=10308

36、 and NOT EXISTS (SELECT 1 FROM OrderItems as b Where p.OrderID=b.OrderID and a.ProductID =b.ProductID) )and OrderID<>10308-35.检索哪几张订单所订购的产品与号订单是完全一样的。-解题步骤：)用tmp1检索出至少购买了订单所含全部产品的订单；)用tmp2检索出tmp1中这些订单所含的产品；)用tmp3检索出哪些订单包含了订单中所没有的产品；)除tmp3之外的订单，就是满足条件的订单;with tmp1 as( SELECT * FROM Orders as p

37、Where NOT EXISTS(SELECT 1 FROM OrderItems as a Where a.OrderID=10308 and NOT EXISTS (SELECT 1 FROM OrderItems as b Where p.OrderID=b.OrderID and a.ProductID =b.ProductID) )and OrderID<>10308 ),tmp2 as(select ProductID,OrderID from OrderItems where OrderID in (select OrderID from tmp1),tmp3 as

38、(select * from tmp2 where ProductID not in (select ProductID from OrderItems where OrderID=10308)select * from tmp1 where OrderID not in (select OrderID from tmp3)-36.使用CTE的WITH子句统计列出每个客户销售额的排名名次。;with tmp as (select CustomerID,SUM(Amount) as 'Amount' from Orders as ajoin OrderItems as b on

39、a.OrderID=b.OrderIDgroup by CustomerID)SELECT a.CustomerID,CompanyName,Amount,Rank() OVER (ORDER BY Amount ) AS 'Rank' from tmp as ajoin Customers as b on a.CustomerID=b.CustomerIDGO-37.统计检索每个产品的销售额在它所属产品类别中的排名名次。-使用排名函数的partition by子句with tmp as (select a.ProductID,CategoryID,SUM(Amount) as

40、 'Amount' from OrderItems ajoin Products b on a.ProductID=b.ProductIDgroup by a.ProductID,CategoryID)select *,rank() over (partition by CategoryID order by Amount desc) as 'AmountRank'from tmp-38.分别使用相关子查询和排名函数，统计检索每个产品类别中销售额排名前位的产品名称。-先计算出每个产品的销售汇总额，再使用排名函数的partition by子句;with tmp1

41、as (select a.ProductID,CategoryID,SUM(Amount) as 'Amount' from OrderItems ajoin Products b on a.ProductID=b.ProductIDgroup by a.ProductID,CategoryID),tmp2 as(select *,rank() over (partition by CategoryID order by Amount desc) as 'AmountRank'from tmp1)select * from tmp2 where AmountRa

42、nk<=3-使用关联查询;with tmp as (select a.ProductID,CategoryID,SUM(Amount) as 'Amount' from OrderItems ajoin Products b on a.ProductID=b.ProductIDgroup by a.ProductID,CategoryID)select * from tmp where Amount in (select top 3 Amount from tmp as a where a.CategoryID=tmp.CategoryID order by Amount

43、 desc)order by CategoryID,Amount desc-39.在客户表中添加两个列（Amount、Num），分别存储每个客户的销售额和订单笔数的合计值。使用UPDATE和相关子查询，订单明细表中的销售额和订单笔数分组汇总后填充到这两个列中去。-将Customers数据复制到myExample中，在myExample表中模拟update操作。if OBJECT_ID('myExample') is not null drop table myExamplegoselect * into myExample from Customers-在myExample中添

44、加两个列alter table myExample add Amount money, Num intgoupdate myExample set Amount=(select SUM(Amount) from OrderItems as a join Orders b on a.OrderID=b.OrderID where b.CustomerID=myExample.CustomerID), Num=(select count(b.OrderID) from OrderItems as a join Orders b on a.OrderID=b.OrderID where b.Cust

45、omerID=myExample.CustomerID)goselect * from myExample-40.使用UNION组合查询，在一个结果集中列出年度每个产品的明细销售记录和汇总销售额。select a.ProductID,Productname,cast(b.OrderID as varchar(10) as 'OrderID',convert(varchar(10),Orderdate,102) as 'OrderDate',Quantity,a.UnitPrice,a.Amount,0 as 'sortflag'from Orde

46、rItems as a join Orders as b on a.OrderID=b.OrderIDjoin Products as c on a.ProductID=c.ProductIDwhere OrderDate between '2008-01-01' and '2008-12-31'union allselect a.ProductID,Productname,'total','',sum(Quantity),SUM(Amount)/SUM(quantity),SUM(Amount),1from OrderItems

47、 as a join Orders as b on a.OrderID=b.OrderIDjoin Products as c on a.ProductID=c.ProductIDwhere OrderDate between '2008-01-01' and '2008-12-31'group by a.ProductID,Productnameorder by ProductID,Sortflag-41.统计检索年月份没有包含Confections类产品的那些订单。select * from Orders where OrderDate between &#

48、39;2009-06-01' and '2009-06-30' and OrderID not in(select distinct b.OrderID from OrderItems a,Orders b where a.OrderID=b.OrderID and OrderDate between '2009-06-01' and '2009-06-30' and ProductID in (select ProductID from Products where CategoryID in (select CategoryID fr

49、om Categories where CategoryName='Confections') ) )-42.统计检索年月份只包含Confections类产品的那些订单。-解题步骤：)查询出包含Confections类别产品的订单tmp1；)查询tmp1中订单所包含的所有产品到tmp2中；)在tmp2订单中查询包含非Confections类产品到tmp3；)在tmp1中去掉tmp3中的那些订单;with tmp1 as(select distinct OrderID from OrderItems where ProductID in (select ProductID fro

50、m Products where CategoryID in (select CategoryID from Categories where CategoryName='Confections') ) ),tmp2 as (select OrderID,ProductID from OrderItems where OrderID in (select OrderID from tmp1),tmp3 as (select * from tmp2 where ProductID in (select ProductID from Products where CategoryI

51、D in(select CategoryID from Categories where CategoryName<>'Confections') ) )select * from tmp1 where OrderID not in (select OrderID from tmp3)-43.统计检索哪些订单中包含Confections类产品的个数最多。-先统计每张订单包含Confections类产品的个数;with tmp1 as(select OrderID,count(*) as 'num' from OrderItems where Prod

52、uctID in (select ProductID from Products where CategoryID in (select CategoryID from Categories where CategoryName='Confections') ) group by OrderID)select * from tmp1 where num=(select MAX(num) from tmp1) -44.统计检索所有客户中利润贡献率最大的前%的客户名称。-先用CTE求出客户中利润贡献率最大的前%的客户编码;with tmp1 as(select CustomerID

53、,SUM(Amount-Quantity*c.Unitprice) as 'Profit' from OrderItems as ajoin orders as b on a.OrderID=b.OrderIDjoin Products as c on a.ProductID=c.ProductIDgroup by CustomerID)select CustomerID,CompanyName from Customers where CustomerID in(select top 20 percent CustomerID from tmp1 order by Profi

54、t desc)-45.统计计算销售额最大的前%客户其销售额的合计数占总销售的百分比。;with tmp1 as(select CustomerID,SUM(Amount) as 'Amount' from OrderItems as ajoin orders as b on a.OrderID=b.OrderIDgroup by CustomerID)select 'Precentage'=100.0*(select SUM(Amount) from tmp1 where CustomerID in (select top 20 percent Customer

55、ID from tmp1 order by Amount desc)/(select sum(Amount) from tmp1) -46.统计检索销售额最大的前%客户的所有订单信息，按客户编码和日期排序。;with tmp1 as(select CustomerID,SUM(Amount) as 'Amount' from OrderItems as ajoin orders as b on a.OrderID=b.OrderIDgroup by CustomerID)select * from orders where CustomerID in (select top 2

56、0 percent CustomerID from tmp1 order by Amount desc)order by CustomerID,OrderDate-47.统计检索销售额最大的前%客户购买次数最多的前%的产品名称。-使用两个CTE;with tmp1 as(select CustomerID,SUM(Amount) as 'Amount' from OrderItems as ajoin orders as b on a.OrderID=b.OrderIDgroup by CustomerID),tmp2 as(select top 20 percent Prod

57、uctID,COUNT(*) as 'NumberofOrders' from OrderItems as ajoin Orders as b on a.OrderID=b.OrderID where CustomerID in (select CustomerID from tmp1) group by ProductIDorder by NumberofOrders desc)select a.ProductID,ProductName,NumberofOrders from tmp2 ajoin Products b on a.ProductID=b.ProductID

58、-48.统计检索销售额最大的前%客户每个月的订单数量和销售额。-先求出每个客户的销售额;with tmp1 as(select top 20 percent CustomerID,SUM(Amount) as 'Amount' from OrderItems as ajoin orders as b on a.OrderID=b.OrderIDgroup by CustomerID Order by Amount desc)select b.CustomerID,month(OrderDate) as 'Month',count(*) as 'Numbe

59、rofOrders',sum(Amount) as 'Amount' from OrderItems as ajoin Orders as b on a.OrderID=b.OrderIDwhere CustomerID in (select CustomerID from tmp1)group by b.CustomerID,month(OrderDate)order by b.CustomerID,month(OrderDate)-49.统计检索所有订单中出现销售单价比成本单价低的这类情况次数最多的产品名称。-先查询每张订单明细中每个产品的销售单价和成本单价，再统计个数，最后求值最大的产品;with tmp1 as (select a.ProductID,b.Productname,b.UnitPrice as 'Costprice',a.UnitPrice*(1-Discount) as 'Saleprice'