课程第12次（2017-03-20星期一）

botang

上完1Z0-051第5章：组函数和分组
上完1Z0-051第6章：表连接
进行1Z0-051第7章：子查询和关联子查询
1Z0-051共12章（上完7章），1Z0-052共19章（上完3章），1Z0-053共21章（上完0章）
总共上完全部52章中的10章

1. select  count(e.commission_pct) from employees  e;
   
2. 
   
3. 
   
4. 
   
5. select  sum(e.commission_pct)/count(e.commission_pct)
   
6. from employees e;
   
7. 
   
8. select  avg(e.commission_pct)
   
9.   from employees e;
   
10.   
    
11.   SELECT AVG(NVL(commission_pct, 0))
    
12. FROM   employees;
    
13. 
    
14. select  sum(e.commission_pct)/count(*)
    
15. from employees e;
    
16. ----
    
17. SELECT   department_id, manager_id, AVG(salary)
    
18. FROM     employees
    
19. GROUP BY department_id , manager_id
    
20. order by 1,2;
    
21. 
    
22. ---
    
23. select  * from employees where employee_id=178;
    
24. 
    
25. ---
    
26. 
    
27. SELECT   department_id , AVG(salary) a
    
28. FROM     employees
    
29. where department_id  <70
    
30. GROUP BY department_id
    
31. having    AVG(salary) > 8000
    
32. ;
    
33. ---
    
34. 
    
35. select  department_id, job_id , avg(salary)
    
36.   from employees
    
37.   group by rollup (department_id, job_id);
    
38.   
    
39. 
    
40. 
    
41. select  department_id, job_id , avg(salary)
    
42.   from employees
    
43.   group by cube (department_id, job_id);
    
44.   
    
45.   ----
    
46.   
    
47.   select e.employee_id, d.department_name, d.department_id
    
48.     from employees e , departments d
    
49.     where e.department_id=d.department_id;
    
50.     ---
    
51.     select e.employee_id, d.department_name, department_id
    
52.     from employees e    join departments d
    
53.     using ( department_id);
    
54.    
    
55.     ---
    
56.       select e.employee_id, d.department_name, e.department_id
    
57.     from employees e  join departments d
    
58.     on ( e.department_id=d.department_id) ;
    
59.    
    
60.     ---
    
61.    
    
62.      select e.employee_id, d.department_name, department_id
    
63.     from employees e  natural   join departments d;
    
64.    
    
65.     select  * from departments;
    
66.     ---
    
67.       select e.employee_id, d.department_name, d.department_id
    
68.     from employees e , departments d
    
69.     where e.department_id=d.department_id  
    
70.      and e.manager_id=d.manager_id;
    
71.      
    
72.      ---
    
73.      SELECT employee_id, city, department_name
    
74. FROM   employees e
    
75. JOIN   departments d
    
76. ON     (d.department_id = e.department_id )
    
77. JOIN   locations l
    
78. ON    ( d.location_id = l.location_id)
    
79. where l.city='Southlake';
    
80. 
    
81. ---
    
82.    SELECT employee_id, city, department_name
    
83. FROM   employees e, departments d, locations l
    
84. where d.department_id = e.department_id and
    
85.   d.location_id = l.location_id
    
86.   and l.city='Southlake';
    
87.   
    
88.   ---
    
89.   
    
90. SELECT worker.last_name emp, manager.last_name mgr
    
91. FROM   employees worker , employees manager
    
92. where    worker.manager_id = manager.employee_id;
    
93. ---
    
94. 
    
95. SELECT e.last_name, e.salary, j.grade_level
    
96. FROM   employees e , job_grades j
    
97. where   e.salary
    
98.        BETWEEN j.lowest_sal AND j.highest_sal;
    
99.       
    
100.        ---
     
101.       
     
102.        select  e.employee_id ,e.last_name  ,d.department_name
     
103.          from employees  e , departments d
     
104.          where e.department_id =d.department_id (+);
     
105.   
     
106. ----
     
107. 
     
108.          
     
109.          select  e.employee_id ,e.last_name  ,d.department_name
     
110.           from employees  e  left outer join departments d
     
111.           on (e.department_id  =d.department_id);
     
112.          
     
113.              select  e.employee_id ,e.last_name  ,d.department_name
     
114.           from employees  e  left outer join departments d
     
115.           using (department_id) ;
     
116.          
     
117.          
     
118.                   select  e.employee_id ,e.last_name  ,d.department_name
     
119.           from  departments d  right outer join  employees  e
     
120.           on (e.department_id  =d.department_id);
     
121.          
     
122.               select  e.employee_id ,e.last_name  ,d.department_name
     
123.           from   departments d      right  outer join  employees  e
     
124.           using (department_id) ;
     
125.           ----
     
126.           select  * from departments ;
     
127. 
     
128.        select  e.employee_id ,e.last_name  ,d.department_name
     
129.          from employees  e , departments d
     
130.          where e.department_id (+) =d.department_id ;
     
131.          
     
132.          select  e.employee_id ,e.last_name  ,d.department_name
     
133.          from employees  e   right outer join  departments d
     
134.          on (e.department_id =d.department_id);
     
135.          
     
136.             select  e.employee_id ,e.last_name  ,d.department_name
     
137.          from employees  e   right outer join  departments d
     
138.          using (department_id );
     
139.          
     
140.           select  e.employee_id ,e.last_name  ,d.department_name
     
141.          from departments d     left outer join  employees  e   
     
142.          on (e.department_id =d.department_id);
     
143.          
     
144.             select  e.employee_id ,e.last_name  ,d.department_name
     
145.          from     departments d   left  outer join  employees  e
     
146.          using (department_id );
     
147.          
     
148.          
     
149.          
     
150.          
     
151.          
     
152.          ---
     
153.    
     
154.        select  e.employee_id ,e.last_name  ,d.department_name
     
155.          from employees  e , departments d
     
156.          where e.department_id (+) =d.department_id
     
157.          union
     
158.            select  e.employee_id ,e.last_name  ,d.department_name
     
159.          from employees  e , departments d
     
160.          where e.department_id  =d.department_id(+);
     
161.          
     
162.          ---
     
163.            select  e.employee_id ,e.last_name  ,d.department_name
     
164.          from     departments d   full  outer join  employees  e
     
165.          using (department_id );
     
166.          
     
167.          
     
168.             select  e.employee_id ,e.last_name  ,d.department_name
     
169.          from    employees  e    full  outer join  departments d
     
170.          using (department_id );
     
171.          
     
172.          ----
     
173.          
     
174.          SELECT last_name, salary
     
175. FROM   employees
     
176. WHERE  salary > any
     
177.                (SELECT salary
     
178.                 FROM   employees
     
179.                 WHERE  last_name  like 'T%');
     
180.                 ----
     
181.      
     
182. select  last_name from employees order by 1;
     
183.            
     
184.         SELECT last_name, job_id, salary
     
185. FROM   employees
     
186. WHERE  job_id  in   
     
187.                 (SELECT job_id
     
188.                  FROM   employees
     
189.                  WHERE  last_name = 'Taylor')
     
190. AND    salary  in
     
191.                 (SELECT salary
     
192.                  FROM   employees
     
193.                  WHERE  last_name = 'Taylor');
     
194.                  
     
195.                  
     
196.                  ---
     
197.                  
     
198.                    SELECT last_name, job_id, salary
     
199. FROM   employees
     
200. WHERE  (job_id , salary)  in   
     
201.                 (SELECT job_id , salary
     
202.                  FROM   employees
     
203.                  WHERE  last_name = 'Taylor')
     
204. ;               
     
205.                
     

复制代码