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

Oracle代码阅读能力及调试跟踪方法系统考核题答题规则时间：原则上不限，但尽量在连续的4个小时内答完；方式：除了与本次学习的学员沟通、请人代做以外的任意方式，含网络搜索等；（即要求独立解决问题能力）要求：为了对其他学员负责，请先参加考核的学员，不要将考核题及结果转给未参与考核的学员，未参与考核的学员也不要询问考题内容及结果；另，注意署名，且写上答题耗费时间oracle基础数据操作语法C类题一张表中包含如下列属性和记录信息：发生日期业务类型商品代码数量成本金额发生金额发生门店收货门店2011/1/4自营进00001100200.00200.00100000010000002011/2/11自营进0000210001000.001000.00100000010000002011/2/11零售00005100300.00400.00100000010000002011/2/12自营进0000320340.6040.60100000010000002011/2/12溢余000047003500.003500.00100000010000002011/2/12溢余000058002400.002400.00100000010000002011/2/12零售000052060.0080.00100000010000002011/2/25溢余000069601920.001920.00100000010000002011/3/1统配出000077302920.003500.00100000010000012011/3/6统配出00008220.0025.00100000010000012011/3/7统配出000093001200.001300.00100000010000022011/3/7零售0000650100.00150.00100000010000002011/3/7调拨出000104545.0045.00100000010000002011/3/11调拨出0001110500.00500.00100000010000002011/3/11零售00008770105.00100000010000002011/3/11零售00003204.0010.00100000010000001. 创建该表，表名定义（注意字段名使用英文）：创建者姓名全拼音_DRPT；Create TABLE HD40.ZHENGBAOSHAN_DRPT( FASHENG_DATE DATE, YEWULEIXING VARCHAR2(255 BYTE), SHANGPIN_NO NUMBER, SHANGPIN_NUMBER NUMBER, CHENGBEN_JINE NUMBER(8,2), FASHENG_JINE NUMBER(8,2), FASHENG_MENDIAN INTEGER, SHOUHUO_MENDIAN NUMBER)2. 插入表中的记录信息；Insert into zhengbaoshan_drpt values (2011/1/4,自营进,00001,100,200.00,200.00,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/2/11,自营进,00002,1000,1000.00,1000.00,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/2/11,零售,00005,100,300.00,400.00,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/2/12,自营进,00003,203,40.60,40.60,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/2/12,溢余,00004,700,3500.00,3500.00,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/2/12,溢余,00005,800,2400.00,2400.00,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/2/12,零售,00005,20,60.00,80.00,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/2/25,溢余,00006,960,1920.00,1920.00,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/3/1,统配出,00007,730,2920.00,3500.00,1000000,1000001);Insert into zhengbaoshan_drpt values (2011/3/6,统配出,00008,2,20.00,25.00,1000000,1000001);Insert into zhengbaoshan_drpt values (2011/3/7,统配出,00009,300,1200.00,1300.00,1000000,1000002);Insert into zhengbaoshan_drpt values (2011/3/7,零售,00006,50,100.00,150.00,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/3/7,调拨出,00010,45,45.00,45.00,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/3/11,调拨出,00011,10,500.00,500.00,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/3/11,零售,00008,7,70,105.00,1000000,1000000);Insert into zhengbaoshan_drpt values (2011/3/11,零售,00003,20,4.00,10.00,1000000,1000000);3. 删除商品代码为00011的记录；Delete from zhengbaoshan_drpt where shangpin_n0=000114. 将发生日期为2011/2/12，商品代码为00004，且业务为” 溢余”的记录，数量调整成70，成本金额调整成350.00，发生金额调整成350.00；update zhengbaoshan_drpt set shangpin_number=70,chengben_jine=350.00,fasheng_jine=350.00 where shangpin_no=00004 and yewuleixing=溢余5. 将发生日期为2011/3/6，商品代码为00008，且业务为” 统配出”的记录，数量增加1，成本金额增加2，发生金额增加5；UPDATE zhengbaoshan_drpt SET shangpin_number = shangpin_number + 1, chengben_jine = chengben_jine + 2, fasheng_jine = fasheng_jine + 5 WHERE fasheng_date = TO_DATE (2011/3/6, yyyy/mm/dd) AND shangpin_no = 00008 and yewuleixing=统配出6. 对发生日期及业务类型字段创建索引，索引名称定义：创建者姓名全拼音_IDX_DRPT；create index zhengbaoshan_index_drpt on zhengbaoshan_drpt (fasheng_date,yewuleixing)7. 统计零售业务的发生金额合计；Select sum(fasheng_jine) from zhengbaoshan_drpt where yewuleixing=零售8. 统计发生日期为2011/3/1，商品代码为00007的配货业务的配货单价（公式：发生金额/数量）Select fasheng_jine/shangpin_number 零售价 from zhengbaoshan_drpt where fasheng_date=to_date(2011/03/01,yyyy/mm/dd) and shangpin_no=000079. 统计每种业务类型的发生成本总额；Select,sum(chengben_jine+fasheng_jine),yewuleixing from zhengbaoshan_drpt group by yewuleixing10. 统计发生成本总额超过1000.00的业务类型；Select yewuleixing from zhengbaoshan_drpt where ( chengben_jin+fashengjiner) 1000.0011. 列出所有零售记录明细，并按照发生日期降序显示；Select * from zhengbaoshan_drpt where yewuleixing=零售 order by fasheng_date desc以上均需在培训环境数据库中创建、执行，并提供脚本另一张表中包含如下列属性和记录信息：代码名称类别00001可口可乐饮料00002餐巾纸日用品00003一次性纸杯日用品00004康师傅方便面食品00005阿明瓜子食品00006旺旺果奶饮料00007旺仔小馒头食品00008婴儿纸尿裤婴儿用品00009康师傅酸梅汤饮料00010圆珠笔文具1. 创建该表，表名定义（注意字段名使用英文）：创建者姓名全拼音_GOODS；CREATE TABLE HD40.ZHENGBAOSHAN_GOODS( DAIMA VARCHAR2(100 BYTE), MINGCHEN VARCHAR2(255 BYTE), LEIBIE VARCHAR2(255 BYTE)2. 插入表中的记录信息；insert into zhengbaoshan_goods values (00002,餐巾纸,日用品);insert into zhengbaoshan_goods values (00003,一次性纸杯,日用品);insert into zhengbaoshan_goods values (00004,康师傅方便面,食品);insert into zhengbaoshan_goods values (00005,阿明瓜子,食品);insert into zhengbaoshan_goods values (00006,旺旺果奶,饮料);insert into zhengbaoshan_goods values (00007,旺仔小馒头,食品);insert into zhengbaoshan_goods values (00008,婴儿纸尿裤,婴儿用);insert into zhengbaoshan_goods values (00009,康师傅酸梅汤,饮料);insert into zhengbaoshan_goods values (00010,圆珠笔,文具);3. 合计每种类别各业务类型的发生总额；select sum(fasheng_jine) 总额, b.LEIBIEfrom zhengbaoshan_drpt a, zhengbaoshan_goods b where a.shangpin_no=b.leibie order by b.leibie4. 修改文具类的调拨出业务，成本金额、发生金额均增加45.00；update zhengbaoshan_drpt aset fasheng_jine=(fasheng_jine+45.00), CHENGBEN_JINE=(chengben_jine+45.00)where (a.shangpin_no in (select a.shangpin_no from zhengbaoshan_drpt a,zhengbaoshan_goods b where b.daima=a.shangpin_no and b.leibie=文具 and a.yewuleixing=调拨出)以上均需在培训环境数据库中创建、执行，并提供脚本；B类题另一张表中包含如下列属性和记录信息：门店代码门店名称1000000总部1000001测试门店11000002测试门店21000003测试门店31. 创建该表，表名定义（注意字段名使用英文）：创建者姓名全拼音_STORE；CREATE TABLE HD40.ZHENGBAOSHAN_STORE( MENDIANDAIMA VARCHAR2(100 BYTE), MENDIANMINCHEN VARCHAR2(100 BYTE)2. 插入表中的记录信息；insert into zhengbaoshan_store values (1000000,总部);insert into zhengbaoshan_store values (1000001,测试门店1);insert into zhengbaoshan_store values (1000002,测试门店2);insert into zhengbaoshan_store values (1000003,测试门店3);3. 查询统配出业务的明细信息，包含发生日期、业务类型、商品代码、商品名称、数量、发生门店代码、发生门店名称、收货门店代码、收货门店名称；SELECT a.fasheng_date, a.yewuleixing, a.shangpin_no, b.mingchen, a.shangpin_number, a.fasheng_mendian, c.mendianminchen 发生门店名称,a.shouhuo_mendian, d.mendianminchen 收获门店名称 FROM zhengbaoshan_drpt a, zhengbaoshan_goods b, zhengbaoshan_store c, zhengbaoshan_store d WHERE a.shangpin_no = b.daima AND a.yewuleixing = 统配出 AND a.fasheng_mendian = c.mendiandaima AND a.shouhuo_mendian = d.mendiandaima4. 按发生日期和门店汇总查询的销售情况，要求显示：发生日期、发生单位、发生单位代码、零售发生总额；SELECT a.fasheng_date, a.fasheng_mendian, c.mendianminchen 发生单位,SUM (a.fasheng_jine) FROM zhengbaoshan_drpt a, zhengbaoshan_store c WHERE a.yewuleixing = 零售 AND a.fasheng_mendian = c.mendiandaimaGROUP BY a.fasheng_date, a.fasheng_mendian, c.mendianminchen5. 按发生日期和门店、类别汇总查询的销售情况，要求显示：发生日期、发生单位、发生单位代码、类别、零售发生总额、毛利总额（公式：发生总额-成本总额），并按照毛利总额降序排序；SELECT a.fasheng_date, c.mendianminchen 发生单位, a.fasheng_mendian,b.leibie, SUM (a.fasheng_jine) 零售发生总额, sum(fasheng_jine) - sum(chengben_jine) 毛利总额 FROM zhengbaoshan_drpt a, zhengbaoshan_goods b,zhengbaoshan