SQL Server 2008 编程入门经典 第七章练习

    <p>7.9 练习</p>

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 行受影响)