07.txt

Oracle database 10g基础教程（第二版） 源码

select ename, hiredate
from emp
where ename like '%A%';

delete from emp where empno = 7211;

 
insert into emp
values(7211, 'Andrew', 'CLERK', 7499, '5-JAN-82', 1500, 300, 20);
insert into emp
values(7211, 'Andrew', 'CLERK', 7499, '5-1月-82', 1500, 300, 20);




insert into emp
values(7211, 'Andrew', 'CLERK', 7499, '5-1月-82', 1500, 300, 20);

insert into emp
values(7212, 'Henry', 'CLERK', 7499, '20-1月-82', 1800, 500, 20);
insert into emp
values(7213, 'Gray', 'CLERK', 7499, date '1982-5-25', 1900, 600, 20);



select ename, hiredate
from emp
where ename like '%A%';



alter session set nls_date_format = 'month-dd-yyyy';
select ename, hiredate
from emp
where ename like '%A%';


alter session set nls_date_format = 'yyyy-month-dd';
select ename, hiredate
from emp
where ename like '%A%';


select to_char(sysdate, 'yyyy, month dd, HH24:MI:SS'),
       to_char(sysdate, 'mm-dd-yy, hh:mi:ss am')
from dual;

select to_date('2008, 8月08', 'yyyy, month dd')
from dual;


select to_char(sysdate, 'yyyy-mon-dd'),
       to_char(to_date('19-6月-15', 'yy-mon-dd'), 'yyyy-mon-dd'),
       to_char(to_date('79-6月-15', 'yy-mon-dd'), 'yyyy-mon-dd')
from dual;
select to_char(sysdate, 'yyyy-mon-dd'),
       to_char(to_date('19-6月-15', 'rr-mon-dd'), 'yyyy-mon-dd'),
       to_char(to_date('79-6月-15', 'rr-mon-dd'), 'yyyy-mon-dd')
from dual;



select to_char(sysdate) as nowDate, 
       add_months(sysdate, 8) as next8Date, 
       add_months(sysdate, -15) as pre15Date
from dual;





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_cost
from bicycle
start with part_id = 1
connect by prior part_id = parent_id;




select level, part_id, parent_id, part_name, mp_cost
from bicycle
start with part_id = 1
connect by prior part_id = parent_id
order by level;







column partName format A35
column mp_cost format 99999.99
select level, 
       lpad(' ', 3*level-1) || part_name as partName,
       mp_cost
from bicycle
start with part_id = 1
connect by prior part_id = parent_id;








select level, 
       lpad(' ', 3*level-1) || part_name as partName,
       mp_cost
from bicycle
start with part_id = 7
connect by prior part_id = parent_id;






select level, 
       lpad(' ', 3*level-1) || part_name as partName,
       mp_cost
from bicycle
start 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_cost
from bicycle
start 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_cost
from bicycle
where part_name <> '驱动系统'
start with part_id = 1
connect by prior part_id = parent_id;



select level, 
       lpad(' ', 3*level-1) || part_name as partName,
       mp_cost
from bicycle
start with part_id = 1
connect by prior part_id = parent_id
    and part_name <> '驱动系统';




select level, 
       lpad(' ', 3*level-1) || part_name as partName,
       mp_cost
from bicycle
where mp_cost >= 80
start with part_id = 1
connect 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 '其他部门' 
       end
from emp;



select ename, case
                  when hiredate < '1-7月-81' then '老员工'
                  when hiredate >= '1-7月-81' and hiredate <= '1-7月-82' then '普通员工'
                  when hiredate > '1-7月-82' then '新员工'
              end
from emp;






select part_name, 
       mp_cost as actualCost, 
       translate(mp_cost, 12345678, 5129837046) as translatedCost
from 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_amount
from sales_fact_2006
group by sale_person
order 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_amount
from sales_fact_2006
group by sale_person
order 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_amount
from sales_fact_2006
where sale_person in ('赵亦', '钱尔', '李斯')
group by sale_person
order 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_amount
from sales_fact_2006
where sale_amount > (select avg(sale_amount)
                     from sales_fact_2006)
group by sale_person
order 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_average
from sales_fact_2006
group by sale_person
order 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_average
from sales_fact_2006
group by sale_person
order 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_average
from sales_fact_2006
group by sale_person
order 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_region
from sales_fact_2006
group by sale_person, sale_region
order 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_2
from sales_fact_2006
group by sale_person, sale_region
order 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_amo
from sales_fact_2006
group by sale_person, sale_region
order by sale_person, sale_region;






select sale_region,
       sum(sale_amount),
       rank() over (order by sum(sale_amount) desc) as rank_amount
from sales_fact_2006
group by sale_region
order by sale_region;




select sale_region,
       sum(sale_amount),
       cume_dist() over (order by sum(sale_amount) desc) as cume_dist
from sales_fact_2006
group by sale_region
order 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);
insert 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 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;