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. 插入表中的记录信息；i