use TSQLFundamentals2008;
go

-- 1.返回2007年6月生成的订单
select orderid, orderdate, custid, empid
from Sales.Orders
where orderdate > '20070601' and orderdate < '20070701'

-- 2.返回每个月的最后一天生成的订单
select orderid, orderdate, custid, empid
from Sales.Orders
where MONTH(orderdate) <> MONTH(dateadd(DAY, 1, orderdate))

select orderid, orderdate, custid, empid
from Sales.Orders
where orderdate = DATEADD(month, datediff(month, N'19000131', orderdate), N'19000131')

--3.返回姓氏中包含'a'两次或更多次的雇员
select empid, firstname, lastname 
from HR.Employees
where len(lastname) - len(replace(lastname,'a', '')) > 1

--4.返回总价格(单价*数量)大于10000的所有订单，并按总价格排序
select orderid, sum(unitprice*qty) as totalvalue
from Sales.OrderDetails
group by orderid
having sum(unitprice*qty) > 10000
order by totalvalue desc

--5.返回2007年平均运费最高的三个发货国家
select top(3) shipcountry, AVG(freight) as avgfreight
from Sales.Orders
where year(orderdate) = 2007
group by shipcountry
order by avgfreight desc

--6.为每个顾客单独根据订单日期的顺序(用orderid作为附加属性)来计算其订单的行号
select custid, orderdate, orderid, ROW_NUMBER() over(partition by custid order by orderid)
from sales.orders
order by custid asc, orderid asc

--7.构造一个select语句,让它根据每个雇员的友好称谓,而返回其性别.
select empid, firstname, lastname, titleofcourtesy, 
case titleofcourtesy
when N'MS.' then N'Female'
when N'Mrs.' then N'Female'
when N'Mr.' then N'Male'
else N'Unknown'
end 
as gender
from HR.Employees

--8.返回每个客户的客户ID和所在区域。对输出中的行按区域排序，NULL值排在最后面(在所有非null值之后)
select custid, region 
from Sales.Customers
order by 
case 
when region is null then 1
else 0
end, region asc