SQL Server 作業練習(group by,join union等等)

以下2個例子是老師在課堂上演練的，以此做為基準出了5題：

SELECT * FROM 客戶
WHERE 客戶編號 NOT IN(SELECT 客戶編號 FROM 訂貨主檔)

SELECT * FROM 產品資料
WHERE 產品編號 NOT IN(SELECT 產品編號
  FROM 訂貨明細 AS A JOIN 訂貨主檔 AS B ON A.訂單號碼=B.訂單號碼
  WHERE DATEPART(YEAR,B.訂單日期)=2002)

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

SELECT * FROM 訂貨明細

--↓ ↓ ↓ ↓ ↓如此算出每則明細的總價

SELECT 訂單號碼,單價*數量*(1-折扣) AS 金額 FROM 訂貨明細

--↓ ↓ ↓ ↓ ↓算出每張單的總價

SELECT 訂單號碼,ROUND(SUM(單價*數量*(1-折扣)),0) AS 金額
FROM 訂貨明細
GROUP BY 訂單號碼
ORDER BY 訂單號碼

--↓ ↓ ↓ ↓ ↓結合訂貨主檔 表列出整個總金額

SELECT A.訂單號碼,A.客戶編號,A.員工編號,A.訂單日期
 ,B.金額,A.運費,B.金額+A.運費 AS 總金額
FROM 訂貨主檔 AS A JOIN (SELECT 訂單號碼,ROUND(SUM(單價*數量*(1-折扣)),0) AS 金額
   FROM 訂貨明細 GROUP BY 訂單號碼) AS B
   ON A.訂單號碼=B.訂單號碼

以下是題目及答案，不一定最完美：

1.以Northwind資料庫為例，寫出一個SQL命令，查詢公司2002年
四季交易總金額，查詢結果格式如下
(無交易之當季，金額以0顯示)
季別 交易總金額

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

=======================================================

SELECT DATEPART(QUARTER,A.訂單日期) AS 季度
   ,ISNULL(SUM(B.總金額),0) AS 交易總金額
FROM 訂貨主檔 AS A JOIN 
(
SELECT 訂單號碼,ROUND(SUM(單價*數量*(1-折扣)),2) AS 總金額
FROM 訂貨明細
GROUP BY 訂單號碼
)AS  B 
ON A.訂單號碼=B.訂單號碼 
WHERE DATEPART(YEAR,A.訂單日期) = 2002
GROUP BY ALL DATEPART(QUARTER,A.訂單日期)
ORDER BY 季度

2.以Northwind資料庫為例，寫出SQL命令，查詢2003年曾經下單
累計總金額超過12000元之客戶清單(金額由大到小取前10名)。查詢結
果格式如下：
客戶編號交易金額

----------- ---------------------
=======================================================

SELECT TOP(10) A.客戶編號,SUM(B.總金額) AS 交易金額
FROM 訂貨主檔 AS A JOIN
(
SELECT 訂單號碼,ROUND(SUM(單價*數量*(1-折扣)),2) AS 總金額
FROM 訂貨明細
GROUP BY 訂單號碼
) 
AS B ON A.訂單號碼 = B.訂單號碼
WHERE A.訂單日期 BETWEEN '2003.1.1' AND '2003.12.31'
GROUP BY A.客戶編號  
HAVING SUM(B.總金額)>12000
ORDER BY 交易金額 DESC

3. 不用ALL找出各職位中，有多少女生?

=======================================================

SELECT 稱呼,職稱,COUNT(*) AS 人數
FROM 員工
WHERE 稱呼 = '小姐'
GROUP BY 稱呼,職稱
UNION
SELECT 稱呼,職稱,0 AS 人數
FROM 員工
WHERE 稱呼 != '小姐'
GROUP BY 稱呼,職稱
ORDER BY 職稱

4. 2004年銷售TOP10

=======================================================

SELECT TOP(10) A.員工編號,B.姓名,SUM(C.訂單總價) AS 年度銷售額
FROM 訂貨主檔 AS A 
JOIN 員工 AS B ON A.員工編號=B.員工編號
JOIN (SELECT 訂單號碼,ROUND(SUM(數量*單價*(1-折扣)),2) AS 訂單總價
  FROM 訂貨明細
  GROUP BY 訂單號碼) AS C ON A.訂單號碼 = C.訂單號碼
WHERE DATEPART(YEAR,A.訂單日期) = 2004
GROUP BY A.員工編號,B.姓名 
ORDER BY 年度銷售額 DESC

5.找出2004最受歡迎及銷售最高的

=======================================================

--賣得最受歡迎的產品(也就是最多人買的)-------------------------------------
SELECT B.產品編號,C.產品,COUNT(*) AS 數量
FROM 訂貨主檔 AS A
JOIN 訂貨明細 AS B ON A.訂單號碼 = B.訂單號碼
JOIN 產品資料 AS C ON B.產品編號 = C.產品編號
WHERE DATEPART(YEAR,訂單日期) =2004
GROUP BY B.產品編號,C.產品
ORDER BY 數量 DESC

--銷售最好的產品(不一定最多人買，但錢最多)---------------------------------
SELECT C.產品,B.產品編號,ROUND(SUM(B.銷售額),2) AS 產品年度銷售額
FROM 訂貨主檔 AS A
JOIN (SELECT 訂單號碼,產品編號,單價*數量*(1-折扣) AS 銷售額
   FROM 訂貨明細) AS B ON A.訂單號碼 = B.訂單號碼
JOIN        產品資料 AS C ON B.產品編號 = C.產品編號
WHERE DATEPART(YEAR,A.訂單日期) = 2004
GROUP BY C.產品,B.產品編號 
ORDER BY 產品年度銷售額 DESC