设为首页收藏本站

Bo's Oracle Station

查看: 846|回复: 0

课程第12次(2016-04-22星期五):两表连接,子查询

[复制链接]

745

主题

1118

帖子

8124

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8124
发表于 2016-4-23 08:52:58 | 显示全部楼层 |阅读模式
本帖最后由 botang 于 2016-4-26 10:09 编辑

课程第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. select  * from locations;


  3. SELECT department_id, department_name,
  4.        location_id, city
  5. FROM   departments d
  6. NATURAL JOIN locations l ;


  7. SELECT department_id, department_name,
  8.        l.location_id, city
  9.    from departments d, locations l
  10.    where d.location_id=l.location_id;
  11.    
  12.    
  13.    select   e.employee_id,e.last_name,d.department_name, e.department_id
  14.      from employees e , departments d
  15.      where e.department_id=d.department_id
  16.      and e.manager_id=d.manager_id;
  17.      
  18.         select   e.employee_id,e.last_name,d.department_name, department_id
  19.      from employees e    natural join      departments d;
  20.   ----
  21.     select   e.employee_id,e.last_name,d.department_name, department_id
  22.      from employees e join  departments d
  23.      using (department_id);
  24.      
  25.      ---
  26.    
  27.   
  28.   ---
  29.      select   e.employee_id,e.last_name,d.department_name, d.department_id
  30.      from employees e   join departments d
  31.      on ( e.department_id=d.department_id)
  32.     ;
  33.      ---
  34.           SELECT l.city, d.department_name
  35. FROM   locations l JOIN departments d
  36. USING (location_id)
  37. WHERE location_id = 1400;
  38. ---

  39. select  worker.last_name||' works for '||manager.last_name
  40. from employees worker, employees  manager
  41. where worker.manager_id=manager.employee_id;
  42. --
  43. select  worker.last_name||' works for '||manager.last_name
  44. from employees worker  join  employees  manager
  45. on (worker.manager_id=manager.employee_id);

  46.      ---
  47. create table  job_grades( grade_level  char(1),
  48.                                            lowest_sal  number(8,2),
  49.                                            highest_sal  number(8,2));
  50.    
  51. insert into job_grades values (

  52. 'F', 25000 , 40000 ) ;

  53. select  * from   job_grades;
  54. ---

  55. select  e.employee_id, e.last_name , j.grade_level
  56.   from employees e , job_grades j
  57.   where e.salary between  j.lowest_sal  and j.highest_sal;
  58.   ---
  59.   select  e.employee_id, e.last_name , j.grade_level
  60.   from employees e  join   job_grades j
  61.   on e.salary between  j.lowest_sal  and j.highest_sal;
  62.   
  63.   ---
  64.   
  65.   select * from employees where employee_id=178;
  66.   
  67.   ---
  68.   
  69.   select  * from departments;
  70.   
  71.   ---
  72.   
  73.   SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  74. FROM   employees e LEFT OUTER JOIN departments d
  75. ON   (e.department_id = d.department_id) ;


  76. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  77. FROM   employees e  , departments d
  78. where  e.department_id =d.department_id(+);

  79.   SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  80. FROM departments d   right OUTER JOIN  employees e
  81. ON   (e.department_id = d.department_id) ;
  82. ---

  83. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  84. FROM   employees e right OUTER JOIN departments d
  85. ON   (e.department_id = d.department_id) ;


  86. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  87. FROM   employees e  , departments d
  88. where  e.department_id(+) =d.department_id;

  89.   SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  90. FROM departments d   left OUTER JOIN  employees e
  91. ON   (e.department_id = d.department_id) ;

  92. --
  93. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  94. FROM   employees e  full OUTER JOIN departments d
  95. ON   (e.department_id = d.department_id) ;

  96. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  97. FROM   departments d   full OUTER JOIN  employees e
  98. ON   (e.department_id = d.department_id) ;



  99. --ERROR
  100. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  101. FROM   employees e  , departments d
  102. where  e.department_id(+) =d.department_id
  103. union
  104. SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  105. FROM   employees e  , departments d
  106. where  e.department_id=d.department_id(+);

  107. ---

  108. select  s.SID , n.NAME , se.VALUE
  109. from v_$session s  , v_$sesstat se , v_$statname  n
  110. where s.sid=se.SID  and se.STATISTIC#=n.STATISTIC#
  111. and s.USERNAME='HR' and n.NAME='redo size'
  112. ;
  113. ---
  114. select  p.SPID , s.USERNAME ,s.TERMINAL
  115.   from v_$session s, v_$process p
  116.   where s.PADDR =p.ADDR
  117.   and s.SID=197;
  118. ---
  119. SELECT employee_id, last_name
  120. FROM   employees
  121. WHERE  salary   in
  122.                 (SELECT   MAX(salary)
  123.                  FROM     employees
  124.                  GROUP BY department_id);
  125.                  
  126.                  ---
  127.                  
  128.     SELECT employee_id, last_name, job_id, salary
  129. FROM   employees
  130. WHERE  salary < ANY
  131.                     (SELECT salary
  132.                      FROM   employees
  133.                      WHERE  job_id = 'IT_PROG')
  134. AND    job_id <> 'IT_PROG';
  135.             
  136. ----

  137. SELECT last_name, salary, department_id
  138. FROM   employees o
  139. WHERE  salary >
  140.   (SELECT AVG(salary)
  141. FROM   employees
  142. WHERE  department_id =  
  143.         o.department_id) ;
  144.         
  145.         ----
  146.         
  147.      SELECT o.last_name, o.salary, o.department_id, a.AVGSAL
  148.       from     employees o , (SELECT AVG(salary)   AVGSAL ,department_id
  149.                                     FROM   employees
  150.                                      group by   department_id ) a
  151.              where o.department_id=a.department_id and o.salary > a.avgsal;
  152.         
  153.         
  154.       ---
  155.       
  156.         SELECT last_name, salary, department_id
  157. FROM   employees o
  158. WHERE salary in
  159.   (SELECT AVG(salary)
  160. FROM   employees
  161. WHERE  department_id =  
  162.         o.department_id) ;

  163. SELECT e.employee_id, last_name,e.job_id
  164. FROM   employees e
  165. WHERE  employee_id in       (SELECT  employee_id
  166.              FROM   job_history
  167.              WHERE
  168.                employee_id = e.employee_id  
  169.                  group by employee_id  having  count(*) >=2);
  170.                  
  171.                  
  172.    SELECT e.employee_id, last_name,e.job_id
  173. FROM   employees e
  174. WHERE exists      (SELECT  1
  175.              FROM   job_history
  176.              WHERE
  177.                employee_id = e.employee_id  
  178.                  group by employee_id  having  count(*) >=2);         




复制代码



回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2019-1-19 08:35 , Processed in 0.171224 second(s), 24 queries .

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