Oracle 實(shí)驗(yàn)3

1、實(shí)驗(yàn)三：高級(jí)SQL語(yǔ)言1. 實(shí)驗(yàn)?zāi)康模菏煜racle10g關(guān)于層次、情景、翻譯、分析、線(xiàn)性回歸等各種高級(jí)查詢(xún)技術(shù)。2. 實(shí)驗(yàn)內(nèi)容（1） 練習(xí)層次查詢(xún)（2） 練習(xí)情景查詢(xún)；（3） 練習(xí)翻譯查詢(xún)；（4） 練習(xí)分析查詢(xún)；（5） 練習(xí)線(xiàn)性回歸查詢(xún)。3. 實(shí)驗(yàn)步驟4. 實(shí)驗(yàn)總結(jié)5. 實(shí)驗(yàn)數(shù)據(jù)和SQL語(yǔ)句層次查詢(xún)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, '自行車(chē)', 379.28, '裝配');insert into bicycle values(2, 1, '導(dǎo)向系統(tǒng)', 101.11, '制造');insert into bicycle values(3, 1, '驅(qū)動(dòng)系統(tǒng)', 159.56, '制造');ins

3、ert into bicycle values(4, 1, '其他部分', 118.61, '制造和采購(gòu)');insert into bicycle values(5, 2, '車(chē)把', 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, '前花盤(pán)', 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, '采購(gòu)');insert into bicycle values(25, 4, '車(chē)架', 81.7

8、8, '制造');insert into bicycle values(26, 4, '車(chē)閘', 15.26, '制造');insert into bicycle values(27, 4, '鏈罩', 7.10, '采購(gòu)');insert into bicycle values(28, 4, '車(chē)鈴', 4.33, '采購(gòu)');insert into bicycle values(29, 4, '車(chē)鎖', 5.02, '采購(gòu)');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 '%花盤(pán)%')connect by prior parent_id = part_id;select level, lpad(' ', 3*level-1) | part_name as partName, mp_costfrom bicyclewhere part_name <> '驅(qū)動(dòng)系統(tǒng)

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 <> '驅(qū)動(dòng)系統(tǒng)'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;情景查詢(xún)select ename, sal, decode(deptno, '10', '會(huì)計(jì)部', '其他部門(mén)')from emp;select ename, sal, decode(deptno, '10', '會(huì)計(jì)部', '20', '研發(fā)部', &#

15、39;30', '銷(xiāo)售部', '其他部門(mén)')from emp;select ename, sal, case deptno when 10 then '會(huì)計(jì)部' when 20 then '研發(fā)部' else '其他部門(mén)' 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;翻譯查詢(xún)select part_name, mp_cost as actualCost, translate(mp_cost, 12345678, 5129837046) as translatedCostfrom bicycle;分析查詢(xún)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', '北京', '錢(qián)爾', 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', '香港', '錢(qián)爾', 12345);insert into sales_fact_2006 values(2006, 3, 9, 'ISBN 7-X', '

21、;澳門(mén)', '馮久', 37817.12);insert into sales_fact_2006 values(2006, 4, 10, 'ISBN 7-X', '澳門(mén)', '馮久', 6524.10);insert into sales_fact_2006 values(2006, 4, 11, 'ISBN 7-X', '臺(tái)北', '李斯', 93415.83);insert into sales_fact_2006 values(2006, 4, 12, 'IS

22、BN 7-X', '臺(tái)北', '孫三', 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 ('趙亦', '錢(qián)爾', '李斯')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