大數(shù)據(jù)庫實(shí)驗(yàn)上機(jī)問題詳解整理 中國石油大學(xué) 龔安

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

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

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

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

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

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

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

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

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

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

10、并給出具體的錯(cuò)誤提示信息〕： insert into 作業(yè)項(xiàng)目表 values('zy2016006','112202002','y005', 10000,'三', '07-01-2016' ,'07-04-2016','07-25-2016', '作業(yè)公司作業(yè)一隊(duì)','堵漏',7000,2500,1000,1400,11900, '四','07-26-2016',11900,'王五','07-28-2016') insert into 材料費(fèi)表 values('zy2016006','wm001',200,10) insert into 材料費(fèi)表 values('zy201600

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

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

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

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

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

16、0), 消耗數(shù)量INT, 單價(jià)MONEY, ); CREATETABLE作業(yè)項(xiàng)目表 (單據(jù)號(hào)CHAR(20), 預(yù)算單位CHAR(20), 井號(hào)CHAR(20), 預(yù)算金額MONEY, 預(yù)算人CHAR(20), 預(yù)算日期DATETIME, 開工日期DATETIME, 完工日期DATETIME, 施工單位CHAR(20), 施工容CHAR(20), 材料費(fèi)money, 人工費(fèi)MONEY, 設(shè)備費(fèi)MONEY, 其他費(fèi)用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','采油一礦一隊(duì)'); insertin

18、to單位代碼表 values ('112201002','采油一礦二隊(duì)'); insertinto單位代碼表 values ('112201003','采油一礦三隊(duì)'); insertinto單位代碼表 values ('112202001','采油二礦一隊(duì)'); 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è)一隊(duì)'); insertinto施工單位表 values ('作業(yè)公司作業(yè)二隊(duì)'); insertinto施工單位表 values ('作業(yè)公司作業(yè)三隊(duì)'); insertinto物碼表 values ('wm001','材料一','噸'); insertinto物碼表 values ('wm002','材料二','米'); insertinto物碼表 values ('wm003','材料三','桶'); insertinto物碼表 values ('wm004','材料四','袋'); insertinto材料費(fèi)表(單據(jù)號(hào),物碼,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

39、---- dropindex預(yù)算日期索引on作業(yè)項(xiàng)目表; dropindex結(jié)算日期索引on作業(yè)項(xiàng)目表; dropindex入賬日期索引on作業(yè)項(xiàng)目表; --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è)項(xiàng)目表 groupby施工單位,convert(varchar(7),結(jié)算日期,111); --another solution --insert --into 結(jié)算金額表 --select 施工單位,YEAR(結(jié)算日期)*100+MONTH(結(jié)算日期),sum(結(jié)算金額) --from 作業(yè)項(xiàng)目表 --group by 施工單位,YEAR(結(jié)算日期)*100+MONTH(結(jié)算日期); --(3)----------------

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

42、ike'采油一礦%' ); rollback ---實(shí)驗(yàn)五----------------------------------------------------------------------------------- ---(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è)項(xiàng)目表 groupby施工單位,convert(varchar(7),結(jié)算日期,111); -------------------------------------------

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

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

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井號(hào)char(20)notNUll; altertable油水井表 addprimarykey (井號(hào)); -------------------------------------------------------

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

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

50、------------------------------------------------------------------------- insertinto油水井表 values ('y007','油井','112203002'); insertinto材料費(fèi)表 values('zy2016007','wm006',100,10); begintran update作業(yè)項(xiàng)目表 set施工單位='作業(yè)公司作業(yè)五隊(duì)' where單據(jù)號(hào)='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計(jì)量單位char(20)notnull; altertable材料費(fèi)表 altercolumn消耗數(shù)量intnotnull; altertable材料費(fèi)表 altercolumn單價(jià)intnotnull; altertable作業(yè)項(xiàng)目表 addconstraint asdaf che

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

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

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

56、1400,11900, '四','07-26-2016',11900,'王五','07-28-2016') insertinto材料費(fèi)表values('zy2016006','wm001',200,10) insertinto材料費(fèi)表values('zy2016006','wm002',200,10) insertinto材料費(fèi)表values('zy2016006','wm003',200,10) insertinto材料費(fèi)表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è)項(xiàng)目表 open yb declare單據(jù)號(hào)varchar(20) declare預(yù)算單位varchar(20) declare井號(hào)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材料費(fèi)varchar(20) declare人工費(fèi)varchar(20) declare設(shè)備費(fèi)varchar(20) declare其它費(fèi)用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ù)號(hào) '+ '預(yù)算單位 '+ '井號(hào) '+ '預(yù)算金額 '+ '預(yù)算人 '+ '預(yù)算日期 '+ '開工日期 '+ '完工日期 '+ '施工單位 '+ '施工容 '+ '材料費(fèi) '+ '人工費(fèi) '+ '設(shè)備費(fèi) '+ '其它費(fèi)用 '+ '結(jié)算金額 '+ '結(jié)算人 '+ '結(jié)算日期 '+ '入賬金額 '+ '入賬人'+ '入賬日期' fetchnextfrom yb into單據(jù)號(hào), 預(yù)算單位, 井號(hào), 預(yù)算金額, 預(yù)算人, 預(yù)算日期, 開工日期, 完工日期, 施工單位, 施工容, 材料

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

61、 入賬日期 fetchnextfrom yb into 單據(jù)號(hào), 預(yù)算單位, 井號(hào), 預(yù)算金額, 預(yù)算人, 預(yù)算日期, 開工日期, 完工日期, 施工單位, 施工容, 材料費(fèi), 人工費(fèi), 設(shè)備費(fèi), 其它費(fèi)用, 結(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è)項(xiàng)目表 where預(yù)算單位like單位代碼+'%'and(預(yù)算日期between起始日期and完畢日期)) set結(jié)算

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

64、算金額)from作業(yè)項(xiàng)目表 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)+'本錢運(yùn)營狀況' 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è)項(xiàng)目表 afterinsert as declare結(jié)算金額money declare temp1 money,temp2 money,temp3 money,temp4 money; select temp1 =材料費(fèi),temp2 =人工費(fèi),temp3 =設(shè)備費(fèi),temp4 =其他費(fèi)用 fr