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

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

2、00011100200.000200.00010000000100000002011/2/111自营进00002210001000.001000.0010000000100000002011/2/111零售000055100300.000400.00010000000100000002011/2/122自营进00003320340.60040.60010000000100000002011/2/122溢余0000447003500.003500.0010000000100000002011/2/122溢余0000558002400.002400.0010000000100000002011/2

3、/122零售0000552060.00080.00010000000100000002011/2/255溢余0000669601920.001920.0010000000100000002011/3/1统配出0000777302920.003500.0010000000100000012011/3/6统配出000088220.00025.00010000000100000012011/3/7统配出0000993001200.001300.0010000000100000022011/3/7零售00006650100.000150.00010000000100000002011/3/7调拨出00

4、01004545.00045.00010000000100000002011/3/111调拨出00011110500.000500.00010000000100000002011/3/111零售000088770105.00010000000100000002011/3/111零售000033204.0010.0001000000010000000创建该表，表表名定义（注意字段名使用英文）：创建者姓名全拼音_DRPT；Creatte TABLLE HDD40.ZHENNGBAOOSHANN_DRPPT( FASSHENGG_DATTE DAATE, YEWWULEIIXINGG VAARCHA

5、AR2(255 BYTEE), SHAANGPIIN_NOO NUUMBERR, SHAANGPIIN_NUUMBERR NUUMBERR, CHEENGBEEN_JIINE NUUMBERR(8,22), FASSHENGG_JINNE NUUMBERR(8,22), FASSHENGG_MENNDIANN INNTEGEER, SHOOUHUOO_MENNDIANN NUUMBERR)插入表中的的记录信息息；Inserrt intoo zhenngbaooshann_drppt valuues (20111/1/4,自营进,000001,100,200.00,200.00,10000000

6、,10000000);Inserrt intoo zhenngbaooshann_drppt valuues (20111/2/11,自营进,000002,10000,10000.00,10000.00,10000000,10000000);Inserrt intoo zhenngbaooshann_drppt valuues (20111/2/11,零售,000005,100,300.00,400.00,10000000,10000000);Inserrt intoo zhenngbaooshann_drppt valuues (20111/2/12,自营进,000003,203,40.66

7、0,40.660,10000000,10000000);Inserrt intoo zhenngbaooshann_drppt valuues (20111/2/12,溢余,000004,700,35000.00,35000.00,10000000,10000000);Inserrt intoo zhenngbaooshann_drppt valuues (20111/2/12,溢余,000005,800,24000.00,24000.00,10000000,10000000);Inserrt intoo zhenggbaosshan_drptt valuues (20111/2/12,零售,

8、000005,20,60.000,80.000,10000000,10000000);Inserrt intoo zhenngbaooshann_drppt valuues (20111/2/25,溢余,000006,960,19200.00,19200.00,10000000,10000000);Inserrt intoo zhenngbaooshann_drppt valuues (20111/3/1,统配出,000007,730,29200.00,3500.00,10000000,10000001);Inserrt intoo zhenngbaooshann_drppt valuues

9、(20111/3/6,统配出,000008,2,20.000,25.000,10000000,10000001);Inserrt intoo zhenngbaooshann_drppt valuues (20111/3/7,统配出,000009,300,12000.00,13000.00,10000000,10000002);Inserrt intoo zhenngbaooshann_drppt valuues (20111/3/77,零售,000006,50,100.00,150.00,10000000,10000000);Inserrt intoo zhenngbaooshann_drpp

10、t valuues (20111/3/7,调拨出,000110,45,45.000,45.000,10000000,10000000);Inserrt intoo zhenngbaooshann_drppt valuues (20111/3/11,调拨出,000111,10,500.00,500.00,10000000,10000000);Inserrt intoo zhenngbaooshann_drppt valuues (20111/3/11,零售,000008,7,70,105.00,10000000,10000000);Inserrt intoo zhenngbaooshann_dr

11、ppt valuues (20111/3/11,零售,000003,20,4.000,10.000,10000000,10000000);删除商品代代码为000111的记录；Delette ffrom zhenngbaooshann_drppt whhere shanngpinn_n0=000111将发生日期期为20111/2/12，商商品代码为为000004，且业务务为” 溢余”的记录，数数量调整成成70，成本金金额调整成成350.00，发生金金额调整成成350.00；updatte zhenngbaooshann_drppt set sshanggpin_numbber=70,chenng

12、benn_jinne=350.00,fashheng_jinee=350.00 wherre shhangppin_nno=000004 and yyewulleixiing=溢余将发生日期期为20111/3/6，商品品代码为000008，且业务为为” 统配出”的记录，数数量增加1，成本金额额增加2，发生金金额增加5；UPDATTE zhenngbaooshann_drppt SEET shhangppin_nnumbeer = shaangpiin_nuumberr + 1, chhengbben_jjine = cheengbeen_jiine + 2, faashenng_jiine =

13、 fasshengg_jinne + 5 WHERRE faashenng_daate = TO_DDATE (20111/3/6, yyyyy/mmm/dd) ANND shhangppin_nno = 000008 and yyewulleixiing=统配出对发生日期期及业务类类型字段创创建索引，索索引名称定定义：创建建者姓名全全拼音_IDX_DRPTT；creatte indeex zhhengbbaoshhan_iindexx_drppt onn zhenngbaooshann_drppt (fashheng_datee,yewuuleixxing)统计零售业业务的发生生金额合计计；

14、Selecct suum(faashenng_jiine) fromm zheengbaaoshaan_drrpt wwheree yewwuleiixingg=零售统计发生日日期为20011/33/1，商商品代码为为00007的配货业业务的配货货单价（公公式：发生生金额/数量）Selecct faashenng_jiine/sshanggpin_numbber 零售价 froom zhhengbbaoshhan_ddrpt wherre faashenng_daate=tto_daate(20111/03/01,yyyyy/mm/dd) annd shhangppin_nno=000007统

15、计每种业业务类型的的发生成本本总额；Selecct,sum(chenngbenn_jinne+fashheng_jinee),yewwuleiixingg from zhenngbaooshann_drppt grouup by yewuuleixxing统计发生成成本总额超超过10000.00的业务类类型；Selecct yeewuleeixinng frrom zzhenggbaosshan_drptt wheere ( cheengbeen_jiin+faashenngjinner) 10000.000列出所有零零售记录明明细，并按按照发生日日期降序显显示；Selecct * fromm

16、 zheengbaaoshaan_drrpt wwheree yewwuleiixingg=零售 ordder bby faashenng_daate ddesc以上均需在在培训环境境数据库中中创建、执执行，并提提供脚本另一张表中中包含如下下列属性和和记录信息息：代码名称类别000011可口可乐饮料000022餐巾纸日用品000033一次性纸杯杯日用品000044康师傅方便便面食品000055阿明瓜子食品000066旺旺果奶饮料000077旺仔小馒头头食品000088婴儿纸尿裤裤婴儿用品000099康师傅酸梅梅汤饮料000100圆珠笔文具创建该表，表表名定义（注意字段名使用英文）：创建者姓名全

17、拼音_GOODS；CREATTE TABLLE HDD40.ZHENGGBAOSSHAN_GOODDS( DAIIMA VVARCHHAR2(100 BYTEE), MINNGCHEEN VVARCHHAR2(255 BYTEE), LEIIBIE VVARCHHAR2(255 BYTEE)插入表中的的记录信息息；inserrt intoo zhenngbaooshann_gooods valuues (000002,餐巾纸,日用品);inserrt intoo zhenngbaooshann_gooods valuues (000003,一次性纸纸杯,日用品);inserrt intoo z

18、henngbaooshann_gooods valuues (000004,康师傅方方便面,食品);inserrt intoo zhenngbaooshann_gooods valuues (000005,阿明瓜子,食品);inserrt intoo zhenngbaooshann_gooods valuues (000006,旺旺果奶,饮料);inserrt intoo zhenngbaooshann_gooods valuues (000007,旺仔小馒馒头,食品);inserrt intoo zhenggbaosshan_goodds valuues (000008,婴儿纸尿尿裤,婴儿用

19、);inserrt intoo zhenngbaooshann_gooods valuues (000009,康师傅酸酸梅汤,饮料);inserrt intoo zhenngbaooshann_gooods valuues (000010,圆珠笔,文具);合计每种类类别各业务务类型的发发生总额；selecct sum(fashheng_jinee) 总额, b.LEIBBIEfrom zhenngbaooshann_drppt a, zheengbaaoshaan_gooods b wheree a.sshanggpin_no=bb.leiibie ordeer byy b.lleibiie修

20、改文具类类的调拨出出业务，成成本金额、发生金额额均增加45.000；updatte zhenngbaooshann_drppt aset ffasheeng_jjine=(fasshengg_jinne+45.000), CHHENGBBEN_JJINE=(cheengbeen_jiine+45.000)wheree (a.shhangppin_nno inn (sellect a.shaangpiin_noo froom zhenngbaooshann_drppt a,zheengbaaoshaan_gooods b whhere b.daiima=aa.shaangpiin_noo and

21、d b.leibbie=文具 and a.yewwuleiixingg=调拨出)以上均需在在培训环境境数据库中中创建、执执行，并提提供脚本；B类题另一张表中中包含如下下列属性和和记录信息息：门店代码门店名称10000000总部10000001测试门店1110000002测试门店2210000003测试门店33创建该表，表表名定义（注注意字段名名使用英文文）：创建建者姓名全全拼音_STORRE；CREATTE TABLLE HDD40.ZHENNGBAOOSHANN_STOORE( MENNDIANNDAIMMA VARRCHARR2(100 BYTEE), MENNDIANNMINCCHEN

22、VARRCHARR2(100 BYTEE)插入表中的的记录信息息；inserrt intoo zhenngbaooshann_stoore valuues (100000000,总部);inserrt intoo zhenngbaooshann_stoore valuues (100000011,测试门店1);inserrt intoo zhenngbaooshann_stoore valuues (100000022,测试门店2);inserrt intoo zhenngbaooshann_stoore valuues (100000033,测试门店3);查询统配出出业务的明明细信息，包包含

23、发生日日期、业务务类型、商商品代码、商品名称称、数量、发生门店店代码、发发生门店名名称、收货货门店代码码、收货门门店名称；SELECCT a.fasshengg_datte, a.yewwuleiixingg, a.shaangpiin_noo, b.mingcchen, a.shanggpin_numbber, a.fasshengg_menndiann, c.menddianmminchhen 发生门店店名称,aa.shoouhuoo_menndiann, d.menddianmminchhen 收获门店店名称 FROOM zhenngbaooshann_drppt a, zhhengbb

24、aoshhan_ggoodss b, zhhengbbaoshhan_sstoree c, zhhengbbaoshhan_sstoree d WHERRE a.shaangpiin_noo = b.daimma ANND a.yewulleixiing = 统配出 ANND a.fasshengg_menndiann = c.menddianddaimaa ANND a.shoouhuoo_menndiann = d.menddianddaimaa按发生日期期和门店汇汇总查询的的销售情况况，要求显显示：发生生日期、发发生单位、发生单位位代码、零零售发生总总额；SELECCT a.fasshe

25、ngg_datte, a.fasshengg_menndiann, c.menddianmminchhen 发生单位位,SUMM (a.faashenng_jiine) FFROM zhenngbaooshann_drppt a, zhenngbaooshann_stoore cc WHHERE a.yewwuleiixingg = 零售 AND a.fasshengg_menndiann = c.menddianddaimaaGROUPP BY a.fasshengg_datte, a.fasshengg_menndiann, c.menddianmminchhen按发生日期期和门店、类别汇

26、总总查询的销销售情况，要要求显示：发生日期期、发生单单位、发生生单位代码码、类别、零售发生生总额、毛毛利总额（公公式：发生生总额-成本总额额），并按按照毛利总总额降序排排序；SELECCT a.fasshengg_datte, c.menddianmminchhen 发生单位位, a.fasshengg_menndiann,b.leibbie, SUM (a.faashenng_jiine) 零售发生生总额, sum(fashheng_jinee) - sum(chenngbenn_jinne) 毛利总额额 FROMM zhenngbaooshann_drppt a, zhenngbaooshann_gooods bb,zhenngbaooshann_stoore c