Bo's Oracle Station

查看: 2419|回复: 0

第40和41次:2015-08-15星期六上下午

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2015-8-15 22:32:26 | 显示全部楼层 |阅读模式
上完1Z0-051第6章        (52-30)
上完1Z0-051第7章        (52-31)
上完1Z0-051第8章        (52-32)
上完1Z0-051第9章        (52-33)
上完1Z0-051第10章        (52-34)
上完1Z0-051第11章        (52-35)
2015-08-15a.sql
  1. select  e.employee_id, d.department_name
  2. from employees e
  3. natural join departments d;


  4. select   e.employee_id, d.department_name
  5.   from employees e, departments d
  6.   where e.department_id=d.department_id and
  7.             e.manager_id=d.manager_id;
  8.             
  9.    select   e.employee_id, d.department_name
  10.   from employees e, departments d
  11.   where e.department_id=d.department_id;      
  12.   
  13.   select  e.employee_id, d.department_name, department_id
  14. from employees e
  15. natural join departments d;
  16.       
  17.      select   e.employee_id, d.department_name, d.department_id
  18.   from employees e, departments d
  19.   where e.department_id=d.department_id and
  20.             e.manager_id=d.manager_id;  
  21.             
  22.    ---
  23.   select  e.employee_id, d.department_name, department_id
  24. from employees e
  25.   join departments d
  26.   using (department_id, manager_id)
  27.   where  department_id > 60 and
  28.   d.department_name ='Sales';
  29.   ---
  30.   SELECT e.employee_id, e.last_name, d.department_id,
  31.        d.department_id, d.location_id
  32. FROM   employees e JOIN departments d
  33. ON     (e.department_id = d.department_id)
  34. and  (e.department_id>100);

  35.   SELECT e.employee_id, e.last_name, d.department_id,
  36.        d.department_id, d.location_id
  37. FROM   employees e ,departments d
  38. where      (e.department_id = d.department_id);

  39.   SELECT e.employee_id, e.last_name, d.department_id, l.state_province,
  40.        d.department_id, d.location_id
  41. FROM   employees e JOIN departments d
  42. ON     (e.department_id = d.department_id)
  43. join locations l
  44. on (d.location_id=l.location_id);


  45.     SELECT e.employee_id, e.last_name, d.department_id,
  46.        d.department_id, d.location_id
  47. FROM   employees e JOIN departments d
  48. ON     (e.department_id = d.department_id)
  49. where  (e.department_id>100);
  50.   
  51.      ---
  52.      
  53.      select  e.last_name||' works for '||m.last_name  
  54.        from employees e , employees m
  55.        where e.manager_id=m.employee_id
  56.         and e.employee_id=101;   
  57.      ----
  58.      
  59.      select  * from employees where employee_id=178;
  60.      
  61.      ---
  62.      
  63.      
  64.   SELECT e.employee_id, e.last_name, d.department_id,
  65.        d.department_id, d.location_id
  66. FROM   employees e ,departments d
  67. where      e.department_id = d.department_id(+)  order by 1;
  68.         

  69. select  * from departments ;
  70.         
  71.     SELECT e.employee_id, e.last_name, d.department_id,
  72.        d.department_id, d.location_id
  73. FROM   employees e ,departments d
  74. where      e.department_id(+) = d.department_id;   


  75. SELECT e.employee_id, e.last_name,
  76.        department_id, d.location_id
  77. FROM   employees e   right join departments d
  78. using (department_id);



  79.                   

  80.   SELECT e.employee_id, e.last_name, d.department_id,
  81.        d.department_id, d.location_id
  82. FROM   employees e ,departments d
  83. where      e.department_id = d.department_id(+)
  84. union
  85.     SELECT e.employee_id, e.last_name, d.department_id,
  86.        d.department_id, d.location_id
  87. FROM   employees e ,departments d
  88. where      e.department_id(+) = d.department_id;


  89. SELECT e.employee_id, e.last_name, d.department_id,
  90.        d.department_id, d.location_id
  91. FROM   employees e   full  outer join (departments d)
  92. on (   e.department_id = d.department_id)  order by 1;
  93.    
  94.                   
复制代码

2015-0815b.sql
  1. SELECT employee_id, last_name
  2. FROM   employees
  3. WHERE  salary > any
  4.                 (SELECT   MIN(salary)
  5.                  FROM     employees
  6.                  GROUP BY department_id);
  7.                  
  8.                  
  9.      SELECT last_name, job_id
  10. FROM   employees
  11. WHERE  job_id =
  12.                 (SELECT job_id
  13.                  FROM   employees
  14.                  WHERE  last_name = 'Haas');
  15.                  
  16.                  
  17.      SELECT * FROM departments
  18. WHERE NOT EXISTS
  19. (SELECT 1 FROM employees
  20. WHERE employees.department_id=departments.department_id);
  21.             
  22.              SELECT * FROM departments
  23. WHERE department_id not in
  24. (SELECT   employees.department_id   FROM employees
  25. WHERE employees.department_id=departments.department_id);

  26. SELECT employee_id, last_name, job_id, department_id
  27. FROM   employees outer
  28. WHERE not  EXISTS ( SELECT 'X'
  29.                  FROM   employees
  30.                  WHERE  manager_id =
  31.                         outer.employee_id);

  32. select  * from job_history order by 1,2;

  33. select  * from employees where employee_id in (101,176,200)  order by 1;

  34. SELECT employee_id, job_id "X",sysdate,null
  35. FROM   employees  where employee_id in (101,176,200)
  36. UNION all
  37. SELECT employee_id, job_id "Y" , start_date,'CCCC'
  38. FROM   job_history where employee_id in (101,176,200)  order by 1,3 ;


  39. create table t05109_a( a  number default 999 );

  40. select  * from user_tab_columns tc where tc.TABLE_NAME  like 'T05109_%';

  41. create table t05109_b ( a  number ) ;

  42. insert into t05109_a(a) values (default);

  43. select  * from   t05109_a;

  44. create table t05110( pic_name varchar2(20), pic  blob ) ;
  45. select  * from t05110;

  46. declare
  47.   v_f bfile;
  48.   v_b blob;
  49.   v_filename varchar2(20) :='dawn.png';
  50. begin
  51.    v_f :=bfilename('DIR1', v_filename);
  52.    insert into    t05110 values ( v_filename, empty_blob() ) return pic into  v_b;
  53.    DBMS_LOB.FILEOPEN  (v_f, DBMS_LOB.FILE_READONLY);
  54.    dbms_lob.loadfromfile(dest_lob => v_b,
  55.                                          src_lob => v_f,                                         
  56.                                          amount =>dbms_lob.getlength(v_f));   
  57.    DBMS_LOB.FILECLOSE (v_f);
  58.    commit;
  59. end;


  60. create table t05310_f( a  interval year(3) to month ) ;

  61. insert into t05310_f  values ( interval '100-1'   year(3) to month ) ;

  62. select  sysdate+a  from t05310_f;

  63. create table t05310_g( a     interval  day(3) to second(3)) ;

  64. insert into      t05310_g values (  interval '10 05:10:10.333' day(3) to second(3));

  65. select   sysdate+a from t05310_g;
  66.             



  67. create table t05110_nn ( a  number  constraint  c1   not null );

  68. select  * from user_constraints c where c.TABLE_NAME
  69.      in ( 'T05110_NN','T05110_CHK', 'T05110_NN2'  );


  70. create table t05110_chk( a  number  constraint c2 check( a >5 )) ;

  71. create table t05110_nn2 ( a  number,  constraint  c3  check(a is  not null ));

  72.   create table clone1 as select * from  t05110_nn;
  73.   
  74.   create table clone2 as select * from t05110_chk;
  75.   
  76.   create table clone3 as select * from t05110_nn2;

  77.   select  * from user_constraints c where c.TABLE_NAME  like 'CLONE%';
  78.      
  79.   
  80.   create table t05110_cons ( a   number ) ;
  81.   
  82.   alter table t05110_cons add ( b number ) ;
  83.   select  * from user_constraints c where c.TABLE_NAME='T05110_CONS';
  84.   
  85.   alter table  t05110_cons   add constraint chk1 check ( a+ b=1) ;
  86.   
  87.   insert into t05110_cons  values   ( 0.4, 0.6) ;
  88.   
  89.   select  * from  t05110_cons;
  90.   
  91.    insert into t05110_cons  values   ( 0.4, 0.5) ;
  92.    
  93.    select  * from user_constraints c where c.TABLE_NAME in ('TPARENT','TSON');
  94.    
  95.    select * from tparent;
  96.    
  97.    select  * from tson;
  98.    
  99.    alter table tson drop constraint SYS_C0014929;
  100.    
  101.    alter table tson add constraint fk_tson
  102.     foreign key (b) referencing  tparent on delet;
  103.    
  104.     delete from tparent where a=1;
  105.    
  106.     select  * from tson;
  107.    
  108.     create table t05110_u( a  number ) ;
  109.    
  110.     select * from user_indexes i where i.TABLE_NAME='T05110_U';
  111.    
  112.     alter table t05110_u drop constraint  uk_t05110_u;
  113.     alter table t05110_u add constraint uk_t05110_u  unique(a) ;
  114.    
  115.     insert into t05110_u values (1) ;
  116.    
  117.     select  * from t05110_u;
  118.    
  119.     create index  i05110_u  on t05110_u(a) ;
  120.    
  121.     alter table t05110_u add constraint uk_t05110_u  unique(a)  enable novalidate ;
  122.    
  123.     select  * from  t05110_u;
  124.    
  125.     insert into  t05110_u values (1) ;
  126.    
  127.     create table t05110_x( a  number ) ;
  128.    
  129.     alter table t05110_x  add constraint uk_t05110_x  unique(a) disable validate ;
  130.    
  131.     insert into t05110_x values ( 2) ;


  132.    
  133.     create table t05110_y ( a number );
  134.    
  135.      create  unique   index i05110_y  on t05110_y ( a);
  136.      drop index i05110_y;
  137.      
  138.      alter table  t05110_y add     constraint uk_t05110_y unique(a) deferrable  initially deferred  ;
  139.    
  140.         select * from user_indexes i where i.TABLE_NAME='T05110_Y';
  141.    
  142.      alter table  t05110_y  modify constraint uk_t05110_y   initially immediate;
  143.    
  144.    
  145.    
  146.     CREATE   or replace   VIEW         empvu80
  147. AS SELECT  employee_id, last_name, salary,  department_id
  148.     FROM    employees
  149.     WHERE   department_id = 80  with check  option ;
  150.    
  151.    
  152.     select  * from empvu80;

  153. update empvu80  set department_id = 80 where employee_id=146;

  154. create table t05111_a ( a  number ) ;
  155.     insert into t05111_a  values ( 4) ;
  156.     create or replace  view v05111_a
  157.      as select   a/2  b  from t05111_a;
  158.      
  159.      select  * from  t05111_a;
  160.    
  161. delete from  v05111_a;
  162. update  v05111_a set b=1 ;

  163. select  * from user_sequences  s  where s.SEQUENCE_NAME='SEQ2';

  164. create sequence seq1 ;

  165. create sequence seq2
  166.   nocache ;
  167.    
  168.    alter  sequence seq2   order ;
  169.    
  170.    create sequence seq3
  171.      increment by -10
  172.      start with 100
  173.      maxvalue 100
  174.      minvalue -100
  175.      cycle;
  176.   
  177.   
  178.   

复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 05:43 , Processed in 0.046390 second(s), 24 queries .

快速回复 返回顶部 返回列表