实验三：高级SQL语言.doc

实验三：高级SQL语言1. 实验目的熟悉Oracle10g关于层次、情景、翻译、分析、线性回归等各种高级查询技术。2. 实验内容（1） 练习层次查询；（2） 练习情景查询；（3） 练习翻译查询；（4） 练习分析查询；（5） 练习线性回归查询。3. 实验步骤层次查询create table bicycle(part_id number(5) constraint pk_bicycle_part_id primary key,parent_id number(5) constraint fk_bicycle_pid references bicycle(part_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, 制造);insert 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 values(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.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, 制造);insert 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(21, 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.78, 制造);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 into 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_idorder 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 bicyclestart 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( , 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 驱动系统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_name 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, 研发部, 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 and 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 number(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);insert 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, 北京, 孙三, 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, 上海, 孙三, 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, 澳门, 冯久, 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, ISBN 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 (order 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 row) 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) 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 moving_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 between 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_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_amount) 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_fact_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, 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_region;select sale_region, sum(sale_amount), rank() over (order by sum(sale_amount) desc) as rank_amountfrom sales_fact_2006group by sale_regionorder by sale_region;select sale_region, sum(sale_amount), cume_dist() over (order by sum(sale_amount) desc) as c