1111郑宝山 Oracle代码阅读能力及调试跟踪方法系统考核题 oracle基础数据操作语法 不合格

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

2、0.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.001000000100000020

3、11/2/25溢余000069601920.001920.00100000010000002011/31统配出000077302920.003500.00100000010000012011/36统配出00008220.0025.00100000010000012011/37统配出000093001200.001300.00100000010000022011/37零售0000650100.00150.00100000010000002011/37调拨出000104545.0045.00100000010000002011/311调拨出0001110500.00500.001000000100

4、00002011/311零售00008770105.00100000010000002011/311零售00003204.0010.00100000010000001.创建该表，表名定义（注意字段名使用英文）:创建者姓名全拼音_DRPTCreate TABLE HD40 . ZHENGBAOSHAN_DRPT( 一FASHENG_DATEDATE,SHANGPIN_NONUMBERSHANGPIN NUMBERNUMBERCHENGBEN_JINENUMBER(8,2),FASHENG JINENUMBER(8,2),FASHENG_MENDIANINTEGER ,SHOUHUO MENDIA

5、NNUMBER)2.插入表中的记录信息;In sertintozhengbaoshan_drptvalues(2011/1/4，自营进,00001,100200.00 , 200.00 ,1000000 , 1000000 );In sertintozhengbaoshan_drptvalues(2011/2/11,自营进,00002,1000,1000.00 , 1000.00,1000000,1000000 );In sertintozhengbaoshan_drptvalues(2011/2/11,零售,00005,100 ,300.00, 400.00, 1000000 , 10000

6、00 );In sertintozhengbaoshan drptvalues(2011/2/12,自营进,00003,203 ,40.60 , 40.60 , 1000000 , 1000000 );In sertintozhengbaoshan drptvalues(2011/2/12,溢余,00004,700 ,3500.00, 3500.00,1000000 ,1000000 );In sertintozhengbaoshan_drptvalues(2011/2/12,溢余,00005,800 ,2400.00, 2400.00,1000000 ,1000000 );In sertin

7、tozhengbaoshan_drptvalues(2011/2/12,零售,00005,20 , 60.00 , 80.00 , 1000000 , 1000000 );In sertintozhengbaoshan drptvalues(2011/2/25,溢余,00006,960 ,1920.00, 1920.00,1000000 ,1000000 );In sertintozhengbaoshan_drptvalues(2011/3/1,统配出,00007,730 ,2920.00, 3500.00,1000000 ,1000001 );In sertintozhengbaoshan_

8、drptvalues(2011/3/6,统配出,00008,2, 20.00 , 25.00 , 1000000,1000001);In sertintozhengbaoshan drptvalues(2011/3/7,统配出,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/

9、7,调拨出,00010,45,45.00 , 45.00 , 1000000,1000000)订In sertintozhengbaoshan_drptvalues(2011/3/11,调拨出,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,零售,00003,20,4.00 , 10.

10、00 , 10000001000000);3. 删除商品代码为00011 的记录;Delete from zhe ngbaosha n_drpt where sha ngpin_n0= 000114. 将发生日期为2011/712，商品代码为00004，且业务为”溢余”的记录，数量调整成 70, 成本金额调整成 350.00，发生金额调整成 350.00 ；updatezhe ngbaosha n drptsetshangpin_number =70 , chengben_jine=350 . 00 , fasheng_jine =350 . 00 whereshangpin_no =0000

11、4 and yewuleixing=溢余5. 将发生日期为 2011/36，商品代码为 00008，且业务为”统配出”的记录，数量增加1，成本金额增加2，发生金额增加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_DATE (2011/3/6, yyyy/mm/dd )AND shangpin_no= 00008 an

12、d yewuleixing =统配岀6.对发生日期及业务类型字段创建索引，索引名称定义：创建者姓名全拼音DX_DRPTcreatein dexzhe ngbaosha n in dex drptonzhe ngbaosha n drpt(fasheng_date, yewuleixing)7. 统计零售业务的发生金额合计；Select sum(fasheng_jine) from zhengbaoshan_drpt where yewuleixing= 零售8. 统计发生日期为2011/31，商品代码为00007的配货业务的配货单价(公式：发生金额/数量)Select fashe ng_jin

13、 e/sha ngpin_nu mber零售价 fromzhe ngbaosha n_drptwherefasheng_date=to_date( 2011/03/01,yyyy/mm/dd and shangpin_no= 000079. 统计每种业务类型的发生成本总额；Select,sum( chengben jine+ fasheng jine), yewuleixingfromzhengbaoshan_drptgroup by yewuleixing10. 统计发生成本总额超过1000.00的业务类型；Select yewuleix ing from zhe ngbaosha n_dr

14、pt where ( che ngbe n_jin+fashe ngji ner) 1000.00修改后：Select yewuleixi ng from11. 列出所有零售记录明细，并按照发生日期降序显示；Select * from zhengbaoshan_drpt where yewuleixing= 零售order by fasheng_date desc 以上均需在培训环境数据库中创建、执行，并提供脚本另一张表中包含如下列属性和记录信息:代码名称类别00001可口可乐饮料00002餐巾纸日用品00003一次性纸杯日用品00004康师傅方便面食品00005阿明瓜子食品00006旺旺果奶

15、饮料00007旺仔小馒头食品00008婴儿纸尿裤婴儿用品00009康师傅酸梅汤饮料00010圆珠笔文具1. 创建该表，表名定义(注意字段名使用英文):创建者姓名全拼音_GOODSCREATE TABLE HD40 . ZHENGBAOSHAN_GOODS(DAIMAVARCHAR2(100BYTE),MINGCHEN VARCHAR2( 255 BYTE),LEIBIEVARCHAR2(255BYTE)2. 插入表中的记录信息；in sertintozhengbaoshan goodsvalues(00002,餐巾纸,日用品)；in sertintozhengbaoshan_goodsvalu

16、es(00003,一次性纸杯,日用品)；in sertintozhengbaoshan goodsvalues(00004,康师傅方便面11口 1.,食品 );in sertintozhengbaoshan goodsvalues(00005,阿明瓜子食品)；in sertintozhengbaoshan goodsvalues(00006,旺旺果奶饮料);in sertintozhengbaoshan goodsvalues(00007,旺仔小馒头, 食品 );in sertintozhengbaoshan_goodsvalues(00008,婴儿纸尿裤,婴儿用);in sertintozh

17、engbaoshan goodsvalues(00009,康师傅酸梅汤,饮料);in sertintozhengbaoshan_goodsvalues(00010,圆珠笔,文具);3. 合计每种类别各业务类型的发生总额；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.leibie4. 修改文具类的调拨出业务，成本金额、发生金额均增加 45.00 ；updatezhengbaoshan

18、_drptaset fasheng_jine=( fasheng_jine+ 45.00 ),CHENGBEN JINE =( chengben jine+45.00 )where (a. shangpin_noin (selecta. shangpin_nofrom zhe ngbaosha n_drpta, zhengbaoshan_goodsb whereb. daima =a. shangpin_no and b . leibie = 文具 and a. yewuleixing =调拨岀 )以上均需在培训环境数据库中创建、执行，并提供脚本；B类题另一张表中包含如下列属性和记录信息:门店

19、代码门店名称1000000总部1000001测试门店11000002测试门店21000003测试门店31.创建该表，表名定义(注意字段名使用英文)：创建者姓名全拼音_STORECREATE TABLE HD40 . ZHENGBAOSHAN_STORE(MENDIANDAIMAVARCHAR2( 100 BYTE),MENDIANMINCHENVARCHAR2( 100 BYTE)2.3.)插入表中的记录信息;in sertintozhe ngbaosha n storevalues(1000000,总部);in sertintozhe ngbaosha n_storevalues(10000

20、01,测试门店1);in sertintozhe ngbaosha n storevalues(1000002,测试门店2);in sertintozhe ngbaosha n_storevalues(1000003,测试门店3);查询统配出业务的明细信息，包含发生日期、业务类型、商品代码、商品名称、数量、 发生门店代码、发生门店名称、收货门店代码、收货门店名称；SELECT a. fasheng date, a. yewuleixing, a. shangpin no, b . mingchena. shangpin_number,a.fashe ng_me ndia n,c . mendi

21、anminchen发生门店名称,a. shouhuo_mendian,d .men dia nmin che n收获门店名称FROM zhengbaoshan_drpta,zhengbaoshan goodsb ,zhe ngbaosha n_storec ,zhe ngbaosha n storedWHERE a. shangpin no=b . daimaAND a. yewuleixing=统配岀AND a. fasheng_mendian=c . mendiandaimaAND a. shouhuo mendian=d . mendiandaima4.按发生日期和门店汇总查询的销售情况

22、，要求显示：发生日期、发生单位、发生单位代 码、零售发生总额；SELECT a. fasheng_date,a. fasheng_mendian, c . mendianminchen发生单位,SUM (a. fasheng_jine)FROM zhengbaoshan_drpta, zhe ngbaosha n_storecWHERE a. yewuleixing=零售 AND a. fasheng mendian= c. mendiandaimaGROUP BY a. fasheng_date,a. fasheng_mendian, c . mendianminchen5.按发生日期和门店、类别汇总查询的销售情况，要求显示：发生日期、发生单位、发生 单位代码、类别、零售发生总额、毛利总额(公式：发生总额-成本总额)，并按照毛利总额降序排序；SELECTa. fasheng date, c . mendianminchen发生单位,a. fasheng_mendian, b. leibie , SUM (a. fasheng_jine)零售发生总额,sum( fasheng_jine) - sum( chengben_jine)毛利总额FROM zhengbaoshan_drpta, zhengbaoshan_goodsb, zhengbaoshan_stor