単一テーブルクエリ基本構造

SELECT [DISTINCT] 列名|式|集約関数() [AS] [別名]
FROM テーブル名[別名]|DUAL
[WHERE 条件式]
[GROUP BY グループ化列]
[HAVING グループ条件]
[ORDER BY 列|数値|別名|式 [ASC|DESC] [NULLS FIRST|NULLS LAST]];

SELECT句の操作

-- DISTINCTは後続の全ての列に適用
SELECT * FROM employees;
SELECT employee_id, last_name, job_id FROM employees;
SELECT employee_id AS "社員番号", salary * 12 年収 FROM employees;
SELECT DISTINCT department_id FROM employees;
SELECT DISTINCT department_id, job_id FROM employees;

WHERE句の条件指定

-- 日付形式はDB設定に依存、大文字小文字区別あり
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;
SELECT * FROM employees WHERE hire_date BETWEEN '01-JAN-2000' AND '31-DEC-2000';
SELECT * FROM employees WHERE employee_id IN (100, 101, 102);
SELECT * FROM employees WHERE commission_pct IS NOT NULL;
SELECT * FROM employees WHERE last_name LIKE 'S%';
SELECT * FROM employees WHERE department_id = 50 AND salary > 6000;

ORDER BY句の並び替え

SELECT * FROM employees ORDER BY salary DESC;
SELECT employee_id, last_name, salary * 12 年収 FROM employees ORDER BY 年収 DESC;
SELECT * FROM employees ORDER BY department_id NULLS FIRST;
SELECT * FROM employees ORDER BY department_id ASC, salary DESC;

GROUP BY句の集約処理

SELECT department_id, AVG(salary) FROM employees 
GROUP BY department_id HAVING AVG(salary) > 7000;
SELECT department_id, job_id, SUM(salary) FROM employees 
GROUP BY department_id, job_id;
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

グループ化拡張機能

SELECT department_id, job_id, SUM(salary) FROM employees
GROUP BY ROLLUP(department_id, job_id);

Oracle関数の活用

文字列操作関数

SELECT LOWER('ORACLE'), UPPER('sql'), INITCAP('database') FROM DUAL;
SELECT CONCAT('Hello', 'World') FROM DUAL;
SELECT SUBSTR('OracleDB', 4, 3) FROM DUAL; -- cle
SELECT LENGTH('データ'), LENGTHB('データ') FROM DUAL; -- 2, 6
SELECT REPLACE('Java', 'a', 'X') FROM DUAL; -- JXvX
SELECT INSTR('Database', 'a') FROM DUAL; -- 2

数値処理関数

SELECT ROUND(123.456, 2), ROUND(123.456, -1) FROM DUAL; -- 123.46, 120
SELECT TRUNC(123.456, 2), TRUNC(123.456, -1) FROM DUAL; -- 123.45, 120
SELECT MOD(1500, 400) FROM DUAL; -- 300

日付関連関数

SELECT SYSDATE - 1 昨日, SYSDATE 今日 FROM DUAL;
SELECT last_name, MONTHS_BETWEEN(SYSDATE, hire_date) 勤続月数 FROM employees;
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
SELECT NEXT_DAY(SYSDATE, '月曜日') FROM DUAL;

型変換関数

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI') FROM DUAL;
SELECT TO_CHAR(12345.67, 'L99G999D99') FROM DUAL; -- ￥12,345.67
SELECT TO_NUMBER('￥12,345', 'L99G999') FROM DUAL; -- 12345
SELECT TO_DATE('2023-12-01', 'YYYY-MM-DD') FROM DUAL;

NULL処理関数

SELECT salary, commission_pct, NVL(commission_pct, 0) FROM employees;
SELECT salary, commission_pct, NVL2(commission_pct, salary*1.1, salary) FROM employees;
SELECT COALESCE(commission_pct, salary, 0) FROM employees;