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