SQL 練習 ドリル 研修コースに参加してみた

SELECT empno, ename, job, salary+comm FROM emp WHERE deptno=30;

解説
• + で計算ができます
• sqlary+comm を別の列名にするときは、横にその別名を書く

  SELECT empno, ename, job, salary+comm annsal FROM emp WHERE deptno=30;

SELECT deptno,ename,job,salary FROM emp WHERE salary NOT BETWEEN 300000 AND 400000;
SELECT deptno,ename,job,salary FROM emp WHERE salary < 300000 OR salary > 400000;
SELECT deptno,ename,job,salary FROM emp WHERE NOT (salary >= 300000 AND salary <= 400000);

解説
• OR に書き換えるのは考えるのが難しい
• NOT ( 条件1 AND 条件2) の () を外すとどうなるでしょう？
  • NOT の方が強いので、 AND は条件から外れます

SELECT empno,ename FROM emp WHERE ename LIKE '%木　%';

解説ポイント
• LIKE を使う
• 文字列のパターンを示すには % % でくくる
  • 1 文字を示すのが _
• 頭の文字がわからない状態で LIKE を使うとパフォーマンスが落ちる

SELECT empno,ename,deptno FROM emp ORDER BY deptno NULLS FIRST;
SELECT empno,ename,job,salary+comm FROM emp ORDER BY salary+comm;
SELECT empno,ename,job,salary+comm FROM emp ORDER BY 4;
SELECT empno,ename,job,NVL(salary,0)+NVL(comm,0) annsal FROM emp ORDER BY annsal;

解説
• NULL は最大値の扱いになってしまう（ Oracle 、 PostgreSQL の場合）
• NULLS FIRST を使う
• 値に変えてしまう、というやり方もある
  • NVL(カラム, 値)

SELECT COUNT(*),SUM(salary),AVG(salary),MAX(salary),MIN(salary) FROM emp;

解説
• 集計関数
  • 人数 COUNT()
    • カラム名だけでなく * ですべて指定することもできる
    • * で指定すると NULL もカウントされる
      • カラム名で指定すると NULL はカウントしない
  • 合計 SUM()
  • 平均 AVG()
  • 最大値 MAX()
  • 最小値 MIN()

SELECT deptno,COUNT(*),COUNT(comm),COUNT(DISTINCT job) FROM emp GROUP BY deptno;

解説
• GROUP BY を使います
  • GROUP BY で指定した列名以外を SELECT には指定できない
  • SELECT に入れたい列名があれば、 GROUP BY にも入れる
  • ただし挙動は RDBMS によって変わる
• 種類数
  • まず DISTINCT で種類を求める
  • その上で COUNT()

SELECT deptno,dname FROM dept d 
  WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno=d.deptno);
/* IN を使う場合 */
SELECT deptno,dname FROM dept WHERE deptno IN (SELECT deptno FROM emp);

解説
• WHERE は IN を使って複数レコードが返ってきても良いようにする
• ただ何回も同じことを比較してしまうので、この問題ではパフォーマンスが悪い
• EXISTS を使う
  • 存在チェックを行う
  • 1 つでも値があれば、以降はチェックしない
  • (SELECT 1 FROM emp e WHERE e.deptno=d.deptno) の 1 は何でも良い
• 演算子で >, <, >=, <= を使うときはどうするか
  • ANY か ALL を使う
    • ALL すべての値と比較
    • ANY いずれかの値と比較

  SELECT deptno,dname FROM dept WHERE deptno > ANY (SELECT deptno FROM emp);

SELECT empno,ename,salary,deptno,mgrno 
  FROM emp WHERE (deptno,mgrno) IN (SELECT deptno,mgrno 
  FROM emp WHERE salary = (SELECT MIN(salary) FROM emp));

解説
• 分解して考えましょう
  • 最も給与が少ない従業員
  • 従業員の部門番号(deptno)と直属の上司(mgrno）
• カラムが 2 つに返ってくる場合は WHERE (カラム1, カラム2)

/* oracle */
SELECT d.deptno,d.dname,COUNT(*),SUM(salary) 
  FROM dept d,emp e WHERE d.deptno = e.deptno GROUP BY d.deptno,d.dname;
/* FROM に副問合せを使う */
SELECT d.deptno,d.dname,e.cntEmp,e.sumSal 
  FROM dept d,(SELECT deptno,COUNT(*) cntEmp,SUM(salary) sumSal 
  FROM emp GROUP BY deptno) e WHERE d.deptno = e.deptno;
/* 普通の JOIN  */
SELECT d.deptno,d.dname,COUNT(*),SUM(salary) 
  FROM dept d JOIN emp e ON d.deptno = e.deptno 
  GROUP BY d.deptno,d.dname;

解説
• FROM を使った副問合せは JOIN よりパフォーマンスが良い可能性がある
  • JOIN はテーブルごと結合するが、 WHERE などでレコードが少なくなっている

SELECT e.ename,e.salary,g.grade 
  FROM emp e, salgrade g WHERE e.salary 
  BETWEEN g.lowsal AND g.hisal ORDER BY salary DESC;

SELECT e.ename,e.salary,g.grade 
  FROM emp e JOIN salgrade g ON e.salary 
  BETWEEN g.lowsal AND g.hisal ORDER BY salary DESC;

解説
• 別テーブルの値を BETWEEN に入れて WHERE で比較すればできる
• なお NATURAL JOIN や USING は等価結合なので使えない

/* Oracle */
SELECT d.deptno,d.dname,e.empno,e.ename 
  FROM dept d,emp e WHERE d.deptno(+) = e.deptno;
/* SQL 1999 構文*/
SELECT d.deptno,d.dname,e.empno,e.ename 
  FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;
SELECT d.deptno,d.dname,e.empno,e.ename 
  FROM dept d RIGHT OUTER JOIN emp e ON d.deptno = e.deptno;

解説
• 外部結合を使う
• Oracle では (+) を使う
• SQL 1999 構文では OUTER JOIN を使う
  • LEFT では OUTER より左側の FROM のテーブルを使う
  • RIGHT では OUTER より右側の JOIN のテーブルを使う

SELECT deptno FROM dept MINUS SELECT deptno FROM emp;

解説
• 集合演算子は 3 つ
  • UNION くっつけてすべて返す
  • INTERSECT 同じ部分だけを返す
  • MINUS 重なった部分だけをひく
    
    • NOT IN でも書けるが長くなる