关于SQL的周总结

--任务1：统计图书表中每个作者出版了多少本书
use BooksManager
go
select AuthorID as 作者, count(*)  as 图书数量
from Books
group by AuthorID
--任务2：统计图书表中不同作者出版的不同类别的图书的平均价格，要求显示图书平均价格在40以上的记录。
use BooksManager
go
select AuthorID as 作者, avg(UnitPrice) as 平均值
from Books
group by AuthorID
HAVING avg(UnitPrice) >=40
--任务3：统计不同种类图书的数量，要求只显示图书数量在5到15之间的图书类别。
use BooksManager
go
select CategoryID as 图书类别, sum(Quantity) as 图书数量
from Books
group by CategoryID
Having sum(Quantity)>=5 and sum(Quantity)<=15


--任务4：统计每个会员订购日期在2010-02-01之前的订单总金额，并要求只显示订单总金额大于6000的。
use BooksManager
go
select CustomerID as 会员, sum(Total) as 总金额
from Orders
where OrderDate < '2010-02-01'
group by CustomerID
having sum(Total)>6000


--任务5：查询每个订单的订书总量，要求只显示订书量在50本以上的，并按订书量降序排列。


use BooksManager
go
select OrderID as 书名,sum(Quantity) as 订购数量
from OrderDetails
group by OrderID
having sum(Quantity) >50
order by sum(Quantity) desc


--复习任务：
--（1）查询20岁以上的男性作者的姓名和居住的城市
use BooksManager
go
select AuthorName as 作者姓名, City as 城市
from Authors
where Sex = 1 and '2018-05-14' - Birthday > 20
group by AuthorName, City


--（2）找出年龄最大的三个作者姓名及年龄
use BooksManager
go
select top 3 AuthorName as 作者姓名,Age as 年龄
from Authors
group by AuthorName,Age
order by Age desc




--（3）查询作者的居住城市（去除重复行）
use BooksManager
go