SQL_training - note4recurrent @ ウィキ

• 複数テーブルの結合と絞りこみ

  select Hell.id, Hell.name, Element.name as element, Grade.name as grade
  from Hell
  join Element
  on Hell.element_id = Element.id
  join Grade
  on Hell.grade_id = Grade.id
  where Element.name ="Air" and  Grade.name ="Boss"
  

• 副問合せと複数joinの組み合わせ
  • Hellテーブルから、モンスターの「Graffiacane」(Hellテーブルのnameが「 Graffiacane 」のレコード)
  • の弱点となる属性名を持ち、階級が「Boss」であるレコードをすべて出力してください。
  • 弱点はElementCompatibilityテーブルに格納されており、
  • 弱点に対応する属性名はelement_id、weakness_element_idを外部キーとしてElementテーブルを
  • 外部参照することで取得できます。

    select H.id , H.name, E.name as element, G.name as grade
    from Hell as H
    join Element as E　
    on H.element_id = E.id
    join Grade as G　
    on H.grade_id = G.id
    join ElementCompatibility as EC
    on E.id = EC.weakness_element_id
    where G.name = "Boss" and H.element_id = (
    select EC.weakness_element_id from Hell as H
    join Element as E
    on E.id = H.element_id
    join ElementCompatibility as EC
    on E.id = EC.element_id
    where H.name ="Graffiacane"
    )
    

• SCOTT と同じdeptno,job の社員の ename,deptno.jobを表示

  SELECT ename, deptno, job
  FROM   emp
  WHERE  (deptno,job)  = (SELECT deptno,job 
                          FROM   emp
                          WHERE  ename='SCOTT');
  

• 最低sal のename,sal

  SELECT ename, sal
  FROM   emp
  WHERE  sal = (SELECT MIN(sal) 
                FROM   emp);
  
  

• deptno 別の平均sal が最低の部署のdeptno,平均salを表示

  SELECT deptno, TRUNC(AVG(sal)) as AVG_SAL
  FROM   emp
  GROUP BY deptno
  HAVING AVG(sal) = (SELECT MIN(AVG(sal))
                     FROM   emp
                     GROUP BY deptno);
  

• 「自分の所属する部門の平均給与よりも、給与が高い社員は誰か」

  SELECT ename, deptno, sal
  FROM   emp o
  WHERE  sal > (SELECT AVG(sal)
                FROM   emp i
                GROUP BY deptno
                HAVING i.deptno = o.deptno );
  

• 上と同じ条件

  SELECT  e.ename, e.deptno, e.sal
  FROM    emp e 
  JOIN    (SELECT deptno, AVG(sal) avg_sal
  　         FROM   emp
  　         GROUP BY deptno) i
  ON      e.deptno = i.deptno
  WHERE   e.sal > i.avg_sal;