课程第12次（2016-04-22星期五）：两表连接，子查询

botang

课程第12次（2016-04-22星期五）：两表连接，子查询
【上完1Z0-051的第6章】：两表连接
【上完1Z0-051的第7章】：子查询
【1Z0-051】：共8章
【1Z0-052】：共3章
2016-04-22.sql：

1. select  * from departments;
   
2. 
   
3. select  * from locations;
   
4. 
   
5. 
   
6. SELECT department_id, department_name,
   
7.        location_id, city
   
8. FROM   departments d
   
9. NATURAL JOIN locations l ;
   
10. 
    
11. 
    
12. SELECT department_id, department_name,
    
13.        l.location_id, city
    
14.    from departments d, locations l
    
15.    where d.location_id=l.location_id;
    
16.    
    
17.    
    
18.    select   e.employee_id,e.last_name,d.department_name, e.department_id
    
19.      from employees e , departments d
    
20.      where e.department_id=d.department_id
    
21.      and e.manager_id=d.manager_id;
    
22.      
    
23.         select   e.employee_id,e.last_name,d.department_name, department_id
    
24.      from employees e    natural join      departments d;
    
25.   ----
    
26.     select   e.employee_id,e.last_name,d.department_name, department_id
    
27.      from employees e join  departments d
    
28.      using (department_id);
    
29.      
    
30.      ---
    
31.    
    
32.   
    
33.   ---
    
34.      select   e.employee_id,e.last_name,d.department_name, d.department_id
    
35.      from employees e   join departments d
    
36.      on ( e.department_id=d.department_id)
    
37.     ;
    
38.      ---
    
39.           SELECT l.city, d.department_name
    
40. FROM   locations l JOIN departments d
    
41. USING (location_id)
    
42. WHERE location_id = 1400;
    
43. ---
    
44. 
    
45. select  worker.last_name||' works for '||manager.last_name
    
46. from employees worker, employees  manager
    
47. where worker.manager_id=manager.employee_id;
    
48. --
    
49. select  worker.last_name||' works for '||manager.last_name
    
50. from employees worker  join  employees  manager
    
51. on (worker.manager_id=manager.employee_id);
    
52. 
    
53.      ---
    
54. create table  job_grades( grade_level  char(1),
    
55.                                            lowest_sal  number(8,2),
    
56.                                            highest_sal  number(8,2));
    
57.    
    
58. insert into job_grades values (
    
59. 
    
60. 'F', 25000 , 40000 ) ;
    
61. 
    
62. select  * from   job_grades;
    
63. ---
    
64. 
    
65. select  e.employee_id, e.last_name , j.grade_level
    
66.   from employees e , job_grades j
    
67.   where e.salary between  j.lowest_sal  and j.highest_sal;
    
68.   ---
    
69.   select  e.employee_id, e.last_name , j.grade_level
    
70.   from employees e  join   job_grades j
    
71.   on e.salary between  j.lowest_sal  and j.highest_sal;
    
72.   
    
73.   ---
    
74.   
    
75.   select * from employees where employee_id=178;
    
76.   
    
77.   ---
    
78.   
    
79.   select  * from departments;
    
80.   
    
81.   ---
    
82.   
    
83.   SELECT e.employee_id, e.last_name, e.department_id, d.department_name
    
84. FROM   employees e LEFT OUTER JOIN departments d
    
85. ON   (e.department_id = d.department_id) ;
    
86. 
    
87. 
    
88. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
    
89. FROM   employees e  , departments d
    
90. where  e.department_id =d.department_id(+);
    
91. 
    
92.   SELECT e.employee_id, e.last_name, e.department_id, d.department_name
    
93. FROM departments d   right OUTER JOIN  employees e
    
94. ON   (e.department_id = d.department_id) ;
    
95. ---
    
96. 
    
97. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
    
98. FROM   employees e right OUTER JOIN departments d
    
99. ON   (e.department_id = d.department_id) ;
    
100. 
     
101. 
     
102. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
     
103. FROM   employees e  , departments d
     
104. where  e.department_id(+) =d.department_id;
     
105. 
     
106.   SELECT e.employee_id, e.last_name, e.department_id, d.department_name
     
107. FROM departments d   left OUTER JOIN  employees e
     
108. ON   (e.department_id = d.department_id) ;
     
109. 
     
110. --
     
111. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
     
112. FROM   employees e  full OUTER JOIN departments d
     
113. ON   (e.department_id = d.department_id) ;
     
114. 
     
115. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
     
116. FROM   departments d   full OUTER JOIN  employees e
     
117. ON   (e.department_id = d.department_id) ;
     
118. 
     
119. 
     
120. 
     
121. --ERROR
     
122. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
     
123. FROM   employees e  , departments d
     
124. where  e.department_id(+) =d.department_id
     
125. union
     
126. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
     
127. FROM   employees e  , departments d
     
128. where  e.department_id=d.department_id(+);
     
129. 
     
130. ---
     
131. 
     
132. select  s.SID , n.NAME , se.VALUE
     
133. from v_$session s  , v_$sesstat se , v_$statname  n
     
134. where s.sid=se.SID  and se.STATISTIC#=n.STATISTIC#
     
135. and s.USERNAME='HR' and n.NAME='redo size'
     
136. ;
     
137. ---
     
138. select  p.SPID , s.USERNAME ,s.TERMINAL
     
139.   from v_$session s, v_$process p
     
140.   where s.PADDR =p.ADDR
     
141.   and s.SID=197;
     
142. ---
     
143. SELECT employee_id, last_name
     
144. FROM   employees
     
145. WHERE  salary   in
     
146.                 (SELECT   MAX(salary)
     
147.                  FROM     employees
     
148.                  GROUP BY department_id);
     
149.                  
     
150.                  ---
     
151.                  
     
152.     SELECT employee_id, last_name, job_id, salary
     
153. FROM   employees
     
154. WHERE  salary < ANY
     
155.                     (SELECT salary
     
156.                      FROM   employees
     
157.                      WHERE  job_id = 'IT_PROG')
     
158. AND    job_id <> 'IT_PROG';
     
159.             
     
160. ----
     
161. 
     
162. SELECT last_name, salary, department_id
     
163. FROM   employees o
     
164. WHERE  salary >
     
165.   (SELECT AVG(salary)
     
166. FROM   employees
     
167. WHERE  department_id =  
     
168.         o.department_id) ;
     
169.         
     
170.         ----
     
171.         
     
172.      SELECT o.last_name, o.salary, o.department_id, a.AVGSAL
     
173.       from     employees o , (SELECT AVG(salary)   AVGSAL ,department_id
     
174.                                     FROM   employees
     
175.                                      group by   department_id ) a
     
176.              where o.department_id=a.department_id and o.salary > a.avgsal;
     
177.         
     
178.         
     
179.       ---
     
180.       
     
181.         SELECT last_name, salary, department_id
     
182. FROM   employees o
     
183. WHERE salary in
     
184.   (SELECT AVG(salary)
     
185. FROM   employees
     
186. WHERE  department_id =  
     
187.         o.department_id) ;
     
188. 
     
189. SELECT e.employee_id, last_name,e.job_id
     
190. FROM   employees e
     
191. WHERE  employee_id in       (SELECT  employee_id
     
192.              FROM   job_history
     
193.              WHERE
     
194.                employee_id = e.employee_id  
     
195.                  group by employee_id  having  count(*) >=2);
     
196.                  
     
197.                  
     
198.    SELECT e.employee_id, last_name,e.job_id
     
199. FROM   employees e
     
200. WHERE exists      (SELECT  1
     
201.              FROM   job_history
     
202.              WHERE
     
203.                employee_id = e.employee_id  
     
204.                  group by employee_id  having  count(*) >=2);         
     
205. 
     
206. 
     
207. 
     
208. 
     

复制代码