SQL4 択難易度 応用

FROM のサブクエリに別名 t を付けて t.s を使いたい。正しいのは？

SELECT t.dept FROM AS t(SELECT dept, SUM(salary) AS s FROM emp GROUP BY dept)WHERE t.s >= 600 ORDER BY t.dept;

正答

1. SELECT t.dept FROM t AS (SELECT dept, SUM(salary) AS s FROM emp GROUP BY dept) WHERE t.s >= 600 ORDER BY t.dept;
2. SELECT t.dept FROM (SELECT dept, SUM(salary) AS s FROM emp GROUP BY dept) AS t WHERE t.s >= 600 ORDER BY t.dept;
3. SELECT t.dept FROM (SELECT dept, SUM(salary) AS s FROM emp GROUP BY dept AS t) WHERE t.s >= 600 ORDER BY t.dept;
4. SELECT t.dept FROM (SELECT dept, SUM(salary) AS s FROM emp GROUP BY dept) WHERE t.s >= 600 ORDER BY t.dept AS t;

解説

FROM 句のサブクエリに別名を付けるときは、括弧で囲んだ問い合わせの直後に AS t と書く。表に別名を付ける FROM emp AS e と同じ形で、「中身 AS 名前」の順になる。t AS (...) は CTE の書き方と混ざった形で、FROM の中では構文エラーになる。閉じ括弧の内側に AS t を書くと GROUP BY の途中に別名を置くことになり失敗する。ORDER BY の後ろに置く形も文法にない。

参考: www.sqlite.org

subqueryalias