AP データベース SQL基礎

AP データベースに戻る

概要

　応用情報処理技術者試験の問題に取り上げられるようなSQLを紹介していきます。未完成です。2割くらいしかまだまとめれてないと思います。令和7年春の試験に間に合わず。力になれず申し訳なし。

　

CREATE TABLE

CREATE TABLE 社員 (
  社員ID INT PRIMARY KEY,
  氏名 VARCHAR(20),
  所属部署 VARCHAR(20),
  給与 INT CHECK (給与 >= 0)
);

　表に以下の列が定義された状態のデータベースが作成されます。

• 社員ID 整数型 主キー

• 氏名 文字型20桁

• 所属部署 文字型20桁

• 給与 整数型 0以上の整数である検査付き

こういった列ごとの入力できる形式を定義することを制約 CONSTRAINT と呼んでいます。

社員

社員ID	氏名	所属部署	給与

　

INSERT

　INSERT句でデータを挿入できます。以下の例では4レコード挿入しています。これで表にデータがある状態になりました。ここで作った表をこの記事のベースにしたいと思います。

INSERT INTO 社員(社員ID, 氏名, 所属部署, 給与)
VALUES
  (1,	'山田太郎',	'営業部',	300000),
  (2,	'佐藤花子',	'開発部',	400000),
  (3,	'鈴木一郎',	'営業部',	350000),
  (4,	'田中美咲',	'総務部',	280000);

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
2	佐藤花子	開発部	400000
3	鈴木一郎	営業部	350000
4	田中美咲	総務部	280000

　

SELECT

　SELECT句でデータの抽出ができます。抜き出したい列を指定すると、以下のようになります。列に*を指定すると、すべての列が表示されます。

SELECT 氏名, 給与 FROM 社員;

社員

氏名	給与
山田太郎	300000
佐藤花子	400000
鈴木一郎	350000
田中美咲	280000

　

FROM

SELECT 所属部署毎給与合計.所属部署, 給与合計 
FROM 
( SELECT 所属部署, SUM(給与) AS 給与合計 
  GROUP BY 所属部署
  FROM 社員) 所属部署毎給与合計;

　表を指定するときにFROM句で指定します。FROM句の中で加工済みの表を指定する副問合せも指定できますので、SELECT句の後にまたSELECT句があるときはFROM句のような表参照のための句があると考えていいです。SELECT句の後のSELECT句が来る場合は、後で紹介する内部結合のINNER JOIN句、あるいは外部結合のLEFT OUTER JOIN句、RIGHT OUTER JOIN句、FULL OUTER JOIN句があると考えてよいでしょう。応用処理技術者試験ではよく穴埋めになる箇所です。SELECT句の直前が穴埋めになっている場合は、この副問合せか、後ほど紹介する内部結合、外部結合の句が使われます。表の中から列を抜き出す射影や再集計をしない場合はSELCT句が来ない場合もあるので、注意が必要です。

所属部署毎給与合計;

所属部署毎給与合計.所属部署	給与合計
営業部	650000
開発部	400000
総務部	280000

WHERE

WHERE句でグループ化される前のフィルタリング条件指定ができます。

SELECT * FROM 社員 WHERE 所属部署='営業部';

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
3	鈴木一郎	営業部	350000

　

ORDER BY

　昇順や降順に並べる列名を指定するときに使います。DESCで降順です。ASCで昇順です。ORDER BY句のASCは規定値です。なのでASCの指定は省略できます。

SELECT * FROM 社員 ORDER BY 給与 DESC;

社員

社員ID	氏名	所属部署	給与
2	佐藤花子	開発部	400000
3	鈴木一郎	営業部	350000
1	山田太郎	営業部	300000
4	田中美咲	総務部	280000

　

GROUP BY

SELECT 所属部署, AVG(給与) AS 平均給与 
FROM 社員 
GROUP BY 所属部署;

社員

所属部署	平均給与
営業部	325,000
開発部	400,000
総務部	280,000

　

HAVING

　HAVING句はWHERE句に似ていますが、以下のような違いがあります。

• WHERE句はグループ化前に個々の行にフィルタリングを行います。
• HAVING句はグループ化後に集約結果に対する条件を指定します。

　したがってGROUP BY 句のところでよく使われたりします。

SELECT 所属部署, AVG(給与) AS 平均給与 
FROM 社員 
GROUP BY 所属部署 
HAVING AVG(給与) > 300000;

社員

所属部署	平均給与
営業部	325,000
開発部	400,000

　

DISTINCT

　抽出した結果に重複があれば、省略して結果表示してくれます。

SELECT DISTINCT 所属部署 FROM 社員;

社員

所属部署
営業部
開発部
総務部

　

LIKE

　%によるワイルドカード指定ができます。IPAのSQLのDB変数みたいなものバインド変数またはプレースホルダですが、これを含めるときは '%' || :変数名 || '%'のように指定します。実際に使うときは先に記述した指定したとおりになりますが、プレースホルダの指定方法について、IPAでは特にきまりは無いので、'%:変数名%'として正解にしてくれていると思います。たぶん。実際はアプリケーションで組み込むときには違う表現になるような気もします。

PHPだとこんな感じ。

$stmt = $pdo->prepare("SELECT * FROM 社員 WHERE 氏名 LIKE :name");
$stmt->execute([':name' => '%' . $searchWord . '%']);

　SQL自体での動作はシンプルは以下のようなものです。

SELECT * FROM 社員 WHERE 氏名 LIKE '田%';

社員

社員ID	氏名	所属部署	給与
4	田中美咲	総務部	280000

　

UPDATE ～ SET ～

UPDATE 社員 SET 給与=310000 WHERE 氏名='山田太郎';

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	310000
2	佐藤花子	開発部	400000
3	鈴木一郎	営業部	350000
4	田中美咲	総務部	280000

　

DELETE

DELETE FROM 社員 WHERE 所属部署='総務部';

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
3	鈴木一郎	営業部	350000

　

INNER JOIN、JOIN

　以下のテーブルを追加します。ON句のキーワードでくっつけ方をよく指定します。テーブル名.列名=テーブル名.列名という対応での指定をします。2個以上の列条件の一致が必要な場合はANDとかも使います。INNER JOINは単にJOINと書いても良いです。

部署テーブル

部署名	拠点
営業部	東京
開発部	大阪
総務部	名古屋

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 
INNER JOIN 部署
ON 社員.所属部署 = 部署.部署名;

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京
田中美咲	総務部	名古屋

　

LEFT JOIN、LEFT OUTER JOIN

　以下のテーブルを追加します。LEFT JOIN と LEFT OUTER JOIN は同じです。

部署テーブル

部署名	拠点
営業部	東京
開発部	大阪

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 LEFT JOIN 部署
ON 社員.所属部署 = 部署.部署名;

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京
田中美咲	総務部	NULL

この状態でINNER JOINで結合すると

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 INNER JOIN 部署
ON 社員.所属部署 = 部署.部署名;

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京

　となり、総務部の情報の表示はなくなります。

　

FULL OUTER JOIN、FULL JOIN

　追加されたテーブルが以下のテーブルだったとします。FULL JOIN と FULL OUTER JOIN は同じです。

部署テーブル

部署名	拠点
営業部	東京
開発部	大阪
試作部	三重

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員 FULL JOIN 部署
ON 社員.所属部署 = 部署.部署名;

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京
田中美咲	総務部	NULL
NULL	試作部	三重

のように社員テーブルに該当の社員がいない試作部の情報も結合後のテーブルに表示されます。

　

UNION

SELECT 氏名 FROM 社員
UNION
SELECT 部署名 FROM 部署;

社員

氏名
山田太郎
佐藤花子
鈴木一郎
田中美咲
営業部
開発部
総務部

のように列がまとめられて、すべての要素が表示されます。足し算のような効果を生み出すのがUNIONだと思えばいいと思います。

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員
LEFT JOIN 部署 ON 社員.所属部署 = 部署.部署名

UNION ALL

SELECT 氏名, 社員.所属部署, 拠点
FROM 社員
RIGHT JOIN 部署 ON 社員.所属部署 = 部署.部署名
WHERE 氏名 IS NULL;

のようにするとFULL JOINが使えない環境でもUNIONとRIGHT JOINだけでFULL JOINが実現できます。

FULLは左外部結合と右外部結合のUNION(つまり足し算)と言えるからです。

社員

氏名	所属部署	拠点
山田太郎	営業部	東京
佐藤花子	開発部	大阪
鈴木一郎	営業部	東京
田中美咲	総務部	NULL
NULL	試作部	三重

　上記のようになります。UNION ALLでなくて UNIONでも結果は変わらないですが、FULL JOINと同じにしたい場合はUNION ALLにしておきます。例えば同姓同名で同じ部署の人がいた場合、UNIONを使った場合は重複を省くので、同姓同名でもちゃんと2レコードださないといけないときに、消えてしまいます。DISTINCTを掛けなければいけない場合はUNIONでいいということにもなります。

　

IN(副問合せ)

　以降、以下のテーブルを追加します。

部署テーブル

部署名	拠点
営業部	東京
開発部	東京
開発部	大阪
総務部	名古屋

複数の値が返ってくる可能性のあるデータと一致するかをそれぞれ確認してくれます。以下だと、東京にある部署の名前が返ってきて、社員テーブルの所属部署名と比較してくれます。一致するレコードはすべて抽出されます。

SELECT 氏名 FROM 社員
WHERE 所属部署 IN (SELECT 部署名 FROM 部署 WHERE 拠点='東京');

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
2	佐藤花子	開発部	400000
3	鈴木一郎	営業部	350000

　部署テーブルの拠点が東京のものには、営業部と、開発部がありますので、社員テーブルの営業部と開発部に一致するレコードが抽出されます。

SELECT 氏名 FROM 社員
WHERE 所属部署 IN ('営業部', '開発部');

と実行したのと同じことです。INは副問合せ専用のキーワードではないです。INに指定されたデータに一致するものすべてという意味です。副問合せによく使うというだけです。

EXIST(副問合せ)

　レコードごとに副問合せの結果が存在したかどうかを判定します。

SELECT 氏名 FROM 社員 S
WHERE EXISTS (
  SELECT * FROM 部署 D
  WHERE D.部署名 = S.所属部署
    AND D.拠点='東京'
);

社員

社員ID	氏名	所属部署	給与
1	山田太郎	営業部	300000
2	佐藤花子	開発部	400000
3	鈴木一郎	営業部	350000

　何かしらの抽出レコードが副問合せの結果に存在したか、どうかを判定するだけです。真偽です。あったか、なかったか。

　

CREATE VIEW

　参照するため専用のビュー(編集できない)を作成します。

CREATE VIEW 東京勤務社員 AS
SELECT 氏名, 所属部署 
FROM 社員
WHERE 所属部署 IN (
  SELECT 部署名 FROM 部署 WHERE 拠点='東京'
);

東京勤務社員 ビュー

氏名	所属部署
山田太郎	営業部
鈴木一郎	営業部

　ビューを作った上でまた次のSQLを使い、このビューをFROM ビュー名のように指定することで利用ができるようになっています。これを一つのSQLでやろうとするのが次のWITH句です。CREATE VIEW ビュー名 AS SELECT * FROM xx;のような形式です。ASの後に()がいらないことを覚えましょう。東京勤務社員という表名の後ろに東京勤務社員(列名1, 列名2, …, 列名n)のように列名の別名が指定できて、この別名が最も優先されます。

　

WITH

　一時的なビューを作成しつつ、そのビューを使ったSQLを実行することができます。

WITH 部署別平均給与(平均給与表示用所属部署, 平均給与表示用平均給与) AS (
    SELECT
        所属部署,
        AVG(給与) AS 平均給与
    FROM
        社員
    GROUP BY
        所属部署
)
SELECT
    S.所属部署,
    COUNT(S.社員ID) AS 社員数,
    AVG_SAL.平均給与表示用平均給与
FROM
    社員 AS S
JOIN
    部署別平均給与 AS AVG_SAL
ON
    S.所属部署 = AVG_SAL.平均給与表示用所属部署
GROUP BY
    S.所属部署, AVG_SAL.平均給与表示用平均給与;

WITH句で作成した一時テーブルは以下のようになります。一時テーブル表名直後のカッコ内で列の別名が指定でき、ここで指定した別名が最も優先されます。別名はSELECT句による列指定でも指定できます。

部署別平均給与（一時テーブル）

平均給与表示用所属部署	平均給与表示用平均給与
営業部	325000
開発部	400000
総務部	280000

　この一時テーブルを利用しつつSQLを実行した結果、得られる表は以下のようなものです。

部署別社員数と平均給与

所属部署	社員数	平均給与
営業部	2	325000
開発部	1	400000
総務部	1	280000

　のようにWITH句で作成した部署別平均給与という表を使って上記のような結果を得ることができます。「WITH 一時的に作成する表名 AS (表を実現するSQL) SELECT * FROM 一時的に作成する表名」 のような形式です。一時的に作成する表名の後のASの後ろにカッコがいります。覚えれるかな。

　

WITH RECURSIVE

　再帰的な一時的表を作る必要がある場合に使う句です。例えば以下のようなカテゴリ表があったとしたら

商品カテゴリ

カテゴリID	カテゴリ名	親カテゴリID
1	電子機器	NULL
2	PC	1
3	スマートフォン	1
4	ノートPC	2
5	デスクトップPC	2
6	周辺機器	NULL
7	マウス	6
8	キーボード	6

WITH RECURSIVE カテゴリ階層 AS (
    -- アンカーメンバー：再帰の開始点（親カテゴリIDがNULLのトップレベルカテゴリ）
    SELECT
        カテゴリID,
        カテゴリ名,
        親カテゴリID,
        0 AS 階層レベル -- 階層の深さを示す
    FROM
        商品カテゴリ
    WHERE
        カテゴリID = 1 -- 電子機器カテゴリから開始

    UNION ALL

    -- 再帰メンバー：前回の結果（カテゴリ階層）を参照して子カテゴリを取得
    SELECT
        C.カテゴリID,
        C.カテゴリ名,
        C.親カテゴリID,
        CH.階層レベル + 1 AS 階層レベル
    FROM
        商品カテゴリ AS C
    JOIN
        カテゴリ階層 AS CH ON C.親カテゴリID = CH.カテゴリID
)
SELECT
    カテゴリID,
    カテゴリ名,
    親カテゴリID,
    階層レベル
FROM
    カテゴリ階層
ORDER BY
    階層レベル, カテゴリID;

のようにすると

　

AP データベースに戻る