1、实验三：高级 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
2、 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 value
3、s(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, 前轴
4、碗, 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
5、.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, 制
6、造);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
7、 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;colum
8、n 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
9、 part_id = parent_id;select level, lpad( , 3*level-1) | part_name as partName,mp_costfrom bicyclestart with part_id = (select part_idfrom bicyclewhere 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_
10、id = (select part_idfrom bicyclewhere 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 驱动系统;select level, lpad( , 3*level-1) | part_name as partName,mp_costfrom bicyclewhere mp_cost = 80start with par
11、t_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 deptnowhen 10 then 会计部when 20 then 研发部else 其他部门 endfrom emp;select ename, casewhen hiredate = 1-7 月-8
12、1 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 va
13、rchar2(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, 北京, 赵亦, 1
14、3526.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, 上海, 周武, 34
15、55.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
16、);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_amoun
17、t) 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_per
18、sonorder 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
19、_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_pers
20、on,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_personorde
21、r 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_v
22、alue(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 fol
23、lowing) 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
24、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 s
25、ale_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
26、,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_region
27、order 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 cume_distfrom sales_f
28、act_2006group by sale_regionorder by sum(sale_amount);线性回归查询create table sales(year number(4),amount number(10, 2);insert into sales values(1993, 670);insert into sales values(1994, 800);insert into sales values(1995, 1000);insert into sales values(1996, 1250);insert into sales values(1997, 1480);in
29、sert into sales values(1998, 1726);insert into sales values(1999, 2090);insert into sales values(2000, 2520);insert into sales values(2001, 3290);insert into sales values(2002, 4110);insert into sales values(2003, 5206);insert into sales values(2004, 6911);insert into sales values(2005, 8210);insert
30、 into sales values(2006, 9917);insert into sales values(2007, 11202);insert into sales values(2008, 12528);select regr_slope(amount, year),regr_intercept(amount, year)from sales;4. 实验总结通过本次实验，我熟悉 Oracle10g 关于层次、情景、翻译、分析、线性回归等各种高级查询技术，基本上掌握了各种高级查询的语法结构，达到了实验的目的。但是有些语句仍不熟悉，需要在课后多加看书复习。通过本次实验，我的动手能力也得到了进一步的加强。
