数据库习题及答案查询语句.docx
《数据库习题及答案查询语句.docx》由会员分享，可在线阅读，更多相关《数据库习题及答案查询语句.docx（21页珍藏版）》请在冰点文库上搜索。
数据库习题及答案查询语句
--1.在产品表中检索所有产品名称以字符串’en’或’ton’结尾的产品，并按单价降序排序。
select*fromProductswhereProductNamelike'%en%'orProductNamelike'%ton%'
orderbyUnitPricedesc
--2.根据产品表，在单价$15~$25之间的产品中随机检索个产品。
--利用随机函数NewID（）
selecttop5*fromProductswhereUnitPricebetween15and25
orderbyNewID（）
--3.在客户表中检索所有美国客户来自于哪些城市。
--使用distinct去掉重复记录
selectdistinctCityfromCustomerswhereCountry='USA'
--4.在供应商表中检索所有邮政编码（Postalcode）是字母开头的而且传真号（Fax）为非空（NULL）的供应商信息。
--使用like和isnotnull
select*fromSupplierswherepostalcodelike'[A-Z]%'andFaxisnotnull
--5.在员工表中检索所有职位为SalesRepresentative的这些员工的主管（ReportsTo）的编码。
--使用distinct去掉重复记录
selectdistinctReportsTofromEmployeeswheretitle='SalesRepresentative'
--6.在订单表中检索所有在年月日之前需要发货但还没有发货的订单信息。
--注：
不能省略ShippedDate这个条件，它的含义为：
在年月日之后发货的订单在当时（年月日之前）等同于还没有发货
select*fromOrderswhereRequiredDate<='2009-06-30'and（ShippedDateisnullorShippedDate>='2009-06-30'）
--7.按产品类别编码对产品表进行分组汇总，检索平均单价$30元以上的所有产品类别。
--使用groupby和having
selectCategoryID,AVG（UnitPrice）fromProducts
groupbyCategoryID
havingAVG（UnitPrice）>=30
--8.按供应商和产品类别进行分组汇总，检索每个供应商提供的每类产品的平均单价。
--使用带两个关键字的groupby
selectSupplierID,CategoryID,AVG（UnitPrice）fromProducts
groupbySupplierID,CategoryID
orderbySupplierID,CategoryID
--9.按供应商编码对产品表进行分组汇总，检索哪些供应商至少提供了两个单价在$20以下的产品。
--在使用groupby的查询语句中，注意where和having的出现顺序位置
selectSupplierIDfromProducts
whereUnitPrice<20
groupbySupplierID
havingcount（*）>=2
--10.按客户和月份对订单表进行分组汇总，统计检索年度每个客户每月的订单数量。
--使用带两个关键字的groupby
selectCustomerID,Month（OrderDate）as'Month',COUNT（*）as'NumberofOrders'fromOrders
whereOrderDatebetween'2009-01-01'and'2009-12-31'
groupbyCustomerID,MONTH（OrderDate）
orderbyCustomerID,MONTH（OrderDate）
--11.统计检索年度每个产品的订单数和订单额。
--使用带where的groupby
selectProductID,COUNT（*）as'NumberofOrders',SUM（Amount）as'Amount'
fromOrdersasa
joinOrderItemsasbona.OrderID=b.OrderID
whereOrderDatebetween'2009-01-01'and'2009-12-31'
groupbyProductID
orderbyProductID
--12.统计检索年销售额大于$150万的员工姓名。
--使用带where、having和多表连接的groupby
selectFirstname+''+LastnameasEmployeeNamefromOrdersasa
joinOrderItemsasbona.OrderID=b.OrderID
joinEmployeesascona.EmployeeID=c.EmployeeID
whereOrderDatebetween'2009-01-01'and'2009-12-31'
groupbyFirstname+''+Lastname
havingSUM（Amount）>1500000
orderbyEmployeeName
--13.统计检索与Tofu同一类别的产品中，哪些产品的单价比Tofu的单价两倍还大。
--在where子句中使用子查询
selectProductID,UnitpricefromProducts
whereUnitPrice>（selectUnitPrice*2fromProductswhereProductName='tofu'）
andCategoryID=（selectCategoryIDfromProductswhereProductName='tofu'）
--14.统计检索哪几类产品的平均单价大于Beverages类产品的平均单价。
--修改题目：
统计检索哪几类产品的平均单价大于Confections类产品的平均单价。
--在having子句中使用子查询和多表连接
selectCategoryID,AVG（UnitPrice）fromProducts
groupbyCategoryIDhavingAVG（UnitPrice）>
（selectAVG（UnitPrice）fromProductsasa
joincategoriesasbona.CategoryID=b.CategoryIDwhereCategoryName='Confections'）
--15.统计检索订单表中订单数量在张以上的这些客户的名称。
--在groupby中使用多表连接
selectCompanyName,COUNT（*）fromCustomersasa
joinOrdersasbona.CustomerID=b.CustomerID
groupbyCompanyName
havingCOUNT（b.CustomerID）>=20
--16.统计检索哪些客户的订单数量最多。
--使用临时表和子查询
ifOBJECT_ID（'tmp'）isnotnulldroptabletmp
go
selectCustomerID,COUNT（*）as'Num'intotmpfromOrders
groupbyCustomerID
select*fromtmpwherenum=（selectMAX（num）fromtmp）
--不使用子查询，而使用变量
declare@xmoney
select@x=MAX（num）fromtmp
select*fromtmpwherenum=@x
--17.统计检索哪些订单所包含的产品个数最多。
--与上题相似
ifOBJECT_ID（'tmp'）isnotnulldroptabletmp
go
selectOrderID,COUNT（*）as'Num'intotmpfromOrderItems
groupbyOrderID
go
select*fromtmpwhereNum=（selectMAX（Num）fromtmp）
--18.统计检索哪几类产品其所属的产品个数最多、平均单价最高。
--使用表变量和insert...select语句
declare@tmptable（CategoryIDint,numint,avgpricemoney）
insertinto@tmp
selectCategoryID,COUNT（*）,AVG（UnitPrice）fromProductsgroupbyCategoryID
--产品个数最多
select*from@tmpwherenum=（selectMAX（num）from@tmp）
--平均单价最高
select*from@tmpwhereavgprice=（selectMAX（avgprice）from@tmp）
--19.分别使用EXISTS、IN和ANY这个子句检索美国供应商提供的所有产品名称。
--使用exists
selectProductNamefromProductsasawhereexists
（select1fromSuppliersasbwherea.SupplierID=b.SupplierIDandCountry='USA'）
--使用IN
selectProductNamefromProductswhereSupplierIDin
（selectSupplierIDfromSupplierswhereCountry='USA'）
--使用ANY
selectProductNamefromProductswhereSupplierID=any
（selectSupplierIDfromSupplierswhereCountry='USA'）
--20.利用随机函数，从产品表单价排名最低的前个产品中随机取出个产品。
--使用IN，注意orderbyNEWID（）出现在主查询中
selecttop5*fromProductswhereProductIDin（
selecttop20ProductIDfromProductsorderbyUnitPrice）
orderbyNEWID（）
--使用exists
selecttop5*fromProductsasawhereexists
（selecttop20*fromProductsasbwherea.ProductID=b.ProductIDorderbyUnitPrice）
orderbyNEWID（）
--21.统计检索Confections这类产品中单价最便宜的产品名称。
--使用排名函数，先将排名结果放在一个临时表tmp中
ifOBJECT_ID（'tmp'）isnotnulldroptabletmp
go
SELECTROW_NUMBER（）OVER（ORDERBYUnitPrice）AS'PriceRank',ProductName,UnitPrice,a.CategoryID
intotmpfromProductsasa
joinCategoriesasbona.CategoryID=b.CategoryID
whereCategoryName='Confections'
--从tmp表中检索排名第一的产品
select*fromtmpwherePriceRank=1
--不使用排名函数
ifOBJECT_ID（'tmp'）isnotnulldroptabletmp
go
SELECTProductID,ProductName,UnitPrice,a.CategoryID
intotmpfromProductsasa
joinCategoriesasbona.CategoryID=b.CategoryID
whereCategoryName='Confections'
orderbyUnitPrice
select*fromtmpwhereUnitPrice=（selectmin（UnitPrice）fromtmp）
--22.统计检索Confections这类产品中每个产品单价与平均单价的差额
--在查询列表中使用子查询
SELECTProductID,ProductName,UnitPrice,UnitPrice-（selectAVG（UnitPrice）fromProductsasa
joincategoriesbona.CategoryID=b.CategoryIDandcategoryname='Confections'）fromProductsasa
joincategoriesbona.CategoryID=b.CategoryIDandcategoryname='Confections'
--23.统计检索ChefAnton'sGumboMix产品的单价在所有产品中的排名名次。
--假设从大到小排序
--使用变量分步实现，先求出这个产品的单价
declare@xmoney
select@x=UnitPricefromProductswhereProductName='ChefAnton''sGumboMix'
--统计比这个产品单价大的其他产品的个数，这个个数加就是这个产品的排名。
注意这个算法。
selectcount（*）+1as'PriceRank'fromProductswhereUnitPrice>@x
--或不使用变量，直接使用子查询
selectcount（*）+1as'PriceRank'fromProductswhereUnitPrice>
（selectUnitPricefromProductswhereProductName='ChefAnton''sGumboMix'）
--使用排名函数，注意要使用临时表
ifOBJECT_ID（'tmp'）isnotnulldroptabletmp
go
SELECTROW_NUMBER（）OVER（ORDERBYUnitPricedesc）AS'PriceRank',*intotmp
fromProductsasa
select*fromtmpwhereProductName='ChefAnton''sGumboMix'
--24.统计检索ChefAnton'sGumboMix产品的单价在它所属的那类产品中的排名名次。
--假设从小大到大排序
--不使用变量，直接使用子查询
selectcount（*）+1as'PriceRank'fromProductsasa
whereUnitPrice<
（selectUnitPricefromProductsawhereProductName='ChefAnton''sGumboMix'）
andCategoryIDin（selectCategoryIDfromProductswhereProductName='ChefAnton''sGumboMix'）
--使用排名函数，并使用partitionby子句
ifOBJECT_ID（'tmp'）isnotnull
droptabletmp
go
SELECTrank（）OVER（partitionbyCategoryIDORDERBYUnitPrice）AS'PriceRank',*intotmp
fromProductsasa
select*fromtmpwhereProductName='ChefAnton''sGumboMix'
--25.统计检索价格最低的前%的产品是由哪些供应商提供的。
--使用IN连接子查询
select*fromSupplierswhereSupplierIDin
（selecttop10percentSupplierIDfromProductsorderbyUnitPrice）
--26.统计检索年上半年哪些客户没有销售订单记录。
--使用notin连接子查询。
先找到年上半年有销售记录的那些客户，再使用排除法
select*fromCustomerswhereCustomerIDnotin
（selectCustomerIDfromOrderswhereOrderDatebetween'2008-01-01'and'2008-06-30'）
--27.统计检索哪些产品的所有销售单价都大于成本单价。
--使用notin。
先在子查询中找到销售单价小于成本单价的那些产品销售记录，再使用排除法
select*fromProductswhereProductIDnotin
（selecta.ProductIDfromOrderItemsasa
joinProductsasbona.ProductID=b.ProductIDanda.UnitPrice<=b.UnitPrice）
--28.统计检索哪些产品的平均销售单价大于成本单价。
--修改题目：
统计检索哪些产品的平均销售单价大于成本单价的.2倍。
--不能使用avg（UnitPrice）函数。
要先求出总销售额和销售量，然后求出平均单价，计算公式如下：
sum（Amount）/sum（quantity）
ifOBJECT_ID（'tmp'）isnotnulldroptabletmp
go
selectProductID,sum（Amount）asamt,sum（quantity）asqtyintotmpfromOrderItemsasa
groupbyProductID
select*fromtmpasa
whereamt/qty>1.2*（selectUnitPricefromProductsasbwhereb.ProductID=a.ProductID）
--或不使用临时表，直接在having中使用子查询。
selectProductID,sum（Amount）asamt,sum（quantity）asqtyfromOrderItemsasa
groupbyProductID
havingsum（Amount）/sum（quantity）>1.2*（selectUnitPricefromProductsasbwhereb.ProductID=a.ProductID）
--29.统计检索平均单价小于元的这些产品的销售订单信息。
--修改题目：
统计检索平均销售单价小于元的这些产品的销售订单信息。
--使用IN连接子查询，在子查询中检索平均销售单价小于元的产品
select*fromOrderswhereOrderIDin
（selectOrderIDfromOrderItemswhereProductIDin
（selectProductIDfromOrderItemsgroupbyProductIDhavingsum（Amount）/sum（quantity）<30）
）
--30.根据订单明细表中销售单价与成本单价之间的差，计算汇总每笔订单的盈利额，并按降序排序。
--使用衍生表，也可以使用临时表或withas
selecta.*,p.profitfromOrdersasa
join（
selectOrderID,SUM（Amount-quantity*b.UnitPrice）as'profit'fromOrderItemsasa
joinProductsasbona.ProductID=b.ProductIDgroupbyOrderID）asp
ona.OrderID=p.OrderID
orderbyprofitdesc
--31.统计检索哪些产品与Chocolate这个产品的单价最接近。
--先使用临时表tmp求出所有产品与Chocolate这个产品单价之差的绝对值，然后求绝对值最小的产品（Chocolate本身除外）。
ifOBJECT_ID（'tmp'）isnotnulldroptabletmp
go
select*,abs（UnitPrice-（selectUnitPricefromProductswhereProductName='Chocolate'））as'Difference'
intotmpfromProductswhereProductName<>'Chocolate'
orderbydifference
go
selectProductID,Productname,Unitprice,Differencefromtmp
whereDifference=（selectmin（difference）fromtmp）
--32.分别使用排名函数和其他方法，统计检索哪些产品的价格是相同的。
--使用排名函数rank（），求出单价排名相同的产品存放到临时表tmp中
ifOBJECT_ID（'tmp'）isnotnulldroptabletmp
go
SELECTRank（）OVER（ORDERBYUnitPrice）AS'PriceRank',*intotmp
fromProductsasa
--使用自连接找到单价相同的产品
selecta.ProductID,a.ProductName,a.UnitPrice,a.pricerankfromtmpasa,tmpasb
wherea.pricerank=b.pricerankanda.ProductID<>b.ProductID
--或直接使用自连接，而不使用排名函数
selecta.ProductID,a.ProductName,a.UnitPricefromProductsasa,Productsasb
wherea.ProductID<>b.Pr
升级会员 