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

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 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 title='Sales Representative'

--6.在订单表中检索所有在年月日之前需要发货但还没有发货的订单信息。

--注：不能省略ShippedDate这个条件，它的含义为：在年月日之后发货的订单在当时（年月日之前）等同于还没有发货

select * from Orders where RequiredDate<='2009-06-30' and (ShippedDate is null or ShippedDate>='2009-06-30')

--7.按产品类别编码对产品表进行分组汇总，检索平均单价$30元以上的所有产品类别。

--使用group by和having

select CategoryID,AVG(UnitPrice) from Products

group by CategoryID

having AVG(UnitPrice)>=30

--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.按客户和月份对订单表进行分组汇总，统计检索年度每个客户每月的订单数量。

--使用带两个关键字的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.统计检索年度每个产品的订单数和订单额。

--使用带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.统计检索年销售额大于$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.统计检索订单表中订单数量在张以上的这些客户的名称。

--在group by中使用多表连接

select CompanyName,COUNT(*) from Customers as a

join Orders as b on a.CustomerID=b.CustomerID

group by CompanyName

having COUNT(b.CustomerID)>=20

--16.统计检索哪些客户的订单数量最多。

--使用临时表和子查询

if OBJECT_ID('tmp') is not null  drop table tmp

go

select CustomerID,COUNT(*) as 'Num' into tmp from Orders

group by CustomerID

select * from tmp where num=(select MAX(num) from tmp)

--不使用子查询，而使用变量

declare @x money

select @x=MAX(num) from tmp

select * from tmp where num=@x

--17.统计检索哪些订单所包含的产品个数最多。

--与上题相似

if OBJECT_ID('tmp') is not null  drop table tmp

go

select OrderID,COUNT(*) as 'Num' into tmp from OrderItems

group by OrderID

go

select * from tmp where Num=(select MAX(Num) from tmp)

--18.统计检索哪几类产品其所属的产品个数最多、平均单价最高。

--使用表变量和insert...select语句

declare @tmp table(CategoryID int,num int,avgprice money)

insert into @tmp

select CategoryID,COUNT(*),AVG(UnitPrice) from Products group by CategoryID

--产品个数最多

select * from @tmp where num=(select MAX(num) from @tmp)

--平均单价最高

select * from @tmp where avgprice=(select MAX(avgprice) from @tmp)

--19.分别使用EXISTS、IN和ANY这个子句检索美国供应商提供的所有产品名称。

--使用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')

--使用ANY

select ProductName from Products where SupplierID=any 

(select SupplierID from Suppliers where Country='USA')

--20.利用随机函数，从产品表单价排名最低的前个产品中随机取出个产品。

--使用IN，注意order by NEWID()出现在主查询中

select top 5 * from Products where ProductID in (

select top 20 ProductID from Products order by UnitPrice)

order by NEWID()

--使用exists

select top 5 * from Products as a where exists 

(select top 20 * from Products as b where a.ProductID=b.ProductID order by UnitPrice)

order by NEWID()

--21.统计检索Confections这类产品中单价最便宜的产品名称。

--使用排名函数，先将排名结果放在一个临时表tmp中

if OBJECT_ID('tmp') is not null  drop table tmp

go

SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice) AS 'PriceRank',ProductName,UnitPrice,a.CategoryID

into tmp from Products as a

join Categories as b on a.CategoryID=b.CategoryID

where CategoryName='Confections'

--从tmp表中检索排名第一的产品

select * from tmp where PriceRank=1

--不使用排名函数

if OBJECT_ID('tmp') is not null  drop table tmp

go

SELECT ProductID,ProductName,UnitPrice,a.CategoryID 

into tmp from Products as a

join Categories as b on a.CategoryID=b.CategoryID

where CategoryName='Confections'

order by UnitPrice

select * from tmp where UnitPrice=(select min(UnitPrice) from tmp)

--22.统计检索Confections这类产品中每个产品单价与平均单价的差额

--在查询列表中使用子查询

SELECT ProductID,ProductName,UnitPrice,UnitPrice-(select AVG(UnitPrice) from Products as a

join categories b on a.CategoryID=b.CategoryID and categoryname='Confections') from Products as a

join categories b on a.CategoryID=b.CategoryID and categoryname='Confections'

--23.统计检索Chef Anton's Gumbo Mix产品的单价在所有产品中的排名名次。

--假设从大到小排序

--使用变量分步实现，先求出这个产品的单价

declare @x money

select @x=UnitPrice from Products where ProductName='Chef Anton''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 Anton''s Gumbo Mix')

--使用排名函数，注意要使用临时表

if OBJECT_ID('tmp') is not null   drop table tmp

go

SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice desc) AS 'PriceRank',* into tmp 

from Products as a

select * from tmp where ProductName='Chef Anton''s Gumbo Mix'

--24.统计检索Chef Anton'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='Chef Anton''s Gumbo Mix')

--使用排名函数，并使用partition by子句

if OBJECT_ID('tmp') is not null

    drop table tmp

go

SELECT rank() OVER (partition by CategoryID ORDER BY UnitPrice) AS 'PriceRank',* into tmp 

from Products as a

select * from tmp where ProductName='Chef Anton''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 from 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 a

join Products as b on a.ProductID=b.ProductID and a.UnitPrice<=b.UnitPrice)

--28.统计检索哪些产品的平均销售单价大于成本单价。

--修改题目：统计检索哪些产品的平均销售单价大于成本单价的.2倍。

--不能使用avg(UnitPrice)函数。要先求出总销售额和销售量，然后求出平均单价，计算公式如下：sum(Amount)/sum(quantity)

if OBJECT_ID('tmp') is not null  drop table tmp

go

select ProductID,sum(Amount) as amt,sum(quantity) as qty into tmp from OrderItems as a

group by ProductID

select * from tmp as a

where 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 a

group by ProductID

having sum(Amount)/sum(quantity)>1.2*(select UnitPrice from 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(quantity)<30)

)

--30.根据订单明细表中销售单价与成本单价之间的差，计算汇总每笔订单的盈利额，并按降序排序。

--使用衍生表，也可以使用临时表或with as

select a.*,p.profit from Orders as a

join (

select OrderID,SUM(Amount-quantity*b.UnitPrice) as 'profit' from OrderItems as a

join Products as b on a.ProductID=b.ProductID group by OrderID ) as p 

on a.OrderID=p.OrderID

order by profit desc

--31.统计检索哪些产品与Chocolate这个产品的单价最接近。

--先使用临时表tmp求出所有产品与Chocolate这个产品单价之差的绝对值，然后求绝对值最小的产品（Chocolate本身除外）。

if OBJECT_ID('tmp') is not null drop table tmp

go

select *,abs(UnitPrice-(select UnitPrice from Products where ProductName='Chocolate')) as 'Difference' 

into tmp from Products where ProductName<>'Chocolate'

order by difference

go

select ProductID,Productname,Unitprice,Difference from tmp 

where Difference=(select min(difference) from tmp)

--32.分别使用排名函数和其他方法，统计检索哪些产品的价格是相同的。

--使用排名函数rank()，求出单价排名相同的产品存放到临时表tmp中

if OBJECT_ID('tmp') is not null  drop table tmp

go

SELECT 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 b

where a.pricerank=b.pricerank and a.ProductID<>b.ProductID

--或直接使用自连接，而不使用排名函数

select a.ProductID,a.ProductName,a.UnitPrice from Products as a,Products as b

where a.ProductID<>b.ProductID and a.UnitPrice=b.UnitPrice

Order by a.UnitPrice,a.ProductID

--33.使用自连接，检索员工表中每个员工的直接主管（ReportsTo）的姓名和职务。

select a.EmployeeID,a.Firstname,a.Lastname,a.Title,a.ReportsTo,

b.FirstName+' '+b.LastName as 'LeaderName',b.Title as 'LeaderTitle'

from Employees as a,Employees as b

where 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 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 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 

(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 a

join OrderItems as b on a.OrderID=b.OrderID

group by CustomerID)

SELECT a.CustomerID,CompanyName,Amount,Rank() OVER (ORDER BY Amount ) AS 'Rank' from tmp as a

join Customers as b on a.CustomerID=b.CustomerID

GO

--37.统计检索每个产品的销售额在它所属产品类别中的排名名次。

--使用排名函数的partition by子句

with tmp as 

(select a.ProductID,CategoryID,SUM(Amount) as 'Amount' from OrderItems a

join Products b on a.ProductID=b.ProductID

group by a.ProductID,CategoryID)

select *,rank() over (partition by CategoryID order by Amount desc) as 'AmountRank'

from tmp

--38.分别使用相关子查询和排名函数，统计检索每个产品类别中销售额排名前位的产品名称。

--先计算出每个产品的销售汇总额，再使用排名函数的partition by子句

;with tmp1 as 

(select a.ProductID,CategoryID,SUM(Amount) as 'Amount' from OrderItems a

join Products b on a.ProductID=b.ProductID

group by a.ProductID,CategoryID),

tmp2 as

(select *,rank() over (partition by CategoryID order by Amount desc) as 'AmountRank'

from tmp1)

select * from tmp2 where AmountRank<=3

--使用关联查询

;with tmp as 

(select a.ProductID,CategoryID,SUM(Amount) as 'Amount' from OrderItems a

join Products b on a.ProductID=b.ProductID

group 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 desc)

order by CategoryID,Amount desc

--39.在客户表中添加两个列（Amount、Num），分别存储每个客户的销售额和订单笔数的合计值。使用UPDATE和相关子查询，订单明细表中的销售额和订单笔数分组汇总后填充到这两个列中去。

--将Customers数据复制到myExample中，在myExample表中模拟update操作。

if OBJECT_ID('myExample') is not null

  drop table myExample

go

select * into myExample from Customers

--在myExample中添加两个列

alter table myExample 

  add Amount money, Num int

go

update 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.CustomerID=myExample.CustomerID)

go

select * 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 OrderItems as a 

join Orders as b on a.OrderID=b.OrderID

join Products as c on a.ProductID=c.ProductID

where OrderDate between '2008-01-01' and '2008-12-31'

union all

select a.ProductID,Productname,'total','',sum(Quantity),SUM(Amount)/SUM(quantity),SUM(Amount),1

from OrderItems as a 

join Orders as b on a.OrderID=b.OrderID

join Products as c on a.ProductID=c.ProductID

where OrderDate between '2008-01-01' and '2008-12-31'

group by a.ProductID,Productname

order by ProductID,Sortflag

--41.统计检索年月份没有包含Confections类产品的那些订单。

select * from Orders where OrderDate between '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 from 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 from 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 CategoryID 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 ProductID 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,SUM(Amount-Quantity*c.Unitprice) as 'Profit' from OrderItems as a

join orders as b on a.OrderID=b.OrderID

join Products as c on a.ProductID=c.ProductID

group by CustomerID)

select CustomerID,CompanyName from Customers where CustomerID in

(select top 20 percent CustomerID from tmp1 order by Profit desc)

--45.统计计算销售额最大的前%客户其销售额的合计数占总销售的百分比。

;with tmp1 as

(select CustomerID,SUM(Amount) as 'Amount' from OrderItems as a

join orders as b on a.OrderID=b.OrderID

group by CustomerID)

select 'Precentage'=100.0*(select SUM(Amount) from tmp1 where CustomerID in (

select top 20 percent CustomerID from tmp1 order by Amount desc))/

(select sum(Amount) from tmp1) 

--46.统计检索销售额最大的前%客户的所有订单信息，按客户编码和日期排序。

;with tmp1 as

(select CustomerID,SUM(Amount) as 'Amount' from OrderItems as a

join orders as b on a.OrderID=b.OrderID

group by CustomerID)

select * from orders where CustomerID in 

(select top 20 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 a

join orders as b on a.OrderID=b.OrderID

group by CustomerID),

tmp2 as

(select top 20 percent ProductID,COUNT(*) as 'NumberofOrders' from OrderItems as a

join Orders as b on a.OrderID=b.OrderID where CustomerID in (select CustomerID from tmp1) 

group by ProductID

order by NumberofOrders desc)

select a.ProductID,ProductName,NumberofOrders from tmp2 a

join Products b on a.ProductID=b.ProductID 

--48.统计检索销售额最大的前%客户每个月的订单数量和销售额。

--先求出每个客户的销售额

;with tmp1 as

(select top 20 percent CustomerID,SUM(Amount) as 'Amount' from OrderItems as a

join orders as b on a.OrderID=b.OrderID

group by CustomerID Order by Amount desc)

select b.CustomerID,month(OrderDate) as 'Month',count(*) as 'NumberofOrders',

sum(Amount) as 'Amount' from OrderItems as a

join Orders as b on a.OrderID=b.OrderID

where 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'

 from OrderItems a

 join Products b on a.ProductID=b.ProductID),

tmp2 as

(select Productname,count(*) as 'Num' from tmp1 where Salepricegroup by Productname)

select * from tmp2 where num=(select MAX(num) from tmp2) 

--50.统计检索每个客户购买的产品中折扣率的最大值。

;with tmp as

(select CustomerID,ProductID,MAX(discount) as MaxDiscount from OrderItems a

join Orders b on a.OrderID=b.OrderID

group by CustomerID,ProductID )

select a.CustomerID,CompanyName,a.ProductID,ProductName,MaxDiscount from tmp a

join Customers b on a.CustomerID=b.CustomerID

join Products c on a.ProductID=c.ProductID

WHERE MaxDiscount>0

order by a.CustomerID,a.ProductID

--51.使用相关子查询，计算订单表中每笔订单的销售额占当月销售额的百分比。

--先求出每张订单的销售额

;with tmp1 as

(select a.OrderID,OrderDate,sum(Amount) as 'Amount' from OrderItems a

join orders b on a.OrderID=b.OrderID

group by a.OrderID,OrderDate)

select a.OrderID,OrderDate,Amount,

100.0*Amount/(select SUM(Amount) from tmp1 where MONTH(OrderDate)=month(a.OrderDate) and year(OrderDate)=year(a.OrderDate)) as 'Percentage'

from tmp1 as a

--52.统计检索哪些订单包含两个及两个以上的产品，而且每个产品的销售单价（打折后）都低于成本单价。

--先使用NOT IN检索出每个产品的销售单价（打折后）都低于成本单价的订单

--在使用自连接检索包含两个及两个以上的产品的订单

;with tmp as

(select OrderID,ProductID from OrderItems where OrderID not in (

select distinct a.OrderID from OrderItems a 

join Products b on a.ProductID=b.ProductID

where a.UnitPrice>=b.UnitPrice))

select distinct a.OrderID from tmp as a

join tmp as b on a.OrderID=b.OrderID and a.ProductID<>b.ProductID

--53.使用CASE语句，在输出员工表时添加员工的性别和雇佣年限。当员工编码最后一位字符为’F’时显示'Female'，'M'时显示'Male'。

select EmployeeID,LastName,FirstName,Minit,Title,BirthDate,Address,

CASE right(RTRIM(employeeid),1)

when 'F' then 'Female'

when 'M' then 'Male'

else 'Error'

END as 'Gender' from Employees

--54.在UPDATE语句中使用CASE语句修改产品表中所有Beverages类产品的价格。价格≤$5：涨价%；$5.01~$15：涨价%；$15.01~$25：涨价%；$25.01~$40：涨价%；其它不涨价。

--在myProducts表中模拟数据操作

if OBJECT_ID('myProducts') is not null

    drop table myProducts

go

select * into myProducts from Products

go

update myProducts set UnitPrice=Unitprice*CASE

when UnitPrice<=5  then 1.25

when UnitPrice<=15 then 1.2

when UnitPrice<=25 then 1.15

when UnitPrice<=40 then 1.0

END from categories where myProducts.CategoryID=categories.CategoryID and CategoryName='Beverages'

go

select a.ProductID,a.ProductName,c.categoryname,b.Unitprice as 'Oldprice',a.UnitPrice as 'newPrice'

 from myProducts a,Products b,Categories c where a.ProductID=b.ProductID and a.CategoryID=c.CategoryID

--55.在员工表中添加两个列AccountReceivable和AccountReceived，使用相关子查询将每个员工的销售额和货款回笼款分别计算填充到这两个列中去。

--在myExample表中模拟数据操作

if OBJECT_ID('myExample') is not null  drop table myExample

go

select * into myExample from Employees

--在模拟表中添加两个列

alter table myExample

  add AccountReceivable money,AccountReceived money

go

update myExample set 

AccountReceivable=(select sum(Amount) from OrderItems a 

join Orders b on a.OrderID=b.OrderID and EmployeeID=myexample.employeeid),

AccountReceived=(select sum(Amount) from OrderCollections a 

join Orders b on a.OrderID=b.OrderID and EmployeeID=myexample.employeeid)

go

select employeeID,Lastname,Firstname,AccountReceivable,AccountReceived from myExample

--56.在SUM函数中使用CASE语句，在一个结果集中统计检索每类产品中单价在下列各个区间中的产品个数。$1～$10、$10.01～$20、$20.01～$30、$30.01～$40、$40.01～$50、>$50元

--注意SUM语句中使用CASE的方法

select CategoryName,COUNT(*) as 'All',

SUM(CASE WHEN UnitPrice between 1     and 10 then 1 else 0 end) as 'Range1',

SUM(CASE WHEN UnitPrice between 10.01 and 20 then 1 else 0 end) as 'Range2',

SUM(CASE WHEN UnitPrice between 20.01 and 30 then 1 else 0 end) as 'Range3',

SUM(CASE WHEN UnitPrice between 30.01 and 40 then 1 else 0 end) as 'Range4',

SUM(CASE WHEN UnitPrice between 40.01 and 50 then 1 else 0 end) as 'Range5',

SUM(CASE WHEN UnitPrice>50 then 1 else 0 end) as 'Range6'

from Products a,Categories b where a.CategoryID=b.CategoryID

group by CategoryName 

--57.统计检索年下半年订单销售额每个月都在万以上的产品名称。

--先求出产品年度每个月的销售额

;with tmp as

(select ProductID,month(OrderDate) as 'Month',sum(Amount) as 'Amount'

from OrderItems a

join orders b on a.OrderID=b.OrderID

where OrderDate between '2009-06-01' and '2009-12-31'

group by ProductID,month(OrderDate)

having sum(Amount)>30000  )

select ProductID from tmp 

group by ProductID having COUNT(*)=6

--58.统计检索年每个月销售额最大的客户名称。

--解题步骤：）先用tmp求出年每个客户每个月的销售额；）再使用相关子查询检索每个月销售额的最大值

;with tmp as

(select CustomerID,month(OrderDate) as 'Month',sum(Amount) as 'Amount'

from OrderItems a

join orders b on a.OrderID=b.OrderID

where OrderDate between '2009-01-01' and '2009-12-31'

group by CustomerID,month(OrderDate)

)

select MONTH,CustomerID,Amount from tmp as a where CustomerID in

(select top 1 CustomerID from tmp as b where month=a.month order by Amount desc)

order by month

--59.分别使用相关子查询和UNION ALL，统计检索每张订单的销售额和货款回笼额，同时列出订单的其它全部信息。

--使用相关子查询，效率较低

select *,

(select SUM(Amount) from OrderItems where OrderID=a.OrderID) as 'SalesAmount',

(select SUM(Amount) from OrderCollections where OrderID=a.OrderID) as 'CollectionAmount'

from orders as a

--使用UNION ALL,先将订单信息与订单的销售额和货款回笼额组合在一个结果集中，这是每张订单有两行记录，然后再使用group by汇总在一个订单中

;with tmp as

(select OrderID,OrderDate,CustomerID,EmployeeID,RequiredDate,Freight,0 as 'SalesAmount',0 as 'CollectionAmount'

from orders

union all

select a.OrderID,a.OrderDate,a.CustomerID,a.EmployeeID,a.RequiredDate,a.Freight,SUM(b.Amount),SUM(c.Amount) from Orders a 

join OrderItems b on a.OrderID=b.OrderID

join orderCollections c on a.OrderID=c.OrderID

group by a.OrderID,a.OrderDate,a.CustomerID,a.EmployeeID,a.RequiredDate,a.Freight)

select OrderID,OrderDate,CustomerID,EmployeeID,RequiredDate,Freight,sum(SalesAmount) as 'SalesAmount',

sum(CollectionAmount) as 'CollectionAmount' from tmp 

group by OrderID,OrderDate,CustomerID,EmployeeID,RequiredDate,Freight

--60.使用UNION ALL将年及年度销售额最大的前位客户合并在一个结果集中，并按客户编码排序。

 select a.* from (select top 5 CustomerID,SUM(Amount) as 'Amount' from OrderItems a,Orders b 

where a.OrderID=b.OrderID

 and year(OrderDate)=2008

 group by CustomerID order by Amount desc) as a

 union all 

 select * from (select top 5 CustomerID,SUM(Amount) as 'Amount' from OrderItems a,Orders b 

where a.OrderID=b.OrderID

 and year(OrderDate)=2009

 group by CustomerID order by Amount desc) as b

 order by CustomerID

 --注：不使用衍生表无法在单个查询中排序