TSQL2012练习示例

TSQL2012数据库脚本下载链接：https://pan.baidu.com/s/1OkzZM8vJEL1hEsVUU1Jxhg

1、查询表Sales.Orders，返回每月最后一天的订单

use TSQL2012;

select orderid,custid,empid,orderdate
from Sales.Orders
where MONTH(DATEADD(day,1,orderdate)) <> MONTH(orderdate);

2、查询Sales.Orders表，返回2007年中平均运费最高的3个国家

use TSQL2012;

select top 3 shipcountry,SUM(freight)/COUNT(freight) as average
from Sales.Orders
where requireddate >= '2007-01-01' and requireddate < '2008-01-01'
group by shipcountry
order by average desc;

3、查询Sales.Orders表，分别对每个客户的订单按订单日期排序（使用订单ID作为决胜属性），计算订单编号。

use TSQL2012;

select custid,orderdate,orderid,
	row_number() over(partition by custid order by orderid) as rownum
from Sales.Orders
order by custid,orderid;

4、查询HR.Employees表，根据称谓判断性别。“Ms.”和“Mrs.”返回“Female”，“Mr.”返回“Male”，其他返回“Unknown”

use TSQL2012;

select empid,firstname,lastname,titleofcourtesy,
	case titleofcourtesy
		when 'Ms.' then 'Female'
		when 'Mrs.' then 'Female'
		when 'Mr.' then 'Male'
		else 'Unknown'
	end as gender
from HR.Employees;

5、查询Sales.Customers表，返回客户ID和地区。按地区排序输出，具有NULL标记的行最后排序（排在非NULL值之后）

注：T-SQL对NULL标记的默认先排序。

use TSQL2012;

select custid,region
from Sales.Customers
order by 
	case 
		when region IS null then 1
		else 0
	end,region;