SQL4 択難易度 基礎

給与 200 以上の行だけを部署ごとに数えたい。正しいのは？

SELECT dept, COUNT(*) AS n FROM empGROUP BY dept WHERE salary >= 200ORDER BY dept;

正答

1. SELECT dept, COUNT(*) AS n WHERE salary >= 200 FROM emp GROUP BY dept ORDER BY dept;
2. SELECT dept, COUNT(*) AS n FROM emp WHERE salary >= 200 GROUP BY dept ORDER BY dept;
3. SELECT dept, COUNT(*) AS n FROM emp GROUP BY dept ORDER BY dept WHERE salary >= 200;
4. SELECT dept, COUNT(*) AS n FROM emp WHERE salary >= 200 ORDER BY dept GROUP BY dept;

解説

WHERE はグループ化の前に個々の行を絞る句なので、GROUP BY より前に書く。書く順番は FROM → WHERE → GROUP BY → ORDER BY で、評価の順番も同じく WHERE が先、GROUP BY が後になる。GROUP BY の後ろに WHERE を書くと SQLite は構文エラーを返す。ORDER BY を GROUP BY より前に置く形も誤りで、並べ替えはグループ化した後の結果に対して行うため最後のほうに書く。

参考: www.sqlite.org

wheregroup-byclause-order