皆さん、AP試験対策お疲れ様です。科目Bのデータベース分野でよく出るSQL文パターンを、暗記用にまとめますね。
## 1. 基本的なSELECT文
```sql
- 単純な条件抽出
SELECT 商品名, 単価 FROM 商品 WHERE 単価 >= 1000;
- 複数条件（AND/OR）
SELECT * FROM 社員 WHERE 部署コード = '01' AND 年齢 >= 30;
- パターンマッチ（LIKE）
SELECT * FROM 顧客 WHERE 氏名 LIKE '田中%';
- NULL判定
SELECT * FROM 注文 WHERE 発送日 IS NULL;
```
## 2. 集合関数・GROUP BY・HAVING
```sql
-- 集計 SELECT 部署コード, COUNT(*), AVG(給与) FROM 社員 GROUP BY 部署コード;
- 集計結果の絞り込み(HAVING)
SELECT 部署コード, SUM(売上) FROM 売上実績 GROUP BY 部署コード HAVING SUM(売上) > 1000000;
```
※ WHEREは集計前の行を絞り、HAVINGは集計後のグループを絞る、という違いが頻出ポイントです。
## 3. 結合(JOIN)
```sql
- 内部結合
SELECT A.社員名, B.部署名 FROM 社員 A INNER JOIN 部署 B ON A.部署コード = B.部署コード;
- 外部結合(左外部結合)
SELECT A.社員名, B.部署名 FROM 社員 A LEFT OUTER JOIN 部署 B ON A.部署コード = B.部署コード;
```
※ 左外部結合は「右側にマッチしなくても左側は全件出す」がポイント(未所属社員も出したい場合など)。
## 4. 副問い合わせ(サブクエリ)
```sql
- IN を使ったサブクエリ
SELECT 氏名 FROM 社員 WHERE 部署コード IN (SELECT 部署コード FROM 部署 WHERE 地域 = '東海');
- 相関副問い合わせ(EXISTS)
SELECT 氏名 FROM 社員 A WHERE EXISTS ( SELECT * FROM 注文 B WHERE A.社員コード = B.担当者コード);
```
※ EXISTSは「外側の行ごとに」内側を評価する相関サブクエリが定番の出題パターンです。
## 5. 更新系(UPDATE/DELETE/INSERT)
```sql
- 条件付き更新
UPDATE 商品 SET 単価 = 単価 * 1.1 WHERE 分類コード = '02';
- サブクエリを使った削除
DELETE FROM 注文明細 WHERE 注文番号 IN (SELECT 注文番号 FROM 注文 WHERE キャンセル区分 = '1');
- 挿入
INSERT INTO 顧客 (顧客コード, 氏名, 登録日) VALUES ('C001', '鈴木一郎', CURRENT_DATE);```
## 6. ビュー(VIEW)
```sql
CREATE VIEW 部署別売上 AS SELECT 部署コード, SUM(売上高) AS 売上合計 FROM 売上実績 GROUP BY 部署コード;
```
※ ビューへのUPDATEが可能かどうか(集計関数を含む場合は不可、など)も過去問で問われます。
## 7. トランザクション制御
```sql
BEGIN TRANSACTION; UPDATE 口座 SET 残高 = 残高 - 10000 WHERE 口座番号 = 'A001'; UPDATE 口座 SET 残高 = 残高 + 10000 WHERE 口座番号 = 'B001'; COMMIT;
- 異常時は ROLLBACK;
```
※ ACID特性(原子性・一貫性・独立性・耐久性)とセットで問われやすいです。
## 8. 権限制御(GRANT/REVOKE)
GRANT SELECT, UPDATE ON 社員 TO ユーザーA; REVOKE UPDATE ON 社員 FROM ユーザーA;
```
## 9. インデックス・制約
```sql
CREATE INDEX idx_社員_部署 ON 社員(部署コード);
ALTER TABLE 社員 ADD CONSTRAINT fk_部署 FOREIGN KEY (部署コード) REFERENCES 部署(部署コード);
```
## 10. インデックス・制約ALTER TABLE 頻出パターン集
- 一意制約(UNIQUE)の追加
ALTER TABLE 社員 ADD CONSTRAINT uq_社員番号 UNIQUE (社員番号);
- 列の追加
ALTER TABLE 社員 ADD 電話番号 VARCHAR(15);
- 列の削除
ALTER TABLE 社員 DROP COLUMN 電話番号;
- 列のデータ型・定義変更
ALTER TABLE 社員 MODIFY 給与 DECIMAL(10,2);
- ※ MODIFYはMySQL系。標準SQL/一部DBでは下記の書き方も出る
ALTER TABLE 社員 ALTER COLUMN 給与 TYPE DECIMAL(10,2);
- 列名の変更
ALTER TABLE 社員 RENAME COLUMN 氏名 TO 従業員氏名;
- テーブル名の変更
ALTER TABLE 社員 RENAME TO 従業員;
- NOT NULL制約の追加
ALTER TABLE 社員 MODIFY 部署コード VARCHAR(2) NOT NULL;
- 主キー制約の追加
ALTER TABLE 社員 ADD CONSTRAINT pk_社員 PRIMARY KEY (社員コード);
- 外部キー制約の追加
ALTER TABLE 社員 ADD CONSTRAINT fk_部署 FOREIGN KEY (部署コード) REFERENCES 部署(部署コード);
- 制約の削除
ALTER TABLE 社員 DROP CONSTRAINT fk_部署;
- デフォルト値の設定
ALTER TABLE 社員 ALTER COLUMN 入社区分 SET DEFAULT '正社員';
## 11.WITH句(共通表式/CreateViewのような機能)の例
```sql
WITH 部署別平均給与 AS ( SELECT 部署コード, AVG(給与) AS 平均給与 FROM 社員 GROUP BY 部署コード ) SELECT A.社員名, A.給与, B.平均給与 FROM 社員 A JOIN 部署別平均給与 B ON A.部署コード = B.部署コード WHERE A.給与 > B.平均給与;
```
やっていること**:
1. `WITH` で「部署別平均給与」という一時的な集計結果(名前付きのテーブルのようなもの)を先に作る
2. そのあとの `SELECT` 文で、社員テーブルと結合して「自分の部署の平均給与より高い給与をもらっている社員」を抽出する
ポイント(試験で狙われやすいところ)**:
- WITH句を使わずに同じことをやろうとすると、サブクエリを何度も書く必要があって読みにくくなる → WITH句は「複雑な副問い合わせを分かりやすく名前付けして再利用できる」のがメリット
- WITH句で定義した名前(この例では「部署別平均給与」)は、そのSQL文の中でだけ使える一時的なものであり、実テーブルとして保存されるわけではない
- 複数のWITH句をカンマ区切りで並べて定義することもできる(例:`WITH A AS (...), B AS (...) SELECT ...`)
科目Bでは「このWITH句を使わずに書き直すとどうなるか」「WITH句の中身を読み解いて結果を答える」といった形式で出やすいので、上の例のように「先に集計→あとで結合・比較」という流れをイメージで掴んでおくと読みやすくなりますよ。
特に頻出の狙われ方**:
- WHERE と HAVING の使い分け
- 内部結合 vs 外部結合の結果件数の違い
- 相関副問い合わせ(EXISTS/NOT EXISTS)の動作の読み取り
- トランザクションの排他制御(デッドロック含む)
- 正規化(第1～第3正規形)とE-R図の対応関係
もし希望があれば、この中の特定パターン(例:相関副問い合わせの読み取り問題)だけを深掘りした練習問題を作ることもできますよ。
