Oracle 实验3

1、实验三：高级SQL语言1. 实验目的：熟悉Oracle10g关于层次、情景、翻译、分析、线性回归等各种高级查询技术。2. 实验内容（1） 练习层次查询（2） 练习情景查询；（3） 练习翻译查询；（4） 练习分析查询；（5） 练习线性回归查询。3. 实验步骤4. 实验总结5. 实验数据和SQL语句层次查询create table bicycle(part_id number(5) constraint pk_bicycle_part_id primary key,parent_id number(5) constraint fk_bicycle_pid references bicycle(pa

2、rt_id),part_name varchar2(30) not null,mp_cost number(9, 2),describe varchar2(30);insert into bicycle values(1, null, '自行车', 379.28, '装配');insert into bicycle values(2, 1, '导向系统', 101.11, '制造');insert into bicycle values(3, 1, '驱动系统', 159.56, '制造');ins

3、ert into bicycle values(4, 1, '其他部分', 118.61, '制造和采购');insert into bicycle values(5, 2, '车把', 37.28, '制造');insert into bicycle values(6, 2, '前叉', 24.35, '制造');insert into bicycle values(7, 2, '前轴', 19.67, '制造');insert into bicycle value

4、s(8, 2, '前轮', 19.81, '制造');insert into bicycle values(9, 7, '前轴棍', 8.16, '制造');insert into bicycle values(10, 7, '前轴身', 4.82, '制造');insert into bicycle values(11, 7, '前轴碗', 6.69, '制造');insert into bicycle values(12, 10, '前轴管', 1

5、.61, '制造');insert into bicycle values(13, 10, '前花盘', 3.21, '制造');insert into bicycle values(14, 3, '脚蹬', 18.99, '制造');insert into bicycle values(15, 3, '中轴', 25.27, '制造');insert into bicycle values(16, 3, '链条', 21.65, '制造');inse

6、rt into bicycle values(17, 3, '飞轮', 29.12, '制造');insert into bicycle values(18, 3, '后轴', 31.72, '制造');insert into bicycle values(19, 3, '后轮', 32.81, '制造');insert into bicycle values(20, 17, '外套', 9.35, '制造');insert into bicycle values(2

7、1, 17, '平档', 5.82, '制造');insert into bicycle values(22, 17, '芯子', 5.11, '制造');insert into bicycle values(23, 17, '千斤', 6.56, '制造');insert into bicycle values(24, 17, '钢珠', 2.28, '采购');insert into bicycle values(25, 4, '车架', 81.7

8、8, '制造');insert into bicycle values(26, 4, '车闸', 15.26, '制造');insert into bicycle values(27, 4, '链罩', 7.10, '采购');insert into bicycle values(28, 4, '车铃', 4.33, '采购');insert into bicycle values(29, 4, '车锁', 5.02, '采购');insert int

9、o bicycle values(30, 4, '支架', 5.12, '制造');select part_id, parent_id, part_name, mp_costfrom bicyclestart with part_id = 1connect by prior part_id = parent_id;select level, part_id, parent_id, part_name, mp_costfrom bicyclestart with part_id = 1connect by prior part_id = parent_idorde

10、r by level;column partName format A35column mp_cost format 99999.99select level, lpad(' ', 3*level-1) | part_name as partName, mp_costfrom bicyclestart with part_id = 1connect by prior part_id = parent_id;select level, lpad(' ', 3*level-1) | part_name as partName, mp_costfrom bicycle

11、start with part_id = 7connect by prior part_id = parent_id;select level, lpad(' ', 3*level-1) | part_name as partName, mp_costfrom bicyclestart with part_id = (select part_id from bicycle where part_name like '%飞轮%')connect by prior part_id = parent_id;select level, lpad(' ',

12、 3*level-1) | part_name as partName, mp_costfrom bicyclestart with part_id = (select part_id from bicycle where part_name like '%花盘%')connect by prior parent_id = part_id;select level, lpad(' ', 3*level-1) | part_name as partName, mp_costfrom bicyclewhere part_name <> '驱动系统

13、'start with part_id = 1connect by prior part_id = parent_id;select level, lpad(' ', 3*level-1) | part_name as partName, mp_costfrom bicyclestart with part_id = 1connect by prior part_id = parent_id and part_name <> '驱动系统'select level, lpad(' ', 3*level-1) | part_nam

14、e as partName, mp_costfrom bicyclewhere mp_cost >= 80start with part_id = 1connect by prior part_id = parent_id;情景查询select ename, sal, decode(deptno, '10', '会计部', '其他部门')from emp;select ename, sal, decode(deptno, '10', '会计部', '20', '研发部', &#

15、39;30', '销售部', '其他部门')from emp;select ename, sal, case deptno when 10 then '会计部' when 20 then '研发部' else '其他部门' endfrom emp;select ename, case when hiredate < '1-7月-81' then '老员工' when hiredate >= '1-7月-81' and hiredate <=

16、'1-7月-82' then '普通员工' when hiredate > '1-7月-82' then '新员工' endfrom emp;翻译查询select part_name, mp_cost as actualCost, translate(mp_cost, 12345678, 5129837046) as translatedCostfrom bicycle;分析查询create table sales_fact_2006(sale_year number(4) not null,sale_quarter num

17、ber(1) not null,sale_month number(2) not null,sale_book_id varchar2(20) not null,sale_region varchar2(10) not null,sale_person varchar2(10) not null,sale_amount number(10, 2) null,constraint pk_sales_f2006 primary key( sale_year, sale_quarter, sale_month, sale_book_id, sale_region, sale_person);inse

18、rt into sales_fact_2006 values(2006, 1, 1, 'ISBN 7-X', '北京', '赵亦', 13526.12);insert into sales_fact_2006 values(2006, 1, 2, 'ISBN 7-X', '北京', '钱尔', 8213.91);insert into sales_fact_2006 values(2006, 1, 3, 'ISBN 7-X', '北京', '孙三',

19、33871.52);insert into sales_fact_2006 values(2006, 2, 4, 'ISBN 7-X', '北京', '李斯', 22343.80);insert into sales_fact_2006 values(2006, 2, 5, 'ISBN 7-X', '上海', '周武', 3455.93);insert into sales_fact_2006 values(2006, 2, 6, 'ISBN 7-X', '上海',

20、'孙三', 23427.72);insert into sales_fact_2006 values(2006, 3, 7, 'ISBN 7-X', '香港', '杨琪', 897.15);insert into sales_fact_2006 values(2006, 3, 8, 'ISBN 7-X', '香港', '钱尔', 12345);insert into sales_fact_2006 values(2006, 3, 9, 'ISBN 7-X', '

21、;澳门', '冯久', 37817.12);insert into sales_fact_2006 values(2006, 4, 10, 'ISBN 7-X', '澳门', '冯久', 6524.10);insert into sales_fact_2006 values(2006, 4, 11, 'ISBN 7-X', '台北', '李斯', 93415.83);insert into sales_fact_2006 values(2006, 4, 12, 'IS

22、BN 7-X', '台北', '孙三', 23232.82);select sale_person, sum(sale_amount) as person_amount, sum(sum(sale_amount) over () as cumulative_amountfrom sales_fact_2006group by sale_personorder by sale_person;select sale_person, sum(sale_amount) as person_amount, sum(sum(sale_amount) over (or

23、der by sale_person rows between unbounded preceding and current row) as cumulative_amountfrom sales_fact_2006group by sale_personorder by sale_person;select sale_person, sum(sale_amount) as person_amount, sum(sum(sale_amount) over (order by sale_person rows between unbounded preceding and current ro

24、w) as cumulative_amountfrom sales_fact_2006where sale_person in ('赵亦', '钱尔', '李斯')group by sale_personorder by sale_person;select sale_person, sum(sale_amount) as person_amount, sum(sum(sale_amount) over (order by sale_person rows between unbounded preceding and current row)

25、as cumulative_amountfrom sales_fact_2006where sale_amount > (select avg(sale_amount) from sales_fact_2006)group by sale_personorder by sale_person;select sale_person, sum(sale_amount) as person_amount, avg(sum(sale_amount) over (order by sale_person rows between 2 preceding and current row) as mo

26、ving_2_average, avg(sum(sale_amount) over (order by sale_person rows between 5 preceding and current row) as moving_5_averagefrom sales_fact_2006group by sale_personorder by sale_person;select sale_person, sum(sale_amount) as person_amount, avg(sum(sale_amount) over (order by sale_person rows betwee

27、n 1 preceding and 1 following) as center_1_averagefrom sales_fact_2006group by sale_personorder by sale_person;select sale_person, sum(sale_amount) as person_amount, first_value(sum(sale_amount) over (order by sale_person rows between 1 preceding and 1 following) as first_value, last_value(sum(sale_

28、amount) over (order by sale_person rows between 1 preceding and 1 following) as first_value, avg(sum(sale_amount) over (order by sale_person rows between 1 preceding and 1 following) as center_1_averagefrom sales_fact_2006group by sale_personorder by sale_person;select sale_person, sum(sum(sale_amou

29、nt) over (partition by sale_person) as total_per_person, avg(sum(sale_amount) over (partition by sale_person) as avg_per_person, sale_region, sum(sum(sale_amount) over (partition by sale_region) as total_per_region, min(sum(sale_amount) over (partition by sale_region) as min_per_regionfrom sales_fac

30、t_2006group by sale_person, sale_regionorder by sale_person, sale_region;select sale_person, sale_region, sum(sale_amount) as per_reg_amount, sum(sum(sale_amount) over (partition by sale_region) as reg_amount, sum(sale_amount) / sum(sum(sale_amount) over (partition by sale_region) as per_reg_rat_1,

31、ratio_to_report(sum(sale_amount) over (partition by sale_region) as per_reg_rat_2from sales_fact_2006group by sale_person, sale_regionorder by sale_person, sale_region;select sale_person, sum(sale_amount) as person_amount, lag(sum(sale_amount), 1) over (order by sale_person) as perv_1_per_amo, lag(sum(sale_amount), 2) over (order by sale_person) as perv_2_per_amo, lag(sum(sale_amount), 3) over (order by sale_person) as perv_3_per_amofrom sales_fact_2006group by sale_person, sale_regionorder by sale_person, sale