數(shù)據(jù)庫實驗上機答案解析整編中國石油大學(xué)龔安

1、實驗四 SQL 練習(xí) 2一、實驗?zāi)康?掌握索引的建立、刪除及使用；2掌握單表查詢、連接查詢、嵌套查詢和集合查詢；3掌握插入數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)語句的非常用形式。二、實驗學(xué)時2 學(xué)時 三、實驗內(nèi)容1 利用 Query Analyzer 完成以下操作： 在預(yù)算日期、結(jié)算日期和入賬日期上分別建立索引，并在查 詢操作中體會索引的作用。 在完成第 2 題的查詢操作后，刪除預(yù)算日期、結(jié)算日期和入 賬日期上的索引。2 利用 Query Analyzer 完成以下操作： 采油一礦二隊2016-5-1到 2016-5-28有哪些項目完成了預(yù) 采油一礦二隊 2016-5-1到 2016-5-28有哪些項目完成

2、了入算，列出相應(yīng)明細。 采油一礦二隊2016-5-1到 2016-5-28有哪些項目完成了結(jié)算，列出相應(yīng)明細。 采油一礦二隊2016-5-1到 2016-5-28有哪些項目完成了結(jié)算，列出相應(yīng)的材料費消耗明細。賬，列出相應(yīng)明細。列出采油一礦二隊2016-5-12016-5-28總的預(yù)算金額。列出采油一礦二隊2016-5-12016-5-28總的結(jié)算金額。(11)(12)列出采油一礦二隊2016-5-12016-5-28總的入賬金額。列出采油一礦 2016-5-1 到 2016-5-28 總的入賬金額。有哪些人員參與了入賬操作。列出 2016-5-1 到 2016-5-28 進行了結(jié)算但未入賬的

3、項目。列出采油一礦二隊的所有項目，按入賬金額從高到低排列。列出有哪些施工單位實施了項目，并計算各單位所有項目結(jié)算金額總和。（13）找出消耗了材料三且消耗超過了2000元的項目，列出相應(yīng)消耗明細（利用子查詢 ）。作業(yè)公司二隊參與了哪些項目。(15)作業(yè)公司一隊和二隊參與了哪些項目（利用 union ）。(16)采油一礦的油井是哪些作業(yè)隊參與施工的。3利用 Query Analyzer 完成以下操作：建立數(shù)據(jù)表（包含3個屬性列：施工單位、年月、結(jié)算金額）保存各個施工單位每月的結(jié)算金額總和。 用子查詢將各個施工單位每月的結(jié)算金額總和插入到所建立的數(shù)據(jù)表中。 用帶子查詢的修改語句將采油一礦油井作業(yè)項目

4、的結(jié)算人改為“李兵”。 用帶子查詢的刪除語句刪除采油一礦油井作業(yè)項目。 撤消上述兩個操作。四、實驗報告提交實驗內(nèi)容中用 SQL 語句完成的題目的 SQL 語句文檔及相應(yīng) 的執(zhí)行結(jié)果。實驗五 SQL 練習(xí) 3一、實驗?zāi)康?掌握基本表的刪除與修改；2掌握實體完整性、參照完整性和用戶定義的完整性的定義、檢查和違約處理；3掌握視圖的定義、查詢和更新，了解視圖的作用。二、實驗學(xué)時2 學(xué)時 三、實驗內(nèi)容1 利用 Query Analyzer 完成以下操作： 向在實驗四中所定義的數(shù)據(jù)表增加“備注”列，其數(shù)據(jù)類型 為字符型，并查看新增列的值。 對上述數(shù)據(jù)表增加主碼約束條件，并觀察在數(shù)據(jù)表中存在數(shù) 據(jù)的情況下主

5、碼約束是否創(chuàng)建成功， 然后再次執(zhí)行實驗四中實驗內(nèi)容3.2 的操作，并觀察記錄執(zhí)行結(jié)果。 刪除上述數(shù)據(jù)表中的數(shù)據(jù)，然后再刪除該數(shù)據(jù)表，對這兩個 操作進行比較。2 利用 Query Analyzer 完成以下任務(wù)： 對實驗三中所定義的 6 個數(shù)據(jù)表增加主碼約束條件，并觀察 在數(shù)據(jù)表中存在數(shù)據(jù)的情況下主碼約束是否創(chuàng)建成功， 然后執(zhí)行以下2 個操作，觀察并記錄實體完整性的檢查和違約處理。intoinsertvalues('zy2016001','wm004',100,10)intoinsertvalues('zy2016002',NULL,200,10)

6、注：“材料費表”根據(jù)自己所命名的表名進行相應(yīng)的替換。 對實驗三中所定義的 6 個數(shù)據(jù)表增加相應(yīng)的參照完整性約束， 并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下參照完整性約束是否創(chuàng)建成功， 然后執(zhí)行以下操作，觀察并記錄參照完整性的檢查和違約處理。 將（ y007 油井 112203002 ）插入到油水井表。insertinto材料values('zy2016007','wm006',100,10) 將作業(yè)項目編號 zy2016001 的施工單位修改為 “作業(yè)公司作業(yè)五隊”。 將單位代碼表中的（ 112202002采油二礦二隊）刪除，查看油水井表和作業(yè)項目表中的數(shù)據(jù)有何變化。

7、將物碼表中的（wm004材料四 袋）修改為（wm04材料四 袋）。 撤銷上述成功的更新操作。注：“材料費表”根據(jù)自己所命名的表名進行相應(yīng)的替換。 對實驗三中所定義的 6 個數(shù)據(jù)表按以下要求增加相應(yīng)的完整 性約束條件，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下完整性約束是否創(chuàng) 建成功。 單位代碼表的單位名稱不能取空值、且取值唯一。 油水井表的井別只允許取“油井”或“水井” ，單位代碼不能 取空值。 物碼表的名稱規(guī)格不能取空值、且取值唯一，計量單位不能 取空值。 材料費表的消耗數(shù)量不能取空值，單價不能取空值。 對作業(yè)項目表根據(jù)實際應(yīng)用的要求定義適當?shù)挠脩舳x的完3整性約束條件。利用 Query Analyz

8、er 完成以下操作：定義一個視圖，用于保存作業(yè)項目表和材料費表的全部列。查詢上面定義的視圖，可任意組合查詢條件，構(gòu)造出 2 個查詢。定義一個反映作業(yè)項目預(yù)算狀態(tài)的視圖，并向該視圖插入('zy2016008','112202002','y005',10000,'張三', '2016-07-02') ，查 看作業(yè)項目表的數(shù)據(jù)有何變化。 撤銷上述成功的更新操作。四、實驗報告提交實驗內(nèi)容中用 SQL 語句完成的題目的 SQL 語句文檔及相應(yīng) 的執(zhí)行結(jié)果。實驗六 其它數(shù)據(jù)庫對象的管理一、實驗?zāi)康?掌握事務(wù)的概念、性質(zhì)、定義及

9、使用；2掌握游標的概念、組成、創(chuàng)建及使用；3掌握存儲過程的概念、類型、特點、創(chuàng)建、執(zhí)行及管理。4掌握觸發(fā)器的概念、創(chuàng)建、管理及使用。二、實驗學(xué)時2 學(xué)時 三、實驗內(nèi)容1 利用 Query Analyzer 進行如下事務(wù)處理練習(xí)（把下列五條 語句作為一個事務(wù)處理， 只有五條語句全部成功執(zhí)行才做提交， 并給 出成功的提示信息；否則就做回退處理，并給出具體的錯誤提示信息） ：intoinsert values('zy2016006','112202002','y005',10000,' 張三', '07-01-2016'

10、 ,'07-04-2016','07-25-2016',' 作業(yè)公司作業(yè)一隊 ',' 堵漏 ',7000,2500,1000,1400,11900,'李四','07-26-2016',11900,' 王五 ','07-28-2016')insert into材料費表values('zy2016006','wm001',200,10)insert into材料費表values('zy2016006','wm002&

11、#39;,200,10)insert into材料費表values('zy2016006','wm003',200,10)insert into材料費表values('zy2016006','wm004',100,10)注：“作業(yè)項目表”、“材料費表”根據(jù)自己所命名的表名進行相 應(yīng)的替換。2 利用 Query Analyzer 進行如下游標練習(xí)：定義一個游標， 用于存放作業(yè)項目表的全部行數(shù)據(jù)， 并打印以下 表頭和各行數(shù)據(jù)。表頭：單據(jù)號 預(yù)算單位 井號 預(yù)算金額 預(yù)算人 預(yù)算日期 開工 日期 完工日期 施工單位 施工內(nèi)容 材料費 人

12、工費 設(shè)備費 其它費 用 結(jié)算金額 結(jié)算人 結(jié)算日期 入賬金額 入賬人 入賬日期執(zhí)行以上所定義的游標，查看是否能正確輸出結(jié)果。3 利用 Query Analyzer 定義一個存儲過程，要求完成以下功厶匕能：生成某單位 （ 單位可以是采油廠或采油礦或采油隊 ）某段時間內(nèi)的成本運行情況輸入?yún)?shù)：單位代碼起始日期 結(jié)束日期）。輸出格式* 單位 * 時間 -*時間成本運行情況預(yù)算金額結(jié)算金額 入賬金額未結(jié)算金額 未入賬金額* * * * * * *其中：未結(jié)算金額 =預(yù)算金額 -結(jié)算金額未入賬金額 =結(jié)算金額 -入賬金額分三種情況（單位分別為：采油廠、采油礦、采油隊）執(zhí)行以上 定義的存儲過程，查看執(zhí)行

13、輸出結(jié)果。4 利用 Query Analyzer 針對作業(yè)項目表定義三個觸發(fā)器，分別完成以下功能： 對作業(yè)項目表插入一行數(shù)據(jù)時，自動計算并插入結(jié)算金額字 段（結(jié)算金額 =材料費+人工費 +設(shè)備費 +其它費用）。當修改作業(yè)表的某行數(shù)據(jù)時自動修改結(jié)算金額字段。當刪除作業(yè)表中一行數(shù)據(jù)時，自動刪除材料費表中相應(yīng)明細數(shù)據(jù)。對上述 3 個觸發(fā)器用適當?shù)母抡Z句進行驗證，并查看結(jié)果是否達到預(yù)期結(jié)果。四、實驗報告提交實驗內(nèi)容中用 SQL 語句完成的題目的 SQL 語句文檔及相應(yīng) 的執(zhí)行結(jié)果。實驗七安全機制2 .利用Query Analyzer完成以下操作: 建立采油一礦的作業(yè)項目的視圖，把該視圖的查詢權(quán)限授予

14、 給采油一礦的用戶user11，以user11的身份查詢該視圖，觀察執(zhí)行 情況；再以其他用戶的身份查詢該視圖，觀察執(zhí)行情況。 創(chuàng)建一個用戶user12，以user12的身份執(zhí)行實驗六中所定 義的存儲過程，觀察記錄是否成功執(zhí)行；然后把該存儲過程的執(zhí)行權(quán) 限授予給user12，再次以user12的身份執(zhí)行該存儲過程，觀察記錄 是否成功執(zhí)行。 定義觸發(fā)器，實現(xiàn)只能在工作時間內(nèi)更新“作業(yè)項目表”的數(shù)據(jù), 然后通過選擇不同的時間進行適當?shù)母虏僮鱽眚炞C。-實驗3CREATE TABLE單位代碼表 (單位代碼 CHAR (20),單位名稱CHAR (20);CREATE TABLE油水井表 (井號 CHA

15、R (20),井別 CHAR (20),單位代碼CHAR (20);CREATE TABLE施工單位表 (施工單位名稱CHAR (20);CREATE TABLE 物碼表(物碼 CHAR (20),名稱規(guī)格CHAR (20),計量單位 CHAR (20);create table材料費表(單據(jù)號 CHAR (20),物碼 CHAR (20),消耗數(shù)量INT, 單價MONEY , );CREATE TABLE 作業(yè)項目表(單據(jù)號 CHAR (20),預(yù)算單位CHAR (20),井號 CHAR (20),預(yù)算金額MONEY ,預(yù)算人 CHAR (20),預(yù)算日期DATETIME ,開工日期DATE

16、TIME ,完工日期DATETIME ,施工單位CHAR (20),施工內(nèi)容CHAR (20),insert into油水井表材料費money ,人工費MONEY ,設(shè)備費MONEY ,其他費用money ,結(jié)算金額MONEY ,結(jié)算人CHAR (20),結(jié)算日期DATETIME ,入賬金額MONEY ,入賬人 CHAR (10),入賬日期DATETIME ) insert in to 單位代碼表values ('1122','采油廠');values （'112201','采油一礦'）;insert in to 單位代碼表val

17、ues （'112202','采油二礦'）;insert in to 單位代碼表values （'112201001','采油一礦一隊'）;insert in to 單位代碼表values （'112201002','采油一礦二隊'）;insert in to 單位代碼表values （'112201003','采油一礦三隊'）;insert in to 單位代碼表values （'112202001','采油二礦一隊'）;insert

18、 into油水井表 values （'y001','油井','112201001'）;values('y002',油井'，112201001'）；in sertinto油水井表values('y003',油井'，112201002'）；in sertinto油水井表values('s001','水井'，112201002'）；in sertinto油水井表values('y004','油井','11220

19、1003'）；in sertinto油水井表values('S002','水井，112202001'）；in sertinto油水井表values('S003','水井,'112202001'）；in sertinto油水井表values('y005','油井','112202002'）；in sertinto施工單位表values（'作業(yè)公司作業(yè)一隊'）;insert into 施工單位表values （'作業(yè)公司作業(yè)二隊'）;ins

20、ert into 施工單位表values （'作業(yè)公司作業(yè)三隊'）;insert into 物碼表values ('wmOOl','材料一','噸');insert into 物碼表values Cwm002','材料二','米');insert into 物碼表values Cwm003','材料三','桶');insert into 物碼表values Cwm004','材料四','袋');insert in

21、to材料費表（單據(jù)號，物碼，消耗數(shù)量，單價）values ('zy2016001' ,'wm001' ,200, Y 10);insert into材料費表（單據(jù)號，物碼，消耗數(shù)量，單價） values ('zy2016001'，wm002' ,200, Y 10);insert into材料費表（單據(jù)號，物碼,消耗數(shù)量單價） values ('zy2016001' ,'wm003' ,200, Y 10);insert into材料費表（單據(jù)號，物碼，消耗數(shù)量，單價） values ('zy201

22、6003' ,'wm001' ,200, Y 10);insert into材料費表（單據(jù)號，物碼，消耗數(shù)量，單價） values ('zy2016003' ,'wm002' ,200, Y 10);insert into材料費表（單據(jù)號，物碼，消耗數(shù)量，單價） values ('zy2016003' ,'wm003' ,250, Y 10);insert into材料費表（單據(jù)號，物碼，消耗數(shù)量，單價） values ('zy2016004' ,'wm001' ,200,

23、Y 10);insert into材料費表（單據(jù)號，物碼，消耗數(shù)量，單價）values ('zy2016004'，wm004' ,200, Y 10);in sertinto材料費表（單據(jù)號，物碼,消耗數(shù)量單價）insert into作業(yè)項目表values ('zy2016003' ,'112201002' ,'s001' ,Y 10500 ,'張三','2016/5/1',valuesin sertinto材料費表（單據(jù)號，物碼，消耗數(shù)量，單價）('zy2016005' ,

24、'wm001' ,200, Y 10);valuesin sertinto材料費表（單據(jù)號，物碼，消耗數(shù)量，單價）('zy2016005' ,'wm002' ,200, Y 10);values('zy2016005' ,'wm004' ,300, Y 10);insert into作業(yè)項目表 values ('zy2016001' ,'112201001' ,'y001' ,Y 10000 ,'張三','2016/5/1', '

25、;2016/5/4' ,'2016/5/23','作業(yè)公司作業(yè)一隊','堵漏',Y7000 ,Y 2500 ,Y 1000 ,Y 1400 ,Y 10900 ,'王五 ','2016/5/26',Y 10900 ,'王五','2016/5/28');'2016/5/6' ，2016/5/23','作業(yè)公司作業(yè)二隊','調(diào)剖',Y 6500 ,Y 2000 ,Y 500 ,Y 1400 ,Y 10400 ,'李四

26、','2016/5/26',Y 10400 ,'王五','2016/5/28' ）； insert into作業(yè)項目表 values ('zy2016004' ,'112202001' ,'s002' ,Y 12000 ,'張三','2016/5/1', '2016/5/4' ,'2016/5/24','作業(yè)公司作業(yè)三隊','解堵',Y6000 ,Y 2000 ,Y 1000 ,Y 1600 ,Y

27、 10600 ,'李四','2016/5/26', Y 10600 ,'趙六','2016/5/28');insert into作業(yè)項目表 values ('zy2016005' ,'112202002' ,'y005' ,Y 12000 ,'張三','2016/5/1', '2016/5/4' ,'2016/5/28','作業(yè)公司作業(yè)三隊','防砂',Y7000 ,Y 1000 ,Y 2

28、000 ,Y 1300 ,Y 11300 ,'李四 ','2016/6/1',NULL,NULL,NULL);beg in tran up date 作業(yè)項目表set人工費=人工費+200where 單據(jù)號='zy2016005'up date 作業(yè)項目表set結(jié)算金額=結(jié)算金額+200 where 單據(jù)號='zy2016005'deletefrom作業(yè)項目表 where 入賬金額is NULL;rollback tran-實驗四-1index預(yù)算日期索引on作業(yè)項目表（預(yù)算日期）;index結(jié)算日期索引on作業(yè)項目表（結(jié)算日期）

29、;index入賬日期索引on作業(yè)項目表（入賬日期）;createcreatecreate-(1) selectfrom作業(yè)項目表where預(yù)算日期 between'2016/5/1' and '2016/5/28' and 預(yù)算單位（select單位代碼-ano ther soluti onselect *from作業(yè)項目表，單位代碼表 where單位代碼=預(yù)算單位and單位名稱='采油一礦二隊and預(yù)算日期between'2016/5/1' and '2016/5/28'" select *from作業(yè)項目表w

30、here結(jié)算日期 between '2016/5/1' and '2016/5/28' and 預(yù)算單位(select單位代碼from單位代碼表 where單位名稱='采油一礦二隊');-ano ther soluti on-select *-from作業(yè)項目表，單位代碼表-where 單位名稱-采油一礦二隊'and預(yù)算單位=單位代碼and結(jié)算日期='2016-5-1'and結(jié) 算日期 <='2016-5-28'- select *from材料費表where 單據(jù)號=(select單據(jù)號from作業(yè)項

31、目表where 預(yù)算日期 between '2016/5/1'and'2016/5/28'and預(yù)算單位(select單位代碼from單位代碼表where單位名稱='采油一礦二隊');-select *from作業(yè)項目表where入賬日期 between '2016/5/1'and'2016/5/28'and預(yù)算單位(select單位代碼from單位代碼表where單位名稱='采油一礦二隊');-(5)select ,sum (預(yù)算金額)預(yù)算金額總和from作業(yè)項目表where 預(yù)算日期 betwe

32、en '2016/5/1'and'2016/5/28'and預(yù)算單位(select單位代碼from單位代碼表where單位名稱='采油一礦二隊');-(6)select ,sum (結(jié)算金額)結(jié)算金額總和from作業(yè)項目表where單位名稱like'采油一礦%'）;from 施工單位表left join作業(yè)項目表on （施工單位表.施工單位名稱=作業(yè)項目表.施工單位）where 結(jié)算日期 between '2016/5/1' and'2016/5/28'and預(yù)算單位（select單位代碼from單

33、位代碼表where單位名稱='采油一礦二隊'）;"select sum （入賬金額）入賬金額總和from作業(yè)項目表where 入賬日期 between '2016/5/1' and'2016/5/28'and預(yù)算單位（select單位代碼from單位代碼表where單位名稱='采油一礦二隊'）;-(8)select sum （入賬金額）采油一礦入賬金額總和from作業(yè)項目表，單位代碼表where 入賬日期 between '2016/5/1' and'2016/5/28'and預(yù)算單位=單

34、位代碼and單位名稱like '采油一礦%'-ano ther soluti on select sum （入賬金額）入賬金額總和-(9) select distinet 入賬人from作業(yè)項目表 where 入賬人 is not null;-(10) select單據(jù)號from作業(yè)項目表 where 結(jié)算日期 between '2016/5/1' and '2016/5/28' and 結(jié)算金額 is not null and 入賬金額is null;-(11) select單據(jù)號from作業(yè)項目表 where 預(yù)算單位 = （select單位

35、代碼from單位代碼表 where單位名稱='采油一礦二隊'） order by 入賬金額 desc;-(12)select施工單位表.施工單位名稱,count （結(jié)算金額）項目個數(shù),sum （結(jié)算金額）結(jié)算金額總和group by施工單位表.施工單位名稱;(13) select *from材料費表 where單據(jù)號=(select單據(jù)號from材料費表3-(1)create table結(jié)算金額表(where 物碼=(select物碼from物碼表where名稱規(guī)格='材料三')and消耗數(shù)量*單價 2000 );(14)select distinet 單據(jù)號f

36、rom作業(yè)項目表where施工單位='作業(yè)公司作業(yè)二隊'(15)select distinct單據(jù)號from作業(yè)項目表where施工單位='作業(yè)公司作業(yè)一隊'unionwhere施工單位='作業(yè)公司作業(yè)二隊'(16)select distinet施工單位from作業(yè)項目表，油水井表，單位代碼表where預(yù)算單位=單位代碼表.單位代碼and單位代碼表.單位代碼=油水井表.單位代碼and單位名稱like '采油一礦%'-ano ther soluti onselect distinet施工單位from作業(yè)項目表 where 井號 in

37、( select 井號from油水井表where 單位代碼 in ( select 單位代碼from單位代碼表 where單位名稱like '采油一礦%');droP index預(yù)算日期索引on作業(yè)項目表；droP index結(jié)算日期索引on作業(yè)項目表；droP index入賬日期索引on作業(yè)項目表；where單位名稱like '采油一礦%'施工單位char （20）,年月 varchar (7),結(jié)算金額money）； -(2) in sertinto結(jié)算金額表 select 施工單位，con vert (varchar (7),結(jié)算日期,111 ),sum

38、(結(jié)算金額)from作業(yè)項目表 group by 施工單位,convert (varchar (7),結(jié)算日期,111 );-ano ther soluti on-i nsert-i nto結(jié)算金額表-select施工單位，丫EAR（結(jié)算日期）*100+MONTH（ 結(jié)算日期）,sum（結(jié)算金額）-from作業(yè)項目表-group by施工單位,YEAR（結(jié)算日期）*100+MONTH（ 結(jié)算日期）;-(3) beg in tran up date 作業(yè)項目表set結(jié)算人='李兵' where 預(yù)算單位in （select單位代碼）； - deletefrom作業(yè)項目表 wher

39、e 預(yù)算單位in （select單位代碼from單位代碼表where單位名稱like '采油一礦%'）； rollback實驗五 alter table結(jié)算金額表 add 備注 char (20);select *from結(jié)算金額表" alter table結(jié)算金額表 alter column 施工單位 char (20) not null;alter table結(jié)算金額表alter column 年月 varchar not null;alter table結(jié)算金額表 add primary key （施工單位，年月）;TRUNCATE TABLE結(jié)算金額表 in

40、sertinto結(jié)算金額表（施工單位，年月，結(jié)算金額） select 施工單位，con vert (varchar (7),結(jié)算日期,111 ),sum (結(jié)算金額)from作業(yè)項目表 group by 施工單位,convert (varchar (7),結(jié)算日期,111 );delete from結(jié)算金額表;delete結(jié)算金額表;-(1)alter table材料費表alter table材料費表alter column單據(jù)號 char (20) not null;alter column物碼 char (20) not null;alter table材料費表 add primary k

41、ey (單據(jù)號，物碼);altertable施工單位表altertable 作業(yè)項目表altercolumn 單據(jù)號 char (20) not null;altertable 作業(yè)項目表addprimary key (單據(jù)號);altertable單位代碼表altercolumn 單位代碼 char (20) not null;altertable單位代碼表addprimary key (單位代碼);altertable施工單位表altercolumn 施工單位名稱 char (20) not null;addprimary key （施工單位名稱）;altertable物碼表alteral

42、tertable物碼表addprimary key (物碼);altertable油水井表column 物碼 char (20) not null;alteraltertable油水井表addprimary key (井號);begintra ncolumn 井號 char (20) not NUll;in sertinto 材料費表 values ('zy2016001' ,'wm004' ,100,10);in sertinto材料費表values ('zy2016002' ,NULL, 200 ,10);rollback-(2)alter

43、table材料費表add constraint a1 foreign key (單據(jù)號)references 作業(yè)項目表(單據(jù)號);alter table材料費表add constraint a2 foreignkey （物碼）references 物碼表（物碼）;alter table 作業(yè)項目表add constraint b1 foreignkey （預(yù)算單位）references 單位代碼表（單位代碼）;alter table 作業(yè)項目表add constraint b2 foreignkey （井號）references 油水井表（井號）;alter table 作業(yè)項目表add c

44、onstraint b3 foreignkey （施工單位）references 施工單位表（施工單位名稱）;insert into油水井表values ('y007','油井','112203002');in sertinto材料費表values ('zy2016007' ,'wm006' ,100,10);beg in tranup date 作業(yè)項目表set施工單位='作業(yè)公司作業(yè)五隊where單據(jù)號 ='zy2016001'deletefrom單位代碼表where單位代碼 ='

45、;112202002'up date物碼表set 物碼='wm04'where名稱規(guī)格='材料四'rollback tran-（3） alter table單位代碼表add constraint aa1 check （單位名稱is not null ）;alter table單位代碼表 add constraint uni unique （單位代碼）;alter table油水井表 add constraint aa2 check （井別 in （'油井','水井'）;altertable油水井表alteraltertab

46、le物碼表column 單位代碼 char (20) not null;alteraltertable物碼表column 名稱規(guī)格 char (20) not null;add constraint uni2 unique (名稱規(guī)格);altertable物碼表alteraltertable材料費表column 計量單位 char (20) not null;alteraltertable材料費表altercolumn 單價 int not null ;altertable 作業(yè)項目表column消耗數(shù)量 int not null ;add constraint asdaf check （材

47、料費+人工費+設(shè)備費+其他費用 =結(jié)算金額）;- create view 視圖一asselect作業(yè)項目表.*,物碼消耗數(shù)量,單價from作業(yè)項目表，材料費表where作業(yè)項目表.單據(jù)號 =材料費表.單據(jù)號;"select結(jié)算金額，消耗數(shù)量 from視圖一 where 物碼='wm003'select預(yù)算單位，施工內(nèi)容 from視圖一 where 單據(jù)號 ='zy2016001' and 物碼 ='wm001'-(3) create view 預(yù)算狀態(tài)asselect單據(jù)號，預(yù)算單位，井號，預(yù)算金額，預(yù)算人，預(yù)算日期from作業(yè)項目表b

48、egintranin sertinto預(yù)算狀態(tài) values ('zy2016008'，112202002'，y005' ,Y 10000,'張三'，'2016-07-02');rollback tran-實驗六 beg in tran go insert into 作業(yè)項目表 values ('zy2016006' ,'112202002' ,'y005',10000 ,'張三','07-01-2016','07-04-2016',&

49、#39;07-25-2016', '作業(yè)公司作業(yè)一隊','堵漏',7000 ,2500 ,1000 ,1400 ,11900 ,'李四','07-26-2016',11900 ,'王五','07-28-2016')if error<> 0in sertinto材料費表values ('zy2016006','wm001',200 ,10)in sertinto材料費表values ('zy2016006','wm002

50、9;,200 ,10)in sertinto材料費表values ('zy2016006','wm003',200 ,10)in sertinto材料費表values ('zy2016006','wm004',100 ,10)begin print '語句執(zhí)行失敗'rollbackendelsebeginprint '語句執(zhí)行成功'commitendbeg in trandeclare yb cursor forselect *from作業(yè)項目表open ybdeclare 單據(jù)號 varchar

51、(20)declare預(yù)算單位 varchar (20)declare 井號 varchar (20)declare預(yù)算金額 varchar (20)declare 預(yù)算人 varchar (20)declare預(yù)算日期varchar(20)declare開工日期varchar(20)declare完工日期varchar(20)declare施工單位varchar(20)declare施工內(nèi)容 varchar (20)declare 材料費 varchar (20)declare 人工費 varchar (20)declare 設(shè)備費 varchar (20)declare其它費用varcha

52、r (20)declare結(jié)算金額 varchar (20)declare 結(jié)算人 varchar (20)declare結(jié)算日期 varchar (20)declare入賬金額varchar (20)declare 入賬人 varchar (20)declare入賬日期 varchar (20)print '單據(jù)號'+'預(yù)算單位'+'井號'+'預(yù)算金額'+'預(yù)算人'+'預(yù)算日期'+'開工日期'+'完工日期'+'施工單位'+'施工內(nèi)容'

53、+'材料費'+ '人工費'+ '設(shè)備費'+ '其它費用'+ '結(jié)算金額'+ '結(jié)算人'+ '結(jié)算日期'+ '入賬金額'+ '入賬人'+'入賬日期' fetch next from yb into 單據(jù)號,預(yù)算單位， 井號，預(yù)算金額, 預(yù)算人，預(yù)算日期,開工日期，完工日期，施工單位，施工內(nèi)容，材料費， 人工費， 設(shè)備費，其它費用,結(jié)算金額, 結(jié)算人，結(jié)算日期,入賬金額, 入賬人，入賬日期 while (fetch_status = 0)

54、begin pri nt 單據(jù)號+預(yù)算單位 井號+預(yù)算金額預(yù)算人 +預(yù)算日期開工日期完工日期施工單位施工內(nèi)容材料費 +人工費 +設(shè)備費 +其它費用+結(jié)算金額結(jié)算人 +結(jié)算日期入賬金額入賬人 +入賬日期 intofetch next fromyb單據(jù)號,預(yù)算單位， 井號，預(yù)算金額, 預(yù)算人，預(yù)算日期,開工日期，完工日期，施工單位，施工內(nèi)容， 材料費， 人工費， 設(shè)備費，其它費用,結(jié)算金額, 結(jié)算人，結(jié)算日期,入賬金額, 入賬人，入賬日期； end if object_id ('_someDepCost' ，p') is not null drop procedure _s

55、omeDepCost ;go create procedure _someDepCost單位代碼 varchar (40),起始日期datetime ,結(jié)束日期 datetimeas declare 單位名稱 varchar (40);declare 預(yù)算金額 money ,結(jié)算金額money ,入賬金額money ,未結(jié)算金額money ,未入賬金額 money ;select 單位名稱=單位名稱from 單位代碼表where單位代碼=單位代碼set 預(yù)算金額 =（select sum （預(yù)算金額）from 作業(yè)項目表where 預(yù)算單位like 單位代碼 + '%' and

56、 （預(yù)算日期 between起始日期and結(jié)束日期）set 結(jié)算金額 =（select sum （結(jié)算金額）from 作業(yè)項目表where 預(yù)算單位like 單位代碼 + '%' and （結(jié)算日期 between起始日期and結(jié)束日期）set 入賬金額 =（select sum （入賬金額）from 作業(yè)項目表where 預(yù)算單位like 單位代碼 + '%' and （入賬日期 between起始日期and結(jié)束日期）and（入賬金額is not null）set 未結(jié)算金額=（select sum （預(yù)算金額）from作業(yè)項目表where （結(jié)算金額is n

57、ull） and （預(yù)算日期 between起始日期and 結(jié)束日期）set 未入賬金額=（select sum （結(jié)算金額）from作業(yè)項目表where （入賬金額is null） and （結(jié)算日期 between起始日期and 結(jié)束日期）set 未結(jié)算金額=預(yù)算金額-結(jié)算金額set 未入賬金額=結(jié)算金額-入賬金額if 預(yù)算金額isnullset 預(yù)算金額if 結(jié)算金額isnullset 結(jié)算金額if 入賬金額isnullset 入賬金額=0 if 未結(jié)算金額is nullset 未結(jié)算金額=0if 未入賬金額is nullset 未入賬金額=0 declare result varchar（IOO） set result = con vert （varchar , 預(yù)算金額）+ ' '+ c on vert （varchar , 結(jié)算金額）+''+ c