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

botang

上完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. 
   
5. 
   
6. select   e.employee_id, d.department_name
   
7.   from employees e, departments d
   
8.   where e.department_id=d.department_id and
   
9.             e.manager_id=d.manager_id;
   
10.             
    
11.    select   e.employee_id, d.department_name
    
12.   from employees e, departments d
    
13.   where e.department_id=d.department_id;      
    
14.   
    
15.   select  e.employee_id, d.department_name, department_id
    
16. from employees e
    
17. natural join departments d;
    
18.       
    
19.      select   e.employee_id, d.department_name, d.department_id
    
20.   from employees e, departments d
    
21.   where e.department_id=d.department_id and
    
22.             e.manager_id=d.manager_id;  
    
23.             
    
24.    ---
    
25.   select  e.employee_id, d.department_name, department_id
    
26. from employees e
    
27.   join departments d
    
28.   using (department_id, manager_id)
    
29.   where  department_id > 60 and
    
30.   d.department_name ='Sales';
    
31.   ---
    
32.   SELECT e.employee_id, e.last_name, d.department_id,
    
33.        d.department_id, d.location_id
    
34. FROM   employees e JOIN departments d
    
35. ON     (e.department_id = d.department_id)
    
36. and  (e.department_id>100);
    
37. 
    
38.   SELECT e.employee_id, e.last_name, d.department_id,
    
39.        d.department_id, d.location_id
    
40. FROM   employees e ,departments d
    
41. where      (e.department_id = d.department_id);
    
42. 
    
43.   SELECT e.employee_id, e.last_name, d.department_id, l.state_province,
    
44.        d.department_id, d.location_id
    
45. FROM   employees e JOIN departments d
    
46. ON     (e.department_id = d.department_id)
    
47. join locations l
    
48. on (d.location_id=l.location_id);
    
49. 
    
50. 
    
51.     SELECT e.employee_id, e.last_name, d.department_id,
    
52.        d.department_id, d.location_id
    
53. FROM   employees e JOIN departments d
    
54. ON     (e.department_id = d.department_id)
    
55. where  (e.department_id>100);
    
56.   
    
57.      ---
    
58.      
    
59.      select  e.last_name||' works for '||m.last_name  
    
60.        from employees e , employees m
    
61.        where e.manager_id=m.employee_id
    
62.         and e.employee_id=101;   
    
63.      ----
    
64.      
    
65.      select  * from employees where employee_id=178;
    
66.      
    
67.      ---
    
68.      
    
69.      
    
70.   SELECT e.employee_id, e.last_name, d.department_id,
    
71.        d.department_id, d.location_id
    
72. FROM   employees e ,departments d
    
73. where      e.department_id = d.department_id(+)  order by 1;
    
74.         
    
75. 
    
76. select  * from departments ;
    
77.         
    
78.     SELECT e.employee_id, e.last_name, d.department_id,
    
79.        d.department_id, d.location_id
    
80. FROM   employees e ,departments d
    
81. where      e.department_id(+) = d.department_id;   
    
82. 
    
83. 
    
84. SELECT e.employee_id, e.last_name,
    
85.        department_id, d.location_id
    
86. FROM   employees e   right join departments d
    
87. using (department_id);
    
88. 
    
89. 
    
90. 
    
91.                   
    
92. 
    
93.   SELECT e.employee_id, e.last_name, d.department_id,
    
94.        d.department_id, d.location_id
    
95. FROM   employees e ,departments d
    
96. where      e.department_id = d.department_id(+)
    
97. union
    
98.     SELECT e.employee_id, e.last_name, d.department_id,
    
99.        d.department_id, d.location_id
    
100. FROM   employees e ,departments d
     
101. where      e.department_id(+) = d.department_id;
     
102. 
     
103. 
     
104. SELECT e.employee_id, e.last_name, d.department_id,
     
105.        d.department_id, d.location_id
     
106. FROM   employees e   full  outer join (departments d)
     
107. on (   e.department_id = d.department_id)  order by 1;
     
108.    
     
109.                   

复制代码

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. 
    
27. SELECT employee_id, last_name, job_id, department_id
    
28. FROM   employees outer
    
29. WHERE not  EXISTS ( SELECT 'X'
    
30.                  FROM   employees
    
31.                  WHERE  manager_id =
    
32.                         outer.employee_id);
    
33. 
    
34. select  * from job_history order by 1,2;
    
35. 
    
36. select  * from employees where employee_id in (101,176,200)  order by 1;
    
37. 
    
38. SELECT employee_id, job_id "X",sysdate,null
    
39. FROM   employees  where employee_id in (101,176,200)
    
40. UNION all
    
41. SELECT employee_id, job_id "Y" , start_date,'CCCC'
    
42. FROM   job_history where employee_id in (101,176,200)  order by 1,3 ;
    
43. 
    
44. 
    
45. create table t05109_a( a  number default 999 );
    
46. 
    
47. select  * from user_tab_columns tc where tc.TABLE_NAME  like 'T05109_%';
    
48. 
    
49. create table t05109_b ( a  number ) ;
    
50. 
    
51. insert into t05109_a(a) values (default);
    
52. 
    
53. select  * from   t05109_a;
    
54. 
    
55. create table t05110( pic_name varchar2(20), pic  blob ) ;
    
56. select  * from t05110;
    
57. 
    
58. declare
    
59.   v_f bfile;
    
60.   v_b blob;
    
61.   v_filename varchar2(20) :='dawn.png';
    
62. begin
    
63.    v_f :=bfilename('DIR1', v_filename);
    
64.    insert into    t05110 values ( v_filename, empty_blob() ) return pic into  v_b;
    
65.    DBMS_LOB.FILEOPEN  (v_f, DBMS_LOB.FILE_READONLY);
    
66.    dbms_lob.loadfromfile(dest_lob => v_b,
    
67.                                          src_lob => v_f,                                         
    
68.                                          amount =>dbms_lob.getlength(v_f));   
    
69.    DBMS_LOB.FILECLOSE (v_f);
    
70.    commit;
    
71. end;
    
72. 
    
73. 
    
74. create table t05310_f( a  interval year(3) to month ) ;
    
75. 
    
76. insert into t05310_f  values ( interval '100-1'   year(3) to month ) ;
    
77. 
    
78. select  sysdate+a  from t05310_f;
    
79. 
    
80. create table t05310_g( a     interval  day(3) to second(3)) ;
    
81. 
    
82. insert into      t05310_g values (  interval '10 05:10:10.333' day(3) to second(3));
    
83. 
    
84. select   sysdate+a from t05310_g;
    
85.             
    
86. 
    
87. 
    
88. 
    
89. create table t05110_nn ( a  number  constraint  c1   not null );
    
90. 
    
91. select  * from user_constraints c where c.TABLE_NAME
    
92.      in ( 'T05110_NN','T05110_CHK', 'T05110_NN2'  );
    
93. 
    
94. 
    
95. create table t05110_chk( a  number  constraint c2 check( a >5 )) ;
    
96. 
    
97. create table t05110_nn2 ( a  number,  constraint  c3  check(a is  not null ));
    
98. 
    
99.   create table clone1 as select * from  t05110_nn;
    
100.   
     
101.   create table clone2 as select * from t05110_chk;
     
102.   
     
103.   create table clone3 as select * from t05110_nn2;
     
104. 
     
105.   select  * from user_constraints c where c.TABLE_NAME  like 'CLONE%';
     
106.      
     
107.   
     
108.   create table t05110_cons ( a   number ) ;
     
109.   
     
110.   alter table t05110_cons add ( b number ) ;
     
111.   select  * from user_constraints c where c.TABLE_NAME='T05110_CONS';
     
112.   
     
113.   alter table  t05110_cons   add constraint chk1 check ( a+ b=1) ;
     
114.   
     
115.   insert into t05110_cons  values   ( 0.4, 0.6) ;
     
116.   
     
117.   select  * from  t05110_cons;
     
118.   
     
119.    insert into t05110_cons  values   ( 0.4, 0.5) ;
     
120.    
     
121.    select  * from user_constraints c where c.TABLE_NAME in ('TPARENT','TSON');
     
122.    
     
123.    select * from tparent;
     
124.    
     
125.    select  * from tson;
     
126.    
     
127.    alter table tson drop constraint SYS_C0014929;
     
128.    
     
129.    alter table tson add constraint fk_tson
     
130.     foreign key (b) referencing  tparent on delet;
     
131.    
     
132.     delete from tparent where a=1;
     
133.    
     
134.     select  * from tson;
     
135.    
     
136.     create table t05110_u( a  number ) ;
     
137.    
     
138.     select * from user_indexes i where i.TABLE_NAME='T05110_U';
     
139.    
     
140.     alter table t05110_u drop constraint  uk_t05110_u;
     
141.     alter table t05110_u add constraint uk_t05110_u  unique(a) ;
     
142.    
     
143.     insert into t05110_u values (1) ;
     
144.    
     
145.     select  * from t05110_u;
     
146.    
     
147.     create index  i05110_u  on t05110_u(a) ;
     
148.    
     
149.     alter table t05110_u add constraint uk_t05110_u  unique(a)  enable novalidate ;
     
150.    
     
151.     select  * from  t05110_u;
     
152.    
     
153.     insert into  t05110_u values (1) ;
     
154.    
     
155.     create table t05110_x( a  number ) ;
     
156.    
     
157.     alter table t05110_x  add constraint uk_t05110_x  unique(a) disable validate ;
     
158.    
     
159.     insert into t05110_x values ( 2) ;
     
160. 
     
161. 
     
162.    
     
163.     create table t05110_y ( a number );
     
164.    
     
165.      create  unique   index i05110_y  on t05110_y ( a);
     
166.      drop index i05110_y;
     
167.      
     
168.      alter table  t05110_y add     constraint uk_t05110_y unique(a) deferrable  initially deferred  ;
     
169.    
     
170.         select * from user_indexes i where i.TABLE_NAME='T05110_Y';
     
171.    
     
172.      alter table  t05110_y  modify constraint uk_t05110_y   initially immediate;
     
173.    
     
174.    
     
175.    
     
176.     CREATE   or replace   VIEW         empvu80
     
177. AS SELECT  employee_id, last_name, salary,  department_id
     
178.     FROM    employees
     
179.     WHERE   department_id = 80  with check  option ;
     
180.    
     
181.    
     
182.     select  * from empvu80;
     
183. 
     
184. update empvu80  set department_id = 80 where employee_id=146;
     
185. 
     
186. create table t05111_a ( a  number ) ;
     
187.     insert into t05111_a  values ( 4) ;
     
188.     create or replace  view v05111_a
     
189.      as select   a/2  b  from t05111_a;
     
190.      
     
191.      select  * from  t05111_a;
     
192.    
     
193. delete from  v05111_a;
     
194. update  v05111_a set b=1 ;
     
195. 
     
196. select  * from user_sequences  s  where s.SEQUENCE_NAME='SEQ2';
     
197. 
     
198. create sequence seq1 ;
     
199. 
     
200. create sequence seq2
     
201.   nocache ;
     
202.    
     
203.    alter  sequence seq2   order ;
     
204.    
     
205.    create sequence seq3
     
206.      increment by -10
     
207.      start with 100
     
208.      maxvalue 100
     
209.      minvalue -100
     
210.      cycle;
     
211.   
     
212.   
     
213.   
     
214. 
     

复制代码