大數(shù)據(jù)庫實驗上機問題詳解整理 中國石油大學 龔安

《大數(shù)據(jù)庫實驗上機問題詳解整理 中國石油大學 龔安》由會員分享，可在線閱讀，更多相關《大數(shù)據(jù)庫實驗上機問題詳解整理 中國石油大學 龔安（46頁珍藏版）》請在裝配圖網(wǎng)上搜索。

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

2、算，列出相應明細。 ⑵ 采油一礦二隊2016-5-1到2016-5-28有哪些項目完成了結(jié)算，列出相應明細。 ⑶ 采油一礦二隊2016-5-1到2016-5-28有哪些項目完成了結(jié)算，列出相應的材料費消耗明細。 ⑷ 采油一礦二隊2016-5-1到2016-5-28有哪些項目完成了入賬，列出相應明細。 ⑸ 列出采油一礦二隊2016-5-1到2016-5-28總的預算金額。 ⑹ 列出采油一礦二隊2016-5-1到2016-5-28總的結(jié)算金額。 ⑺ 列出采油一礦二隊2016-5-1到2016-5-28總的入賬金額。 ⑻ 列出采油一礦2016-5-1到2016-5-28總的入賬金額。

3、 ⑼ 有哪些人員參與了入賬操作。 ⑽ 列出2016-5-1到2016-5-28進展了結(jié)算但未入賬的項目。 ⑾ 列出采油一礦二隊的所有項目，按入賬金額從高到低排列。 ⑿ 列出有哪些施工單位實施了項目，并計算各單位所有項目結(jié)算金額總和。 ⒀ 找出消耗了材料三且消耗超過了2000元的項目，列出相應消耗明細(利用子查詢)。 ⒁ 作業(yè)公司二隊參與了哪些項目。 ⒂ 作業(yè)公司一隊和二隊參與了哪些項目〔利用union〕。 ⒃ 采油一礦的油井是哪些作業(yè)隊參與施工的。 3．利用Query Analyzer完成以下操作： ⑴ 建立數(shù)據(jù)表(包含3個屬性列：★施工單位、★年月、◆結(jié)算金額)保存各個施工

4、單位每月的結(jié)算金額總和。 ⑵ 用子查詢將各個施工單位每月的結(jié)算金額總和插入到所建立的數(shù)據(jù)表中。 ⑶ 用帶子查詢的修改語句將采油一礦油井作業(yè)項目的結(jié)算人改為“兵〞。 ⑷ 用帶子查詢的刪除語句刪除采油一礦油井作業(yè)項目。 ⑸ 撤消上述兩個操作。 四、實驗報告 提交實驗容中用SQL語句完成的題目的SQL語句文檔與相應的執(zhí)行結(jié)果。 實驗五 SQL練習3 一、實驗目的 1．掌握根本表的刪除與修改； 2．掌握實體完整性、參照完整性和用戶定義的完整性的定義、檢查和違約處理； 3．掌握視圖的定義、查詢和更新，了解視圖的作用。 二、實驗學時 2學時 三、實驗

5、容 1．利用Query Analyzer完成以下操作： ⑴ 向在實驗四中所定義的數(shù)據(jù)表增加“備注〞列，其數(shù)據(jù)類型為字符型，并查看新增列的值。 ⑵ 對上述數(shù)據(jù)表增加主碼約束條件，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下主碼約束是否創(chuàng)建成功，然后再次執(zhí)行實驗四中實驗容3.2的操作，并觀察記錄執(zhí)行結(jié)果。 ⑶ 刪除上述數(shù)據(jù)表中的數(shù)據(jù)，然后再刪除該數(shù)據(jù)表，對這兩個操作進展比擬。 2．利用Query Analyzer完成以下任務： ⑴對實驗三中所定義的6個數(shù)據(jù)表增加主碼約束條件，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下主碼約束是否創(chuàng)建成功，然后執(zhí)行以下2個操作，觀察并記錄實體完整性的檢查和違約處理。 ① i

6、nsert into 材料費表 values('zy2016001','wm004',100,10) ②insert into 材料費表 values('zy2016002',NULL,200,10) 注：“材料費表〞根據(jù)自己所命名的表名進展相應的替換。 ⑵對實驗三中所定義的6個數(shù)據(jù)表增加相應的參照完整性約束，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下參照完整性約束是否創(chuàng)建成功，然后執(zhí)行以下操作，觀察并記錄參照完整性的檢查和違約處理。 ① 將〔y007 油井 112203002〕插入到油水井表。 ② insert into 材料費表 values('zy2016007','

7、wm006',100,10) ③ 將作業(yè)項目編號zy2016001的施工單位修改為“作業(yè)公司作業(yè)五隊〞。 ④ 將單位代碼表中的〔112202002 采油二礦二隊〕刪除，查看油水井表和作業(yè)項目表中的數(shù)據(jù)有何變化。 ⑤ 將物碼表中的〔wm004 材料四 袋〕修改為〔wm04 材料四 袋〕。 ⑥ 撤銷上述成功的更新操作。 注：“材料費表〞根據(jù)自己所命名的表名進展相應的替換。 ⑶ 對實驗三中所定義的6個數(shù)據(jù)表按以下要求增加相應的完整性約束條件，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下完整性約束是否創(chuàng)建成功。 ① 單位代碼表的單位名稱不能取空值、且取值唯一。 ② 油水井表的井別只允許取“油井〞

8、或“水井〞，單位代碼不能取空值。 ③ 物碼表的名稱規(guī)格不能取空值、且取值唯一，計量單位不能取空值。 ④ 材料費表的消耗數(shù)量不能取空值，單價不能取空值。 ⑤ 對作業(yè)項目表根據(jù)實際應用的要求定義適當?shù)挠脩舳x的完整性約束條件。 3．利用Query Analyzer完成以下操作： ⑴ 定義一個視圖，用于保存作業(yè)項目表和材料費表的全部列。 ⑵ 查詢上面定義的視圖，可任意組合查詢條件，構(gòu)造出2個查詢。 ⑶ 定義一個反映作業(yè)項目預算狀態(tài)的視圖，并向該視圖插入('zy2016008','112202002','y005',10000,'三', '2016-07-02')，查看作業(yè)項目表的數(shù)據(jù)有

9、何變化。 ⑷ 撤銷上述成功的更新操作。 四、實驗報告 提交實驗容中用SQL語句完成的題目的SQL語句文檔與相應的執(zhí)行結(jié)果。 實驗六 其它數(shù)據(jù)庫對象的管理 一、實驗目的 1．掌握事務的概念、性質(zhì)、定義與使用； 2．掌握游標的概念、組成、創(chuàng)建與使用； 3．掌握存儲過程的概念、類型、特點、創(chuàng)建、執(zhí)行與管理。 4．掌握觸發(fā)器的概念、創(chuàng)建、管理與使用。 二、實驗學時 2學時 三、實驗容 1．利用Query Analyzer進展如下事務處理練習〔把如下五條語句作為一個事務處理，只有五條語句全部成功執(zhí)行才做提交，并給出成功的提示信息；否如此就做回退處理，

10、并給出具體的錯誤提示信息〕： insert into 作業(yè)項目表 values('zy2016006','112202002','y005', 10000,'三', '07-01-2016' ,'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('zy201600

11、6','wm002',200,10) insert into 材料費表 values('zy2016006','wm003',200,10) insert into 材料費表 values('zy2016006','wm004',100,10) 注：“作業(yè)項目表〞、“材料費表〞根據(jù)自己所命名的表名進展相應的替換。 2．利用Query Analyzer進展如下游標練習： 定義一個游標，用于存放作業(yè)項目表的全部行數(shù)據(jù)，并打印以下表頭和各行數(shù)據(jù)。 表頭：單據(jù)號預算單位井號預算金額預算人預算日期開工日期完工日期施工單位施工容材料費人工費設備費其它費用結(jié)算金額結(jié)算人結(jié)算日期入賬金額入賬人入賬

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

13、采油廠、采油礦、采油隊〕執(zhí)行以上定義的存儲過程，查看執(zhí)行輸出結(jié)果。 4．利用Query Analyzer針對作業(yè)項目表定義三個觸發(fā)器，分別完成以下功能： ⑴ 對作業(yè)項目表插入一行數(shù)據(jù)時，自動計算并插入結(jié)算金額字段〔結(jié)算金額=材料費+人工費+設備費+其它費用〕。 ⑵ 當修改作業(yè)表的某行數(shù)據(jù)時自動修改結(jié)算金額字段。 ⑶ 當刪除作業(yè)表中一行數(shù)據(jù)時，自動刪除材料費表中相應明細數(shù)據(jù)。 ⑷ 對上述3個觸發(fā)器用適當?shù)母抡Z句進展驗證，并查看結(jié)果是否達到預期結(jié)果。 四、實驗報告 提交實驗容中用SQL語句完成的題目的SQL語句文檔與相應的執(zhí)行結(jié)果。 實驗七 安全機制 2．利用Query A

14、nalyzer完成以下操作： ⑴ 建立采油一礦的作業(yè)項目的視圖，把該視圖的查詢權限授予給采油一礦的用戶user11，以user11的身份查詢該視圖，觀察執(zhí)行情況；再以其他用戶的身份查詢該視圖，觀察執(zhí)行情況。 ⑵ 創(chuàng)建一個用戶user12，以user12的身份執(zhí)行實驗六中所定義的存儲過程，觀察記錄是否成功執(zhí)行；然后把該存儲過程的執(zhí)行權限授予給user12，再次以user12的身份執(zhí)行該存儲過程，觀察記錄是否成功執(zhí)行。 ⑶ 定義觸發(fā)器，實現(xiàn)只能在工作時間更新“作業(yè)項目表〞的數(shù)據(jù)，然后通過選擇不同的時間進展適當?shù)母虏僮鱽眚炞C。 --實驗3------------------------

15、----------------------------------------- CREATETABLE單位代碼表 (單位代碼CHAR(20), 單位名稱CHAR (20)); CREATETABLE油水井表 (井號CHAR(20), 井別CHAR(20), 單位代碼CHAR(20)); CREATETABLE施工單位表 (施工單位名稱CHAR(20)); CREATETABLE物碼表 (物碼CHAR(20), 名稱規(guī)格CHAR(20), 計量單位CHAR(20)); createtable材料費表( 單據(jù)號CHAR(20), 物碼CHAR(2

16、0), 消耗數(shù)量INT, 單價MONEY, ); CREATETABLE作業(yè)項目表 (單據(jù)號CHAR(20), 預算單位CHAR(20), 井號CHAR(20), 預算金額MONEY, 預算人CHAR(20), 預算日期DATETIME, 開工日期DATETIME, 完工日期DATETIME, 施工單位CHAR(20), 施工容CHAR(20), 材料費money, 人工費MONEY, 設備費MONEY, 其他費用money, 結(jié)算金額MONEY, 結(jié)算人CHAR(20), 結(jié)算日期DATETIME, 入賬金額MONEY, 入賬人CHA

17、R(10), 入賬日期DATETIME) ------------------------------------------------------------------------------- insertinto單位代碼表 values ('1122','采油廠'); insertinto單位代碼表 values ('112201','采油一礦'); insertinto單位代碼表 values ('112202','采油二礦'); insertinto單位代碼表 values ('112201001','采油一礦一隊'); insertin

18、to單位代碼表 values ('112201002','采油一礦二隊'); insertinto單位代碼表 values ('112201003','采油一礦三隊'); insertinto單位代碼表 values ('112202001','采油二礦一隊'); insertinto油水井表 values ('y001','油井','112201001'); insertinto油水井表 values ('y002','油井','112201001'); insertinto油水井表 values ('y003','油井','112201002'

19、); insertinto油水井表 values ('s001','水井','112201002'); insertinto油水井表 values ('y004','油井','112201003'); insertinto油水井表 values ('s002','水井','112202001'); insertinto油水井表 values ('s003','水井','112202001'); insertinto油水井表 values ('y005','油井','112202002'); insertinto施工單位表 values ('作業(yè)公

20、司作業(yè)一隊'); insertinto施工單位表 values ('作業(yè)公司作業(yè)二隊'); insertinto施工單位表 values ('作業(yè)公司作業(yè)三隊'); insertinto物碼表 values ('wm001','材料一','噸'); insertinto物碼表 values ('wm002','材料二','米'); insertinto物碼表 values ('wm003','材料三','桶'); insertinto物碼表 values ('wm004','材料四','袋'); insertinto材料費表(單據(jù)號,物碼,

21、消耗數(shù)量,單價) values ('zy2016001','wm001',200,￥10); insertinto材料費表(單據(jù)號,物碼,消耗數(shù)量,單價) values ('zy2016001','wm002',200,￥10); insertinto材料費表(單據(jù)號,物碼,消耗數(shù)量,單價) values ('zy2016001','wm003',200,￥10); ---------------------------------------------------------------- insertinto材料費表(單據(jù)號,物碼,消耗數(shù)量,單價) value

22、s ('zy2016003','wm001',200,￥10); insertinto材料費表(單據(jù)號,物碼,消耗數(shù)量,單價) values ('zy2016003','wm002',200,￥10); insertinto材料費表(單據(jù)號,物碼,消耗數(shù)量,單價) values ('zy2016003','wm003',250,￥10); -------------------------------------------------------------------- insertinto材料費表(單據(jù)號,物碼,消耗數(shù)量,單價) values ('zy20160

23、04','wm001',200,￥10); insertinto材料費表(單據(jù)號,物碼,消耗數(shù)量,單價) values ('zy2016004','wm002',200,￥10); insertinto材料費表(單據(jù)號,物碼,消耗數(shù)量,單價) values ('zy2016004','wm004',200,￥10); ------------------------------------------------------ insertinto材料費表(單據(jù)號,物碼,消耗數(shù)量,單價) values ('zy2016005','wm001',200,￥10); in

24、sertinto材料費表(單據(jù)號,物碼,消耗數(shù)量,單價) values ('zy2016005','wm002',200,￥10); insertinto材料費表(單據(jù)號,物碼,消耗數(shù)量,單價) values ('zy2016005','wm004',300,￥10); -------------------------------------------------------------------- insertinto作業(yè)項目表 values('zy2016001','112201001','y001',￥10000,'三','2016/5/1', '2016/5/4

25、','2016/5/23','作業(yè)公司作業(yè)一隊','堵漏',￥7000,￥2500,￥1000,￥1400,￥10900,'王五','2016/5/26', ￥10900,'王五','2016/5/28'); ---------------------------------------------------------- insertinto作業(yè)項目表 values ('zy2016003','112201002','s001',￥10500,'三','2016/5/1', '2016/5/6','2016/5/23','作業(yè)公司作業(yè)二隊','調(diào)剖',￥6500,￥2000,

26、￥500,￥1400,￥10400,'四','2016/5/26', ￥10400,'王五','2016/5/28' ); --------------------------------------------- insertinto作業(yè)項目表 values ('zy2016004','112202001','s002',￥12000,'三','2016/5/1', '2016/5/4','2016/5/24','作業(yè)公司作業(yè)三隊','解堵',￥6000,￥2000,￥1000,￥1600,￥10600,'四','2016/5/26',￥10600,'六','2016/5/2

27、8'); -------------------------------------------- insertinto作業(yè)項目表 values('zy2016005','112202002','y005',￥12000,'三','2016/5/1', '2016/5/4','2016/5/28','作業(yè)公司作業(yè)三隊','防砂',￥7000,￥1000,￥2000,￥1300,￥11300,'四','2016/6/1', NULL,NULL,NULL); --------------------------------------- begintran update作業(yè)項目表

28、 set人工費=人工費+200 where單據(jù)號='zy2016005'; update作業(yè)項目表 set結(jié)算金額=結(jié)算金額+200 where單據(jù)號='zy2016005'; delete from作業(yè)項目表 where入賬金額isNULL; rollbacktran --實驗四----------------------------------------------- --1 createindex預算日期索引on作業(yè)項目表(預算日期); createindex結(jié)算日期索引on作業(yè)項目表(結(jié)算日期); createindex入賬日期索引on作業(yè)項目

29、表(入賬日期); --(1) select* from作業(yè)項目表 where預算日期between'2016/5/1'and'2016/5/28'and預算單位= (select單位代碼 from單位代碼表 where單位名稱='采油一礦二隊'); --another solution select* from作業(yè)項目表,單位代碼表 where單位代碼=預算單位and單位名稱='采油一礦二隊'and預算日期between'2016/5/1'and'2016/5/28'; --(2) select* from作業(yè)項目表 where結(jié)算日期between'2016/5

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

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

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

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

34、'and'2016/5/28'and預算單位=單位代碼and單位名稱like'采油一礦%'; --another solution selectsum(入賬金額)入賬金額總和 from作業(yè)項目表 where入賬日期between'2016/5/1'and'2016/5/28'and預算單位in (select單位代碼 from單位代碼表 where單位名稱like'采油一礦%'); --(9)------------------------------- selectdistinct入賬人 from作業(yè)項目表 where入賬人isnotnull; --(10)---

35、----------------------------- select單據(jù)號 from作業(yè)項目表 where結(jié)算日期between'2016/5/1'and'2016/5/28'and結(jié)算金額isnotnulland入賬金額isnull; --(11)---------------------------------- select單據(jù)號 from作業(yè)項目表 where預算單位= (select單位代碼 from單位代碼表 where單位名稱='采油一礦二隊') orderby入賬金額desc; --(12)-------------------- select施工

36、單位表.施工單位名稱,count(結(jié)算金額)項目個數(shù),sum(結(jié)算金額)結(jié)算金額總和 from施工單位表leftjoin作業(yè)項目表on (施工單位表.施工單位名稱=作業(yè)項目表.施工單位) groupby施工單位表.施工單位名稱; --〔13〕-------------------------------------------- select* from材料費表 where單據(jù)號=(select單據(jù)號 from材料費表 where物碼=(select物碼 from物碼表 where名稱規(guī)格='材料三') and消耗數(shù)量*單價>2000); --〔14〕-------

37、------------------------------ selectdistinct單據(jù)號 from作業(yè)項目表 where施工單位='作業(yè)公司作業(yè)二隊'; --〔15〕-------------------------------- selectdistinct單據(jù)號 from作業(yè)項目表 where施工單位='作業(yè)公司作業(yè)一隊' union selectdistinct單據(jù)號 from作業(yè)項目表 where施工單位='作業(yè)公司作業(yè)二隊'; --〔16〕--------------------------- selectdistinct施工單位 from作業(yè)項目

38、表,油水井表,單位代碼表 where預算單位=單位代碼表.單位代碼and單位代碼表.單位代碼=油水井表.單位代碼and單位名稱like'采油一礦%'; --another solution selectdistinct施工單位 from作業(yè)項目表 where井號in(select井號 from油水井表 where單位代碼in(select單位代碼 from單位代碼表 where單位名稱like'采油一礦%')); -------------------------------------------------------------------------------

39、---- dropindex預算日期索引on作業(yè)項目表; dropindex結(jié)算日期索引on作業(yè)項目表; dropindex入賬日期索引on作業(yè)項目表; --3--------------------------------------------------- --(1)------------------------------------------ createtable結(jié)算金額表( 施工單位char(20), 年月varchar(7), 結(jié)算金額money ); --(2)----------------------------------------- in

40、sert into結(jié)算金額表 select施工單位,convert(varchar(7),結(jié)算日期,111),sum(結(jié)算金額) from作業(yè)項目表 groupby施工單位,convert(varchar(7),結(jié)算日期,111); --another solution --insert --into 結(jié)算金額表 --select 施工單位,YEAR(結(jié)算日期)*100+MONTH(結(jié)算日期),sum(結(jié)算金額) --from 作業(yè)項目表 --group by 施工單位,YEAR(結(jié)算日期)*100+MONTH(結(jié)算日期); --(3)----------------

41、----------------------------------- begintran update作業(yè)項目表 set結(jié)算人='兵' where預算單位in(select單位代碼 from單位代碼表 where單位名稱like'采油一礦%' ); --(4)----------------------------------------------------------------------------------------------- delete from作業(yè)項目表 where預算單位in(select單位代碼 from單位代碼表 where單位名稱l

42、ike'采油一礦%' ); rollback ---實驗五----------------------------------------------------------------------------------- ---(1)------------------- altertable結(jié)算金額表 add備注char(20); select* from結(jié)算金額表 --(2)----------------------------------------------- altertable結(jié)算金額表 altercolumn施工單位char(20)notnu

43、ll; altertable結(jié)算金額表 altercolumn年月varchar(7)notnull; altertable結(jié)算金額表 addprimarykey(施工單位,年月); TRUNCATETABLE結(jié)算金額表 insert into結(jié)算金額表(施工單位,年月,結(jié)算金額) select施工單位,convert(varchar(7),結(jié)算日期,111),sum(結(jié)算金額) from作業(yè)項目表 groupby施工單位,convert(varchar(7),結(jié)算日期,111); -------------------------------------------

44、-------------------- delete from結(jié)算金額表; delete結(jié)算金額表; --2--------------------------------------------------- --(1)----------------------------------------------- altertable材料費表 altercolumn單據(jù)號char(20)notnull; altertable材料費表 altercolumn物碼char(20)notnull; altertable材料費表 addprimarykey (單據(jù)

45、號,物碼); ----------------------------------------------------------------------------------------------------------------- altertable作業(yè)項目表 altercolumn單據(jù)號char(20)notnull; altertable作業(yè)項目表 addprimarykey (單據(jù)號); ---------------------------------------------------------------------------------------

46、----------- altertable單位代碼表 altercolumn單位代碼char(20)notnull; altertable單位代碼表 addprimarykey (單位代碼); ---------------------------------------------------- altertable施工單位表 altercolumn施工單位名稱char(20)notnull; altertable施工單位表 addprimarykey (施工單位名稱); ---------------------------------------------

47、---------- altertable物碼表 altercolumn物碼char(20)notnull; altertable物碼表 addprimarykey (物碼); ------------------------------------------------------------- altertable油水井表 altercolumn井號char(20)notNUll; altertable油水井表 addprimarykey (井號); -------------------------------------------------------

48、-------------- begintran insertinto材料費表values('zy2016001','wm004',100,10); insertinto材料費表values('zy2016002',NULL,200,10); rollback --(2)------------------------------------------------------------------ altertable材料費表 addconstraint a1 foreignkey (單據(jù)號)references作業(yè)項目表(單據(jù)號); altertable材料費表

49、 addconstraint a2 foreignkey (物碼)references物碼表(物碼); altertable作業(yè)項目表 addconstraint b1 foreignkey(預算單位)references單位代碼表(單位代碼); altertable作業(yè)項目表 addconstraint b2 foreignkey(井號)references油水井表(井號); altertable作業(yè)項目表 addconstraint b3 foreignkey(施工單位)references施工單位表(施工單位名稱); ----------------

50、------------------------------------------------------------------------- insertinto油水井表 values ('y007','油井','112203002'); insertinto材料費表 values('zy2016007','wm006',100,10); begintran update作業(yè)項目表 set施工單位='作業(yè)公司作業(yè)五隊' where單據(jù)號='zy2016001'; delete from單位代碼表 where單位代碼='112202002'; u

51、pdate物碼表 set物碼='wm04' where名稱規(guī)格='材料四'; rollbacktran --(3)------------------------------- altertable單位代碼表 addconstraint aa1 check (單位名稱isnotnull); altertable單位代碼表 addconstraint uni unique(單位代碼); altertable油水井表 addconstraint aa2 check(井別in('油井','水井')); altertable油水井表 altercolumn單位代碼c

52、har(20)notnull; altertable物碼表 altercolumn名稱規(guī)格char(20)notnull; altertable物碼表 addconstraint uni2 unique(名稱規(guī)格); altertable物碼表 altercolumn計量單位char(20)notnull; altertable材料費表 altercolumn消耗數(shù)量intnotnull; altertable材料費表 altercolumn單價intnotnull; altertable作業(yè)項目表 addconstraint asdaf che

53、ck (材料費+人工費+設備費+其他費用=結(jié)算金額); --3------------------------------------------------------------------------------------ --(1)-------------------------------------------- createview視圖一 as select作業(yè)項目表.*,物碼,消耗數(shù)量,單價 from作業(yè)項目表,材料費表 where作業(yè)項目表.單據(jù)號=材料費表.單據(jù)號; --(2)------------ select結(jié)算金額,消耗數(shù)量 from視圖

54、一 where物碼='wm003'; select預算單位,施工容 from視圖一 where單據(jù)號='zy2016001'and物碼='wm001'; --(3)------------------------- createview預算狀態(tài) as select單據(jù)號,預算單位,井號,預算金額,預算人,預算日期 from作業(yè)項目表 begintran insert into預算狀態(tài) values ('zy2016008','112202002','y005',￥10000,'三','2016-07-02'); rollbacktran --實驗六--

55、----------------------------------------------------------------------------------------------------------------- --1---------------------------- begintran go insertinto作業(yè)項目表values('zy2016006','112202002','y005', 10000,'三','07-01-2016','07-04-2016','07-25-2016', '作業(yè)公司作業(yè)一隊','堵漏',7000,2500,1000,

56、1400,11900, '四','07-26-2016',11900,'王五','07-28-2016') insertinto材料費表values('zy2016006','wm001',200,10) insertinto材料費表values('zy2016006','wm002',200,10) insertinto材料費表values('zy2016006','wm003',200,10) insertinto材料費表values('zy2016006','wm004',100,10) iferror<>0 begin print'語句執(zhí)行失敗' rollback

57、 end else begin print'語句執(zhí)行成功' mit end --2--------------------------------- begintran declare yb cursorfor select* from作業(yè)項目表 open yb declare單據(jù)號varchar(20) declare預算單位varchar(20) declare井號varchar(20) declare預算金額varchar(20) declare預算人varchar(20) declare預算日期varchar(20) declare開工日期varc

58、har(20) declare完工日期varchar(20) declare施工單位varchar(20) declare施工容varchar(20) declare材料費varchar(20) declare人工費varchar(20) declare設備費varchar(20) declare其它費用varchar(20) declare結(jié)算金額varchar(20) declare結(jié)算人varchar(20) declare結(jié)算日期varchar(20) declare入賬金額varchar(20) declare入賬人varchar(20) declare入賬

59、日期varchar(20) print'單據(jù)號 '+ '預算單位 '+ '井號 '+ '預算金額 '+ '預算人 '+ '預算日期 '+ '開工日期 '+ '完工日期 '+ '施工單位 '+ '施工容 '+ '材料費 '+ '人工費 '+ '設備費 '+ '其它費用 '+ '結(jié)算金額 '+ '結(jié)算人 '+ '結(jié)算日期 '+ '入賬金額 '+ '入賬人'+ '入賬日期' fetchnextfrom yb into單據(jù)號, 預算單位, 井號, 預算金額, 預算人, 預算日期, 開工日期, 完工日期, 施工單位, 施工容, 材料

60、費, 人工費, 設備費, 其它費用, 結(jié)算金額, 結(jié)算人, 結(jié)算日期, 入賬金額, 入賬人, 入賬日期 while(fetch_status= 0) begin print 單據(jù)號+' '+ 預算單位+' '+ 井號+' '+ 預算金額+' '+ 預算人+' '+ 預算日期+' '+ 開工日期+' '+ 完工日期+' '+ 施工單位+' '+ 施工容+' '+ 材料費+' '+ 人工費+' '+ 設備費+' '+ 其它費用+' '+ 結(jié)算金額+' '+ 結(jié)算人+' '+ 結(jié)算日期+' '+ 入賬金額+' '+ 入賬人+' '+

61、 入賬日期 fetchnextfrom yb into 單據(jù)號, 預算單位, 井號, 預算金額, 預算人, 預算日期, 開工日期, 完工日期, 施工單位, 施工容, 材料費, 人工費, 設備費, 其它費用, 結(jié)算金額, 結(jié)算人, 結(jié)算日期, 入賬金額, 入賬人, 入賬日期; end --3---------------------------------- ifobject_id('_someDepCost','p')isnotnull dropprocedure _someDepCost; go createprocedure

62、_someDepCost 單位代碼varchar(40), 起始日期datetime, 完畢日期datetime as declare單位名稱varchar(40); declare預算金額money, 結(jié)算金額money, 入賬金額money, 未結(jié)算金額money, 未入賬金額money; select單位名稱=單位名稱from單位代碼表 where單位代碼=單位代碼 set預算金額=(selectsum(預算金額)from作業(yè)項目表 where預算單位like單位代碼+'%'and(預算日期between起始日期and完畢日期)) set結(jié)算

63、金額=(selectsum(結(jié)算金額)from作業(yè)項目表 where預算單位like單位代碼+'%'and(結(jié)算日期between起始日期and完畢日期)) set入賬金額=(selectsum(入賬金額)from作業(yè)項目表 where預算單位like單位代碼+'%'and(入賬日期between起始日期and完畢日期)and (入賬金額isnotnull)) set未結(jié)算金額=(selectsum(預算金額)from作業(yè)項目表 where (結(jié)算金額isnull)and(預算日期between起始日期and完畢日期)) set未入賬金額=(selectsum(結(jié)

64、算金額)from作業(yè)項目表 where (入賬金額isnull)and(結(jié)算日期between起始日期and完畢日期)) set未結(jié)算金額=預算金額-結(jié)算金額 set未入賬金額=結(jié)算金額-入賬金額 if預算金額isnull set預算金額= 0 if結(jié)算金額isnull set結(jié)算金額= 0 if入賬金額isnull set入賬金額= 0 if未結(jié)算金額isnull set未結(jié)算金額= 0 if未入賬金額isnull set未入賬金額= 0 declare result varchar(100) set result =convert(

65、varchar,預算金額)+' '+convert(varchar,結(jié)算金額)+ ' '+convert(varchar,入賬金額)+' '+convert(varchar,未結(jié)算金額)+' '+convert(varchar,未入賬金額) print單位名稱+'單位'+convert(varchar,起始日期,102)+'--'+convert(varchar,完畢日期,102)+'本錢運營狀況' print'預算金額結(jié)算金額入賬金額未結(jié)算金額未入賬金額' print result go execute _someDepCost單位代碼='1122',起始日期='2016

66、-5-1',完畢日期='2016-5-29'; execute _someDepCost單位代碼='112201',起始日期='2016-5-1',完畢日期='2016-5-29'; execute _someDepCost單位代碼='112201001',起始日期='2016-5-1',完畢日期='2016-5-29'; go --4--------------------------------------------- --(1)---------------------------- ifOBJECT_ID('trmytr1','TR')isnotnull droptrigger tr_mytrl go createtrigger trmytr1 on作業(yè)項目表 afterinsert as declare結(jié)算金額money declare temp1 money,temp2 money,temp3 money,temp4 money; select temp1 =材料費,temp2 =人工費,temp3 =設備費,temp4 =其他費用 fr