SQL and Database Programming
Domain 3 - SQL and database programming
Questions test precise SQL semantics: joins, subqueries, aggregation, NULL handling, and the DDL/DML/DCL split. You also need ストアドプロシージャ, トリガ, ビュー, and 埋め込みSQL/動的SQL.
Core rules
- DDL (CREATE/ALTER/DROP) defines objects; DML (SELECT/INSERT/UPDATE/DELETE) manipulates rows; DCL (GRANT/REVOKE) controls privileges.
- INNER JOIN drops unmatched rows; LEFT OUTER JOIN keeps all left rows and fills NULLs.
- 相関副問い合わせ references the outer query and is evaluated per row; 非相関 can be evaluated once.
- NOT IN with a subquery that returns NULL yields UNKNOWN for every row, so no rows come back; use NOT EXISTS.
- WHERE filters rows before grouping; HAVING filters groups after GROUP BY.
- Aggregate functions ignore NULL, except COUNT(*).
Worked example
"List departments with no employees."
SELECT d.部門コード FROM 部門 d WHERE NOT EXISTS (SELECT 1 FROM 社員 e WHERE e.部門コード = d.部門コード);
The correlated EXISTS is TRUE only when a matching employee exists, so NOT EXISTS keeps empty departments. The LEFT JOIN form FROM 部門 d LEFT JOIN 社員 e ON ... WHERE e.社員番号 IS NULL works only if 社員番号 is NOT NULL; otherwise a matched row with a NULL key is misread as unmatched. NOT IN (SELECT 部門コード FROM 社員) fails if any 部門コード is NULL, because the comparison becomes UNKNOWN.
Traps
COUNT(*) vs COUNT(列); nesting aggregates without a subquery; selecting columns not in GROUP BY; implicit type conversion disabling an index; ORDER BY interacting with DISTINCT; and recursive triggers.