第二章 单表查询(4)

use TSQLFundamentals2008;go-- 1.返回2007年6月生成的订单select orderid, orderdate, custid, empidfrom Sales.Orderswhere orderdate > '20070601' and orderdate < '20070701'-- 2.返回每个月的最后一天生成的订单select orderid, orderdate, custid, empidfrom Sales.Orderswhere MONTH(orderdate) <> MONTH(dateadd(DAY, 1, orderdate))select orderid, orderdate, custid, empidfrom Sales.Orderswhere orderdate = DATEADD(month, datediff(month, N'19000131', orderdate), N'19000131')--3.返回姓氏中包含'a'两次或更多次的雇员select empid, firstname, lastname from HR.Employeeswhere len(lastname) - len(replace(lastname,'a', '')) > 1--4.返回总价格(单价*数量)大于10000的所有订单，并按总价格排序select orderid, sum(unitprice*qty) as totalvaluefrom Sales.OrderDetailsgroup by orderidhaving sum(unitprice*qty) > 10000order by totalvalue desc--5.返回2007年平均运费最高的三个发货国家select top(3) shipcountry, AVG(freight) as avgfreightfrom Sales.Orderswhere year(orderdate) = 2007group by shipcountryorder by avgfreight desc--6.为每个顾客单独根据订单日期的顺序(用orderid作为附加属性)来计算其订单的行号select custid, orderdate, orderid, ROW_NUMBER() over(partition by custid order by orderid)from sales.ordersorder by custid asc, orderid asc--7.构造一个select语句,让它根据每个雇员的友好称谓,而返回其性别.select empid, firstname, lastname, titleofcourtesy, case titleofcourtesywhen N'MS.' then N'Female'when N'Mrs.' then N'Female'when N'Mr.' then N'Male'else N'Unknown'end as genderfrom HR.Employees--8.返回每个客户的客户ID和所在区域。对输出中的行按区域排序，NULL值排在最后面(在所有非null值之后)select custid, region from Sales.Customersorder by case when region is null then 1else 0end, region asc