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

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

2、算，列出相應(yīng)明細。 ⑵ 采油一礦二隊2016-5-1到2016-5-28有哪些項目完成了結(jié)算，列出相應(yīng)明細。 ⑶ 采油一礦二隊2016-5-1到2016-5-28有哪些項目完成了結(jié)算，列出相應(yīng)的材料費消耗明細。 ⑷ 采油一礦二隊2016-5-1到2016-5-28有哪些項目完成了入賬，列出相應(yīng)明細。 ⑸ 列出采油一礦二隊2016-5-1到2016-5-28總的預(yù)算金額。 ⑹ 列出采油一礦二隊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īng)消耗明細(利用子查詢)。 ⒁ 作業(yè)公司二隊參與了哪些項目。 ⒂ 作業(yè)公司一隊和二隊參與了哪些項目〔利用union〕。 ⒃ 采油一礦的油井是哪些作業(yè)隊參與施工的。 3．利用Query Analyzer完成以下操作： ⑴ 建立數(shù)據(jù)表(包含3個屬性列：★施工單位、★年月、◆結(jié)算金額)保存各個施工

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

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完成以下任務(wù)： ⑴對實驗三中所定義的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ù)自己所命名的表名進展相應(yīng)的替換。 ⑵對實驗三中所定義的6個數(shù)據(jù)表增加相應(yīng)的參照完整性約束，并觀察在數(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ù)自己所命名的表名進展相應(yīng)的替換。 ⑶ 對實驗三中所定義的6個數(shù)據(jù)表按以下要求增加相應(yīng)的完整性約束條件，并觀察在數(shù)據(jù)表中存在數(shù)據(jù)的情況下完整性約束是否創(chuàng)建成功。 ① 單位代碼表的單位名稱不能取空值、且取值唯一。 ② 油水井表的井別只允許取“油井〞

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

9、何變化。 ⑷ 撤銷上述成功的更新操作。 四、實驗報告 提交實驗容中用SQL語句完成的題目的SQL語句文檔與相應(yīng)的執(zhí)行結(jié)果。 實驗六 其它數(shù)據(jù)庫對象的管理 一、實驗?zāi)康? 1．掌握事務(wù)的概念、性質(zhì)、定義與使用； 2．掌握游標(biāo)的概念、組成、創(chuàng)建與使用； 3．掌握存儲過程的概念、類型、特點、創(chuàng)建、執(zhí)行與管理。 4．掌握觸發(fā)器的概念、創(chuàng)建、管理與使用。 二、實驗學(xué)時 2學(xué)時 三、實驗容 1．利用Query Analyzer進展如下事務(wù)處理練習(xí)〔把如下五條語句作為一個事務(wù)處理，只有五條語句全部成功執(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ù)自己所命名的表名進展相應(yīng)的替換。 2．利用Query Analyzer進展如下游標(biāo)練習(xí)： 定義一個游標(biāo)，用于存放作業(yè)項目表的全部行數(shù)據(jù)，并打印以下表頭和各行數(shù)據(jù)。 表頭：單據(jù)號預(yù)算單位井號預(yù)算金額預(yù)算人預(yù)算日期開工日期完工日期施工單位施工容材料費人工費設(shè)備費其它費用結(jié)算金額結(jié)算人結(jié)算日期入賬金額入賬人入賬

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

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

14、nalyzer完成以下操作： ⑴ 建立采油一礦的作業(yè)項目的視圖，把該視圖的查詢權(quán)限授予給采油一礦的用戶user11，以user11的身份查詢該視圖，觀察執(zhí)行情況；再以其他用戶的身份查詢該視圖，觀察執(zhí)行情況。 ⑵ 創(chuàng)建一個用戶user12，以user12的身份執(zhí)行實驗六中所定義的存儲過程，觀察記錄是否成功執(zhí)行；然后把該存儲過程的執(zhí)行權(quán)限授予給user12，再次以user12的身份執(zhí)行該存儲過程，觀察記錄是否成功執(zhí)行。 ⑶ 定義觸發(fā)器，實現(xiàn)只能在工作時間更新“作業(yè)項目表〞的數(shù)據(jù)，然后通過選擇不同的時間進展適當(dāng)?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), 預(yù)算單位CHAR(20), 井號CHAR(20), 預(yù)算金額MONEY, 預(yù)算人CHAR(20), 預(yù)算日期DATETIME, 開工日期DATETIME, 完工日期DATETIME, 施工單位CHAR(20), 施工容CHAR(20), 材料費money, 人工費MONEY, 設(shè)備費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預(yù)算日期索引on作業(yè)項目表(預(yù)算日期); createindex結(jié)算日期索引on作業(yè)項目表(結(jié)算日期); createindex入賬日期索引on作業(yè)項目

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

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

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

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

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

34、'and'2016/5/28'and預(yù)算單位=單位代碼and單位名稱like'采油一礦%'; --another solution selectsum(入賬金額)入賬金額總和 from作業(yè)項目表 where入賬日期between'2016/5/1'and'2016/5/28'and預(yù)算單位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預(yù)算單位= (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預(yù)算單位=單位代碼表.單位代碼and單位代碼表.單位代碼=油水井表.單位代碼and單位名稱like'采油一礦%'; --another solution selectdistinct施工單位 from作業(yè)項目表 where井號in(select井號 from油水井表 where單位代碼in(select單位代碼 from單位代碼表 where單位名稱like'采油一礦%')); -------------------------------------------------------------------------------

39、---- dropindex預(yù)算日期索引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預(yù)算單位in(select單位代碼 from單位代碼表 where單位名稱like'采油一礦%' ); --(4)----------------------------------------------------------------------------------------------- delete from作業(yè)項目表 where預(yù)算單位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(預(yù)算單位)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 (材料費+人工費+設(shè)備費+其他費用=結(jié)算金額); --3------------------------------------------------------------------------------------ --(1)-------------------------------------------- createview視圖一 as select作業(yè)項目表.*,物碼,消耗數(shù)量,單價 from作業(yè)項目表,材料費表 where作業(yè)項目表.單據(jù)號=材料費表.單據(jù)號; --(2)------------ select結(jié)算金額,消耗數(shù)量 from視圖

54、一 where物碼='wm003'; select預(yù)算單位,施工容 from視圖一 where單據(jù)號='zy2016001'and物碼='wm001'; --(3)------------------------- createview預(yù)算狀態(tài) as select單據(jù)號,預(yù)算單位,井號,預(yù)算金額,預(yù)算人,預(yù)算日期 from作業(yè)項目表 begintran insert into預(yù)算狀態(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預(yù)算單位varchar(20) declare井號varchar(20) declare預(yù)算金額varchar(20) declare預(yù)算人varchar(20) declare預(yù)算日期varchar(20) declare開工日期varc

58、har(20) declare完工日期varchar(20) declare施工單位varchar(20) declare施工容varchar(20) declare材料費varchar(20) declare人工費varchar(20) declare設(shè)備費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ù)號 '+ '預(yù)算單位 '+ '井號 '+ '預(yù)算金額 '+ '預(yù)算人 '+ '預(yù)算日期 '+ '開工日期 '+ '完工日期 '+ '施工單位 '+ '施工容 '+ '材料費 '+ '人工費 '+ '設(shè)備費 '+ '其它費用 '+ '結(jié)算金額 '+ '結(jié)算人 '+ '結(jié)算日期 '+ '入賬金額 '+ '入賬人'+ '入賬日期' fetchnextfrom yb into單據(jù)號, 預(yù)算單位, 井號, 預(yù)算金額, 預(yù)算人, 預(yù)算日期, 開工日期, 完工日期, 施工單位, 施工容, 材料

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

61、 入賬日期 fetchnextfrom yb into 單據(jù)號, 預(yù)算單位, 井號, 預(yù)算金額, 預(yù)算人, 預(yù)算日期, 開工日期, 完工日期, 施工單位, 施工容, 材料費, 人工費, 設(shè)備費, 其它費用, 結(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預(yù)算金額money, 結(jié)算金額money, 入賬金額money, 未結(jié)算金額money, 未入賬金額money; select單位名稱=單位名稱from單位代碼表 where單位代碼=單位代碼 set預(yù)算金額=(selectsum(預(yù)算金額)from作業(yè)項目表 where預(yù)算單位like單位代碼+'%'and(預(yù)算日期between起始日期and完畢日期)) set結(jié)算

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

64、算金額)from作業(yè)項目表 where (入賬金額isnull)and(結(jié)算日期between起始日期and完畢日期)) set未結(jié)算金額=預(yù)算金額-結(jié)算金額 set未入賬金額=結(jié)算金額-入賬金額 if預(yù)算金額isnull set預(yù)算金額= 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,預(yù)算金額)+' '+convert(varchar,結(jié)算金額)+ ' '+convert(varchar,入賬金額)+' '+convert(varchar,未結(jié)算金額)+' '+convert(varchar,未入賬金額) print單位名稱+'單位'+convert(varchar,起始日期,102)+'--'+convert(varchar,完畢日期,102)+'本錢運營狀況' print'預(yù)算金額結(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 =設(shè)備費,temp4 =其他費用 fr