Microsoft SQL Server 2008技术内幕:T-SQL语言基础 第二章课后练习答案
----------------------------------------------------------------------
----------------- 练习题 --------------------------------------
-- test2.1 --
--- 返回2007年6月 生成的订单
select orderid , orderdate , custid , empid
from Sales.Orders where orderdate between '20070601' and '20070630';
-- test2.2 --
-- 返回每个月最后一天生成的订单
-- 再加上一天 就到下个月 ,造成两个月份不同,这样就找到了每个月的最后一天了
select orderid , orderdate , custid , empid
from Sales.Orders
where MONTH(DATEADD(day,1,orderdate))<>MONTH(orderdate);
-- test 2.3 --
-- 返回姓氏(lastname) 中包含字母‘a'两次或更多次的雇员
select empid , firstname , lastname
from HR.Employees
where lastname like '%a%a%';
-- test 2.4 --
-- 返回总价格大于10000 的所有订单 ,并按总价格排序
select orderid ,sum(unitprice * qty) as totalvalue
from Sales.OrderDetails
group by orderid
having sum(unitprice * qty) >10000
order by sum(unitprice * qty) desc;
-- test 2.5 --
-- 返回2007年平均运费最高的三个发货国家
select top(3) shipcountry, SUM(freight)/COUNT(freight) as avgfreight
from Sales.Orders
where YEAR(orderdate) = 2007
group by shipcountry
order by SUM(freight)/COUNT(freight) desc ;
-- TEST 2.6
-- 为每个顾客单独根据订单日期的顺序(用order ID 作为附加属性)来计算其订单的行号
select custid, orderdate,orderid,row_number() over(partition by custid order by orderdate) as rownum
from Sales.Orders
order by custid;
-- test 2.7 --
-- 构造一个select语句,让他根据每个雇员的友好称谓,而返回其性别。对于’Ms',和‘Mrs ’,则返回'Female';对于'Mr',则返回'Male';对于其他情况(如'Dr'),则返回'Unknown'
select empid, firstname , lastname ,titleofcourtesy,
case
when titleofcourtesy = 'Ms.' then 'Femmal'
when titleofcourtesy = 'Mrs.' then 'Femmal'
when titleofcourtesy = 'Mr.' then 'Male'
else 'Unknown'
end AS gender
from HR.Employees;
-- test2.8 --
-- 返回每个客户的客户id和所在区域。对输出中的行按区域排序,NULL值排在最后面(在所有非NULL值之后)。
-- 注意T-SQL中NULL值的默认排序行为时把NULL值排在前面(这是因为sql server 中null值很小)
-- 解决办法:重新定义NULL值,使得NULL值变大,非null值变小。
select custid , region
from Sales.Customers
order by case when region IS null then 1 else 0 end;