Oracle代碼閱讀能力及調(diào)試跟蹤方法系統(tǒng)考核題oracle基礎(chǔ)數(shù)據(jù)操作語法不合格

1、Oracle代碼閱讀能力及調(diào)試跟蹤方法系統(tǒng)考核題答題規(guī)則時間：原則上不限，但盡量在連續(xù)的 4個小時內(nèi)答完；方式：除了與本次學(xué)習(xí)的學(xué)員溝通、請人代做以外的任意方式，含網(wǎng)絡(luò)搜索等；（即要求獨(dú)立解決問題能力）要求：為了對其他學(xué)員負(fù)責(zé)， 請先參加考核的學(xué)員， 不要將考核題及結(jié)果轉(zhuǎn)給未參與考核的 學(xué)員，未參與考核的學(xué)員也不要詢問考題內(nèi)容及結(jié)果；另，注意署名，且寫上答題耗費(fèi)時間oracle基礎(chǔ)數(shù)據(jù)操作語法c類題一張表中包含如下列屬性和記錄信息:發(fā)生日期業(yè)務(wù)類型商品代碼數(shù)量成本金額發(fā)生金額發(fā)生門店收貨門店2011/1/4自營進(jìn)00001100200.00200.00100000010000002011/2/

2、11自營進(jìn)0000210001000.001000.00100000010000002011/2/11零售00005100300.00400.00100000010000002011/2/12自營進(jìn)0000320340.6040.60100000010000002011/2/12溢余000047003500.003500.00100000010000002011/2/12溢余000058002400.002400.00100000010000002011/2/12零售000052060.0080.00100000010000002011/2/25溢余000069601920.001920.00

3、100000010000002011/31統(tǒng)配出000077302920.003500.00100000010000012011/36統(tǒng)配出00008220.0025.00100000010000012011/37統(tǒng)配出000093001200.001300.00100000010000022011/37零售0000650100.00150.00100000010000002011/37調(diào)撥出000104545.0045.00100000010000002011/311調(diào)撥出0001110500.00500.00100000010000002011/311零售00008770105.00100

4、000010000002011/311零售00003204.0010.00100000010000001.創(chuàng)建該表，表名定義（注意字段名使用英文）:創(chuàng)建者姓名全拼音_DRPTCreate TABLE HD40 . ZHENGBAOSHAN_DRPT（FASHENG_DATEDATE,YEWULEIXINGSHANGPIN_NO SHANGPIN_NUMBER CHENGBEN_JINE FASHENG_JINE FASHENG_MENDIANVARCHAR2( 255 BYTE),NUMBERNUMBERNUMBER(8,2),NUMBER(8,2),INTEGER ,SHOUHUO MEND

5、IAN NUMBER)2.插入表中的記錄信息;In sertintozhengbaoshan_drptvalues('2011/1/4','自營進(jìn)',00001,100,200.00 , 200.00 , 1000000 , 1000000 );In sertintozhengbaoshan_drptvalues('2011/2/11','自營進(jìn)',00002,1000 , 1000.00 , 1000.00,1000000,1000000 );In sertintozhengbaoshan_drptvalues('201

6、1/2/11','零售',00005,100,300.00, 400.00, 1000000 , 1000000 );In sertintozhengbaoshan_drptvalues('2011/2/12','自營進(jìn)',00003,203,40.60 , 40.60 , 1000000 , 1000000 );In sertintozhengbaoshan_drptvalues('2011/2/12','溢余',00004,700,3500.00, 3500.00,1000000 ,1000000 );

7、In sertintozhengbaoshan_drptvalues('2011/2/12','溢余',00005,800,2400.00, 2400.00,1000000 ,1000000 );In sertintozhengbaoshan_drptvalues('2011/2/12','零售',00005,20,60.00 , 80.00 , 1000000 , 1000000 );In sertintozhengbaoshan_drptvalues('2011/2/25','溢余',00006

8、,960,1920.00, 1920.00,1000000 ,1000000 );In sertintozhengbaoshan_drptvalues('2011/3/1','統(tǒng)配出',00007,730,2920.00, 3500.00,1000000 ,1000001 );In sertintozhengbaoshan_drptvalues('2011/3/6','統(tǒng)配出',00008,2, 20.00 , 25.00 , 1000000,1000001);In sertintozhengbaoshan_drptvalues(

9、'2011/3/7','統(tǒng)配出',00009,300,1200.00, 1300.00,1000000 ,1000002 );In sertintozhengbaoshan_drptvalues('2011/3/7','零售',00006,50,100.00 , 150.00, 1000000 , 1000000 );In sertintozhengbaoshan_drptvalues('2011/3/7','調(diào)撥出',00010,45,45.00 , 45.00 , 1000000 , 10000

10、00 );In sertintozhengbaoshan_drptvalues('2011/3/11','調(diào)撥出',00011,10,500.00 , 500.00, 1000000 , 1000000 );In sertintozhengbaoshan_drptvalues('2011/3/11','零售',00008,7, 70 , 105.00, 1000000 ,1000000 );In sertintozhengbaoshan_drptvalues('2011/3/11','零售',000

11、03,20,4.00 , 10.00 , 1000000,1000000);3. 刪除商品代碼為00011 '的記錄;Delete from zhe ngbaosha n_drpt where sha ngpin_nO= '00011'4. 將發(fā)生日期為2011/712，商品代碼為00004，且業(yè)務(wù)為”溢余”的記錄，數(shù)量調(diào)整成70,成本金額調(diào)整成 350.00，發(fā)生金額調(diào)整成 350.00 ；updatezhe ngbaosha n_drptsetshangpin_number =70 , chengben_jine=350 . 00 , fasheng_jine=35

12、0 . 00 whereshangpin_no ='00004' and yewuleixing ='溢余'5. 將發(fā)生日期為 2011/36，商品代碼為 00008，且業(yè)務(wù)為”統(tǒng)配出”的記錄，數(shù)量增加1，成本金額增加2，發(fā)生金額增加5 ；UPDATE zhengbaoshan_drptSET sha ngpin_n umber = sha ngpin_n umber + 1 ,che ngbe n_jine= che ngbe n_jine+ 2,fashe ng_jine= fashe ng_jine+ 5WHEREfasheng_date = TO_DAT

13、E ('2011/3/6', 'yyyy/mm/dd' )AND shangpin_no= '00008' and yewuleixing='統(tǒng)配岀'6.對發(fā)生日期及業(yè)務(wù)類型字段創(chuàng)建索引，索引名稱定義：創(chuàng)建者姓名全拼音DX_DRPTcreatein dexzhe ngbaosha n_in dex_drptonzhengbaoshan_drpt(fasheng_date, yewuleixing)7. 統(tǒng)計零售業(yè)務(wù)的發(fā)生金額合計；Select sum(fasheng_jine) from zhengbaoshan_drpt whe

14、re yewuleixing= '零售'8. 統(tǒng)計發(fā)生日期為2011/31，商品代碼為00007的配貨業(yè)務(wù)的配貨單價(公式：發(fā)生金額/數(shù)量)Select fashe ng_jin e/sha ngpin_nu mber零售價 from zhe ngbaosha n_drptwherefasheng_date=to_date( 2011/03/01,'yyyy/mm/dd 'and shangpin_no= '00007'9. 統(tǒng)計每種業(yè)務(wù)類型的發(fā)生成本總額；Select , sum( chengben_jine+fasheng_jine), ye

15、wuleixingfrom zhengbaoshan_drptgroup by yewuleixing10. 統(tǒng)計發(fā)生成本總額超過1000.00的業(yè)務(wù)類型；Select yewuleix ing from zhe ngbaosha n_drpt where ( che ngbe n_jin+fashe ngji ner) > 1000.00修改后：Select yewuleixi ng from11. 列出所有零售記錄明細(xì)，并按照發(fā)生日期降序顯示；Select * from zhengbaoshan_drpt where yewuleixing= '零售'order by

16、 fasheng_date desc 以上均需在培訓(xùn)環(huán)境數(shù)據(jù)庫中創(chuàng)建、執(zhí)行，并提供腳本另一張表中包含如下列屬性和記錄信息:代碼名稱類別00001可口可樂飲料00002餐巾紙日用品00003一次性紙杯日用品00004康師傅方便面食品00005阿明瓜子食品00006旺旺果奶飲料00007旺仔小饅頭食品00008嬰兒紙尿褲嬰兒用品00009康師傅酸梅湯飲料00010圓珠筆文具1. 創(chuàng)建該表，表名定義(注意字段名使用英文):創(chuàng)建者姓名全拼音_GOODSCREATE TABLE HD40 . ZHENGBAOSHAN_GOODS(DAIMAVARCHAR2(100BYTE),MINGCHEN VARCH

17、AR2( 255 BYTE),LEIBIEVARCHAR2( 255 BYTE)）2.插入表中的記錄信息;in sertintozhengbaoshan_goodsvalues('00002','餐巾紙','日用品）；in sertintozhengbaoshan_goodsvalues('00003','一次性紙杯','日用品');in sertintozhengbaoshan goodsvalues('00004','康師傅方便面11口 1.,食品 );in sertintozh

18、engbaoshan_goodsvalues('00005','阿明瓜子'食品）；in sertintozhengbaoshan goodsvalues('00006','旺旺果奶''飲料）；in sertintozhengbaoshan goodsvalues('00007','旺仔小饅頭11口 1., 食品 );in sertintozhengbaoshan_goodsvalues('00008','嬰兒紙尿褲,'嬰兒用');in sertintozhen

19、gbaoshan goodsvalues('00009','康師傅酸梅湯','飲料');in sertintozhengbaoshan_goodsvalues('00010','圓珠筆','文具'）;3. 合計每種類別各業(yè)務(wù)類型的發(fā)生總額；select sum( fasheng_jine)總額,b . LEIBIEfrom zhe ngbaosha n_drpt a, zhe ngbaosha n_goods b where a.sha ngpin_no=b.l eibie order by b.l

20、eibie4. 修改文具類的調(diào)撥出業(yè)務(wù)，成本金額、發(fā)生金額均增加45.00 ；updatezhengbaoshan_drptaset fasheng_jine =( fasheng_jine+45.00 ),CHENGBEN_JINE =( chengben_jine+45.00 )a, zhengbaoshan_goodsb. daima =a. shangpin_no ')b whereand b . leibie =' 文具' and a. yewuleixing='調(diào)撥岀where (a. shangpin_noin (select a. shangpi

21、n_nofrom zhengbaoshan_drpt以上均需在培訓(xùn)環(huán)境數(shù)據(jù)庫中創(chuàng)建、執(zhí)行，并提供腳本;B類題另一張表中包含如下列屬性和記錄信息:門店代碼門店名稱1000000總部1000001測試門店11000002測試門店21000003測試門店31. 創(chuàng)建該表，表名定義(注意字段名使用英文)：創(chuàng)建者姓名全拼音_STORECREATE TABLE HD40 . ZHENGBAOSHAN_STORE(MENDIANDAIMAVARCHAR2( 100 BYTE),MENDIANMINCHENVARCHAR2( 100 BYTE)2. 插入表中的記錄信息；in sertintozhe ngbao

22、sha n storevalues('1000000','總部');in sertintozhe ngbaosha n_storevalues('1000001','測試門店1');in sertintozhe ngbaosha n storevalues('1000002','測試門店2');in sertintozhe ngbaosha n_storevalues('1000003','測試門店3');3.查詢統(tǒng)配出業(yè)務(wù)的明細(xì)信息，包含發(fā)生日期、業(yè)務(wù)類型、商品代碼

23、、商品名稱、數(shù)量、 發(fā)生門店代碼、發(fā)生門店名稱、收貨門店代碼、收貨門店名稱；SELECT a. fasheng_date, a. yewuleixing, a. shangpin_no, b . mingchena. shangpin number,a. fasheng mendian, c . mendianminchen發(fā)生門店名稱,a. shouhuo_mendiand . mendianminchen收獲門店名稱FROM zhengbaoshan_drpta,zhengbaoshan_goodsb ,zhe ngbaosha n_storec ,zhe ngbaosha n_store

24、dWHERE a. shangpin_no= b.daimaAND a. yewuleixing='統(tǒng)配出AND a. fasheng_mendian=c . mendiandaimaAND a. shouhuo_mendian=d . mendiandaima4.按發(fā)生日期和門店匯總查詢的銷售情況，要求顯示：發(fā)生日期、發(fā)生單位、發(fā)生單位代碼、零售發(fā)生總額；SELECT a. fasheng date,a. fasheng mendian, c . mendianminchen發(fā)生單位,SUM (a. fasheng_jine)FROM zhengbaoshan_drpta, zhe

25、ngbaosha n_storecWHERE a. yewuleixing='零售' AND a. fasheng_mendian= c. mendiandaimaGROUP BY a. fasheng_datea. fasheng_mendian, c . mendianminchen5. 按發(fā)生日期和門店、類別匯總查詢的銷售情況，要求顯示：發(fā)生日期、發(fā)生單位、發(fā)生 單位代碼、類別、零售發(fā)生總額、毛利總額(公式：發(fā)生總額-成本總額)，并按照毛利總額降序排序；SELECT a. fasheng date, c . mendianminchen發(fā)生單位,a. fasheng_mendian, b. leibie , SUM (a. fasheng_jine)零售發(fā)生總額,sum( fasheng_jine) - sum( chengben_jine)毛利總額FROM zhe