Oracle代码阅读能力及调试跟踪方法系统考核题

1、Oracle代代码阅读能力力及调试跟踪踪方法系统考考核题答题规则时间：原则上不不限，但尽量量在连续的4个小时内答答完；方式：除了与本本次学习的学学员沟通、请请人代做以外外的任意方式式，含网络搜搜索等；（即即要求独立解解决问题能力力）要求：为了对其其他学员负责责，请先参加加考核的学员员，不要将考考核题及结果果转给未参与与考核的学员员，未参与考考核的学员也也不要询问考考题内容及结结果；另，注意署名，且且写上答题耗耗费时间oracle基基础数据操作作语法C类题一张表中包含如如下列属性和和记录信息：发生日期业务类型商品代码数量成本金额发生金额发生门店收货门店2011/1/4自营进00001100200

2、.00200.0010000000100000002011/2/11自营进0000210001000.0001000.00010000000100000002011/2/11零售00005100300.00400.0010000000100000002011/2/12自营进0000320340.6040.6010000000100000002011/2/12溢余000047003500.0003500.00010000000100000002011/2/12溢余000058002400.0002400.00010000000100000002011/2/12零售000052060.0080.0

3、010000000100000002011/2/25溢余000069601920.0001920.00010000000100000002011/3/1统配出000077302920.0003500.00010000000100000112011/3/6统配出00008220.0025.0010000000100000112011/3/7统配出000093001200.0001300.00010000000100000222011/3/7零售0000650100.00150.0010000000100000002011/3/7调拨出000104545.0045.0010000000100000

4、002011/3/11调拨出0001110500.00500.0010000000100000002011/3/11零售00008770105.0010000000100000002011/3/11零售00003204.0010.001000000010000000创建该表，表名名定义（注意意字段名使用用英文）：创建者姓姓名全拼音_DRPT；Create TABLEE HD400.ZHENGGBAOSHHAN_DRRPT( FASHEENG_DAATE DATTE, YEWULLEIXINNG VARRCHAR22(255 BYTE), SHANGGPIN_NNO NUMMBER, SHANG

5、GPIN_NNUMBERR NUMMBER, CHENGGBEN_JJINE NUMMBER(88,2), FASHEENG_JIINE NUMMBER(88,2), FASHEENG_MEENDIANN INTTEGER, SHOUHHUO_MEENDIANN NUMMBER)插入表中的记录录信息；Insert into zhenggbaoshhan_drrpt valuees (20111/1/4,自营进,000011,100,200.000,200.000,10000000,10000000);Insert into zhenggbaoshhan_drrpt valuees (20111

6、/2/111,自营进,000022,1000,1000.00,1000.00,10000000,10000000);Insert into zhenggbaoshhan_drrpt valuees (20111/2/111,零售,000055,100,300.000,400.000,10000000,10000000);Insert into zhenggbaoshhan_drrpt valuees (20111/2/122,自营进,000033,203,40.600,40.600,10000000,10000000);Insert into zhenggbaoshhan_drrpt valu

7、ees (20111/2/122,溢余,000044,700,3500.00,3500.00,10000000,10000000);Insert into zhenggbaoshhan_drrpt valuees (20111/2/122,溢余,000055,800,2400.00,2400.00,10000000,10000000);Insert into zhengbbaoshaan_drppt valuees (20111/2/122,零售,000055,20,60.000,80.000,10000000,10000000);Insert into zhenggbaoshhan_drrp

8、t valuees (20111/2/255,溢余,000066,960,1920.00,1920.00,10000000,10000000);Insert into zhenggbaoshhan_drrpt valuees (20111/3/1,统配出,000077,730,2920.00,3500.000,10000000,10000001);Insert into zhenggbaoshhan_drrpt valuees (20111/3/6,统配出,000088,2,20.000,25.000,10000000,10000001);Insert into zhenggbaoshhan_

9、drrpt valuees (20111/3/7,统配出,000099,300,1200.00,1300.00,10000000,10000002);Insert into zhenggbaoshhan_drrpt valuees (2011/3/7,零售,000066,50,100.000,150.000,10000000,10000000);Insert into zhenggbaoshhan_drrpt valuees (20111/3/7,调拨出,000100,45,45.000,45.000,10000000,10000000);Insert into zhenggbaoshhan_

10、drrpt valuees (20111/3/111,调拨出,000111,10,500.000,500.000,10000000,10000000);Insert into zhenggbaoshhan_drrpt valuees (20111/3/111,零售,000088,7,70,105.000,10000000,10000000);Insert into zhenggbaoshhan_drrpt valuees (20111/3/111,零售,000033,20,4.00,10.000,10000000,10000000);删除商品代码为为00011的记录；Delete fromm

11、zhenngbaosshan_ddrpt wwhere shanggpin_nn0=000111将发生日期为22011/22/12，商商品代码为00004，且业务为为” 溢余”的记录，数数量调整成70，成本金额额调整成350.000，发生金额额调整成350.000；update zhenggbaoshhan_drrpt set shhangpiin_nummber=70,chenggben_jjine=350.00,fasheeng_jiine=350.00 wheree shanngpin_no=000004 and yeewuleiixing=溢余将发生日期为22011/33/6，商品品代

12、码为00008，且业务为” 统配出”的记录，数数量增加1，成本金额增增加2，发生金额额增加5；UPDATE zhenggbaoshhan_drrpt SET shanggpin_nnumberr = shanngpin_numbeer + 1, chenggben_jjine = chenngben_jine + 2, fasheeng_jiine = fashheng_jjine + 5 WHERE fasheeng_daate = TO_DAATE (20111/3/6, yyyyy/mm/ddd) AND shanggpin_nno = 000008 and yeewuleiixing=

13、统配出对发生日期及业业务类型字段段创建索引，索索引名称定义义：创建者姓姓名全拼音_IDX_DDRPT；create indexx zhenngbaosshan_iindex_drpt on zhenggbaoshhan_drrpt (fasheeng_daate,yewulleixinng)统计零售业务的的发生金额合合计；Select sum(ffashenng_jinne) frrom zhhengbaaoshann_drptt wherre yewwuleixxing=零售统计发生日期为为2011/3/1，商商品代码为00007的配货业务务的配货单价价（公式：发发生金额/数量）Select

14、 fasheeng_jiine/shhangpiin_nummber 零售价 fromm zhenngbaosshan_ddrpt wwhere fasheeng_daate=too_datee(2011/03/011,yyyy/mm/ddd) andd shanngpin_no=000077统计每种业务类类型的发生成成本总额；Select,sum(chhengbeen_jinne+fasheeng_jiine),yewuuleixiing from zhhengbaaoshann_drptt groupp by yewulleixinng统计发生成本总总额超过1000.00的业务类型型；Se

15、lect yewulleixinng froom zheengbaooshan_drpt wheree ( chhengbeen_jinn+fashhengjiiner) 10000.00列出所有零售记记录明细，并并按照发生日日期降序显示示；Select * froom zheengbaooshan_drpt wheree yewuuleixiing=零售 ordeer by fasheeng_daate deesc以上均需在培训训环境数据库库中创建、执执行，并提供供脚本另一张表中包含含如下列属性性和记录信息息：代码名称类别00001可口可乐饮料00002餐巾纸日用品00003一次性纸杯日用品

16、00004康师傅方便面食品00005阿明瓜子食品00006旺旺果奶饮料00007旺仔小馒头食品00008婴儿纸尿裤婴儿用品00009康师傅酸梅汤饮料00010圆珠笔文具创建该表，表名名定义（注意意字段名使用用英文）：创建者姓姓名全拼音_GOODS；CREATE TABLEE HD400.ZHENGBBAOSHAAN_GOOODS( DAIMAA VARCHHAR2(100 BYTE), MINGCCHEN VARCHHAR2(255 BYTE), LEIBIIE VARCHHAR2(255 BYTE)插入表中的记录录信息；insert into zhenggbaoshhan_gooods va

17、luees (000002,餐巾纸,日用品);insert into zhenggbaoshhan_gooods valuees (000003,一次性纸杯,日用品);insert into zhenggbaoshhan_gooods valuees (000004,康师傅方便便面,食品);insert into zhenggbaoshhan_gooods valuees (000005,阿明瓜子,食品);insert into zhenggbaoshhan_gooods valuees (000006,旺旺果奶,饮料);insert into zhenggbaoshhan_gooods va

18、luees (000007,旺仔小馒头,食品);insert into zhengbbaoshaan_gooods valuees (000008,婴儿纸尿裤,婴儿用);insert into zhenggbaoshhan_gooods valuees (000009,康师傅酸梅梅汤,饮料);insert into zhenggbaoshhan_gooods valuees (000110,圆珠笔,文具);合计每种类别各各业务类型的的发生总额；select sum(faashengg_jinee) 总额, b.LEIBIIEfrom zhhengbaaoshann_drptt a, zzhen

19、gbbaoshaan_gooods b where aa.shanngpin_no=b.leibiie ordder byy b.leeibie修改文具类的调调拨出业务，成成本金额、发发生金额均增增加45.00；update zhenggbaoshhan_drrpt aset fassheng_jine=(fashheng_jjine+45.000), CHEENGBENN_JINEE=(chenngben_jine+45.000)where (a.shaangpinn_no iin (seleect a.shanngpin_no frrom zhengbbaoshaan_drppt a,zh

20、enngbaosshan_ggoods b wheere b.daimaa=a.shaangpinn_no aand b.leibiie=文具 and a.yewuuleixiing=调拨出)以上均需在培训训环境数据库库中创建、执执行，并提供供脚本；B类题另一张表中包含含如下列属性性和记录信息息：门店代码门店名称10000000总部10000011测试门店110000022测试门店210000033测试门店3创建该表，表名名定义（注意意字段名使用用英文）：创创建者姓名全全拼音_STORE；CREATE TABLEE HD400.ZHENGGBAOSHHAN_STTORE( MENDIIANDA

21、IIMA VARCCHAR2(100 BYTE), MENDIIANMINNCHEN VARCCHAR2(100 BYTE)插入表中的记录录信息；insert into zhenggbaoshhan_sttore valuees (10000000,总部);insert into zhenggbaoshhan_sttore valuees (10000001,测试门店1);insert into zhenggbaoshhan_sttore valuees (10000002,测试门店2);insert into zhenggbaoshhan_sttore valuees (10000003,测

22、试门店3);查询统配出业务务的明细信息息，包含发生生日期、业务务类型、商品品代码、商品品名称、数量量、发生门店店代码、发生生门店名称、收收货门店代码码、收货门店店名称；SELECT a.fashheng_ddate, a.yewuuleixiing, a.shanngpin_no, b.mingchhen, a.shangppin_nuumber, a.fashheng_mmendiaan, c.mendiianminnchen 发生门店名名称,a.shoouhuo_mendiian, d.mendiianminnchen 收获门店名名称 FROM zhenggbaoshhan_drrpt a

23、, zhenggbaoshhan_gooods bb, zhenggbaoshhan_sttore cc, zhenggbaoshhan_sttore dd WHERE a.shanngpin_no = b.daimaa AND a.yewuleeixingg = 统配出 AND a.fashheng_mmendiaan = c.mendiiandaiima AND a.shouuhuo_mmendiaan = d.mendiiandaiima按发生日期和门门店汇总查询询的销售情况况，要求显示示：发生日期期、发生单位位、发生单位位代码、零售售发生总额；SELECT a.fashheng_dda

24、te, a.fashheng_mmendiaan, c.mendiianminnchen 发生单位,SSUM (a.fassheng_jine) FROOM zhenggbaoshhan_drrpt a, zhenggbaoshhan_sttore cc WHERRE a.yewuuleixiing = 零售 AND a.fashheng_mmendiaan = c.mendiiandaiimaGROUP BBY a.fashheng_ddate, a.fashheng_mmendiaan, c.mendiianminnchen按发生日期和门门店、类别汇汇总查询的销销售情况，要要求显示：发发生

25、日期、发发生单位、发发生单位代码码、类别、零零售发生总额额、毛利总额额（公式：发发生总额-成本总额），并并按照毛利总总额降序排序序；SELECT a.fashheng_ddate, c.mendiianminnchen 发生单位, a.fashheng_mmendiaan,b.leibiie, SUM (a.fassheng_jine) 零售发生总总额, sum(faashengg_jinee) - sum(chhengbeen_jinne) 毛利总额 FROM zzhengbbaoshaan_drppt a, zhenggbaoshhan_gooods bb,zhenggbaoshhan_sttore cc WHERRE a