--8-2 常用函數的使用
--輸出字串
SELECT '資料庫系統'

--輸出運算式
SELECT 3+5 


SELECT 3+5, '資料庫系統'


--常用的數學函數
--四捨五入函數：

--四捨五入至小數點以下二位：
SELECT ROUND( 84.94, 2 )


--四捨五入至小數點以下一位：
SELECT ROUND( 84.94, 1 )


--四捨五入至整數第一位：
SELECT ROUND( 84.94, 0 )


--四捨五入至整數第二位：
SELECT ROUND( 84.94, -1 )


--天花板函數，小數以下無條件進位至整數
SELECT CEILING( 59.3 )


--天花板函數，小數以下無條件進位至整數
SELECT CEILING( 59.8 )


--地板函數，整數以下無條件捨去
SELECT FLOOR( 59.3 )


--地板函數，整數以下無條件捨去
SELECT FLOOR( 59.8 )

--開平方根(SQRT)
SELECT SQRT( 16 )
SELECT SQRT( 16.556 )

--平方(SQUARE)函數
SELECT SQUARE( 3 )
SELECT SQUARE( 11.25 )

--絕對值函數：
SELECT ABS( -69 )

--常用的轉換函數
--資料型態轉換函數：


--將數值90轉換成變動長度的字串
SELECT CAST( 90 AS VARCHAR )
--等同於
select CONVERT (varchar ,90)

--將數值先經過四捨五入後，再轉換成固定長度型態
SELECT CAST( ROUND( 87.994 , 0 ) AS NUMERIC( 2,0 ) )
--等同於
SELECT CONVERT( NUMERIC( 2, 0 ) , ROUND( 87.994 , 0 ) )

--取固定長度，總長度為3，小數以下1位，取位前也會自動四捨五入處理
SELECT CAST( 87.994 AS NUMERIC( 3, 1 ) )
--等同於
SELECT CONVERT( NUMERIC( 3, 1 ) , 87.994 )


--取固定長度，總長度為2，小數以下0位，取位前也會自動四捨五入處理
SELECT CAST( 87.994 AS NUMERIC( 4, 2 ) )
等同於
SELECT CONVERT( NUMERIC( 4, 2 ) , 87.994 )


--常用的日期函數
--目前系統日期時間函數
SELECT GETDATE( )


--僅查詢日期中之『年』的部份
SELECT DATEPART( year ,'2010/4/30' )

--僅查詢日期中之『季』的部份
SELECT DATEPART( quarter ,'2010/4/30' )

--僅查詢日期中之『月』的部份
SELECT DATEPART( month ,'2010/4/30' )

--僅查詢日期中之『日』的部份
SELECT DATEPART( day ,'2010/4/30' )

--查詢該日期在一週中是第幾天
SELECT DATEPART( weekday , '2010/12/1' )

--將原本日期往前累加一年
SELECT DATEADD( year , 1 , '2010/05/01' )


--將原本日期往前累加一個月
SELECT DATEADD( month , 1 , '2010/05/01' )

--將原本日期往前累加一季
SELECT DATEADD( quarter , 1 , '2010/05/01' )

--將原本日期往回推算三個月前
SELECT DATEADD( month , -3 , '2010/05/01' )

--計算兩個日期在『年』的部份之差異
SELECT DATEDIFF( yy, '2009/12/05', '2010/01/01' )

--計算兩個日期在『年』的部份之差異( startdate大於enddate時會出現負數值 )
SELECT DATEDIFF( yy, '2012/12/05', '2010/01/01' )

--計算兩個日期在『月』的部份之差異
SELECT DATEDIFF( mm, '2010/01/01', '2011/05/05' )

--計算兩個日期在『日』的部份之差異
SELECT DATEDIFF( dd, '2010/01/01', '2010/01/20' )

--計算兩個日期在『季』的部份之差異
SELECT DATEDIFF( qq, '2010/01/01', '2012/05/05' )

--轉換為大寫
SELECT UPPER( 'AbCdE' )

--轉換為小寫
SELECT LOWER( 'AbCdE' )

--計算字串長度函數
SELECT LEN( 'abcde' )
SELECT LEN('我有幾個字呢?')

--沒有指定搜尋的起始位置，便會從頭開始搜尋
SELECT CHARINDEX('DEF', 'ABCDEFGHABCDEFGH')

--有指定搜尋的起始位置，便會從指定的位置開始搜尋
SELECT CHARINDEX('DEF', 'ABCDEFGHABCDEFGH' , 5)

--試試中文字
SELECT CHARINDEX('2朵', '妳我是天上的2朵雲')

--取字串的左、右邊的子字串
SELECT LEFT( 'ABCDEFG', 3 )
SELECT RIGHT( 'ABCDEFG' , 3 )


--取一個字串中的子字串
SELECT SUBSTRING( 'abcdefg' , 2 , 3 )
SELECT SUBSTRING( '日一二三四五六' , 4 , 1 )

--重複某字串數次
SELECT REPLICATE( '*' , 5 )

--取當天的系統日期，並轉換成民國XX年XX月XX日 週X
SELECT '民國'
+CAST(DATEPART(YY,GETDATE()) - 1911 AS VARCHAR)+'年'
       + CAST(DATEPART(MM,GETDATE()) AS VARCHAR)+'月'
       + CAST(DATEPART(DD,GETDATE()) AS VARCHAR)+'日  週'
+SUBSTRING('日一二三四五六',DATEPART(DW,GETDATE()),1)


--如同前一範例，僅將輸出數字部份變該為固定長度2，例如 2月顯示成 02月
SELECT '民國'
+CAST(DATEPART(YY,GETDATE()) - 1911 as VARCHAR)+'年'
+REPLICATE('0', 2 - LEN(CAST(DATEPART(MM,GETDATE()) as VARCHAR)))
       + CAST(DATEPART(MM,GETDATE()) as VARCHAR)+'月'
       + REPLICATE('0', 2 - LEN(CAST(DATEPART(DD,GETDATE()) as VARCHAR)))
       + CAST(DATEPART(DD,GETDATE()) as VARCHAR)+'日  週'
+SUBSTRING('日一二三四五六',DATEPART(DW,GETDATE()),1)
[範例8-1]
--查詢員工的所有資料
SELECT * 
FROM 員工

--使用個別資料行輸出查詢資料表 - FROM
--[範例8-2]
--查詢員工的所有資料
SELECT 員工編號, 姓名, 職稱, 性別, 出生日期, 任用日期, 區域號碼, 地址, 分機號碼, 主管
FROM 員工

--[範例8-3]
--透過『員工』資料表查詢該公司有多少種不同的職務
--相同職稱會重複輸出
SELECT 職稱
FROM 員工

--相同職稱僅會出現一筆
SELECT DISTINCT 職稱
FROM 員工

--輸出兩個資料行時，會以兩個資料行的組合來判斷重複性，可與上一例做比較
SELECT DISTINCT 職稱, 姓名
FROM 員工

--[範例8-4]
--查詢職稱為業務的員工
--[輸出] ( 員工編號, 姓名, 職稱 )
SELECT 員工編號, 姓名, 職稱
FROM 員工
WHERE 職稱 =  '業務'

--[範例8-5]
--查詢公司有哪些男業務
--[輸出] ( 員工編號, 姓名, 職稱, 性別 )
--[提示] 男業務代表所要篩選的條件為，『性別』等於'男'，且『職稱』等於'業務'
SELECT 員工編號, 姓名, 職稱, 性別
FROM 員工
WHERE 職稱 = '業務' AND 性別 = '男'

--[範例8-6]
--查詢男工程師和女業務的基本資料
--[輸出] ( 員工編號, 姓名, 職稱, 性別 )
--[提示] 男工程師和女業務代表所要篩選的條件為，(『性別』等於'男'且『職稱』等於'工程師')或(『性別』等於'女'且『職稱』等於'業務')
SELECT 員工編號, 姓名, 職稱, 性別
FROM 員工
WHERE ( 性別='男' AND 職稱 = '工程師' ) OR
       ( 性別='女' AND 職稱 = '業務')
       
--[範例8-7]
--查詢當月生日的員工資料
--[輸出] ( 員工編號, 姓名, 出生日期 )
--[提示] 必須使用getdate()函數取得當天日期，再使用datepart()或month()函數來取得出生日期與當天的月份來比對。
SELECT 員工編號, 姓名, 出生日期
FROM 員工
WHERE datepart( month, 出生日期 ) = datepart( month, getdate() )

--[範例8-8]
--列出每一位員工的主管之員工編號，並將員工的『姓名』更名為『員工姓名』、『主管』更名為『上司編號』
--[輸出] ( 員工編號, 員工姓名, 上司編號 )
SELECT 員工編號, 姓名 AS 員工姓名, 主管 AS 上司編號
FROM 員工
--省略 『AS』 的寫法
SELECT 員工編號, 姓名 員工姓名, 主管 上司編號
FROM 員工

--[範例8-9]
--查詢每位員工的『年齡』
--[輸出] ( 員工編號, 姓名, 出生日期, 年齡 )
--[提示] 年齡 = 當日的年份 – 出生年份

SELECT 員工編號, 姓名, 出生日期, datediff( year, 出生日期, getdate() ) AS 年齡
FROM 員工

-- [範例8-10]
--查詢從任用日期計算起，超過16年(含)年資的員工，預計在25年後退休的資料
--[輸出] ( 員工編號, 姓名, 任用日期, 預計退休日 )
--[提示1] 預計退休日 = 任用日期 + 25 * 12 個月 ( 25年, 每年12個月 )
--[提示2] 篩選條件：任用日期至本月超過(含) 16 * 12個月 ( 16年, 每年12個月 )
SELECT 員工編號, 姓名, 任用日期, dateadd ( month, 25*12, 任用日期 ) AS 預計退休日
FROM 員工
WHERE datediff( month, 任用日期, getdate() ) >= 16*12

--[範例8-11]
--請列出所有員工資料，並依據單一『任用日期』資料行的遞減排序
--[輸出] ( 員工編號, 姓名, 職稱, 任用日期 )
SELECT 員工編號, 姓名, 職稱, 任用日期
FROM 員工
ORDER BY 任用日期 DESC

--[範例8-12]
--請列出所有員工資料，並依據『職稱』與『員工編號』多個資料行的排序，其中『職稱』資料行遞減排序，『員工編號』資料行遞增排序
--[輸出] ( 員工編號, 姓名, 職稱, 任用日期 )
SELECT 員工編號, 姓名, 職稱, 任用日期
FROM 員工
ORDER BY 職稱 DESC, 員工編號 ASC

--[範例8-13]
--	試將員工先依據『職稱』遞增排序後，再取其前5筆資料。
--[輸出] ( 員工編號, 姓名, 職稱 )
SELECT TOP 5 員工編號, 姓名, 職稱
FROM 員工
ORDER BY 職稱
--也試著比較加入WITH TIES後的結果。
SELECT TOP 5 WITH TIES 員工編號, 姓名, 職稱
FROM 員工
ORDER BY 職稱

--[範例8-14]
--	試將員工先依據『職稱』遞增排序後，再取其前50%的資料。
--[輸出] ( 員工編號, 姓名, 職稱 )
SELECT TOP 50 PERCENT 員工編號, 姓名, 職稱
FROM 員工
ORDER BY 職稱
--也試著比較加入WITH TIES後的結果
SELECT TOP 50 PERCENT WITH TIES 員工編號, 姓名, 職稱
FROM 員工
ORDER BY 職稱

--[範例8-15]
--從員工資料表中挑選出女性員工，並輸出至另一個新的資料表。
--[輸出] ( 員工編號, 姓名, 職稱,性別 )
SELECT 員工編號, 姓名, 職稱, 性別 INTO 女員工
FROM 員工
WHERE 性別='女'

--[範例8-16]
--將員工資料表中( 員工編號, 姓名, 職稱, 性別 )的結構，複製到另一個新的資料表 『tmp_員工』，並將『姓名』資料行更名為『員工姓名』。
SELECT 員工編號, 姓名 AS 員工姓名, 職稱, 性別 INTO tmp_員工
FROM 員工
WHERE 1=0

--[範例8-17]
--查詢出男業務有承接訂單的資料 ,依員工編號遞增排序,訂單編號遞減排序
--[輸出] ( 員工編號, 姓名, 性別, 職稱, 訂單編號, 訂貨日期, 產品編號, 數量 )
--[提示] 此範例主要是進行『員工』與『訂單』基本的『內部合併』

--步驟1：找出需要的資料表，此步驟的結果就是『交叉合併』
SELECT *
FROM 員工, 訂單, 訂單明細

--步驟2：進行『內部合併』
SELECT *
FROM 員工, 訂單, 訂單明細
WHERE 員工.員工編號 = 訂單.員工編號 AND 
 訂單.訂單編號 = 訂單明細.訂單編號

--步驟3：加入『篩選』條件，性別是男生且職稱是業務
SELECT *
FROM 員工, 訂單, 訂單明細
WHERE ( 員工.員工編號 = 訂單.員工編號 AND  
 訂單.訂單編號 = 訂單明細.訂單編號 ) AND
	   (  性別 = '男'  AND 職稱 = '業務' )

--步驟4：填入要輸出的『資料行』
SELECT 員工.員工編號, 姓名, 性別, 職稱, 訂單.訂單編號, 訂貨日期, 產品編號, 數量
FROM 員工, 訂單, 訂單明細
WHERE ( 員工.員工編號 = 訂單.員工編號 AND  
 訂單.訂單編號 = 訂單明細.訂單編號 ) AND
	   (  性別 = '男'  AND 職稱 = '業務' )

--步驟5：『群組與彙總函數』計算
--此需求沒必要此步驟

--步驟6：彙總函數後的結果篩選
--此需求沒必要此步驟

--步驟7：資料行的『排序』
SELECT 員工.員工編號, 姓名, 性別, 職稱, 訂單.訂單編號, 訂貨日期, 產品編號, 數量
FROM 員工, 訂單, 訂單明細
WHERE ( 員工.員工編號 = 訂單.員工編號 AND  
 訂單.訂單編號 = 訂單明細.訂單編號 ) AND
	   (  性別 = '男'  AND 職稱 = '業務' )
ORDER BY 員工.員工編號 ASC, 訂單.訂單編號 DESC

--[範例8-18]
--查詢出 2005 年第 4 季，有承接訂單的員工與其訂單相關資料，並依據員工編號及訂單編號遞增排序
--[輸出] ( 員工編號, 姓名, 訂單編號, 訂貨日期, 產品名稱, 數量 )
SELECT E.員工編號, 姓名, O.訂單編號, 訂貨日期, 產品名稱, 數量
FROM 員工 AS E, 訂單 AS O, 訂單明細 OD, 產品資料 P
WHERE ( E.員工編號 = O.員工編號  AND
        O.訂單編號 = OD.訂單編號 AND
        OD.產品編號 = P.產品編號 ) AND
       ( datepart( year, 訂貨日期) = 2005 AND datepart( quarter, 訂貨日期 ) = 4 )
ORDER BY E.員工編號 ASC, O.訂單編號 ASC

--[範例8-19]
--題目如同[範例8-18]，將SELECT語法改用 JOIN...ON... 的方式寫出
SELECT E.員工編號, 姓名, O.訂單編號, 訂貨日期, 產品名稱, 數量
FROM 員工 AS E 
INNER JOIN 訂單 AS O ON E.員工編號 = O.員工編號
INNER JOIN 訂單明細 AS OD ON O.訂單編號 = OD.訂單編號
INNER JOIN 產品資料 AS P ON OD.產品編號 = P.產品編號
WHERE datepart( year, 訂貨日期) = 2005 AND datepart( quarter, 訂貨日期 ) = 4 
ORDER BY E.員工編號 ASC, O.訂單編號 ASC

--[範例8-20]
--查詢出每一位員工所承接的訂單資料，並依據員工編號遞增排序。
--(無承接訂單的也要列出唷)
--[輸出] ( 員工編號, 姓名, 訂單編號, 訂貨日期 )
SELECT 員工.員工編號, 姓名, 訂單編號, 訂貨日期
FROM 員工 LEFT OUTER JOIN 訂單 ON 員工.員工編號 = 訂單.員工編號
ORDER BY 員工.員工編號
--[範例8-21] ( 易犯錯 )
--查詢出 2005 年第 4 季，所有員工與其訂單相關資料，並依據員工編號及訂單編號遞增排序，無承接訂單之員工也要全部列出
--[輸出] ( 員工編號, 姓名, 訂單編號, 訂貨日期, 產品名稱, 數量 )
	
--[錯誤寫法]
SELECT E.員工編號, 姓名, O.訂單編號, 訂貨日期, 產品名稱, 數量
FROM 員工 AS E 
LEFT OUTER JOIN 訂單 AS O ON E.員工編號 = O.員工編號
LEFT OUTER JOIN 訂單明細 AS OD ON O.訂單編號 = OD.訂單編號
LEFT OUTER JOIN 產品資料 AS P ON OD.產品編號 = P.產品編號
WHERE datepart( year, 訂貨日期 ) = 2005 AND datepart( quarter, 訂貨日期 ) = 4 
ORDER BY E.員工編號 ASC, O.訂單編號 ASC

--[正確寫法]
SELECT E.員工編號, 姓名, O.訂單編號, 訂貨日期, 產品名稱, 數量
FROM 員工 AS E 
LEFT OUTER JOIN ( SELECT * 
FROM 訂單 
WHERE datepart( year, 訂貨日期 ) = 2005 AND 
datepart( quarter, 訂貨日期 ) = 4 )
AS O ON E.員工編號 = O.員工編號
LEFT OUTER JOIN 訂單明細 OD ON O.訂單編號 = OD.訂單編號
LEFT OUTER JOIN 產品資料 P ON OD.產品編號 = P.產品編號
ORDER BY E.員工編號 ASC, O.訂單編號 ASC

--[範例8-22]
--試將所有業務和業務助理的上司資料列出
--[輸出] ( 部屬編號, 部屬姓名, 上司編號, 上司姓名 )
SELECT 部屬.員工編號 AS 部屬編號, 部屬.姓名 AS 部屬姓名, 上司.員工編號 AS 上司編號, 上司.姓名 AS 上司姓名
FROM 員工 AS 部屬, 員工 AS 上司
WHERE 部屬.主管 = 上司.員工編號 AND
        ( 部屬.職稱 = '業務' OR 部屬.職稱 = '業務助理' )

--[範例8-23]
--查詢全公司員工與其上司的基本資料，縱使沒有上司也必須列出該筆資料。
--[輸出] ( 部屬編號, 部屬姓名, 上司編號, 上司姓名 )
SELECT 部屬.員工編號 AS 部屬編號, 部屬.姓名 AS 部屬姓名, 
上司.員工編號 AS 上司編號, 上司.姓名 AS 上司姓名
FROM 員工 AS 部屬 LEFT OUTER JOIN 員工 AS 上司 ON 部屬.主管 = 上司.員工編號

--[範例8-24]
--將『客戶』與『供應商』的資料聯集，重複資料僅會出現一筆 – 使用UNION
--[輸出] (公司名稱)
SELECT 公司名稱
FROM 客戶
UNION
SELECT 供應商名稱
FROM 供應商

--[範例8-25]
--將『客戶』與『供應商』的資料聯集，所有重複資料皆會出現 – 使用UNION ALL
--[輸出] ( 公司名稱 )
SELECT 公司名稱
FROM 客戶
UNION ALL
SELECT 供應商名稱
FROM 供應商

--[範例8-26]
--查詢既是『客戶』也是『供應商』的資料 – 使用INTERSECT
--[輸出] ( 公司名稱 )
SELECT 公司名稱
FROM 客戶
INTERSECT
SELECT 供應商名稱
FROM 供應商

--[範例8-27]
--查詢單純是『客戶』，不是『供應商』的資料 – 使用EXCEPT
--[輸出] ( 公司名稱 )
SELECT 公司名稱
FROM 客戶
EXCEPT
SELECT 供應商名稱
FROM 供應商

--[範例8-28]
--從『客戶』資料表中挑選出住在台北市或高雄市的客戶資料
--[輸出] ( 客戶編號, 公司名稱, 聯絡人, 地址 )
SELECT 客戶編號, 公司名稱, 聯絡人, 地址
FROM 客戶
WHERE LEFT( 地址, 3 ) = '台北市' OR
LEFT( 地址, 3 ) = '高雄市'
--等同於以下寫法
SELECT 客戶編號, 公司名稱, 聯絡人, 地址
FROM 客戶
WHERE LEFT( 地址, 3 ) IN ('台北市', '高雄市' )

--[範例8-29]
--查詢員工編號為1、3、5、7和9的員工資料
--[輸出] ( 員工編號, 姓名, 職稱, 地址 )
SELECT 員工編號, 姓名, 職稱, 地址
FROM 員工
WHERE 員工編號 = 1 OR 員工編號 = 3 OR 員工編號 = 5 OR
員工編號 = 7 OR 員工編號 = 9
--等同於以下寫法
SELECT 員工編號, 姓名, 職稱, 地址
FROM 員工
WHERE 員工編號 IN ( 1, 3, 5, 7, 9 )

--[範例8-30]
--查詢員工編號為7 至 11的員工資料
--[輸出] ( 員工編號,姓名,職稱,地址 )
SELECT 員工編號, 姓名, 職稱, 地址
FROM 員工
WHERE 員工編號 >= 7 AND 員工編號 <= 11

--等同於以下寫法，注意在BETWEEN...AND...的兩個邊界值是有被包括。
SELECT 員工編號, 姓名, 職稱, 地址
FROM 員工
WHERE 員工編號 BETWEEN 7 AND 11

--[範例8-31]
--查詢住在『中山區』的客戶資料。也就是在地址的第四至第六個字元為『中山區』，第一至第三個字元可為任意字元，第七字元之後就為任何字且任何字數。
--[輸出] ( 客戶編號, 公司名稱, 地址 )
SELECT 客戶編號, 公司名稱, 地址
FROM 客戶
WHERE 地址 LIKE '___中山區%'

--[範例8-32]
--查詢客戶地址的第一個字為『宜』或『花』或『高』的客戶資料,依地址排序
--[輸出] ( 客戶編號, 公司名稱, 地址 )

SELECT 客戶編號, 公司名稱, 地址
FROM 客戶
WHERE 地址 LIKE  '[宜花高]%'
ORDER BY 地址

--[範例8-33]
--查詢客戶地址的第一個字排除『宜』、『花』、『高』的客戶資料。也就是地址的第一個字不是『宜』、『花』、『高』的客戶資料,依地址排序
--[輸出] ( 客戶編號, 公司名稱, 地址 )
SELECT 客戶編號, 公司名稱, 地址
FROM 客戶
WHERE 地址 LIKE '[^宜花高]%'
ORDER BY 地址

--[範例8-34]
--從『客戶』資料表中挑選出 不住在 台北市和高雄市的客戶資料
--[輸出] ( 客戶編號, 公司名稱, 聯絡人, 地址 )
SELECT 客戶編號, 公司名稱, 聯絡人, 地址
FROM 客戶
WHERE LEFT( 地址, 3 ) NOT IN ('台北市', '高雄市' )

--[範例8-35]
--查詢員工編號在7 至 11以外的員工資料
--[輸出] ( 員工編號,姓名,職稱,地址 )
SELECT 員工編號, 姓名, 職稱, 地址
FROM 員工
WHERE 員工編號 NOT BETWEEN  7 AND  11

--[範例8-36]
--查詢客戶地址的第一個字排除『宜』、『花』、『高』的客戶資料。也就是地址的第一個字不是『宜』、『花』、『高』的客戶資料,依地址排序
--[輸出] ( 客戶編號, 公司名稱, 地址 )
SELECT 客戶編號, 公司名稱, 地址
FROM 客戶
WHERE 地址 NOT LIKE ' [宜花高]% '
ORDER BY 地址

--[範例8-37]
--查詢『茶類產品資料』中有哪些產品的『建議單價』比『果汁類產品資料』中所有產品的『建議單價』高。
--[輸出] ( 類別名稱, 產品名稱, 建議單價 )
SELECT 類別名稱, 產品名稱, 建議單價
FROM 茶類產品資料
WHERE 建議單價 > ALL ( SELECT 建議單價 FROM 果汁類產品資料 )

--[範例8-38]
--查詢『果汁類產品資料』中有哪些產品的『建議單價』比『茶類產品資料』中任何一項產品的『建議單價』高。
--[輸出] ( 類別名稱, 產品名稱, 建議單價 )
SELECT 類別名稱, 產品名稱, 建議單價
FROM 果汁類產品資料
WHERE 建議單價 > ANY ( SELECT 建議單價 FROM 茶類產品資料 )

--[範例8-39]
--計算出每位員工所承接的每張訂單之總金額，並依據員工姓名與訂單編號遞增排序。
--[輸出] ( 姓名, 訂單編號, 訂貨日期, 總金額 )
SELECT 姓名, 訂單.訂單編號, 訂貨日期, sum( 實際單價 * 數量 ) AS 總金額
FROM 員工, 訂單, 訂單明細
WHERE 員工.員工編號 = 訂單.員工編號 AND
        訂單.訂單編號 = 訂單明細.訂單編號
GROUP BY 姓名, 訂單.訂單編號, 訂貨日期
ORDER BY 姓名 ASC, 訂單.訂單編號 ASC

--[範例8-40]
--挑選出2006年(含)以後的訂單，並計算出每位員工所承接的每張訂單之總金額，並依據員工姓名與訂單編號遞增排序。
--[輸出] ( 姓名, 訂單編號, 訂貨日期, 總金額 )
SELECT 姓名, 訂單.訂單編號, 訂貨日期, sum( 實際單價 * 數量 ) AS 總金額
FROM 員工, 訂單, 訂單明細
WHERE 員工.員工編號 = 訂單.員工編號 AND
        訂單.訂單編號 = 訂單明細.訂單編號 AND
        訂貨日期 >= '2006/01/01'
GROUP BY 姓名, 訂單.訂單編號, 訂貨日期
ORDER BY 姓名 ASC, 訂單.訂單編號 ASC

--[範例8-41]
--挑選出2006年(含)以後的訂單，而且單筆訂單總金額超過1000元(不含)的資料，並依據員工姓名與訂單編號遞增排序。
--[輸出] ( 姓名, 訂單編號, 訂貨日期, 總金額 )
SELECT 姓名, 訂單.訂單編號, 訂貨日期, sum( 實際單價 * 數量 ) AS 總金額
FROM 員工, 訂單, 訂單明細
WHERE 員工.員工編號 = 訂單.員工編號 AND
        訂單.訂單編號 = 訂單明細.訂單編號 AND
訂貨日期 >= '2006/01/01'
GROUP BY 姓名, 訂單.訂單編號, 訂貨日期
HAVING sum( 實際單價 * 數量 ) > 1000
ORDER BY 姓名 ASC, 訂單.訂單編號 ASC

--[範例8-42] 單一欄位多筆紀錄
--從所有供應商中，查詢出有哪些供應商也有提供編號為'S0001'供應商所提供的類別產品。
--[輸出] ( 供應商, 供應商編號, 供應商名稱, 類別編號)
SELECT DISTINCT 供應商.供應商編號, 供應商名稱, 類別編號
FROM 供應商, 產品資料
WHERE 供應商.供應商編號 = 產品資料.供應商編號 AND
        類別編號 IN ( SELECT DISTINCT 類別編號
                     FROM 產品資料
                     WHERE 供應商編號 = 'S0001' )
ORDER BY 供應商編號

--[範例8-43] 多個欄位多筆紀錄
--計算在2006/01/01(含)之後的訂單資料，列出每位員工單筆訂單總金額有超過1000元的訂單筆數。
--[輸出] ( 員工姓名, 筆數 )
SELECT 姓名 AS 員工姓名, COUNT(姓名) AS 筆數
FROM 
(
 SELECT 姓名, 訂單.訂單編號, 訂貨日期, sum( 實際單價 * 數量 ) AS 總金額
 FROM 員工, 訂單, 訂單明細
 WHERE 員工.員工編號 = 訂單.員工編號 AND
         訂單.訂單編號 = 訂單明細.訂單編號 AND
         訂貨日期 >= '2006/01/01'
 GROUP BY 姓名, 訂單.訂單編號, 訂貨日期
 HAVING sum( 實際單價 * 數量 ) > 1000
) AS 績優業績表
GROUP BY 姓名

--[範例8-43]
--查詢既是『客戶』也是『供應商』的資料 – 使用子查詢
--[輸出] ( 公司名稱 )
SELECT 公司名稱 
FROM 客戶
WHERE 公司名稱 IN ( SELECT 供應商名稱 
FROM 供應商 
WHERE 供應商名稱 = 公司名稱 )

--[範例8-44]
--等同於以下直接使用內部合併方式和結果
--[輸出] ( 公司名稱 )
SELECT 客戶.公司名稱 
FROM 客戶, 供應商
WHERE 公司名稱 =供應商名稱

--[範例8-45]
--查詢單純是『客戶』，不是『供應商』的資料 – 使用子查詢
--[輸出] ( 公司名稱 )
SELECT 公司名稱 
FROM 客戶
WHERE 公司名稱 NOT IN ( SELECT 供應商名稱 
FROM 供應商 
WHERE 供應商名稱 = 公司名稱 )

--[範例8-46]
--查詢單純是『客戶』，也是『供應商』的資料 – 使用exists
--[輸出] ( 公司名稱 )
SELECT *
FROM 客戶 
WHERE EXISTS ( SELECT * 
FROM 供應商 
WHERE 供應商名稱 = 公司名稱 )

 --[範例8-47]
--查詢單純是『客戶』，不是『供應商』的資料 – 使用NOT EXISTS
SELECT *
FROM 客戶
WHERE NOT EXISTS ( SELECT * 
FROM 供應商 
WHERE 供應商名稱 = 公司名稱 )

--[範例8-48]
--連結遠端伺服器( 假設遠端另一部的SQL SERVER之IP位址為192.168.1.55 )，並查詢每位供應商有提供哪些產品。
--[輸出] ( 供應商編號, 供應商名稱, 產品編號, 產品名稱 )
SELECT S.供應商編號, 供應商名稱, 產品編號, 產品名稱
FROM [192.168.1.55].CH08範例資料庫.dbo.供應商 AS S, 
[192.168.1.55].CH08範例資料庫.dbo.產品資料 AS P
WHERE S.供應商編號 = P.供應商編號
ORDER BY S.供應商編號

--[範例8-49]跨本地端與遠地端資料庫的合併查詢
--使用遠端( 192.168.1.55 )MS SQL Server中『CH08範例資料庫』內的『員工』資料表，以及使用本地端『CH08範例資料庫』內的訂單資料表，查詢出有承接訂單的員工相關資料。
--[輸出] ( 員工編號, 姓名, 訂單編號, 訂貨日期 )
SELECT E.員工編號, 姓名, 訂單編號, 訂貨日期
FROM [192.168.1.55].CH08範例資料庫.dbo.員工 AS E, 訂單 AS O
WHERE E.員工編號 = O.員工編號
ORDER BY E.員工編號

--[範例8-50]
--計算2006/01/01(含)之後每張訂單的獲利大於300的相關資料。
--[輸出] ( 負責人姓名, 訂單編號, 客戶公司名稱, 毛利 )
--毛利計算 = SUM ( ( 實際單價 – 平均成本 ) * 數量 )

--(1) – (2) 交叉合併
SELECT *
FROM 員工, 訂單, 訂單明細, 產品資料, 客戶

--(3) 內部合併與條件篩選
--1
SELECT *
FROM 員工, 訂單, 訂單明細, 產品資料, 客戶
WHERE 員工.員工編號 = 訂單.員工編號 AND
        訂單.訂單編號 = 訂單明細.訂單編號 AND
        訂單明細.產品編號 = 產品資料.產品編號 AND
        訂單.客戶編號 = 客戶.客戶編號
--2.	
SELECT *
FROM 員工, 訂單, 訂單明細, 產品資料, 客戶
WHERE 員工.員工編號 = 訂單.員工編號 AND
        訂單.訂單編號 = 訂單明細.訂單編號 AND
        訂單明細.產品編號 = 產品資料.產品編號 AND
        訂單.客戶編號 = 客戶.客戶編號  AND
		訂貨日期 >= '2006/01/01'

--(4) 分群組與彙總函數計算
SELECT 姓名 AS 負責人姓名, 訂單.訂單編號, 公司名稱 AS 客戶公司名稱,
        sum( ( 實際單價 - 平均成本 )*數量 ) AS 毛利
FROM 員工, 訂單, 訂單明細, 產品資料, 客戶
WHERE 員工.員工編號 = 訂單.員工編號 AND
        訂單.訂單編號 = 訂單明細.訂單編號 AND
        訂單明細.產品編號 = 產品資料.產品編號 AND
        訂單.客戶編號 = 客戶.客戶編號 AND
        訂貨日期 >= '2006/01/01'
GROUP BY 姓名, 訂單.訂單編號, 公司名稱

--(5) 彙整函數後的條件篩選
SELECT 姓名 AS 負責人姓名, 訂單.訂單編號, 公司名稱 AS 客戶公司名稱,
       sum( ( 實際單價 - 平均成本 )*數量 ) AS 毛利
FROM 員工, 訂單, 訂單明細, 產品資料, 客戶
WHERE 員工.員工編號 = 訂單.員工編號 AND
        訂單.訂單編號 = 訂單明細.訂單編號 AND
        訂單明細.產品編號 = 產品資料.產品編號 AND
        訂單.客戶編號 = 客戶.客戶編號 AND
        訂貨日期 >= '2006/01/01'
GROUP BY 姓名, 訂單.訂單編號, 公司名稱
HAVING sum( ( 實際單價 - 平均成本 )*數量 ) > 300

--(6) 排序
SELECT 姓名 AS 負責人姓名, 訂單.訂單編號, 公司名稱 AS 客戶公司名稱,
       sum( ( 實際單價 - 平均成本 )*數量 ) AS 毛利
FROM 員工, 訂單, 訂單明細, 產品資料, 客戶
WHERE 員工.員工編號 = 訂單.員工編號 AND
        訂單.訂單編號 = 訂單明細.訂單編號 AND
        訂單明細.產品編號 = 產品資料.產品編號 AND
        訂單.客戶編號 = 客戶.客戶編號 AND
        訂貨日期 >= '2006/01/01'
GROUP BY 姓名, 訂單.訂單編號, 公司名稱
HAVING sum( ( 實際單價 - 平均成本 )*數量 ) > 300
ORDER BY 姓名 ASC, 訂單.訂單編號 ASC