7.9 练习

1. 编写一个查询，以MM/DD/YY的格式返回AdventureWorks2008中所有雇员的就职日期。

USE AdventureWorks;

SELECT EmployeeID, CONVERT(varchar(8), HireDate, 3)
FROM HumanResources.Employee;

2. 分别使用JOIN、子查询和EXISTS编写查询，列出AdventureWorks2008中没有下任何订单的所有客户。

JOIN 方式：

USE AdventureWorks ;

SELECT sc.CustomerID, sc.AccountNumber 
FROM 
	Sales.Customer sc
LEFT JOIN 
	Sales.SalesOrderHeader soh
	ON
		sc.CustomerID = soh.CustomerID 
WHERE
	soh.CustomerID IS NULL;

子查询方式：

USE AdventureWorks ;

SELECT sc.CustomerID, sc.AccountNumber 
FROM 
	Sales.Customer sc
WHERE sc.CustomerID NOT IN (
	SELECT DISTINCT soh.CustomerID
	FROM Sales.SalesOrderHeader soh
);

EXISTS 方式：

USE AdventureWorks ;

SELECT sc.CustomerID, sc.AccountNumber 
FROM 
	Sales.Customer sc
WHERE NOT EXISTS (
	SELECT DISTINCT soh.CustomerID
	FROM Sales.SalesOrderHeader soh
	WHERE soh.CustomerID = sc.CustomerID 
);

3. 编写查询显示 AdventureWorks2008中花费超过70 000美元的账号所对应的最近5个订单。

USE AdventureWorks ;

SELECT TOP 5 tt.SalesOrderID, tt.Rev, soh.OrderDate FROM
(SELECT t.SalesOrderID, t.Rev
FROM 
(SELECT sod.SalesOrderID, SUM(sod.UnitPrice*(1-UnitPriceDiscount) * OrderQty) AS Rev
FROM Sales.SalesOrderDetail sod
GROUP BY
	sod.SalesOrderID) t
WHERE 
	t.Rev > 70000) tt
JOIN Sales.SalesOrderHeader soh
	ON tt.SalesOrderID = soh.SalesOrderID 
ORDER BY soh.OrderDate DESC

显示结果为：

SalesOrderID Rev                   OrderDate

------------ --------------------- -----------------------

71784        89869.2768            2004-06-01 00:00:00.000

71824        85393.7415            2004-06-01 00:00:00.000

71841        83076.5707            2004-06-01 00:00:00.000

71847        89981.79              2004-06-01 00:00:00.000

71894        70205.79              2004-06-01 00:00:00.000

(5 行受影响)