Transact-SQL销售资料库范例
一、E-R Diagram
1
客戶
m
訂單
1
1
m
產品
1
訂單明細
二、資料表定義
請用create table敘述，寫出建立以下資料表的語法為何？並設定資料表間的關連。
1、客戶資料表(Cust)定義
屬性編號名稱縣市地址電話
英文CustNo
領域char(5)
預設值不可空
唯独
主鍵
外來鍵
參照
檢核
C_Name varchar(24) City Addr
varchar(10) varcgar(45) varchar(20)
2、產品表格(Prod)定義
屬性編號名稱
英文ProdNo
領域char(10)
預設值不可空
唯独
主鍵
外來鍵
參照
檢核
P_Name varchar(35)
售價庫存
Price Amt
Smallint Smallint
value>0 value>0
3、訂單表格(OrderM)定義
屬性
英文
領域
預設值
不可空
唯独主鍵
外來鍵
編號日期客戶
OrderNo ODate CustNo
char(5)
DateTime Getdate() char(5)
Cust(CustNo)
參照
檢核
4、訂單明細表格(OrderD)定義
屬性訂單產品數量
英文
領域
預設值不可空唯独主鍵
外來鍵
參照OrderM(OrderNo) Prod(ProdNo)
value>0 檢核
OrderNo Char(5) ProdNo char(10) Amt
smallint
三、某一時間點的資料庫內容
請用Insert敘述，寫入以下資料至資料表。1、客戶資料表(Cust)
編號CustNo 名稱C_Name
A0001 A0002 A0003 A0004 A0005
洽興金屬工業股份有限公司新益機械工股份有限公司天源義記機械股份有限公司家鄉事業股份有限公司四維企業(股)公司
縣市City 地址Addr
台中市台北市台北市台北市台南縣
西屯區工業區北投區承德路松山區敦化北路
12路5號
04-7081881
7段371-1號02-7752450 112號5樓02-9015105
02-1879991
851號06-8792123
復興北路57號5樓歸仁鄉南興村中山路
2、產品資料表(Prod)
編號ProdNo
MB486V3R16 MB586E7R32 SCSIPB SVGAVIM
名稱P_Name
486主機板VL slot*3 16MB RAM 586主機板EISA slot *7 32MB RAM SCSI card PCI BUS
Super VGA 1280*1024 VL BUS 1MB
售價Price
13200 15800 2000 3700
庫存Amt
77 100 115 32
3、訂單資料表(OrderM)
編號OrderNo
W0001 W0002 W0003 W0004 W0005
日期ODate
1998/3/4 1999/1/1 1999/2/1 1999/2/3 1999/4/23
客戶CustNo
A0001 A0001 A0002 A0003 A0004
4、訂單明細資料表(OrderD)
訂單OrderNo
W0001 W0001 W0001 W0002 W0003 W0004 W0004 W0005
產品ProdNo
MB486V3R16 MB586E7R32 SCSIPB MB486V3R16 SVGAVIM MB586E7R32 SCSIPB MB586E7R32
數量Amt
30 45 30 14 6 120 44 23
四、修改/刪除記錄資料
將客戶(Cust)資料表中，客戶編號為'A0005'之縣市改為'台南市'。將產品(Prod)資料表中，產品編號為'SCSIPB'之售價改為2300元。將訂單(OrderM) 資料表中，訂單編號為'W0005'之日期改為'2002/7/15'。將客戶(Cust)資料表中，刪除電話為'06-2792123'之記錄。
五、寫出下列作業的查詢語法查詢客戶資料表的所有資料。查詢產品資料表的所有資料。查詢訂單資料表的所有資料。查詢訂單明細資料表的所有資料。查詢客戶資料表所有的客戶名稱、電話。查詢產品資料表所有的產品名稱、售價、庫存。查詢訂單資料表所有的日期、客戶。查詢客戶資料表的客戶住在哪些縣市？查詢有哪些客戶訂購產品？查詢客戶資料表的客戶是住在
'台北市' 的所有資料。
10000元的所有資料。
查詢產品資料表的產品售價是大於
查詢產品資料表的產品售價是大於資料。
查詢訂單資料表的日期是查詢訂單資料表的客戶是
10000元且庫存量小於100的所有
1999年的所有資料。'A0001' 的所有資料。
'W0004' 的所有資料。
30的所有資料。
查詢訂單明細資料表的訂單號碼是
查詢訂單明細資料表的訂單數量是不大於查詢產品資料表的產品售價是大於排序的所有資料。
查詢訂單資料表的日期是資料。
查詢客戶'A0001'訂了哪些產品及數量？查詢客戶'A0002'訂了哪些產品及數量？查詢訂單'W0001'訂了哪些產品及數量？
10000元且依售價由高至低的方式
1999年且依日期由低至高的方式排序的所有
查詢訂單'W0001'所訂的產品之名稱、售價及庫存？查詢尚未訂購產品的客戶名稱、地址？查詢已訂購產品的客戶名稱、地址？計算每一項產品庫存所佔的百分比？計算每一項產品已被訂購的數量？
查詢客戶名稱有”機械”二個字的客戶編號、名稱、所在的縣市？查詢產品名稱有”主機板”二個字的產品編號、產品名稱、及售價？查詢客戶電話是”02”開頭的客戶名稱、電話？
查詢產品售價是在3000和15000之間的產品名稱、售價？查詢訂單資料表的日期是
1999年2月的所有資料。
Transact-SQL销售资料库范例
