PostgreSQL 正規化結合副問合せ

2025-02-142025-03-15

正規化

データを適切な単位で分割し複数の冗長なデータを持たなくする
複数のテーブルを更新する必要がなくなり、データの整合性を担保できる
検索時に複数のテーブルを関連付ける必要がある

正規形

参考URL：https://zenn.dev/keisuke90/articles/66ecb7956a6816
第１正規形：一つのセルの中には一つの値しか含まない
第２正規形：部分従属を排除して完全関数従属のみのテーブルを作る
- 何とかコードとか、何とかIDが一つのテーブルに2つ以上ある場合、社員コードと部署コードが存在し社員名や、部署名もあるような場合が部分関数従属が存在すると言えます。
第３正規形：推移的関数従属を排除すること
- 例えば、部署に属する担当があるとすれば推移的関数従属が存在することになり、それを別のテーブルに分割することです
ボイス・コッド正規形：非キーからキーへの関数従属性をなくした状態
第４正規形：多値従属性が複数存在するテーブルを分割する
第５正規形：第４正規形の発展形

内部結合(INNER JOIN ON句)

一般的な結合
複数のテーブルから一致する要素がある行のみを結合して表示します。

２種類の書き方

INNER JOIN型

FROM 
 table1 AS t1
INNER JOIN
 table2 AS t2
ON
 ti.id = t2.id

WHERE型

FROM
 table1 AS t1,
 table2 AS t2,
WHERE
 ti.id=t2.id

社員テーブル(employee)と所属部署テーブル(depart)テーブルを内部結合し、在職している(retired<>1)社員の、氏(l_name)、名(f_name)、役職(class)を所属部署名(depart_name)を取得せよ。その際、所属部署コード(depart_id)、社員コード(s_id)の昇順で出力せよ。

sql

SELECT
 l_name,
 f_name,
 class,
 depart_name
FROM
 employee
;

result

sql

SELECT
 l_name,
 f_name,
 class,
 depart_name
FROM
 employee
INNER JOIN
 depart 
ON
 employee.depart_id = depart.depart_id
;

result

l_name	f_name	class	depart_name
相沢	聡	部長	総務部
大門	一郎	担当	人事部
藤井	雄太	主任	第一営業部
藤岡	幸太郎	担当	第一営業部
速水	和幸	部長	第一営業部
葉山	俊輔	アシスタント	第一営業部
川口	裕子	アシスタント	経営企画部
加藤	昭雄	アシスタント	経営企画部
神田	佐知子	アシスタント	人事部
木村	一郎	担当	経営企画部
中澤	康代	アシスタント	総務部
野上	利江	アシスタント	総務部
佐藤	智子	部長	経営企画部
妹尾	春樹	課長	第一営業部
清水	春子	主任	人事部
田中	真由子	主任	経営企画部
田辺	正一	アシスタント	第一営業部
寺岡	陽一	部長	第一営業部
近田	晃子	課長	総務部
遠山	真一	課長	人事部
戸川	よしみ	担当	総務部
津村	知美	主任	総務部
上田	一也	部長	人事部
山田	奈美	課長	経営企画部

sql

SELECT
 l_name,
 f_name,
 class,
 depart_name
FROM
 employee
INNER JOIN
 depart 
ON
 employee.depart_id = depart.depart_id
WHERE
 retired <> 1
;

result

l_name	f_name	class	depart_name
相沢	聡	部長	総務部
大門	一郎	担当	人事部
藤井	雄太	主任	第一営業部
藤岡	幸太郎	担当	第一営業部
葉山	俊輔	アシスタント	第一営業部
加藤	昭雄	アシスタント	経営企画部
神田	佐知子	アシスタント	人事部
木村	一郎	担当	経営企画部
野上	利江	アシスタント	総務部
佐藤	智子	部長	経営企画部
妹尾	春樹	課長	第一営業部
清水	春子	主任	人事部
田中	真由子	主任	経営企画部
田辺	正一	アシスタント	第一営業部
寺岡	陽一	部長	第一営業部
近田	晃子	課長	総務部
遠山	真一	課長	人事部
戸川	よしみ	担当	総務部
津村	知美	主任	総務部
上田	一也	部長	人事部
山田	奈美	課長	経営企画部

sql

SELECT
 l_name,
 f_name,
 class,
 depart_name
FROM
 employee
INNER JOIN
 depart 
ON
 employee.depart_id = depart.depart_id
WHERE
 retired <> 1
ORDER BY
 employee.depart_id,
 employee.s_id
;

result

l_name	f_name	class	depart_name
藤井	雄太	主任	第一営業部
藤岡	幸太郎	担当	第一営業部
葉山	俊輔	アシスタント	第一営業部
妹尾	春樹	課長	第一営業部
田辺	正一	アシスタント	第一営業部
寺岡	陽一	部長	第一営業部
大門	一郎	担当	人事部
神田	佐知子	アシスタント	人事部
清水	春子	主任	人事部
遠山	真一	課長	人事部
上田	一也	部長	人事部
加藤	昭雄	アシスタント	経営企画部
木村	一郎	担当	経営企画部
佐藤	智子	部長	経営企画部
田中	真由子	主任	経営企画部
山田	奈美	課長	経営企画部
相沢	聡	部長	総務部
野上	利江	アシスタント	総務部
近田	晃子	課長	総務部
戸川	よしみ	担当	総務部
津村	知美	主任	総務部

テーブルの確認

employee

s_id	l_name	f_name	l_name_kana	f_name_kana	sex	class	depart_id	b_id	last_update	retired
AI00001	相沢		アイザワ	サトシ	1	部長	S02		2012/02/24	0
DA00001	大門	一郎	ダイモン	イチロウ	1	担当	J01	AI00001	2011/08/10	0
FU00001	藤井	雄太	フジイ	ユウタ	1	主任	E01	SE00001	2011/04/10	0
FU00002	藤岡	幸太郎	フジオカ	コウタロウ	1	担当	E01	FU00001	2011/06/12	0
HA00001	速水	和幸	ハヤミ	カズユキ	1	部長	E01		2007/02/03	1
HA00002	葉山	俊輔	ハヤマ	シュンスケ	1	アシスタント	E01	FU00002	2012/12/11	0
KA00001	川口	裕子	カワグチ	ユウコ	2	アシスタント	S01	KI00001	2012/12/28	1
KA00002	加藤	昭雄	カトウ	アキオ	1	アシスタント	S01	KI00001	2011/11/11	0
KA00003	神田	佐知子	カンダ	サチコ	1	アシスタント	J01	KA00003	2012/01/28	0
KI00001	木村	一郎	キムラ	イチロウ	1	担当	S01	TA00001	2011/07/12	0
NA00001	中澤	康代	ナカザワ	ヤスヨ	2	アシスタント	S02	TO00002	2012/04/17	1
NO00001	野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0
SA00001	佐藤	智子	サトウ	トモコ	2	部長	S01		2010/02/01	0
SE00001	妹尾	春樹	セノオ	ハルキ	1	課長	E01	TE00001	2010/12/10	0
SI00001	清水	春子	シミズ	ハルコ	2	主任	J01	SI00001	2011/12/11	0
TA00001	田中	真由子	タナカ	マユコ	2	主任	S01	YA00001	2011/05/26	0
TA00002	田辺	正一	タナベ	ショウイチ	1	アシスタント	E01	HA00002	2012/12/19	0
TE00001	寺岡	陽一	テラオカ	ヨウイチ	1	部長	E01	HA00001	2009/12/11	0
TI00001	近田	晃子	チカダ	アキコ	2	課長	S02	AI00001	2012/03/01	0
TO00001	遠山	真一	トオヤマ	シンイチ	1	課長	J01	UE00001	2010/12/16	0
TO00002	戸川	よしみ	トガワ	ヨシミ	2	担当	S02	TU00001	2012/04/01	0
TU00001	津村	知美	ツムラ	カズミ	2	主任	S02	TI00001	2012/03/16	0
UE00001	上田	一也	ウエダ	カズヤ	1	部長	J01		2011/11/12	0
YA00001	山田	奈美	ヤマダ	ナミ	2	課長	S01	SA00001	2012/10/15	0
NI00001	西	雄	ニシ	ユウイチ	1	アシスタント	B01		2013/01/10	0

depart

depart_id	depart_name
S01	経営企画部
E01	第一営業部
E02	第二営業部
J01	人事部
S02	総務部

スキーマの確認

depart

No.	論理名	物理名	データ型	Not Null
1	所属部署ID	depart_id	character(3)	Yes (PK)
2	所属部署名	depart_name	character varying(20)

employee

No.	論理名	物理名	データ型	Not Null
1	社員ID	s_id	character(7)	Yes (PK)
2	氏	l_name	character varying(20)
3	名	f_name	character varying(20)
4	氏（カナ）	l_name_kana	character varying(100)
5	名（カナ）	f_name_kana	character varying(100)
6	性別	sex	smallint
7	役職	class	character varying(20)
8	所属部署ID	depart_id	character(3)
9	上司ID	b_id	character(7)
10	最終更新日	last_update	date
11	退職フラグ	retired	smallint

前の問題に別名をつけたり、WHEREでも書いてみる

INNER JOIN

sql

SELECT
  e.l_name,
  e.f_name,
  e.class
FROM
  employee AS e
;

result

l_name	f_name	class
相沢	聡	部長
大門	一郎	担当
藤井	雄太	主任
藤岡	幸太郎	担当
速水	和幸	部長
葉山	俊輔	アシスタント
川口	裕子	アシスタント
加藤	昭雄	アシスタント
神田	佐知子	アシスタント
木村	一郎	担当
中澤	康代	アシスタント
野上	利江	アシスタント
佐藤	智子	部長
妹尾	春樹	課長
清水	春子	主任
田中	真由子	主任
田辺	正一	アシスタント
寺岡	陽一	部長
近田	晃子	課長
遠山	真一	課長
戸川	よしみ	担当
津村	知美	主任
上田	一也	部長
山田	奈美	課長
西	雄一	アシスタント
新渡戸	康治	アシスタント

sql

SELECT
  e.l_name,
  e.f_name,
  d.depart_name,
  e.class
FROM
  employee AS e
INNER JOIN
  depart AS d
ON
  e.depart_id = d.depart_id
;

result

l_name	f_name	depart_name	class
相沢	聡	総務部	部長
大門	一郎	人事部	担当
藤井	雄太	第一営業部	主任
藤岡	幸太郎	第一営業部	担当
速水	和幸	第一営業部	部長
葉山	俊輔	第一営業部	アシスタント
川口	裕子	経営企画部	アシスタント
加藤	昭雄	経営企画部	アシスタント
神田	佐知子	人事部	アシスタント
木村	一郎	経営企画部	担当
中澤	康代	総務部	アシスタント
野上	利江	総務部	アシスタント
佐藤	智子	経営企画部	部長
妹尾	春樹	第一営業部	課長
清水	春子	人事部	主任
田中	真由子	経営企画部	主任
田辺	正一	第一営業部	アシスタント
寺岡	陽一	第一営業部	部長
近田	晃子	総務部	課長
遠山	真一	人事部	課長
戸川	よしみ	総務部	担当
津村	知美	総務部	主任
上田	一也	人事部	部長
山田	奈美	経営企画部	課長

sql

SELECT
  e.l_name,
  e.f_name,
  d.depart_name,
  e.class
FROM
  employee AS e
INNER JOIN
  depart AS d
ON
  e.depart_id = d.depart_id
WHERE
  e.retired <> 1
;

result

l_name	f_name	depart_name	class
相沢	聡	総務部	部長
大門	一郎	人事部	担当
藤井	雄太	第一営業部	主任
藤岡	幸太郎	第一営業部	担当
葉山	俊輔	第一営業部	アシスタント
加藤	昭雄	経営企画部	アシスタント
神田	佐知子	人事部	アシスタント
木村	一郎	経営企画部	担当
野上	利江	総務部	アシスタント
佐藤	智子	経営企画部	部長
妹尾	春樹	第一営業部	課長
清水	春子	人事部	主任
田中	真由子	経営企画部	主任
田辺	正一	第一営業部	アシスタント
寺岡	陽一	第一営業部	部長
近田	晃子	総務部	課長
遠山	真一	人事部	課長
戸川	よしみ	総務部	担当
津村	知美	総務部	主任
上田	一也	人事部	部長
山田	奈美	経営企画部	課長

sql

SELECT
  e.l_name,
  e.f_name,
  d.depart_name,
  e.class
FROM
  employee AS e
INNER JOIN
  depart AS d
ON
  e.depart_id = d.depart_id
WHERE
  e.retired <> 1
ORDER BY
  e.depart_id ASC,
  e.s_id ASC
;

result

l_name	f_name	depart_name	class
藤井	雄太	第一営業部	主任
藤岡	幸太郎	第一営業部	担当
葉山	俊輔	第一営業部	アシスタント
妹尾	春樹	第一営業部	課長
田辺	正一	第一営業部	アシスタント
寺岡	陽一	第一営業部	部長
大門	一郎	人事部	担当
神田	佐知子	人事部	アシスタント
清水	春子	人事部	主任
遠山	真一	人事部	課長
上田	一也	人事部	部長
加藤	昭雄	経営企画部	アシスタント
木村	一郎	経営企画部	担当
佐藤	智子	経営企画部	部長
田中	真由子	経営企画部	主任
山田	奈美	経営企画部	課長
相沢	聡	総務部	部長
野上	利江	総務部	アシスタント
近田	晃子	総務部	課長
戸川	よしみ	総務部	担当
津村	知美	総務部	主任

WHERE

sql

SELECT
  e.l_name,
  e.f_name,
  d.depart_name,
  e.class
FROM
  employee AS e,
  depart AS d
;

result

l_name	f_name	depart_name	class
相沢	聡	経営企画部	部長
相沢	聡	第一営業部	部長
相沢	聡	第二営業部	部長
相沢	聡	人事部	部長
相沢	聡	総務部	部長
大門	一郎	経営企画部	担当
大門	一郎	第一営業部	担当
大門	一郎	第二営業部	担当
大門	一郎	人事部	担当
大門	一郎	総務部	担当
藤井	雄太	経営企画部	主任
藤井	雄太	第一営業部	主任
藤井	雄太	第二営業部	主任
藤井	雄太	人事部	主任
藤井	雄太	総務部	主任
藤岡	幸太郎	経営企画部	担当
藤岡	幸太郎	第一営業部	担当
藤岡	幸太郎	第二営業部	担当
藤岡	幸太郎	人事部	担当
藤岡	幸太郎	総務部	担当

sql

SELECT
  e.l_name,
  e.f_name,
  d.depart_name,
  e.class
FROM
  employee AS e,
  depart AS d
WHERE
    e.depart_id = d.depart_id
;

result

l_name	f_name	depart_name	class
相沢	聡	総務部	部長
大門	一郎	人事部	担当
藤井	雄太	第一営業部	主任
藤岡	幸太郎	第一営業部	担当
速水	和幸	第一営業部	部長
葉山	俊輔	第一営業部	アシスタント
川口	裕子	経営企画部	アシスタント
加藤	昭雄	経営企画部	アシスタント
神田	佐知子	人事部	アシスタント
木村	一郎	経営企画部	担当
中澤	康代	総務部	アシスタント
野上	利江	総務部	アシスタント
佐藤	智子	経営企画部	部長
妹尾	春樹	第一営業部	課長
清水	春子	人事部	主任
田中	真由子	経営企画部	主任
田辺	正一	第一営業部	アシスタント
寺岡	陽一	第一営業部	部長
近田	晃子	総務部	課長
遠山	真一	人事部	課長
戸川	よしみ	総務部	担当
津村	知美	総務部	主任
上田	一也	人事部	部長
山田	奈美	経営企画部	課長

sql

SELECT
  e.l_name,
  e.f_name,
  d.depart_name,
  e.class
FROM
  employee AS e,
  depart AS d
WHERE
    e.depart_id = d.depart_id
  AND
    e.retired <> 1
;

result

l_name	f_name	depart_name	class
相沢	聡	総務部	部長
大門	一郎	人事部	担当
藤井	雄太	第一営業部	主任
藤岡	幸太郎	第一営業部	担当
葉山	俊輔	第一営業部	アシスタント
加藤	昭雄	経営企画部	アシスタント
神田	佐知子	人事部	アシスタント
木村	一郎	経営企画部	担当
野上	利江	総務部	アシスタント
佐藤	智子	経営企画部	部長
妹尾	春樹	第一営業部	課長
清水	春子	人事部	主任
田中	真由子	経営企画部	主任
田辺	正一	第一営業部	アシスタント
寺岡	陽一	第一営業部	部長
近田	晃子	総務部	課長
遠山	真一	人事部	課長
戸川	よしみ	総務部	担当
津村	知美	総務部	主任
上田	一也	人事部	部長
山田	奈美	経営企画部	課長

sql

SELECT
  e.l_name,
  e.f_name,
  d.depart_name,
  e.class
FROM
  employee AS e,
  depart AS d
WHERE
    e.depart_id = d.depart_id
  AND
    e.retired <> 1
ORDER BY
  e.depart_id ASC,
  e.s_id ASC
;

result

l_name	f_name	depart_name	class
藤井	雄太	第一営業部	主任
藤岡	幸太郎	第一営業部	担当
葉山	俊輔	第一営業部	アシスタント
妹尾	春樹	第一営業部	課長
田辺	正一	第一営業部	アシスタント
寺岡	陽一	第一営業部	部長
大門	一郎	人事部	担当
神田	佐知子	人事部	アシスタント
清水	春子	人事部	主任
遠山	真一	人事部	課長
上田	一也	人事部	部長
加藤	昭雄	経営企画部	アシスタント
木村	一郎	経営企画部	担当
佐藤	智子	経営企画部	部長
田中	真由子	経営企画部	主任
山田	奈美	経営企画部	課長
相沢	聡	総務部	部長
野上	利江	総務部	アシスタント
近田	晃子	総務部	課長
戸川	よしみ	総務部	担当
津村	知美	総務部	主任

社員テーブル(employee)とタイムカードテーブル(time_card)から2012年12月の氏名(l_name,f_name)と勤務時間(work_time)を、社員コード(s_id)、日付(r_date)の昇順で取り出すSQL文を完成させろ。

最後に幽霊社員を外部結合で確認します。

sql

SELECT
  e.l_name,
  e.f_name
FROM
  employee AS e
;

result

l_name	f_name
相沢	聡
大門	一郎
藤井	雄太
藤岡	幸太郎
速水	和幸
葉山	俊輔
川口	裕子
加藤	昭雄
神田	佐知子
木村	一郎
中澤	康代
野上	利江
佐藤	智子
妹尾	春樹
清水	春子
田中	真由子
田辺	正一
寺岡	陽一
近田	晃子
遠山	真一
戸川	よしみ
津村	知美
上山田	美也
山田	奈美
西	雄一
新渡戸	康治
(26 行)

sql

SELECT
  e.l_name,
  e.f_name,
  t.work_time
FROM
  employee AS e
INNER JOIN
  time_card AS t
ON
  e.s_id =t.s_id
;

result

l_name	f_name	work_time
田中	真由子	10
田中	真由子	11
田中	真由子	10
田中	真由子	9
田中	真由子	10
田中	真由子	8
田中	真由子	8
田中	真由子	8
田中	真由子	4
田中	真由子	8
田中	真由子	8
田中	真由子	10
田中	真由子	9
田中	真由子	9
田中	真由子	8
田中	真由子	11
田中	真由子	9
田中	真由子	8
田中	真由子	6
木村	一郎	8
木村	一郎	10
木村	一郎	8
木村	一郎	9
木村	一郎	8
木村	一郎	8
木村	一郎	9
木村	一郎	8
木村	一郎	10
木村	一郎	9
木村	一郎	8
— More —

sql

SELECT
  e.l_name,
  e.f_name,
  t.work_time
FROM
  employee AS e
INNER JOIN
  time_card AS t
ON
  e.s_id =t.s_id
WHERE
  t.r_date
BETWEEN
 '2012/12/01' AND '2012/12/31'
;

result

l_name	f_name	work_time
田中	真由子	10
田中	真由子	11
田中	真由子	10
田中	真由子	9
田中	真由子	10
田中	真由子	8
田中	真由子	8
田中	真由子	8
田中	真由子	4
田中	真由子	8
田中	真由子	8
田中	真由子	10
田中	真由子	9
田中	真由子	9
田中	真由子	8
田中	真由子	11
田中	真由子	9
田中	真由子	8
田中	真由子	6
木村	一郎	8
木村	一郎	10
木村	一郎	8
木村	一郎	9
木村	一郎	8
木村	一郎	8
木村	一郎	9
木村	一郎	8
木村	一郎	10
木村	一郎	9
木村	一郎	8
木村	一郎	8
— More —

sql

SELECT
  e.l_name,
  e.f_name,
  t.work_time
FROM
  employee AS e
INNER JOIN
  time_card AS t
ON
  e.s_id =t.s_id
WHERE
  t.r_date
BETWEEN
 '2012/12/01' AND '2012/12/31'
ORDER BY
  e.s_id ASC,
  t.r_date ASC
;

result

l_name	f_name	work_time
大門	一郎	8
大門	一郎	10
大門	一郎	8
大門	一郎	9
大門	一郎	8
大門	一郎	8
大門	一郎	9
大門	一郎	8
大門	一郎	10
大門	一郎	9
大門	一郎	8
大門	一郎	8
大門	一郎	8
大門	一郎	8
大門	一郎	9
大門	一郎	8
大門	一郎	10
大門	一郎	8
大門	一郎	8
藤井	雄太	8
藤井	雄太	10
藤井	雄太	9
藤井	雄太	8
藤井	雄太	8
藤井	雄太	9
藤井	雄太	8
藤井	雄太	10
藤井	雄太	9
藤井	雄太	8
藤井	雄太	8
— More —

sql

SELECT DISTINCT
 e.s_id eid,
 t.s_id tid 
FROM
 employee e
LEFT OUTER JOIN
 time_card t
ON
 e.s_id=t.s_id
ORDER BY
 eid
;

result

eid	tid
AI00001	« NULL »
DA00001	DA00001
FU00001	FU00001
FU00002	FU00002
HA0001	« NULL »
HA0002	HA0002
KA00001	KA00001
KA00002	KA00002
KA00003	KA00003
KI00001	KI00001
NA00001	NA00001
NI00001	« NULL »
NI00002	« NULL »
NO00001	NO00001
SA00001	« NULL »
SE00001	« NULL »
SI00001	SI00001
TA00001	TA00001
TA00002	TA00002
TE00001	« NULL »
TI00001	« NULL »
TO00001	« NULL »
TO00002	TO00002
TU00001	TU00001
UE00001	« NULL »
YA00001	« NULL »

テーブル確認用

employee

s_id	l_name	f_name	l_name_kana	f_name_kana	sex	class	depart_id	b_id	last_update	retired
AI00001	相沢		アイザワ	サトシ	1	部長	S02		2012/02/24	0
DA00001	大門	一郎	ダイモン	イチロウ	1	担当	J01	AI00001	2011/08/10	0
FU00001	藤井	雄太	フジイ	ユウタ	1	主任	E01	SE00001	2011/04/10	0
FU00002	藤岡	幸太郎	フジオカ	コウタロウ	1	担当	E01	FU00001	2011/06/12	0
HA00001	速水	和幸	ハヤミ	カズユキ	1	部長	E01		2007/02/03	1
HA00002	葉山	俊輔	ハヤマ	シュンスケ	1	アシスタント	E01	FU00002	2012/12/11	0
KA00001	川口	裕子	カワグチ	ユウコ	2	アシスタント	S01	KI00001	2012/12/28	1
KA00002	加藤	昭雄	カトウ	アキオ	1	アシスタント	S01	KI00001	2011/11/11	0
KA00003	神田	佐知子	カンダ	サチコ	1	アシスタント	J01	KA00003	2012/01/28	0
KI00001	木村	一郎	キムラ	イチロウ	1	担当	S01	TA00001	2011/07/12	0
NA00001	中澤	康代	ナカザワ	ヤスヨ	2	アシスタント	S02	TO00002	2012/04/17	1
NO00001	野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0
SA00001	佐藤	智子	サトウ	トモコ	2	部長	S01		2010/02/01	0
SE00001	妹尾	春樹	セノオ	ハルキ	1	課長	E01	TE00001	2010/12/10	0
SI00001	清水	春子	シミズ	ハルコ	2	主任	J01	SI00001	2011/12/11	0
TA00001	田中	真由子	タナカ	マユコ	2	主任	S01	YA00001	2011/05/26	0
TA00002	田辺	正一	タナベ	ショウイチ	1	アシスタント	E01	HA00002	2012/12/19	0
TE00001	寺岡	陽一	テラオカ	ヨウイチ	1	部長	E01	HA00001	2009/12/11	0
TI00001	近田	晃子	チカダ	アキコ	2	課長	S02	AI00001	2012/03/01	0
TO00001	遠山	真一	トオヤマ	シンイチ	1	課長	J01	UE00001	2010/12/16	0
TO00002	戸川	よしみ	トガワ	ヨシミ	2	担当	S02	TU00001	2012/04/01	0
TU00001	津村	知美	ツムラ	カズミ	2	主任	S02	TI00001	2012/03/16	0
UE00001	上田	一也	ウエダ	カズヤ	1	部長	J01		2011/11/12	0
YA00001	山田	奈美	ヤマダ	ナミ	2	課長	S01	SA00001	2012/10/15	0
NI00001	西	雄	ニシ	ユウイチ	1	アシスタント	B01		2013/01/10	0

time_card

s_id	r_date	work_time
TA00001	2012/12/01	10
TA00001	2012/12/02	11
TA00001	2012/12/05	10
TA00001	2012/12/06	9
TA00001	2012/12/07	10
TA00001	2012/12/08	8
TA00001	2012/12/09	8
TA00001	2012/12/12	8
TA00001	2012/12/13	8
TA00001	2012/12/14	4
TA00001	2012/12/15	8
TA00001	2012/12/16	8
TA00001	2012/12/19	10
TA00001	2012/12/20	9
TA00001	2012/12/21	9
TA00001	2012/12/22	8
TA00001	2012/12/23	11
TA00001	2012/12/26	9
TA00001	2012/12/27	8
TA00001	2012/12/28	6
KI00001	2012/12/01	8
KI00001	2012/12/02	10
KI00001	2012/12/05	8
KI00001	2012/12/06	9
KI00001	2012/12/07	8
KI00001	2012/12/08	8
KI00001	2012/12/09	9

スキーマの確認

employee

No.	論理名	物理名	データ型	Not Null
1	社員ID	s_id	character(7)	Yes (PK)
2	氏	l_name	character varying(20)
3	名	f_name	character varying(20)
4	氏（カナ）	l_name_kana	character varying(100)
5	名（カナ）	f_name_kana	character varying(100)
6	性別	sex	smallint
7	役職	class	character varying(20)
8	所属部署ID	depart_id	character(3)
9	上司ID	b_id	character(7)
10	最終更新日	last_update	date
11	退職フラグ	retired	smallint

time_card

No.	物理名	データ型	Not Null
1	s_id	character(7)	Yes (PK)
2	r_date	date	Yes (PK)
3	work_time	numeric

店舗テーブル(shop)と月間売り上げテーブル(sales)から2012年12月のs_nameごとのs_valueを売上高(s_value)の高い順で取り出すSQL文を完成させろ。

sql

SELECT
  sh.s_name
FROM
  shop sh
;

result

s_name
三吉町店
三吉町南店
東桜町店
北花町駅前店
北花町東店

sql

SELECT
  sh.s_name,
  sl.s_value
FROM
  shop sh
INNER JOIN
  sales sl
ON
  sh.s_id = sl.s_id
;

result

s_name	s_value
三吉町店	11145
東桜町店	9861
北花町駅前店	11234
北花町東店	10456
三吉町店	10784
三吉町南店	12032
東桜町店	9975
北花町駅前店	10142
北花町東店	10124

sql

SELECT
  sh.s_name,
  sl.s_value
FROM
  shop sh
INNER JOIN
  sales sl
ON
  sh.s_id = sl.s_id
WHERE
  sl.s_date = '2012-12'
;

result

s_name	s_value
三吉町店	11145
東桜町店	9861
北花町駅前店	11234
北花町東店	10456

sql

SELECT
  sh.s_name,
  sl.s_value
FROM
  shop sh
INNER JOIN
  sales sl
ON
  sh.s_id = sl.s_id
WHERE
  sl.s_date = '2012-12'
ORDER BY
  sl.s_value DESC
;

result

s_name	s_value
北花町駅前店	11234
三吉町店	11145
北花町東店	10456
東桜町店	9861

テーブル確認用

shop

s_id	s_name
M0001	三吉町店
M0002	三吉町南店
H0001	東桜町店
K0001	北花町駅前店
K0002	北花町東店

sales

s_id	s_date	s_value
M0001	2012-12	11145
H0001	2012-12	9861
N0001	2012-12	11023
K0001	2012-12	11234
K0002	2012-12	10456
M0001	2012-11	10784
M0002	2012-11	12032
H0001	2012-11	9975
N0001	2012-11	9965
K0001	2012-11	10142
K0002	2012-11	10124

スキーマの確認

shop

No.	物理名	データ型	Not Null
1	s_id	character(5)	Yes (PK)
2	s_name	character varying(30)

sales

No.	物理名	データ型	Not Null
1	s_id	character(5)	Yes (PK)
2	s_date	character(7)	Yes (PK)
3	s_value	integer

店舗テーブル(shop)と月間売り上げテーブル(sales)から店名ごとに2012年の売上高を売上高の低い順で取り出せ。

sql

SELECT
  sh.s_name
FROM
  shop sh
;

result

s_name
三吉町店
三吉町南店
東桜町店
北花町駅前店
北花町東店

sql

SELECT
  sh.s_name,
  sl.s_value
FROM
  shop sh
INNER JOIN
  sales sl
ON
  sh.s_id = sl.s_id
;

result

s_name	s_value
三吉町店	11145
東桜町店	9861
北花町駅前店	11234
北花町東店	10456
三吉町店	10784
三吉町南店	12032
東桜町店	9975
北花町駅前店	10142
北花町東店	10124

sql

SELECT
  sh.s_name,
  sl.s_value
FROM
  shop sh
INNER JOIN
  sales sl
ON
  sh.s_id = sl.s_id
WHERE
  sl.s_date LIKE '2012%'
;

result

s_name	s_value
三吉町店	11145
東桜町店	9861
北花町駅前店	11234
北花町東店	10456
三吉町店	10784
三吉町南店	12032
東桜町店	9975
北花町駅前店	10142
北花町東店	10124

sql

SELECT
  sh.s_name,
  SUM(sl.s_value)
FROM
  shop sh
INNER JOIN
  sales sl
ON
  sh.s_id = sl.s_id
WHERE
  sl.s_date LIKE '2012%'
GROUP BY
  sh.s_name
;

result

s_name	sum
三吉町店	21929
東桜町店	19836
北花町駅前店	21376
北花町東店	20580
三吉町南店	12032

sql

SELECT
  sh.s_name,
  SUM(sl.s_value)
FROM
  shop sh
INNER JOIN
  sales sl
ON
  sh.s_id = sl.s_id
WHERE
  sl.s_date LIKE '2012%'
GROUP BY
  sh.s_name
ORDER BY
  SUM(sl.s_value) ASC
;

result

s_name	sum
三吉町南店	12032
東桜町店	19836
北花町東店	20580
北花町駅前店	21376
三吉町店	21929
(5 行)

テーブル確認用

shop

s_id	s_name
M0001	三吉町店
M0002	三吉町南店
H0001	東桜町店
K0001	北花町駅前店
K0002	北花町東店

sales

s_id	s_date	s_value
M0001	2012-12	11145
H0001	2012-12	9861
N0001	2012-12	11023
K0001	2012-12	11234
K0002	2012-12	10456
M0001	2012-11	10784
M0002	2012-11	12032
H0001	2012-11	9975
N0001	2012-11	9965
K0001	2012-11	10142
K0002	2012-11	10124

スキーマの確認

shop

No.	物理名	データ型	Not Null
1	s_id	character(5)	Yes (PK)
2	s_name	character varying(30)

sales

No.	物理名	データ型	Not Null
1	s_id	character(5)	Yes (PK)
2	s_date	character(7)	Yes (PK)
3	s_value	integer

Q2-1-5:貸し出し記録テーブル(rental)とユーザテーブル(usr)からユーザについて、現在何冊の貸出を行っているかをユーザーID、氏名、貸し出し数を貸し出し数の多い順で取り出せ。

sql

SELECT
  u.user_id,
  u.l_name||u.f_name AS 氏名
FROM
  usr AS u
;

result

user_id	氏名
B200505	根岸裕子
A200403	江本聡
A200402	上原幸一
A200501	山田太郎
A200404	小野雄二
A200401	井上一郎
B200503	森本絵里
A200505	坂口駿
B200403	吉岡遥
aaa	井上次郎
B200402	有木茉莉
B200401	矢口亜由美
A200502	木下一樹
B200504	西島文子
B200501	南沙恵
A200405	河合泰治
B200502	村井佐知子
A200504	児玉毅
B200405	松本博美
A200506	井上一郎
B200404	和田修子
A200503	久保田守
(22 ?s)

sql

SELECT
  u.user_id,
  u.l_name||u.f_name AS 氏名
FROM
  usr AS u
INNER JOIN
  rental AS r
ON
  u.user_id = r.user_id
;

result

user_id	氏名
A200501	山田太郎
A200403	江本聡
A200405	河合泰治
B200403	吉岡遥
B200405	松本博美
A200502	木下一樹
A200505	坂口駿
B200501	南沙恵
B200503	森本絵里
B200504	西島文子
A200401	井上一郎
A200402	上原幸一
A200404	小野雄二
A200405	河合泰治
B200402	有木茉莉
B200404	和田修子
A200502	木下一樹
A200506	井上一郎
B200501	南沙恵
B200503	森本絵里
B200504	西島文子
B200505	根岸裕子
A200405	河合泰治
B200403	吉岡遥
B200405	松本博美
(25行)

sql

SELECT
  u.user_id,
  u.l_name||u.f_name AS 氏名
FROM
  usr AS u
INNER JOIN
  rental AS r
ON
  u.user_id = r.user_id
WHERE
  r.returned = 0
;

result

user_id	氏名
A200402	上原幸一
A200405	河合泰治
A200405	河合泰治
B200403	吉岡遥
B200404	和田修子
B200405	松本博美
B200503	森本絵里
B200504	西島文子
B200504	西島文子
B200505	根岸裕子
(10行)

sql

SELECT
  u.user_id,
  u.l_name||u.f_name AS 氏名,
  COUNT(*) AS 貸し出し数
FROM
  usr AS u
INNER JOIN
  rental AS r
ON
  u.user_id = r.user_id
WHERE
  r.returned = 0
GROUP BY
  u.user_id,
  u.l_name,
  u.f_name
;

result

user_id	氏名	貸し出し数
A200402	上原幸一	1
B200503	森本絵里	1
B200405	松本博美	1
A200405	河合泰治	2
B200404	和田修子	1
B200505	根岸裕子	1
B200504	西島文子	2
B200403	吉岡遥	1
(8行)

sql

SELECT
  u.user_id,
  u.l_name||u.f_name AS 氏名,
  COUNT(*) AS 貸し出し数
FROM
  usr AS u
INNER JOIN
  rental AS r
ON
  u.user_id = r.user_id
WHERE
  r.returned = 0
GROUP BY
  u.user_id,
  u.l_name,
  u.f_name
ORDER BY
  COUNT(*) DESC
;

result

user_id	氏名	貸し出し数
B200504	西島文子	2
A200405	河合泰治	2
B200405	松本博美	1
B200404	和田修子	1
B200505	根岸裕子	1
A200402	上原幸一	1
B200403	吉岡遥	1
B200503	森本絵里	1
(8 行)

テーブル確認用

usr

user_id	l_name	f_name	l_name_kana	f_name_kana	prefecture	city	o_address	tel	email
A200401	井上	一郎	イノウエ	イチロウ	千葉県	東千葉市北町	2-4-1	040-111-0001	inoue@wings.msn.to
A200402	上原	幸一	ウエハラ	コウイチ	神奈川県	神奈川市南町	1-5-2	040-222-0002	uehara@wings.msn.to
A200403	江本	聡	エモト	サトシ	茨城県	茨城市西町	2-1-12	040-333-0003	emoto@wings.msn.to
A200404	小野	雄二	オノ	ユウジ	東京都	東京市南町	3-2-6	040-111-0004	ono@examples.com
A200405	河合	泰治	カワィ	ヤスハル	神奈川県	神奈川市北町	4-2-12	040-222-0005	kawai@wings.msn.to
B200401	矢口	亜由美	ヤグチ	アユミ	千葉県	東千葉市南町	3-4-6	040-111-0011	yaguchi@wings.msn.to
B200402	有木	茉莉	アリキ	マリ	神奈川県	神奈川市北町	1-4-5	040-222-0012	yuki@wings.msn.to
B200403	吉岡	遥	ヨシオカ	ハルカ	茨城県	茨城市東町	7-2-1	040-333-0013	yoshi@examples.com
B200404	和田	修子	ワダ	シュウコ	東京都	東京市北町	6-1-11	040-111-0014	wada@wings.msn.to
B200405	松本	博美	マツモト	ヒロミ	神奈川県	神奈川市南町	3-4-21	040-222-0015	matsu@wings.msn.to
A200501	山田	太郎	ヤマダ	タロウ	東京都	東京市西町	0-0-0	040-111-0031	taro_yama@wings.msn.to
A200502	木下	一樹	キノシタ	カズキ	茨城県	茨城市東町	5-1-10	040-333-0006	kino@wings.msn.to
A200503	久保田	守	クボタ	マモル	千葉県	東千葉市南町	2-1-21	040-111-0007	kubota@wings.msn.to
A200504	児玉	毅	コダマ	ツヨシ	埼玉県	西埼玉市東町	3-4-11	040-444-0008	kodama@wings.msn.to
A200505	坂口	駿	サカグチ	シュン	栃木県	北栃木市西町	6-2-4	040-555-0009	saka@examples.com
A200506	井上	一郎	イノウエ	イチロウ	静岡県	南静岡市花町	4-2-1	040-666-0010	shimi@wings.msn.to
B200501	南	沙恵	ミナミ	サエ	茨城県	茨城市西町	3-3-2	040-333-0016	minami@wings.msn.to
B200502	村井	佐知子	ムライ	サチコ	千葉県	東千葉市北町	4-1-13	040-111-0017	murai@wings.msn.to
B200503	森本	絵里	モリモト	エリ	埼玉県	西埼玉市西町	2-1-4	040-444-0018	morimo@examples.com
B200504	西島	文子	ニシジマ	フミコ	栃木県	北栃木市東町	2-5-7	040-555-0019	nishi@wings.msn.to
B200505	根岸	裕子	ネギシ	ユウコ	静岡県	南静岡市寺町	3-4-8	040-666-0020	negi@wings.msn.to
aaa(22行)	井上	次郎

rental

id	user_id	isbn	rental_date	returned
1	A200501	4-0010-0000-0	2011-12-01	1
2	A200403	4-8833-0000-2	2010-12-03	1
3	A200405	4-8833-0000-2	2011-03-20	1
4	B200403	4-8833-0000-2	2011-04-16	1
5	B200405	4-0010-0000-0	2012-01-16	1
6	A200502	4-8833-0000-2	2011-07-22	1
7	A200505	4-8833-0000-2	2011-08-23	1
8	B200501	4-8833-0000-2	2011-11-10	1
9	B200503	4-8833-0000-2	2012-01-20	1
10	B200504	4-0010-0000-0	2012-03-20	0
11	A200401	4-8833-0000-2	2012-02-11	1
12	A200402	4-8833-0000-2	2012-02-22	0
13	A200404	4-8833-0000-2	2012-03-19	1
14	A200405	4-0010-0000-0	2012-06-14	0
15	B200402	4-8833-0000-2	2012-08-27	1
16	B200404	4-8833-0000-2	2012-12-10	0
17	A200502	4-7980-0522-3	2011-10-25	1
18	A200506	4-7981-0722-0	2012-03-26	1
19	B200501	4-0010-0000-0	2012-09-15	1
20	B200503	4-8833-0000-1	2013-03-16	0
21	B200504	4-7980-0945-8	2013-01-14	0
22	B200505	4-7981-0959-2	2013-01-12	0
23	A200405	4-8833-0000-3	2012-12-22	0
24	B200403	4-0010-0000-9	2012-12-27	0
25	B200405	4-0010-0000-1	2013-03-10	0

スキーマの確認

usr

No.	物理名	データ型	Not Null
1	user_id	character(7)	Yes (PK)
2	I_name	character varying(20)
3	f_name	character varying(20)
4	I_name_kana	character varying(100)
5	f_name_kana	character varying(100)
6	prefecture	character varying(15)
7	city	character varying(20)
8	o_address	character varying(100)
9	tel	character varying(20)
10	email	character varying(255)

sales

No.	物理名	データ型	Not Null
1	s_id	character(5)	Yes (PK)
2	s_date	character(7)	Yes (PK)
3	s_value	integer

外部結合(RIGHT JOIN ON句／LEFT JOIN ON 句)

LEFT OUTER JOIN (左外部結合)

目的: 左テーブルの全レコードを必ず出力。右テーブルで結合条件に合うレコードがあれば結合。
結果:
- 左テーブルの全レコードが出力される。
- 右テーブルに結合条件が合うレコードがない場合、右テーブルのカラムは NULL になる。
イメージ: 左側を基準に右側をくっつける。右になくても左は必ず出す。
使い分け: 「左テーブルの情報を網羅的に見たい。関連する右テーブルの情報があれば付加したい」場合。
注意: WHERE句で右テーブルのカラムに IS NOT NULL 条件をつけると、内部結合に近くなる場合がある。
RIGHT OUTER JOIN (右外部結合)は上記が入れ替わる

FULL OUTER JOIN (完全外部結合):

左側と右側の両方のテーブルの全てのレコードを結果に含めます。
両方のテーブルで結合条件に一致するレコードがあれば結合し、片方のテーブルにしか一致するレコードがない場合は、もう片方のテーブルのカラムは NULL になります。
イメージ: 両方のテーブルの情報を全て表示し、一致する部分はくっつける。どちらかのテーブルにしか情報がない場合でも、それぞれの情報を NULL で補完して表示する。

共通ポイント

ON句: 結合条件は必須。どのカラムで結合するか指定。
NULL値: 結合できない場合、相手テーブルのカラムは NULL になる。
可読性: LEFT JOIN を基本とし、RIGHT JOIN は必要最低限に留める方が可読性向上に繋がる場合がある。 (LEFT JOIN でテーブル順序を入れ替えれば RIGHT JOIN と同じ結果になるため)

使い分けの目安

LEFT OUTER JOIN: 「Aテーブルの情報を全て見たい。Bテーブルに関連情報があれば一緒に表示したい」という場合に利用します。（例：顧客情報と注文履歴。顧客全員を表示し、注文履歴がある顧客には注文履歴も表示する）
RIGHT OUTER JOIN: 「Bテーブルの情報を全て見たい。Aテーブルに関連情報があれば一緒に表示したい」という場合に利用します。（LEFT OUTER JOIN の左右を入れ替えた場合と同じ結果になることが多い）
FULL OUTER JOIN: 「AテーブルとBテーブルの情報を全て見たい。両方のテーブルに存在する情報を繋ぎ合わせ、片方にしかない情報も全て表示したい」という場合に利用します。（例：従業員情報と部署情報。従業員全員と部署全員を表示し、所属している従業員は部署情報も表示する）

Q2-2-1-1:注文明細テーブル(order_desc)と商品テーブル(product)から商品コード(p_id)毎に累計購入数(quantityの合計)と商品名(p_name),価格(price)を取得せよ。

sql

SELECT
 p.p_id,
 p.p_name,
 p.price
FROM
 product AS p
;

product

p_id	p_name	price
PB00000001	黒ボールペン	100
PB00000002	赤ボールペン	100
DE00000001	電卓	600
TO00000001	トナー黒	1000
TO00000002	トナー赤	1000
TU00000001	机	15000
IS00000001	椅子	10000
MA00000000	マウスパッド	1500

sql

SELECT
 p.p_id,
 p.p_name,
 p.price,
 o.quantity
FROM
 product AS p
INNER JOIN
 order_desc AS o
ON
 o.p_id = p.p_id
;

result

p_id	p_name	price	quantity
PB00000001	黒ポールペン	100	200
PB00000002	赤ポールペン	100	300
DE00000001	電卓	600	20
TO00000001	トナー黒	1000	10
TO00000002	トナー赤	1000	10
TU00000001	机	15000	2
IS00000001	椅子	10000	3
PB00000001	黒ポールペン	100	100
PB00000002	赤ポボールペン	100	150
DE00000001	電卓	600	10
TO00000001	トナー黒	1000	5
TO00000002	トナー赤	1000	5
PB00000001	黒ポールペン	100	1
PB00000002	赤ポボールペン	100	50
DE00000001	電卓	600	50
TO00000001	トナー黒	1000	5
TO00000002	トナー赤	1000	5
PB00000001	黒ポールペン	100	100
PB00000002	赤ポールペン	100	100
DE00000001	電卓	600	10

sql

SELECT
 p.p_id,
 p.p_name,
 p.price,
 SUM(o.quantity)
FROM
 product AS p
INNER JOIN
 order_desc AS o
ON
 o.p_id = p.p_id
GROUP BY
 p.p_id
;

result

p_id	p_name	price	sum
IS00000001	椅子	10000	3
PB00000002	赤ポールペン	100	600
DE00000001	電卓	600	90
TO00000001	トナー黒	1000	20
PB00000001	黒ポールペン	100	401
TO00000002	トナー赤	1000	20
TU00000001	机	15000	2

Q2-2-1-2:前頁の結果の価格(price)を累計購入額(quantity×price)として購入額の高い順で取得せよ。

sql

SELECT
 p.p_id,
 p.p_name,
 p.price,
 SUM(o.quantity) sum_q,
 SUM(o.quantity * p.price) sum_qp
FROM
 product AS p
INNER JOIN
 order_desc AS o
ON
 o.p_id = p.p_id
GROUP BY
 p.p_id
ORDER BY
 sum_qp DESC
;

result

p_id	p_name	price	sum_q	sum_qp
PB00000002	赤ポールペン	100	600	60000
DE00000001	電卓	600	90	54000
PB00000001	黒ポールペン	100	401	40100
TU00000001	机	15000	2	30000
IS00000001	椅子	10000	3	30000
TO00000001	トナー黒	1000	20	20000
TO00000002	トナー赤	1000	20	20000

Q2-2-1-3:前頁の結果に商品テーブルに登録されているが注文明細に記録されていない商品も取得せよ。

sql

SELECT
 p.p_id,
 p.p_name,
 p.price,
 SUM(o.quantity) sum_q,
 SUM(o.quantity * p.price) sum_qp
FROM
 product AS p
LEFT JOIN
 order_desc AS o
ON
 o.p_id = p.p_id
GROUP BY
 p.p_id
ORDER BY
 sum_qp DESC
;

result

p_id	p_name	price	sum_q	sum_qp
MA00000000	マウスパッド	1500	NULL	NULL
PB00000002	赤ポールペン	100	600	60000
DE00000001	電卓	600	90	54000
PB00000001	黒ポールペン	100	401	40100
TU00000001	机	15000	2	30000
IS00000001	椅子	10000	3	30000
TO00000002	トナー赤	1000	20	20000
TO00000001	トナー黒	1000	20	20000

テーブル確認用

order_desc

po_id	p_id	quantity
1	PB00000001	200
2	PB00000002	300
3	DE00000001	20
4	TO00000001	10
5	TO00000002	10
6	TU00000001	2
7	IS00000001	3
8	PB00000001	100
9	PB00000002	150
10	DE00000001	10
11	TO00000001	5
12	TO00000002	5
13	PB00000001	1
14	PB00000002	50
15	DE00000001	50
16	TO00000001	5
17	TO00000002	5
18	PB00000001	100
19	PB00000002	100
20	DE00000001	10

product

p_id	p_name	price
PB00000001	黒ボールペン	100
PB00000002	赤ボールペン	100
DE00000001	電卓	600
TO00000001	トナー黒	1000
TO00000002	トナー赤	1000
TU00000001	机	15000
IS00000001	椅子	10000
MA00000000	マウスパッド	1500

スキーマの確認

Q2-2-2ユーザテーブル(usr)と貸し出し記録テーブル(rental)からユーザごとの貸し出し数が多い順で取得するSQL文を完成させろ。なお、貸し出しがないユーザの貸し出し数は「0」として出力せよ。

sql

SELECT
  r.id
FROM
  rental AS r

result

id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

sql

SELECT
  u.l_name,
  u.f_name,
  r.id
FROM
  rental AS r
RIGHT JOIN
  usr AS u
ON
  u.user_id = r.user_id
;

result

l_name	f_name	id
山田	太郎	1
江本	聡	2
河合	泰治	3
吉岡	遥	4
松本	博美	5
木下	一樹	6
坂口	駿	7
南	沙恵	8
森本	絵里	9
西島	文子	10
井上	一郎	11
上原	幸一	12
小野	雄二	13
河合	泰治	14
有木	茉莉	15
和田	修子	16
木下	一樹	17
井上	一郎	18
南	沙恵	19
森本	絵里	20
西島	文子	21
根岸	裕子	22
河合	泰治	23
吉岡	遥	24
松本	博美	25
矢口	亜由美	NULL
井上	次郎	NULL
村井	佐知子	NULL
児玉	毅	NULL
久保田	守	NULL

sql

SELECT
  u.l_name,
  u.f_name,
  COUNT(r.id)
FROM
  rental AS r
RIGHT JOIN
  usr AS u
ON
  u.user_id = r.user_id
GROUP BY
  u.user_id
;

result

l_name	f_name	count
森本	絵里	2
根岸	裕子	1
児玉	毅	0
上原	幸一	1
村井	佐知子	0
松本	博美	2
江本	聡	1
和田	修子	1
有木	茉莉	1
西島	文子	2
木下	一樹	2
坂口	駿	1
南	沙恵	2
河合	泰治	3
井上	次郎	0
久保田	守	0
井上	一郎	1
小野	雄二	1
矢口	亜由美	0
吉岡	遥	2
井上	一郎	1
山田	太郎	1

sql

SELECT
  u.l_name,
  u.f_name,
  COUNT(r.id)
FROM
  rental AS r
RIGHT JOIN
  usr AS u
ON
  u.user_id = r.user_id
GROUP BY
  u.user_id
ORDER BY
  COUNT(r.id) DESC
;

result

l_name	f_name	count
河合	泰治	3
松本	博美	2
西島	文子	2
吉岡	遥	2
森本	絵里	2
南	沙恵	2
木下	一樹	2
山田	太郎	1
根岸	裕子	1
上原	幸一	1
江本	聡	1
和田	修子	1
有木	茉莉	1
坂口	駿	1
井上	一郎	1
小野	雄二	1
井上	一郎	1
児玉	毅	0
矢口	亜由美	0
井上	次郎	0
久保田	守	0
村井	佐知子	0

スキーマの確認

Q2-2-3:社員テーブル(employee)と所属部署テーブル(depart)を結合し所属社員が一人もいない「幽霊」部署を洗い出すSQL文を完成させろ。

sql

SELECT
  d.*
FROM
  depart AS d
;

result

depart_id	depart_name
S01	経営企画部
E01	第一営業部
E02	第二営業部
J01	人事部
S02	総務部

sql

SELECT
  *
FROM
  depart AS d
LEFT JOIN
  employee AS e
ON
  d.depart_id = e.depart_id
;

result

depart_id	depart_name	s_id	l_name	f_name	l_name_kana	f_name_kana	sex	class	depart_id_1	b_id	last_update	retired
S02	総務部	AI00001	相沢	聡	アイザワ	サトシ	1	部長	S02	AI00001	2012/02/24	0
J01	人事部	DA00001	大門	一郎	ダイモン	イチロウ	1	担当	J01	AI00001	2011/06/10	0
E01	第一営業部	FU00001	藤井	健太	フジイ	ユウタ	1	主任	E01	SE00001	2011/04/10	0
E01	第一営業部	FU00002	福岡	幸太郎	フクオカ	コウタロウ	1	担当	E01	FU00001	2011/06/12	0
E01	第一営業部	HA00001	速水	和幸	ハヤミ	カズユキ	1	部長	E01	FU00001	2007/02/03	1
E01	第一営業部	HA00002	葉山	俊輔	ハヤマ	シュンスケ	2	アシスタント	E01	FU00002	2012/12/11	0
S01	経営企画部	KA00001	川口	裕子	カワグチ	ユウコ	2	アシスタント	S01	KI00001	2012/12/28	1
S01	経営企画部	KA00002	加藤	昭雄	カトウ	アキオ	1	アシスタント	S01	KI00001	2011/11/11	0
J01	人事部	KA00003	神田	佐知子	カンダ	サチコ	1	アシスタント	J01	KA00003	2012/01/28	0
S01	経営企画部	KI00001	木村	一郎	キムラ	イチロウ	1	担当	S01	TA00001	2011/07/12	0
S02	総務部	NA00000	中澤	康代	ナカザワ	ヤスコ	2	アシスタント	S02	TO00002	2012/04/17	1
S02	総務部	NO0000	野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0
S01	経営企画部	SA00001	佐藤	智子	サトウ	トモコ	2	部長	S01	SA00001	2010/02/01	0
E01	第一営業部	SE00001	洲尾	春樹	セオ	ハルキ	1	課長	E01	TE00001	2010/12/10	0
J01	人事部	SI00001	清水	春子	シミズ	ハルコ	2	主任	J01	SI00001	2011/12/11	0
S01	経営企画部	TA00001	田中	由美子	タナカ	マユコ	2	主任	S01	YA00001	2011/05/26	0
E01	第一営業部	TA00002	田辺	正一	タナベ	ショウイチ	1	アシスタント	E01	HA00002	2012/12/19	0
E01	第一営業部	TE00001	寺岡	陽一	テラオカ	ヨウイチ	1	部長	E01	HA00001	2009/12/11	0
S02	総務部	TI00001	近田	晃子	チカダ	アキコ	2	課長	S02	AI00001	2012/03/01	0
J01	人事部	TO00001	遠山	真一	トオヤマ	シンイチ	1	課長	J01	UE00001	2010/12/16	0
S02	総務部	TO00002	戸川	よしみ	トガワ	ヨシミ	2	担当	S02	TU00001	2012/04/01	0
S02	総務部	TU00001	津村	知美	ツムラ	カズミ	2	主任	S02	TI00001	2012/03/16	0
J01	人事部	UE00001	上田	一也	ウエダ	カズヤ	2	課長	J01	UE00001	2011/11/12	0
S01	経営企画部	YA00001	山田	京美	ヤマダ	ナミ	2	課長	S01	SA00001	2012/10/15	0
E02	第二営業部	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

sql

SELECT
  d.depart_id,
  d.depart_name  
FROM
  depart AS d
LEFT JOIN
  employee AS e
ON
  d.depart_id = e.depart_id
WHERE
  e.s_id IS NULL
;

result

depart_id	depart_name
E02	第二営業部

sql

SELECT
  u.user_id,
  u.l_name||u.f_name AS 氏名,
  COUNT(*) AS 貸し出し数
FROM
  usr AS u
INNER JOIN
  rental AS r
ON
  u.user_id = r.user_id
WHERE
  r.returned = 0
GROUP BY
  u.user_id,
  u.l_name,
  u.f_name
ORDER BY
  COUNT(*) DESC
;

result

user_id	氏名	貸し出し数
B200504	西島文子	2
A200405	河合泰治	2
B200405	松本博美	1
B200404	和田修子	1
B200505	根岸裕子	1
A200402	上原幸一	1
B200403	吉岡遥	1
B200503	森本絵里	1
(8 行)

Q2-2-4:書籍情報テーブル(books)と貸し出し記録テーブル(rental)を結合し、書籍毎の貸し出し累計数を多い順に取り出せ。この時貸し出しが1回もないものも取り出せ。

sql

SELECT
  r.*
FROM
  rental AS r
;

result

id	user_id	isbn	rental_date	returned
1	A200501	4-0010-0000-0	2011/12/01	1
2	A200403	4-8833-0000-2	2010/12/03	1
3	A200405	4-8833-0000-2	2011/03/20	1
4	B200403	4-8833-0000-2	2011/04/16	1
5	B200405	4-0010-0000-0	2012/01/16	1
6	A200502	4-8833-0000-2	2011/07/22	1
7	A200505	4-8833-0000-2	2011/08/23	1
8	B200501	4-8833-0000-2	2011/11/10	1
9	B200503	4-8833-0000-2	2012/01/20	1
10	B200504	4-0010-0000-0	2012/03/20	0
11	A200401	4-8833-0000-2	2012/02/11	1
12	A200402	4-8833-0000-2	2012/02/22	0
13	A200404	4-8833-0000-2	2012/03/19	1
14	A200405	4-0010-0000-0	2012/06/14	0
15	B200402	4-8833-0000-2	2012/08/27	1
16	B200404	4-8833-0000-2	2012/12/10	0
17	A200502	4-7980-0522-3	2011/10/25	1
18	A200506	4-7981-0722-0	2012/03/26	1
19	B200501	4-0010-0000-0	2012/09/15	1
20	B200503	4-8833-0000-1	2013/03/16	0
21	B200504	4-7980-0945-8	2013/01/14	0
22	B200505	4-7981-0959-2	2013/01/12	0
23	A200405	4-8833-0000-3	2012/12/22	0
24	B200403	4-0010-0000-9	2012/12/27	0
25	B200405	4-0010-0000-1	2013/03/10	0

sql

SELECT
  *
FROM
  rental AS r
RIGHT JOIN
  books AS b
ON
  b.isbn = r.isbn
;

result

user_id	rental_date	returned	isbn_1	title	price	publish	publish_date	category_id
A200501	2011/12/01	1	4-0010-0000-0	ハムスターの観察	900	山田出版	2010/11/01	Z9999
A200403	2010/12/03	1	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
A200405	2011/03/20	1	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
B200403	2011/04/16	1	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
B200405	2012/01/16	1	4-0010-0000-0	ハムスターの観察	900	山田出版	2010/11/01	Z9999
A200502	2011/07/22	1	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
A200505	2011/08/23	1	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
B200501	2011/11/10	1	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
B200503	2012/01/20	1	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
B200504	2012/03/20	0	4-0010-0000-0	ハムスターの観察	900	山田出版	2010/11/01	Z9999
A200401	2012/02/11	1	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
A200402	2012/02/22	0	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
A200404	2012/03/19	1	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
A200405	2012/06/14	0	4-0010-0000-0	ハムスターの観察	900	山田出版	2010/11/01	Z9999
B200402	2012/08/27	1	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
B200404	2012/12/10	0	4-8833-0000-2	SQLプチブック	1600	日経BP	2010/11/30	S2222
A200502	2011/10/25	1	4-7980-0522-3	JSPリファレンス	1800	秀和システム	2010/04/19	J4444
A200506	2012/03/26	1	4-7981-0722-0	XML辞典	3300	翔泳社	2011/09/16	X3333
B200501	2012/09/15	1	4-0010-0000-0	ハムスターの観察	900	山田出版	2010/11/01	Z9999
B200503	2013/03/16	0	4-8833-0000-1	SQLリファレンス	2500	日経BP	2013/02/15	S2222
B200504	2013/01/14	0	4-7980-0945-8	PHP5サンプル集	3000	秀和システム	2012/11/01	P1111
B200505	2013/01/12	0	4-7981-0959-2	PEAR入門	3000	翔泳社	2012/09/08	P1111
A200405	2012/12/22	0	4-8833-0000-3	XMLリファレンス	3200	日経BP	2012/11/24	X3333
B200403	2012/12/27	0	4-0010-0000-9	SQL入門	5500	山田出版	2012/10/30	S2222
B200405	2013/03/10	0	4-0010-0000-1	PHPドリル	5100	山田出版	2013/01/14	P1111
NULL	NULL	NULL	4-0010-0000-6	あひるの観察日記	700	山田出版	2012/11/15	A9999
NULL	NULL	NULL	4-0010-0000-7	かえるの観察日記	800	山田出版	2013/01/15	H9999
NULL	NULL	NULL	4-0010-0000-5	らくだの観察日記	1100	山田出版	2012/12/24	Z9999
NULL	NULL	NULL	4-0010-0000-4	フェレットの観察	1000	山田出版	2012/10/26	Z9999

sql

SELECT
  b.isbn,
  b.title,
  COUNT(r.id)
FROM
  rental AS r
RIGHT JOIN
  books AS b
ON
  b.isbn = r.isbn
GROUP BY
  b.isbn
;

result

isbn	title	count
4-0010-0000-4	フェレットの観察	0
4-0010-0000-5	らくだの観察日記	0
4-0010-0000-1	PHPドリル	1
4-7980-0945-8	PHP5サンプル集	1
4-8833-0000-2	SQLプチブック	12
4-0010-0000-7	かえるの観察日記	0
4-7981-0959-2	PEAR入門	1
4-0010-0000-6	あひるの観察日記	0
4-8833-0000-1	SQLリファレンス	1
4-7980-0522-3	JSPリファレンス	1
4-8833-0000-3	XMLリファレンス	1
4-7981-0722-0	XML辞典	1
4-0010-0000-0	ハムスターの観察	5
4-0010-0000-9	SQL入門	1

sql

SELECT
  b.isbn,
  b.title,
  COUNT(r.id)
FROM
  rental AS r
RIGHT JOIN
  books AS b
ON
  b.isbn = r.isbn
GROUP BY
  b.isbn
ORDER BY
  COUNT(r.id) DESC  
;

result

isbn	title	count
4-8833-0000-2	SQLプチブック	12
4-0010-0000-0	ハムスターの観察	5
4-0010-0000-9	SQL入門	1
4-0010-0000-1	PHPドリル	1
4-7981-0959-2	PEAR入門	1
4-7980-0945-8	PHP5サンプル集	1
4-8833-0000-1	SQLリファレンス	1
4-8833-0000-3	XMLリファレンス	1
4-7980-0522-3	JSPリファレンス	1
4-7981-0722-0	XML辞典	1
4-0010-0000-6	あひるの観察日記	0
4-0010-0000-5	らくだの観察日記	0
4-0010-0000-7	かえるの観察日記	0
4-0010-0000-4	フェレットの観察	0

Q2-2-5:社員テーブル(employee)とタイムカードテーブル(time_card)を結合し、社員ごとの勤務時間平均を多い順に取り出せ。この時記録のない社員ないものも取り出せ。

sql

SELECT
 *
FROM
  employee AS e
;

result

s_id	l_name	f_name	l_name_kana	f_name_kana	sex	class	depart_id	b_id	last_update	retired
AI00001	相沢	聡	アイザワ	サトシ	1	部長	S02		2012/02/24	0
DA00001	大門	一郎	ダイモン	イチロウ	1	担当	J01	AI00001	2011/08/10	0
FU00001	藤井	健太	フジイ	ユウタ	1	主任	E01	SE00001	2011/04/10	0
FU00002	福岡	幸太郎	フジオカ	コウタロウ	1	担当	E01	FU00001	2011/06/12	0
HA00001	速水	和幸	ハヤミ	カズユキ	1	部長	E01		2007/02/03	1
HA00002	葉山	俊輔	ハヤマ	シュンスケ	2	アシスタント	E01	FU00002	2012/12/11	0
KA00001	川口	裕子	カワグチ	ユウコ	2	アシスタント	S01	KI00001	2012/12/28	1
KA00002	加藤	昭雄	カトウ	アキオ	1	アシスタント	S01	KI00001	2011/11/11	0
KA00003	神田	佐知子	カンダ	サチコ	1	アシスタント	J01	KA00003	2012/01/28	0
KI00001	木村	一郎	キムラ	イチロウ	1	担当	S01	TA00001	2011/07/12	0
NA00000	中澤	康代	ナカザワ	ヤスコ	2	アシスタント	S02	TO00002	2012/04/17	1
NO0000	野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0
SA00001	佐藤	智子	サトウ	トモコ	2	部長	S01		2010/02/01	0
SE00001	妹尾	春樹	セノオ	ハルキ	1	課長	E01	TE00001	2010/12/10	0
SI00001	清水	春子	シミズ	ハルコ	2	主任	J01	SI00001	2011/12/11	0
TA00001	田中	真由子	タナカ	マユコ	2	主任	S01	YA00001	2011/05/26	0
TA00002	田辺	正一	タナベ	ショウイチ	1	アシスタント	E01	HA00002	2012/12/19	0
TE00001	寺岡	陽一	テラオカ	ヨウイチ	1	部長	E01	HA00001	2009/12/11	0
TI00001	近田	晃子	チカダ	アキコ	2	課長	S02	AI00001	2012/03/01	0
TO00001	遠山	真一	トオヤマ	シンイチ	1	課長	J01	UE00001	2010/12/16	0
TO00002	戸川	よしみ	トガワ	ヨシミ	2	担当	S02	TU00001	2012/04/01	0
TU00001	津村	知美	ツムラ	カズミ	2	主任	S02	TI00001	2012/03/16	0
UE00001	上田	一也	ウエダ	カズヤ	1	部長	J01		2011/11/12	0
YA00001	山田	奈美	ヤマダ	ナミ	2	課長	S01	SA00001	2012/10/15	0
NI00001	西	雄一	ニシ	ユウイチ	1	アシスタント	B01		2013/01/10	0
NI00002	新渡戸	康治	ニトベ	コウジ	1	アシスタント	B01		2013/01/15	0

sql

SELECT
 *
FROM
  employee AS e
LEFT JOIN
  time_card AS t
ON
  e.s_id = t.s_id
;

result

l_name	f_name	l_name_kana	f_name_kana	sex	class	depart_id	b_id	last_update	retired	s_id_1	r_date	work_time
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/07	10
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/08	8
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/09	9
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/10	8
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/11	9
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/14	8
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/15	8
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/16	8
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/17	9
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/18	10
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/21	8
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/22	8
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/23	8
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/24	8
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/25	9
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/28	8
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/29	10
野上	利江	ノガミ	リエ	2	アシスタント	S02	TO00002	2012/06/30	0	NO00001	2012/11/30	8
新渡戸	康治	ニトベ	コウジ	1	アシスタント	B01		2013/01/15	0	NULL	NULL	NULL
妹尾	春樹	セノオ	ハルキ	1	課長	E01	TE00001	2010/12/10	0	NULL	NULL	NULL
山田	奈美	ヤマダ	ナミ	2	課長	S01	SA00001	2012/10/15	0	NULL	NULL	NULL
寺岡	陽一	テラオカ	ヨウイチ	1	部長	E01	HA00001	2009/12/11	0	NULL	NULL	NULL
佐藤	智子	サトウ	トモコ	2	部長	S01		2010/02/01	0	NULL	NULL	NULL
遠山	真一	トオヤマ	シンイチ	1	課長	J01	UE00001	2010/12/16	0	NULL	NULL	NULL
上田	一也	ウエダ	カズヤ	1	部長	J01		2011/11/12	0	NULL	NULL	NULL
西	雄一	ニシ	ユウイチ	1	アシスタント	B01		2013/01/10	0	NULL	NULL	NULL
相沢	聡	アイザワ	サトシ	1	部長	S02		2012/02/24	0	NULL	NULL	NULL
速水	和幸	ハヤミ	カズユキ	1	部長	E01		2007/02/03	1	NULL	NULL	NULL
近田	晃子	チカダ	アキコ	2	課長	S02	AI00001	2012/03/01	0	NULL	NULL	NULL

sql

SELECT
  e.l_name||e.f_name AS 社員氏名,
  COALESCE(AVG(t.work_time),0) AS 勤務時間平均
FROM
  employee AS e
LEFT JOIN
  time_card AS t
ON
  e.s_id = t.s_id
GROUP BY
  e.s_id
;

result

社員氏名	勤務時間平均
田辺正一	8.476190476190
西雄一	0.0
相沢聡	0.0
藤井雄太	8.476190476190
藤岡幸太郎	8.5
神田佐知子	8.547619047619
戸川よしみ	8.476190476190
葉山俊輔	8.523809523809
山田奈美	0.0
加藤昭雄	8.476190476190
清水春子	8.476190476190
佐藤智子	0.0
妹尾春樹	0.0
木村一郎	8.5
上田一也	0.0
野上利江	8.523809523809
遠山真一	0.0
中澤康代	8.5
速水和幸	0.0
大門一郎	8.5
川口裕子	8.547619047619
新渡戸康治	0.0
津村知美	8.476190476190
寺岡陽一	0.0
田中真由子	8.523809523809
近田晃子	0.0

sql

SELECT
  e.l_name||e.f_name AS 社員氏名,
  COALESCE(AVG(t.work_time),0) AS 勤務時間平均
FROM
  employee AS e
LEFT JOIN
  time_card AS t
ON
  e.s_id = t.s_id
GROUP BY
  e.s_id
ORDER BY
  COALESCE(AVG(t.work_time),0) DESC
;

result

社員氏名	勤務時間平均
神田佐知子	8.547619047619
川口裕子	8.547619047619
葉山俊輔	8.523809523809
田中真由子	8.523809523809
野上利江	8.523809523809
大門一郎	8.5
藤岡幸太郎	8.5
中澤康代	8.5
木村一郎	8.5
田辺正一	8.476190476190
藤井雄太	8.476190476190
戸川よしみ	8.476190476190
加藤昭雄	8.476190476190
清水春子	8.476190476190
津村知美	8.476190476190
妹尾春樹	0.0
遠山真一	0.0
佐藤智子	0.0
速水和幸	0.0
山田奈美	0.0
相沢聡	0.0
新渡戸康治	0.0
近田晃子	0.0
寺岡陽一	0.0
西雄一	0.0
上田一也	0.0

自己結合(JOIN句＋別名定義)

自己結合とは、同じテーブル同士の結合のことをいいます。

sql

FROM 
 table as t1
INNER JOIN
 table AS t2
ON
 t1.id=t2.n_id

最も一般的な用途は循環リレーションシップ（再帰）への対応と言われています。自分のテーブルに存在する列を外部キーとして指定するような、テーブルに循環型の外部キーが含まれる場合があるときに、自己結合はよく用いられます。

Q2-3-1-1:書類目次テーブル(contents)からコンテンツコード(c_id)順に取得せよ。

sql

SELECT
 *
FROM
 contents
ORDER BY
 c_id
;

result

c_id	c_title	next_id
A001	SQLについて	A011
A011	データの取得1	A012
A012	カラムの選択	A013
A013	WHERE句の記述	A014
A014	NULLと空白の扱い	A015
A015	あいまい検索	A016
A016	複数条件の連結（論理演算子）	A017
A017	重複レコードの除去	A021
A021	データの取得2	A022
A022	順位付け	A023
A023	ソート	A024
A024	グルーピング	A025
A025	グルーピング結果の絞り込み	A026
A026	クロス集計	A027
A027	サブクエリー	A028
A028	演算子による集計	A029
A029	複数表の結合	A031
A031	更新系SQL	A032
A032	レコードを1件挿入する	A033
A033	サブクエリで複数レコードを挿入する	A034
A034	全レコード一律の更新を行う	A035
A035	特定行の更新を行う	A036
A036	特定レコードの削除を行う	A037
A037	トランザクション	A041
A041	データベースの作成	A042
A042	データベースの作成／削除	A043
A043	制約条件	A044
A044	テーブルの作成／削除	A045
A045	テーブル情報の変更	A046
A046	インデックスの作成／削除	A047

Q2-3-1-2:前頁の結果に次のコンテンツコード(next_id)が示す次のコンテンツ名(c_title)を追加して取得せよ。

sql

SELECT
  cc.c_title title,
  cn.c_title next
FROM
  contents AS cc
INNER JOIN
  contents AS cn
ON
  cc.next_id = cn.c_id
ORDER BY
  cc.c_id
;

result

title	next
SQLについて	データの取得1
データの取得1	カラムの選択
カラムの選択	WHERE句の記述
WHERE句の記述	NULLと空白の扱い
NULLと空白の扱い	あいまい検索
あいまい検索	複数条件の連結（論理演算子）
複数条件の連結（論理演算子）	重複レコードの除去
重複レコードの除去	データの取得2
データの取得2	順位付け
順位付け	ソート
ソート	グルーピング
グルーピング	グルーピング結果の絞り込み
グルーピング結果の絞り込み	クロス集計
クロス集計	サブクエリー
サブクエリー	演算子による集計
演算子による集計	複数表の結合
複数表の結合	更新系SQL
更新系SQL	レコードを1件挿入する
レコードを1件挿入する	サブクエリで複数レコードを挿入する
サブクエリで複数レコードを挿入する	全レコード一律の更新を行う
全レコード一律の更新を行う	特定行の更新を行う
特定行の更新を行う	特定レコードの削除を行う
特定レコードの削除を行う	トランザクション
トランザクション	データベースの作成
データベースの作成	データベースの作成/削除
データベースの作成/削除	制約条件
制約条件	テーブルの作成/削除
テーブルの作成/削除	テーブル情報の変更
テーブル情報の変更	インデックスの作成/削除

(29 行)

sql

SELECT
  cc.c_title title,
  cn.c_title next
FROM
  contents AS cc
LEFT JOIN
  contents AS cn
ON
  cc.next_id = cn.c_id
ORDER BY
  cc.c_id
;

result

title	next
SQLについて	データの取得1
データの取得1	カラムの選択
カラムの選択	WHERE句の記述
WHERE句の記述	NULLと空白の扱い
NULLと空白の扱い	あいまい検索
あいまい検索	複数条件の連結（論理演算子）
複数条件の連結（論理演算子）	重複レコードの除去
重複レコードの除去	データの取得2
データの取得2	順位付け
順位付け	ソート
ソート	グルーピング
グルーピング	グルーピング結果の絞り込み
グルーピング結果の絞り込み	クロス集計
クロス集計	サブクエリー
サブクエリー	演算子による集計
演算子による集計	複数表の結合
複数表の結合	更新系SQL
更新系SQL	レコードを1件挿入する
レコードを1件挿入する	サブクエリで複数レコードを挿入する
サブクエリで複数レコードを挿入する	全レコード一律の更新を行う
全レコード一律の更新を行う	特定行の更新を行う
特定行の更新を行う	特定レコードの削除を行う
特定レコードの削除を行う	トランザクション
トランザクション	データベースの作成
データベースの作成	データベースの作成/削除
データベースの作成/削除	制約条件
制約条件	テーブルの作成/削除
テーブルの作成/削除	テーブル情報の変更
テーブル情報の変更	インデックスの作成/削除
インデックスの作成/削除	NULL

(30 行)

sql

SELECT
  cc.c_title title,
  cn.c_title next
FROM
  contents AS cc
RIGHT JOIN
  contents AS cn
ON
  cc.next_id = cn.c_id
ORDER BY
  cc.c_id
;

result

title	next
SQLについて	データの取得1
データの取得1	カラムの選択
カラムの選択	WHERE句の記述
WHERE句の記述	NULLと空白の扱い
NULLと空白の扱い	あいまい検索
あいまい検索	複数条件の連結（論理演算子）
複数条件の連結（論理演算子）	重複レコードの除去
重複レコードの除去	データの取得2
データの取得2	順位付け
順位付け	ソート
ソート	グルーピング
グルーピング	グルーピング結果の絞り込み
グルーピング結果の絞り込み	クロス集計
クロス集計	サブクエリー
サブクエリー	演算子による集計
演算子による集計	複数表の結合
複数表の結合	更新系SQL
更新系SQL	レコードを1件挿入する
レコードを1件挿入する	サブクエリで複数レコードを挿入する
サブクエリで複数レコードを挿入する	全レコード一律の更新を行う
全レコード一律の更新を行う	特定行の更新を行う
特定行の更新を行う	特定レコードの削除を行う
特定レコードの削除を行う	トランザクション
トランザクション	データベースの作成
データベースの作成	データベースの作成／削除
データベースの作成／削除	制約条件
制約条件	テーブルの作成／削除
テーブルの作成／削除	テーブル情報の変更
テーブル情報の変更	インデックスの作成／削除
“NULL”	SQLについて

(30 行)

sql

SELECT
  cc.c_title title,
  cn.c_title next
FROM
  contents AS cc
FULL JOIN
  contents AS cn
ON
  cc.next_id = cn.c_id
ORDER BY
  cc.c_id
;

result

title	next
SQLについて	データの取得1
データの取得1	カラムの選択
カラムの選択	WHERE句の記述
WHERE句の記述	NULLと空白の扱い
NULLと空白の扱い	あいまい検索
あいまい検索	複数条件の連結（論理演算子）
複数条件の連結（論理演算子）	重複レコードの除去
重複レコードの除去	データの取得2
データの取得2	順位付け
順位付け	ソート
ソート	グルーピング
グルーピング	グルーピング結果の絞り込み
グルーピング結果の絞り込み	クロス集計
クロス集計	サブクエリー
サブクエリー	演算子による集計
演算子による集計	複数表の結合
複数表の結合	更新系SQL
更新系SQL	レコードを1件挿入する
レコードを1件挿入する	サブクエリで複数レコードを挿入する
サブクエリで複数レコードを挿入する	全レコード一律の更新を行う
全レコード一律の更新を行う	特定行の更新を行う
特定行の更新を行う	特定レコードの削除を行う
特定レコードの削除を行う	トランザクション
トランザクション	データベースの作成
データベースの作成	データベースの作成／削除
データベースの作成／削除	制約条件
制約条件	テーブルの作成／削除
テーブルの作成／削除	テーブル情報の変更
テーブル情報の変更	インデックスの作成／削除
インデックスの作成／削除	“NULL”
“NULL”	SQLについて

(31 行)

schema

Q2-3-3:メニューテーブル(menu)からそれぞれのメニュー名(title)と対応する親メニュー名(title)をページID(page_id)の昇順で取得するSQL文を完成せよ。

sql

SELECT
  mc.title AS メニュー名
FROM
  menu AS mc
ORDER BY
  mc.page_id
;

result

メニュー名
ホーム
WINGSについて
WINGSの歴史
WINGSメンバー紹介
WINGSメンバ募集
技術記事オンライン公開
最新インターネット記事
PHP関連記事
Java関連記事
ASP.NET関連記事
新刊案内
PHP関連新刊
Java関連新刊
ASP.NET関連新刊

(14 rows)

sql

SELECT
  mc.title AS メニュー名,
  mn.title AS 親メニュー名
FROM
  menu AS mc
INNER JOIN
  menu AS mn
ON
  mc.parent = mn.page_id
ORDER BY
  mc.page_id
;

result

メニュー名	親メニュー名
WINGSについて	ホーム
WINGSの歴史	WINGSについて
WINGSメンバー紹介	WINGSについて
WINGSメンバ募集	WINGSについて
技術記事オンライン公開	ホーム
最新インターネット記事	技術記事オンライン公開
PHP関連記事	技術記事オンライン公開
Java関連記事	技術記事オンライン公開
ASP.NET関連記事	技術記事オンライン公開
新刊案内	ホーム
PHP関連新刊	新刊案内
Java関連新刊	新刊案内
ASP.NET関連新刊	新刊案内

(13 rows)

sql

SELECT
 *
FROM
 menu p
FULL JOIN
 menu n
ON
 p.parent=n.page_id
;

result

p.page_id	p.title	p.parent	n.page_id	n.title	n.parent
A0001	ホーム	“NULL”	“NULL”	“NULL”	“NULL”
B0001	WINGSについて	A0001	A0001	ホーム	“NULL”
B0002	WINGSの歴史	B0001	B0001	WINGSについて	A0001
B0003	WINGSメンバー紹介	B0001	B0001	WINGSについて	A0001
B0004	WINGSメンバ募集	B0001	B0001	WINGSについて	A0001
C0001	技術記事オンライン公開	A0001	A0001	ホーム	“NULL”
C0002	最新インターネット記事	C0001	C0001	技術記事オンライン公開	A0001
C0003	PHP関連記事	C0001	C0001	技術記事オンライン公開	A0001
C0004	Java関連記事	C0001	C0001	技術記事オンライン公開	A0001
C0005	ASP.NET関連記事	C0001	C0001	技術記事オンライン公開	A0001
D0001	新刊案内	A0001	A0001	ホーム	“NULL”
D0002	PHP関連新刊	D0001	D0001	新刊案内	A0001
D0003	Java関連新刊	D0001	D0001	新刊案内	A0001
D0004	ASP.NET関連新刊	D0001	D0001	新刊案内	A0001
“NULL”	“NULL”	“NULL”	B0002	WINGSの歴史	B0001
“NULL”	“NULL”	“NULL”	B0004	WINGSメンバ募集	B0001
“NULL”	“NULL”	“NULL”	C0003	PHP関連記事	C0001
“NULL”	“NULL”	“NULL”	D0004	ASP.NET関連新刊	D0001
“NULL”	“NULL”	“NULL”	D0003	Java関連新刊	D0001
“NULL”	“NULL”	“NULL”	C0005	ASP.NET関連記事	C0001
“NULL”	“NULL”	“NULL”	D0002	PHP関連新刊	D0001
“NULL”	“NULL”	“NULL”	B0003	WINGSメンバー紹介	B0001
“NULL”	“NULL”	“NULL”	C0004	Java関連記事	C0001
“NULL”	“NULL”	“NULL”	C0002	最新インターネット記事	C0001

(24 rows)

schema

Q2-3-4: 社員テーブル(employee)からそれぞれ社員氏名(l_nameとf_name)と上司氏名(b_idが上司のs_id)、社員コード(s_id)の昇順で取得せよ。上司のいない社員についても情報を取り出せ。

sql

SELECT
  s.l_name||s.f_name AS 社員氏名
FROM
  employee AS s
ORDER BY
  s.s_id ASC
;

result

社員氏名
相沢聡
大門一郎
藤井雄太
藤岡幸太郎
速水和幸
葉山俊輔
川口裕子
加藤昭雄
神田佐知子
木村一郎
中澤康代
西雄一
新渡戸康治
野上利江
佐藤智子
妹尾春樹
清水春子
田中真由子
田辺正一
寺岡陽一
近田晃子
遠山真一
戸川よしみ
津村知美
上田一也
山田奈美

(26 rows)

SQL

SELECT
  s.l_name||s.f_name AS 社員氏名,
  b.l_name||b.f_name AS 上司氏名
FROM
  employee AS s
LEFT JOIN
  employee AS b
ON
  s.b_id = b.s_id
ORDER BY
  s.s_id ASC
;

result

社員氏名	上司氏名
相沢聡	“NULL”
大門一郎	相沢聡
藤井雄太	妹尾春樹
藤岡幸太郎	藤井雄太
速水和幸	“NULL”
葉山俊輔	藤岡幸太郎
川口裕子	“NULL”
加藤昭雄	“NULL”
神田佐知子	神田佐知子
木村一郎	田中真由子
中澤康代	戸川よしみ
西雄一	“NULL”
新渡戸康治	“NULL”
野上利江	戸川よしみ
佐藤智子	“NULL”
妹尾春樹	寺岡陽一
清水春子	清水春子
田中真由子	山田奈美
田辺正一	葉山俊輔
寺岡陽一	速水和幸
近田晃子	相沢聡
遠山真一	上田一也
戸川よしみ	津村知美
津村知美	近田晃子
上田一也	“NULL”
山田奈美	佐藤智子

26 rows

schema

３つ以上の結合(JOIN句の入れ子)

ここまでは、2つのテーブルの結合を学習してきましたが、ＲＤＢでは正規化されているため、3つ以上の結合が必要となります。
このような結合を実施するためには、結合した結果と、さらに結合する書き方（入れ子）を行います。

Q2-4-1: 書籍情報テーブル(books)と、著者情報テーブル(author_books)、著者テーブル(author)を結合して出版社(publish)が「山田出版」である、刊行日の新しい順で書名(title)、著者名(name)と刊行日(publish_date)を取得せよ。

sql

SELECT
 b.title,
 b.publish_date
FROM
 books AS b
;

result

title	publish_date
PEAR入門	2012-09-08
PHP5サンプル集	2012-11-01
SQLリファレンス	2013-02-15
XML辞典	2011-09-16
JSPリファレンス	2010-04-19
SQLプチブック	2010-11-30
XMLリファレンス	2012-11-24
ハムスターの観察	2010-11-01
フェレットの観察	2012-10-26
SQL入門	2012-10-30
PHPドリル	2013-01-14
らくだの観察日記	2012-12-24
あひるの観察日記	2012-11-15
かえるの観察日記	2013-01-15

(14 rows)

sql

SELECT
 b.title,
 b.publish_date
FROM
 books AS b
INNER JOIN
 author_books AS ab
ON
 b.isbn = ab.isbn
;

result

title	publish_date
ハムスターの観察	2010-11-01
PEAR入門	2012-09-08
PHP5サンプル集	2012-11-01
SQLリファレンス	2013-02-15
フェレットの観察	2012-10-26
XML辞典	2011-09-16
JSPリファレンス	2010-04-19
SQLプチブック	2010-11-30
XMLリファレンス	2012-11-24
SQL入門	2012-10-30
PHPドリル	2013-01-14

(11 rows)

sql

SELECT
  b.title,
  a.name,
  b.publish_date
FROM
  books AS b
INNER JOIN
  author_books AS ab
ON
  b.isbn = ab.isbn
INNER JOIN
  author AS a
ON
  ab.author_id=a.author_id
;

result

title	name	publish_date
ハムスターの観察	山田愛子	2010-11-01
PEAR入門	山田祥寛	2012-09-08
PHP5サンプル集	山田祥寛	2012-11-01
SQLリファレンス	佐藤一郎	2013-02-15
フェレットの観察	山田愛子	2012-10-26
XML辞典	山田祥寛	2011-09-16
JSPリファレンス	山田祥寛	2010-04-19
SQLプチブック	川中智樹	2010-11-30
XMLリファレンス	田中太郎	2012-11-24
SQL入門	守口靖男	2012-10-30
PHPドリル	有吉晴美	2013-01-14

(11 rows)

SQL

SELECT
  b.title,
  a.name,
  b.publish_date
FROM
  books AS b
INNER JOIN
  author_books AS ab
ON
  b.isbn = ab.isbn
INNER JOIN
  author a
ON
  ab.author_id = a.author_id
WHERE
  b.publish = '山田出版'
ORDER BY
  b.publish_date DESC
;

SQL

select
 b.title,
 a.name,
 b.publish_date
from
 books b,
 author_books ab,
 author a
where
 b.isbn=ab.isbn
and
 ab.author_id=a.author_id
and
 b.publish = '山田出版'
ORDER BY
  b.publish_date DESC
;

result

title	name	publish_date
PHPドリル	有吉晴美	2013-01-14
SQL入門	守口靖男	2012-10-30
フェレットの観察	山田愛子	2012-10-26
ハムスターの観察	山田愛子	2010-11-01

4 rows

schema

Q2-4-2: 社員テーブル(employee)と所属部署テーブル(depart)、タイムカードテーブル(time_card)を結合し、社員コード(s_id)’DA00001’における2012年12月分の「所属部署名」「社員氏名」「勤務時間」を日付の昇順で取得するSQL文を完成させよ。

sql

SELECT
  d.depart_name AS 所属部署名
FROM
  depart AS d
;

result

所属部署名
経営企画部
第一営業部
第二営業部
人事部
総務部

(5 rows)

sql

SELECT
 d.depart_name AS 所属部署名,
 e.l_name||e.f_name AS 社員氏名
FROM
 depart AS d
INNER JOIN
 employee AS e
ON
 d.depart_id = e.depart_id
WHERE
 e.s_id = 'DA00001'
;

result

所属部署名	社員氏名
人事部	大門一郎

(1 row)

SQL

SELECT
  d.depart_name AS 所属部署名,
  e.l_name||e.f_name AS 社員氏名,
  t.work_time AS 勤務時間
FROM
  depart AS d
INNER JOIN
  employee AS e
ON
  d.depart_id = e.depart_id
INNER JOIN
  time_card AS t
ON
  e.s_id = t.s_id
WHERE
  e.s_id = 'DA00001'
AND
  t.r_date
BETWEEN 
 '2012/12/01' AND '2012/12/31'
ORDER BY
  t.r_date
;

SQL

SELECT
  d.depart_name AS 所属部署名,
  e.l_name || e.f_name AS 社員氏名,
  t.work_time AS 勤務時間
FROM
  depart d,
  employee AS e,
  time_card t
WHERE
  d.depart_id = e.depart_id
AND
  e.s_id = 'DA00001'
AND
  e.s_id = t.s_id
AND
  t.r_date
BETWEEN
 '2012/12/01' AND '2012/12/31'
ORDER BY
  t.r_date
;

result

所属部署名	社員氏名	勤務時間
人事部	大門一郎	8
人事部	大門一郎	10
人事部	大門一郎	8
人事部	大門一郎	9
人事部	大門一郎	8
人事部	大門一郎	8
人事部	大門一郎	9
人事部	大門一郎	8
人事部	大門一郎	10
人事部	大門一郎	9
人事部	大門一郎	8
人事部	大門一郎	8
人事部	大門一郎	8
人事部	大門一郎	8
人事部	大門一郎	9
人事部	大門一郎	8
人事部	大門一郎	10
人事部	大門一郎	8
人事部	大門一郎	8
人事部	大門一郎	8

20 rows

schema

Q2-4-3:書籍目次テーブル(contents)から前後のコンテンツを「前コンテンツ」「現コンテンツ」「次コンテンツ」として現ページのコンテンツコードの昇順で取り出すSQL文を完成させよ。

sql

SELECT
  cc.c_title AS 現コンテンツ
FROM
 contents cc
ORDER BY
 cc.c_id;

result

現コンテンツ
SQLについて
データの取得1
カラムの選択
WHERE句の記述
NULLと空白の扱い
あいまい検索
複数条件の連結（論理演算子）
重複レコードの除去
データの取得2
順位付け
ソート
グルーピング
グルーピング結果の絞り込み
クロス集計
サブクエリー
演算子による集計
複数表の結合
更新系SQL
レコードを1件挿入する
サブクエリで複数レコードを挿入する
全レコード一律の更新を行う
特定行の更新を行う
特定レコードの削除を行う
トランザクション
データベースの作成
データベースの作成／削除
制約条件
テーブルの作成／削除
テーブル情報の変更
インデックスの作成／削除

(30 rows)

sql

SELECT
  cp.c_title AS 前コンテンツ,
  cc.c_title AS 現コンテンツ
FROM
 contents cp
INNER JOIN
 contents cc
ON
 cc.c_id = cp.next_id
ORDER BY
 cc.c_id;

result

前コンテンツ	現コンテンツ
SQLについて	データの取得1
データの取得1	カラムの選択
カラムの選択	WHERE句の記述
WHERE句の記述	NULLと空白の扱い
NULLと空白の扱い	あいまい検索
あいまい検索	複数条件の連結（論理演算子）
複数条件の連結（論理演算子）	重複レコードの除去
重複レコードの除去	データの取得2
データの取得2	順位付け
順位付け	ソート
ソート	グルーピング
グルーピング	グルーピング結果の絞り込み
グルーピング結果の絞り込み	クロス集計
クロス集計	サブクエリー
サブクエリー	演算子による集計
演算子による集計	複数表の結合
複数表の結合	更新系SQL
更新系SQL	レコードを1件挿入する
レコードを1件挿入する	サブクエリで複数レコードを挿入する
サブクエリで複数レコードを挿入する	全レコード一律の更新を行う
全レコード一律の更新を行う	特定行の更新を行う
特定行の更新を行う	特定レコードの削除を行う
特定レコードの削除を行う	トランザクション
トランザクション	データベースの作成
データベースの作成	データベースの作成／削除
データベースの作成／削除	制約条件
制約条件	テーブルの作成／削除
テーブルの作成／削除	テーブル情報の変更
テーブル情報の変更	インデックスの作成／削除

(29 rows)

SQL

SELECT
  cp.c_title AS 前コンテンツ,
  cc.c_title AS 現コンテンツ,
  cn.c_title AS 次コンテンツ
FROM
 contents cp
INNER JOIN
 contents cc
ON
 cc.c_id = cp.next_id
INNER JOIN
 contents cn
ON
 cc.next_id = cn.c_id
ORDER BY
 cc.c_id;

SQL

SELECT
  cp.c_title AS 前コンテンツ,
  cc.c_title AS 現コンテンツ,
  cn.c_title AS 次コンテンツ
FROM
 contents cp,
 contents cc,
 contents cn
WHERE
 cc.c_id = cp.next_id
AND
 cc.next_id = cn.c_id
ORDER BY
 cc.c_id;

result

前コンテンツ	現コンテンツ	次コンテンツ
SQLについて	データの取得1	カラムの選択
データの取得1	カラムの選択	WHERE句の記述
カラムの選択	WHERE句の記述	NULLと空白の扱い
WHERE句の記述	NULLと空白の扱い	あいまい検索
NULLと空白の扱い	あいまい検索	複数条件の連結（論理演算子）
あいまい検索	複数条件の連結（論理演算子）	重複レコードの除去
複数条件の連結（論理演算子）	重複レコードの除去	データの取得2
重複レコードの除去	データの取得2	順位付け
データの取得2	順位付け	ソート
順位付け	ソート	グルーピング
ソート	グルーピング	グルーピング結果の絞り込み
グルーピング	グルーピング結果の絞り込み	クロス集計
グルーピング結果の絞り込み	クロス集計	サブクエリー
クロス集計	サブクエリー	演算子による集計
サブクエリー	演算子による集計	複数表の結合
演算子による集計	複数表の結合	更新系SQL
複数表の結合	更新系SQL	レコードを1件挿入する
更新系SQL	レコードを1件挿入する	サブクエリで複数レコードを挿入する
レコードを1件挿入する	サブクエリで複数レコードを挿入する	全レコード一律の更新を行う
サブクエリで複数レコードを挿入する	全レコード一律の更新を行う	特定行の更新を行う
全レコード一律の更新を行う	特定行の更新を行う	特定レコードの削除を行う
特定行の更新を行う	特定レコードの削除を行う	トランザクション
特定レコードの削除を行う	トランザクション	データベースの作成
トランザクション	データベースの作成	データベースの作成／削除
データベースの作成	データベースの作成／削除	制約条件
データベースの作成／削除	制約条件	テーブルの作成／削除
制約条件	テーブルの作成／削除	テーブル情報の変更
テーブルの作成／削除	テーブル情報の変更	インデックスの作成／削除

28 rows

schema

Q2-4-4:注文書テーブル(order_main)と注文明細テーブル(order_desc)、ユーザテーブル(usr)、商品テーブル(product)を結合し、未納の注文について、発注日、注文コード、利用者氏名、商品名、商品単価、購入数を発注日、注文コード、商品コードの昇順で取り出せ。

sql

SELECT
  om.order_date AS 発注日,
  om.po_id AS 注文コード
FROM
 order_main om
WHERE
 om.delivery_date IS NULL
ORDER BY
 om.order_date,
 om.po_id
;

result

発注日	注文コード
2012-12-22	19
2012-12-25	20

(2 rows)

sql

--JOIN
SELECT
  om.order_date AS 発注日,
  om.po_id AS 注文コード,
  u.l_name || u.f_name AS 利用者氏名
FROM
 order_main om
INNER JOIN
 usr u
ON
 om.user_id = u.user_id
WHERE
 om.delivery_date IS NULL
ORDER BY
 om.order_date,
 om.po_id
;

--WHERE
SELECT
  om.order_date AS 発注日,
  om.po_id AS 注文コード,
  u.l_name || u.f_name AS 利用者氏名
FROM
 order_main om,
 usr u
WHERE
 om.delivery_date IS NULL
AND
 om.user_id = u.user_id
ORDER BY
 om.order_date,
 om.po_id
;

result

発注日	注文コード	利用者氏名
2012-12-22	19	森本絵里
2012-12-25	20	西島文子

(2 rows)

sql

--JOIN
SELECT
  om.order_date AS 発注日,
  om.po_id AS 注文コード,
  u.l_name || u.f_name AS 利用者氏名,
  od.quantity AS 購入数
FROM
 order_main om
INNER JOIN
 usr u
ON
 om.user_id = u.user_id
INNER JOIN
 order_desc od
ON
 om.po_id = od.po_id
WHERE
 om.delivery_date IS NULL
ORDER BY
 om.order_date,
 om.po_id
;

--WHERE
SELECT
  om.order_date AS 発注日,
  om.po_id AS 注文コード,
  u.l_name || u.f_name AS 利用者氏名,
  od.quantity AS 購入数
FROM
 order_main om,
 usr u,
 order_desc od
WHERE
 om.delivery_date IS NULL
AND
 om.user_id = u.user_id
AND
 om.po_id = od.po_id
ORDER BY
 om.order_date,
 om.po_id
;

result

発注日	注文コード	利用者氏名	購入数
2012-12-22	19	森本絵里	100
2012-12-25	20	西島文子	10

(2 rows)

sql

--JOIN
SELECT
  om.order_date AS 発注日,
  om.po_id AS 注文コード,
  u.l_name || u.f_name AS 利用者氏名,
  p.p_name AS 商品名,
  p.price AS 商品単価,
  od.quantity AS 購入数
FROM
 order_main om
INNER JOIN
 usr u
ON
 om.user_id = u.user_id
INNER JOIN
 order_desc od
ON
 om.po_id = od.po_id
INNER JOIN
 product p
ON
 od.p_id = p.p_id
WHERE
 om.delivery_date IS NULL
ORDER BY
 om.order_date,
 om.po_id,
 p.p_id;

--WHERE
SELECT
  om.order_date AS 発注日,
  om.po_id AS 注文コード,
  u.l_name || u.f_name AS 利用者氏名,
  p.p_name AS 商品名,
  p.price AS 商品単価,
  od.quantity AS 購入数
FROM
 order_main om,
 usr u,
 order_desc od,
 product p
WHERE
 om.delivery_date IS NULL
AND
 om.user_id = u.user_id
AND
 om.po_id = od.po_id
AND
 od.p_id = p.p_id
ORDER BY
 om.order_date,
 om.po_id,
 p.p_id;

result

発注日	注文コード	利用者氏名	商品名	商品単価	購入数
2012-12-22	19	森本絵里	赤ボールペン	100	100
2012-12-25	20	西島文子	電卓	600	10

2 rows

schema

副問い合わせ/サブクエリ（SELECT文の入れ子）

ここまでは、テーブルのカラムを結合する方法を学習してきましたが、検索結果をもとにさらに検索しなくてはならないことがあります。
それを実現するのが、サブクエリと言います。

EXISTS

EXISTS 演算子は、サブクエリ内のレコードの存在をテストするために使用されます。

サブクエリが 1 つ以上のレコードを返す場合、EXISTS 演算子は TRUE を返します。

sql

SELECT
WHERE
 EXISTS (
  SELECT
  FROM
   shop
)
;

result

bool
TRUE
(1 row)

sql

SELECT
WHERE
 NOT EXISTS (
  SELECT
  FROM
   shop
)
;

result

bool
FALSE
(0 rows)

Q2-5-1月間売上テーブル(sales)にて2012年12月分(2012-12)の売り上げが登録されていない店舗の店舗コード(s_id)と店舗名(s_name)を取得せよ。

salesにありshopに登録されていない

sql

SELECT
 *
FROM
 shop s
FULL JOIN
 sales l
ON
 s.s_id=l.s_id
ORDER BY
 l.s_id

result

s_id	s_name	s_id	s_date	s_value
H0001	東桜町店	H0001	2012-11	9975
H0001	東桜町店	H0001	2012-12	9861
K0001	北花町駅前店	K0001	2012-11	10142
K0001	北花町駅前店	K0001	2012-12	11234
K0002	北花町東店	K0002	2012-11	10124
K0002	北花町東店	K0002	2012-12	10456
M0001	三吉町店	M0001	2012-11	10784
M0001	三吉町店	M0001	2012-12	11145
M0002	三吉町南店	M0002	2012-11	12032
“NULL”	“NULL”	N0001	2012-11	9965
“NULL”	“NULL”	N0001	2012-12	11023
(11 rows)

shopをつなげて登録されていないを探す

sql

SELECT
 *
FROM
 shop s
WHERE NOT EXISTS
(
  SELECT
  FROM
   sales l
  WHERE
   s.s_id=l.s_id
  AND
   s_date~'-12'
)
;

result

s_id	s_name
M0002	三吉町南店
(1 row)

shopのs_idがsubquery結果にないもの

sql

SELECT
 *
FROM
 shop
WHERE s_id NOT IN
(
  SELECT
   s_id
  FROM
   sales
  WHERE
   s_date~'-12'
)
;

result

s_id	s_name
M0002	三吉町南店
(1 row)

■ 2. NULL値の取り扱いの違い

NOT EXISTS と NOT IN は意味が似ていますが、NULL の取り扱いにおいて重要な違いがあります。

・NOT EXISTS：　　– サブクエリでNULLが含まれていても問題ありません。
– 対応する行がひとつも見つからなければ、NOT EXISTSはTRUEになります。

・NOT IN：　　– サブクエリから返される結果にNULLが含まれている場合、比較の結果が不定（UNKNOWN）となる可能性があります。
– そのため、サブクエリの結果にNULLが含まれると、意図しない結果（全行が除外されるなど）になる恐れがあります。
– 安全に利用するには、サブクエリ内でNULLを除外する必要があります（例えば、WHERE s_id IS NOT NULL を追加するなど）。

■ 3. パフォーマンスや最適化の違い

また、データベースエンジンによっては、NOT EXISTS と NOT IN の実行計画やパフォーマンスが異なることがあります。
・NOT EXISTS は相関サブクエリとして処理され、インデックスが利用される場合、効率的に評価されます。
・NOT IN もインデックスを利用できるケースはありますが、前述のように結果にNULLが含まれるとパフォーマンスや正しい結果に影響が出ることがあります。

確認のためs_idにnullを含める

sql

SELECT
  a.s_id
FROM
  shop a
RIGHT JOIN
  sales b
ON
  a.s_id = b.s_id
WHERE
  s_date ~ '-12'
;

result

s_id
M0001
H0001
“NULL”
K0001
K0002
(5 rows)

not inでa.s_idにnullがある場合

sql

SELECT
 *
FROM
 shop
WHERE
 s_id NOT IN (
  SELECT
   a.s_id
  FROM
   shop a
  RIGHT JOIN
   sales b
  ON
   a.s_id = b.s_id
  WHERE
   s_date ~ '-12'
 );

result

s_id	s_name

(0 rows)

a.s_idにnullがあるけどwhere is not nullした場合

sql

SELECT
 *
FROM
 shop
WHERE
 s_id NOT IN (
  SELECT
   a.s_id
  FROM
   shop a
  RIGHT JOIN
   sales b
  ON
   a.s_id = b.s_id
  WHERE
   s_date ~ '-12'
  AND
   a.s_id IS NOT NULL
 );

result

s_id	s_name
M0002	三吉町南店
(1 rows)

schema

Q2-5-2:アンケート回答テーブル(quest)から回答者平均年齢を超えている人の回答(answer1、answer2列)を評価の低い順に取得せよ。

回答者のデータ

sql

SELECT
 answer1,
 answer2,
 age
FROM
 quest
ORDER BY
 answer1 ASC;

result

answer1	answer2	age
1	“NULL”	41
1	“NULL”	19
1	絵が少ない。	22
1	ちょっと難しいです。	26
2	“NULL”	26
2	面白いです。	25
2	“NULL”	56
2		36
2	わかりやすいです。	40
3	絵がかわいい。	34
3	次回作に期待しています。	32
3	買ってよかった。	35
3	“NULL”	18
3	文字が小さい。	64
3	読みやすいです。	24
3	重宝しています。	30
(16 rows)

回答者テーブルを軸にしてサブクエリで回答者平均を参照する

sql

SELECT
 answer1,
 answer2,
 age
FROM
 quest
WHERE
 age > (
  SELECT
   AVG(age)
  FROM
   quest
 )
ORDER BY
 answer1 ASC;

result

answer1	answer2	age
1	“NULL”	41
2	“NULL”	56
2	わかりやすいです。	40
2		36
3	絵がかわいい。	34
3	買ってよかった。	35
3	文字が小さい。	64
(7 rows)

Q2-5-3:所属部署テーブル(depart)から社員テーブル(employee)で使われていない部署コード部署名を取り出すSQL文を完成させよ。

外部結合の場合で検出

sql

SELECT
 d.depart_id,
 d.depart_name,
 e.depart_id
FROM
 depart d
LEFT JOIN
 employee e
ON
 d.depart_id = e.depart_id;

result

depart_id	depart_name	depart_id
S02	総務部	S02
J01	人事部	J01
E01	第一営業部	E01
E01	第一営業部	E01
E01	第一営業部	E01
E01	第一営業部	E01
S01	経営企画部	S01
S01	経営企画部	S01
J01	人事部	J01
S01	経営企画部	S01
S02	総務部	S02
S02	総務部	S02
S01	経営企画部	S01
E01	第一営業部	E01
J01	人事部	J01
S01	経営企画部	S01
E01	第一営業部	E01
E01	第一営業部	E01
S02	総務部	S02
J01	人事部	J01
S02	総務部	S02
S02	総務部	S02
J01	人事部	J01
S01	経営企画部	S01
E02	第二営業部	“NULL”
(25 rows)

whereで絞る

sql

SELECT
 d.depart_id,
 d.depart_name,
 e.depart_id
FROM
 depart d
LEFT JOIN
 employee e
ON
 d.depart_id = e.depart_id
WHERE
 e.depart_id IS NULL;

result

depart_id	depart_name	depart_id
E02	第二営業部	“NULL”
(1 row)

サブクエリの内側はinner joinのようになるため

両テーブルのdepart_idが一致しないを指定する

sql

SELECT
 depart_id,
 depart_name
FROM
 depart AS d
WHERE
 NOT EXISTS (
  SELECT
  FROM
   employee AS e
  WHERE
   e.depart_id = d.depart_id
 );

result

depart_id	depart_name
E02	第二営業部
(1 row)

schema

Q2-5-4:店舗テーブル(shop)と月間売り上げテーブル2012年12月の売上高が店舗ごとの売り上げ平均(全ての月総平均)を下回っている店舗名とその売上高を取得せよ。

店舗名の確認

sql

SELECT
 a.s_name
FROM
 shop a;

result

s_name
三吉町店
三吉町南店
東桜町店
北花町駅前店
北花町東店
(5 rows)

12月の店舗と売り上げ

sql

SELECT
 a.s_name,
 b.s_value
FROM
 shop AS a
INNER JOIN
 sales AS b
ON
 a.s_id = b.s_id
WHERE
 b.s_date = '2012-12';

result

s_name	s_value
三吉町店	11145
東桜町店	9861
北花町駅前店	11234
北花町東店	10456
(4 rows)

全ての店舗ごとの総平均

sql

SELECT
 c.s_id,
 AVG(c.s_value)
FROM
 sales c
GROUP BY
 c.s_id;

result

s_id	avg
K0002	10290.0000000000000000
N0001	10494.0000000000000000
M0002	12032.0000000000000000
M0001	10964.5000000000000000
K0001	10688.0000000000000000
H0001	9918.0000000000000000
(6 rows)

総平均を下回っている

sql

SELECT
 a.s_name,
 b.s_value
FROM
 shop AS a
INNER JOIN
 sales AS b
ON
 a.s_id = b.s_id
WHERE
 b.s_date = '2012-12'
AND
 b.s_value < (
  SELECT
   AVG(c.s_value)
  FROM
   sales AS c
  WHERE
   b.s_id = c.s_id
  GROUP BY
   c.s_id
);

result

s_name	s_value
東桜町店	9861
(1 row)

Q2-5-5:貸し出し記録テーブル(rental)を検索し、これまで一度も書籍の貸し出しを行ったことのないユーザの氏名をユーザテーブル(usr)から取得せよ

usr 氏名

sql

SELECT
 u.l_name || u.f_name AS name
FROM
 usr AS u;

result

name
井上一郎
上原幸一
江本聡
小野雄二
河合泰治
矢口亜由美
有木茉莉
吉岡遥
和田修子
松本博美
山田太郎
木下一樹
久保田守
児玉毅
坂口駿
井上一郎
南沙恵
村井佐知子
森本絵里
西島文子
根岸裕子
(21 rows)

usrにありrentalにない名前

sql

SELECT
 u.l_name || u.f_name AS name,
 r.user_id
FROM
 usr AS u
LEFT JOIN
 rental r
ON
 u.user_id = r.user_id;

result

name	user_id
山田太郎	A200501
江本聡	A200403
河合泰治	A200405
吉岡遥	B200403
松本博美	B200405
木下一樹	A200502
坂口駿	A200505
南沙恵	B200501
森本絵里	B200503
西島文子	B200504
井上一郎	A200401
上原幸一	A200402
小野雄二	A200404
河合泰治	A200405
有木茉莉	B200402
和田修子	B200404
木下一樹	A200502
井上一郎	A200506
南沙恵	B200501
森本絵里	B200503
西島文子	B200504
根岸裕子	B200505
河合泰治	A200405
吉岡遥	B200403
松本博美	B200405
矢口亜由美	“NULL”
村井佐知子	“NULL”
児玉毅	“NULL”
久保田守	“NULL”
(29 rows)

外部結合してrentalのuser_idがnullを指定する

sql

--Left join where
SELECT
 u.l_name || u.f_name AS name
FROM
 usr AS u
LEFT JOIN
 rental r
ON
 u.user_id = r.user_id
WHERE
 r.user_id IS NULL;

result

name
矢口亜由美
久保田守
児玉毅
村井佐知子
(4 rows)

usr user_idとrental user_idが一致する行がないをusrから取り出す

sql

--subquery not exists
SELECT
 u.l_name || u.f_name AS name
FROM
 usr AS u
WHERE
 NOT EXISTS (
  SELECT
   *
  FROM
   rental AS r
  WHERE
   r.user_id = u.user_id
 );

result

name
矢口亜由美
久保田守
児玉毅
村井佐知子
(4 rows)

usr user_idがrental user_idの中にあるか調べる

sql

--subquery not in
SELECT
 l_name || f_name AS name
FROM
 usr
WHERE
 user_id NOT IN (
  SELECT
   user_id
  FROM
   rental
 );

result

name
矢口亜由美
久保田守
児玉毅
村井佐知子
(4 rows)

schema

縦結合（UNION）

複数のSELECT文の結果を統合し、重複行を除いて全ての行を結果として返します。

重複保持: UNION ALL は、重複する行を含むすべての行を結合された結果セットに含めます。つまり、複数の SELECT ステートメントが全く同じ行を返した場合、UNION ALL はそれらの行をすべて残します。
ソートなし: UNION ALL は通常、結果セットをソートしません。結果の順序は、SELECT ステートメントの順序と、各テーブル内のデータの順序に依存します。
パフォーマンス: 重複排除の処理を行わないため、一般的に UNION ALL は UNION よりも高速です。

UNION (および UNION ALL) を使用する際の注意点

カラム数とデータ型の一致: UNION で結合する SELECT ステートメントは、同じ数のカラムを持ち、対応するカラムのデータ型が互換性を持っている必要があります。互換性がない場合、エラーが発生します。
カラム名の扱い: UNION 後の結果セットのカラム名は、最初の SELECT ステートメントのカラム名が使用されます。
ORDER BY の位置: 結果セット全体をソートする場合は、ORDER BY 句を最後の SELECT ステートメントの後ろに記述します。個々のSELECTの結果を並び替えたい場合は、それぞれのSELECTの中でORDER BYを利用します。(ただし、一部のデータベースシステムでは、サブクエリ内での ORDER BY が無視されることがあります。)
括弧の使用: 複雑なクエリを作成する場合、括弧を使用して SELECT ステートメントをグループ化し、UNION の適用順序を明確にすることができます。

Q2-6-5著者情報テーブル(author)とユーザテーブル(usr)からそれぞれ氏名の情報を重複を含まない氏名（カナ）の降順で取り出すＳＱＬ文の誤りを正せ。

sql

SELECT
 a.name_kana
FROM
 author AS a
ORDER BY
 1 DESC;

result

name_kana
ヤマダヨシヒロ
ヤマダアイコ
モリグチヤスオ
タナカタロウ
サトウイチロウ
カワナカトモキ
アリヨシハルミ
(7 rows)

sql

SELECT
 u.l_name_kana || u.f_name_kana name_kana
FROM
 usr AS u
ORDER BY
 1 DESC;

result

name_kana
ワダシュウコ
ヨシオカハルカ
ヤマダタロウ
ヤグチアユミ
モリモトエリ
ムライサチコ
ミナミサエ
マツモトヒロミ
ネギシユウコ
ニシジマフミコ
サカグチシュン
コダマツヨシ
クボタマモル
キノシタカズキ
カワイヤスハル
オノユウジ
エモトサトシ
ウエハラコウイチ
イノウエイチロウ
イノウエイチロウ
アリキマリ
(21 rows)

sql

SELECT
 a.name_kana
FROM
 author AS a
UNION
SELECT
 u.l_name_kana || u.f_name_kana
FROM
 usr AS u
ORDER BY
 1 DESC;

result

name_kana
ワダシュウコ
ヨシオカハルカ
ヤマダヨシヒロ
ヤマダタロウ
ヤマダアイコ
ヤグチアユミ
モリモトエリ
モリグチヤスオ
ムライサチコ
ミナミサエ
マツモトヒロミ
ネギシユウコ
ニシジマフミコ
タナカタロウ
サトウイチロウ
サカグチシュン
コダマツヨシ
クボタマモル
キノシタカズキ
カワナカトモキ
カワイヤスハル
オノユウジ
エモトサトシ
ウエハラコウイチ
イノウエイチロウ
アリヨシハルミ
アリキマリ
(27 rows)

Q2-6-1-3:ユーザテーブル(usr)と社員テーブル(employee)の検索結果を足した氏名カナ(l_name_kanaとf_name_kana)を取得せよ。

sql

SELECT
 u.l_name_kana,
 u.f_name_kana
FROM
 usr AS u
ORDER BY
 1,
 2;

result

l_name_kana	f_name_kana
アリキ	マリ
イノウエ	イチロウ
イノウエ	イチロウ
ウエハラ	コウイチ
エモト	サトシ
オノ	ユウジ
カワイ	ヤスハル
キノシタ	カズキ
クボタ	マモル
コダマ	ツヨシ
サカグチ	シュン
ニシジマ	フミコ
ネギシ	ユウコ
マツモト	ヒロミ
ミナミ	サエ
ムライ	サチコ
モリモト	エリ
ヤグチ	アユミ
ヤマダ	タロウ
ヨシオカ	ハルカ
ワダ	シュウコ
(21 rows)

sql

SELECT
 e.l_name_kana,
 e.f_name_kana
FROM
 employee AS e
ORDER BY
 1,
 2;

result

l_name_kana	f_name_kana
アイザワ	サトシ
ウエダ	カズヤ
カトウ	アキオ
カワグチ	ユウコ
カンダ	サチコ
キムラ	イチロウ
サトウ	トモコ
シミズ	ハルコ
セノオ	ハルキ
ダイモン	イチロウ
タナカ	マユコ
タナベ	ショウイチ
チカダ	アキコ
ツムラ	カズミ
テラオカ	ヨウイチ
トオヤマ	シンイチ
トガワ	ヨシミ
ナカザワ	ヤスヨ
ニシ	ユウイチ
ニトベ	コウジ
ノガミ	リエ
ハヤマ	シュンスケ
ハヤミ	カズユキ
フジイ	ユウタ
フジオカ	コウタロウ
ヤマダ	ナミ
(26 rows)

各テーブルをorder by重複あり

sql

(
 SELECT
  u.l_name_kana,
  u.f_name_kana
 FROM
  usr AS u
 ORDER BY
  1,
  2
)
UNION ALL
(
 SELECT
  e.l_name_kana,
  e.f_name_kana
 FROM
  employee AS e
 ORDER BY
  1,
  2
);

result

l_name_kana	f_name_kana
アリキ	マリ
イノウエ	イチロウ
イノウエ	イチロウ
ウエハラ	コウイチ
エモト	サトシ
オノ	ユウジ
カワイ	ヤスハル
キノシタ	カズキ
クボタ	マモル
コダマ	ツヨシ
サカグチ	シュン
ニシジマ	フミコ
ネギシ	ユウコ
マツモト	ヒロミ
ミナミ	サエ
ムライ	サチコ
モリモト	エリ
ヤグチ	アユミ
ヤマダ	タロウ
ヨシオカ	ハルカ
ワダ	シュウコ
アイザワ	サトシ
ウエダ	カズヤ
カトウ	アキオ
カワグチ	ユウコ
カンダ	サチコ
キムラ	イチロウ
サトウ	トモコ
シミズ	ハルコ
セノオ	ハルキ
ダイモン	イチロウ
タナカ	マユコ
タナベ	ショウイチ
チカダ	アキコ
ツムラ	カズミ
テラオカ	ヨウイチ
トオヤマ	シンイチ
トガワ	ヨシミ
ナカザワ	ヤスヨ
ニシ	ユウイチ
ニトベ	コウジ
ノガミ	リエ
ハヤマ	シュンスケ
ハヤミ	カズユキ
フジイ	ユウタ
フジオカ	コウタロウ
ヤマダ	ナミ
(47 rows)

employeeだけorder by重複あり

sql

(
 SELECT
  u.l_name_kana,
  u.f_name_kana
 FROM
  usr AS u
)
UNION ALL
(
 SELECT
  e.l_name_kana,
  e.f_name_kana
 FROM
  employee AS e
 ORDER BY
  1,
  2
)
;

result

l_name_kana	f_name_kana
イノウエ	イチロウ
ウエハラ	コウイチ
エモト	サトシ
オノ	ユウジ
カワイ	ヤスハル
ヤグチ	アユミ
アリキ	マリ
ヨシオカ	ハルカ
ワダ	シュウコ
マツモト	ヒロミ
ヤマダ	タロウ
キノシタ	カズキ
クボタ	マモル
コダマ	ツヨシ
サカグチ	シュン
イノウエ	イチロウ
ミナミ	サエ
ムライ	サチコ
モリモト	エリ
ニシジマ	フミコ
ネギシ	ユウコ
アイザワ	サトシ
ウエダ	カズヤ
カトウ	アキオ
カワグチ	ユウコ
カンダ	サチコ
キムラ	イチロウ
サトウ	トモコ
シミズ	ハルコ
セノオ	ハルキ
ダイモン	イチロウ
タナカ	マユコ
タナベ	ショウイチ
チカダ	アキコ
ツムラ	カズミ
テラオカ	ヨウイチ
トオヤマ	シンイチ
トガワ	ヨシミ
ナカザワ	ヤスヨ
ニシ	ユウイチ
ニトベ	コウジ
ノガミ	リエ
ハヤマ	シュンスケ
ハヤミ	カズユキ
フジイ	ユウタ
フジオカ	コウタロウ
ヤマダ	ナミ
(47 rows)

各テーブルorder by重複なし

sql

(
 SELECT
  u.l_name_kana,
  u.f_name_kana
 FROM
  usr AS u
 ORDER BY
  1,
  2
)
UNION
(
 SELECT
  e.l_name_kana,
  e.f_name_kana
 FROM
  employee AS e
 ORDER BY
  1,
  2
);

result

l_name_kana	f_name_kana
ナカザワ	ヤスヨ
コダマ	ツヨシ
ハヤミ	カズユキ
テラオカ	ヨウイチ
ヨシオカ	ハルカ
ウエダ	カズヤ
クボタ	マモル
モリモト	エリ
マツモト	ヒロミ
キノシタ	カズキ
ヤマダ	ナミ
カワグチ	ユウコ
サトウ	トモコ
ヤグチ	アユミ
ダイモン	イチロウ
イノウエ	イチロウ
ツムラ	カズミ
トガワ	ヨシミ
ムライ	サチコ
トオヤマ	シンイチ
ネギシ	ユウコ
タナベ	ショウイチ
ミナミ	サエ
オノ	ユウジ
ニシジマ	フミコ
ハヤマ	シュンスケ
ウエハラ	コウイチ
アイザワ	サトシ
チカダ	アキコ
タナカ	マユコ
キムラ	イチロウ
サカグチ	シュン
シミズ	ハルコ
フジオカ	コウタロウ
ノガミ	リエ
ニシ	ユウイチ
カトウ	アキオ
ヤマダ	タロウ
アリキ	マリ
ニトベ	コウジ
フジイ	ユウタ
セノオ	ハルキ
カワイ	ヤスハル
カンダ	サチコ
エモト	サトシ
ワダ	シュウコ
(46 rows)

一括order by重複あり

sql

SELECT
 u.l_name_kana,
 u.f_name_kana
FROM
 usr AS u
UNION ALL
SELECT
 e.l_name_kana,
 e.f_name_kana
FROM
 employee AS e
ORDER BY
 1,
 2;

result

l_name_kana	f_name_kana
アイザワ	サトシ
アリキ	マリ
イノウエ	イチロウ
イノウエ	イチロウ
ウエダ	カズヤ
ウエハラ	コウイチ
エモト	サトシ
オノ	ユウジ
カトウ	アキオ
カワイ	ヤスハル
カワグチ	ユウコ
カンダ	サチコ
キノシタ	カズキ
キムラ	イチロウ
クボタ	マモル
コダマ	ツヨシ
サカグチ	シュン
サトウ	トモコ
シミズ	ハルコ
セノオ	ハルキ
ダイモン	イチロウ
タナカ	マユコ
タナベ	ショウイチ
チカダ	アキコ
ツムラ	カズミ
テラオカ	ヨウイチ
トオヤマ	シンイチ
トガワ	ヨシミ
ナカザワ	ヤスヨ
ニシ	ユウイチ
ニシジマ	フミコ
ニトベ	コウジ
ネギシ	ユウコ
ノガミ	リエ
ハヤマ	シュンスケ
ハヤミ	カズユキ
フジイ	ユウタ
フジオカ	コウタロウ
マツモト	ヒロミ
ミナミ	サエ
ムライ	サチコ
モリモト	エリ
ヤグチ	アユミ
ヤマダ	タロウ
ヤマダ	ナミ
ヨシオカ	ハルカ
ワダ	シュウコ
(47 rows)

一括してorder by重複なし

sql

SELECT
 u.l_name_kana,
 u.f_name_kana
FROM
 usr AS u
UNION
SELECT
 e.l_name_kana,
 e.f_name_kana
FROM
 employee AS e
ORDER BY
 1,
 2;

result

l_name_kana	f_name_kana
アイザワ	サトシ
アリキ	マリ
イノウエ	イチロウ
ウエダ	カズヤ
ウエハラ	コウイチ
エモト	サトシ
オノ	ユウジ
カトウ	アキオ
カワイ	ヤスハル
カワグチ	ユウコ
カンダ	サチコ
キノシタ	カズキ
キムラ	イチロウ
クボタ	マモル
コダマ	ツヨシ
サカグチ	シュン
サトウ	トモコ
シミズ	ハルコ
セノオ	ハルキ
ダイモン	イチロウ
タナカ	マユコ
タナベ	ショウイチ
チカダ	アキコ
ツムラ	カズミ
テラオカ	ヨウイチ
トオヤマ	シンイチ
トガワ	ヨシミ
ナカザワ	ヤスヨ
ニシ	ユウイチ
ニシジマ	フミコ
ニトベ	コウジ
ネギシ	ユウコ
ノガミ	リエ
ハヤマ	シュンスケ
ハヤミ	カズユキ
フジイ	ユウタ
フジオカ	コウタロウ
マツモト	ヒロミ
ミナミ	サエ
ムライ	サチコ
モリモト	エリ
ヤグチ	アユミ
ヤマダ	タロウ
ヤマダ	ナミ
ヨシオカ	ハルカ
ワダ	シュウコ
(46 rows)

Q2-6-2:アンケート回答テーブル(quest)と社員テーブル(employee)から女性の氏名（カナ）だけを、氏（カナ）名（カナ）昇順で重複データをそのまま取り出すSQL文を完成させよ。

sql

氏名カナが一列、sexが男女

SELECT
 q.name_kana,
 q.sex
FROM
 quest q;

result

name_kana	sex
ヤマダタロウ	男
イノウエマリ	女
ウエハラハルカ	女
エモトシュウコ	女
オノヒロミ	女
カワイタロウ	男
ヤグチカズキ	男
アリキマモル	男
ヨシオカツヨシ	男
ワダシュン	男
シミズイチロウ	男
ミナミコウイチ	男
ムライサトシ	男
モリモトユウジ	男
ニシジマヤスハル	男
ネギシアユミ	女
(16 rows)

sql

氏名カナ分かれてる、sexが12

SELECT
 e.l_name_kana,
 e.f_name_kana,
 e.sex
FROM
 employee e;

result

l_name_kana	f_name_kana	sex
アイザワ	サトシ	1
ダイモン	イチロウ	1
フジイ	ユウタ	1
フジオカ	コウタロウ	1
ハヤミ	カズユキ	1
ハヤマ	シュンスケ	1
カワグチ	ユウコ	2
カトウ	アキオ	1
カンダ	サチコ	1
キムラ	イチロウ	1
ナカザワ	ヤスヨ	2
ノガミ	リエ	2
サトウ	トモコ	2
セノオ	ハルキ	1
シミズ	ハルコ	2
タナカ	マユコ	2
タナベ	ショウイチ	1
テラオカ	ヨウイチ	1
チカダ	アキコ	2
トオヤマ	シンイチ	1
トガワ	ヨシミ	2
ツムラ	カズミ	2
ウエダ	カズヤ	1
ヤマダ	ナミ	2
ニシ	ユウイチ	1
ニトベ	コウジ	1
(26 rows)

sql

SELECT
 q.name_kana,
 q.sex
FROM
 quest AS q
WHERE
 q.sex = '女';

result

name_kana	sex
イノウエマリ	女
ウエハラハルカ	女
エモトシュウコ	女
オノヒロミ	女
ネギシアユミ	女
(5 rows)

sql

SELECT
 e.l_name_kana || e.f_name_kana
AS
 name_kana,
 sex
FROM
 employee AS e
WHERE
 e.sex = 2;

result

name_kana	sex
カワグチユウコ	2
ナカザワヤスヨ	2
ノガミリエ	2
サトウトモコ	2
シミズハルコ	2
タナカマユコ	2
チカダアキコ	2
トガワヨシミ	2
ツムラカズミ	2
ヤマダナミ	2
(10 rows)

sql

氏名だけ取り出したい

SELECT
 q.name_kana
FROM
 quest AS q
WHERE
 q.sex = '女'
UNION ALL
SELECT
 e.l_name_kana || e.f_name_kana
AS
 name_kana
FROM
 employee AS e
WHERE
 e.sex = 2
ORDER BY
 1 ASC;

result

name_kana
イノウエマリ
ウエハラハルカ
エモトシュウコ
オノヒロミ
カワグチユウコ
サトウトモコ
シミズハルコ
タナカマユコ
チカダアキコ
ツムラカズミ
トガワヨシミ
ナカザワヤスヨ
ネギシアユミ
ノガミリエ
ヤマダナミ
(15 rows)

sql

氏名とsexを取り出したい

SELECT
 q.name_kana,
 CASE
  WHEN sex = '男' THEN 1
  WHEN sex = '女' THEN 2
 END sex
FROM
 quest AS q
WHERE
 q.sex = '女'
UNION ALL
SELECT
 e.l_name_kana || e.f_name_kana AS name_kana,
 e.sex
FROM
 employee AS e
WHERE
 e.sex = 2
ORDER BY
 1 ASC;

result

name_kana	sex
イノウエマリ	2
ウエハラハルカ	2
エモトシュウコ	2
オノヒロミ	2
カワグチユウコ	2
サトウトモコ	2
シミズハルコ	2
タナカマユコ	2
チカダアキコ	2
ツムラカズミ	2
トガワヨシミ	2
ナカザワヤスヨ	2
ネギシアユミ	2
ノガミリエ	2
ヤマダナミ	2
(15 rows)

縦結合（EXCEPT）

一方のSELECT文の結果から、他方に存在する行を除く演算子です。（データベースによって名称が異なり、SQL ServerであればEXCEPT、OracleではMINUSが使われます）
例: あるテーブルには存在するが、別のテーブルには存在しない行を取得したい場合。

Q2-6-3ユーザテーブル(usr)とアンケート回答テーブル(quest)のユーザ名を比較しユーザテーブルにしか登録されていないもののみを取り出すSQL文を完成させよ

sql

SELECT
 u.l_name || u.f_name name
FROM
 usr AS u
ORDER BY
 1 DESC;

result

name
和田修子
有木茉莉
矢口亜由美
木下一樹
南沙恵
村井佐知子
西島文子
森本絵里
上原幸一
松本博美
小野雄二
児玉毅
山田太郎
坂口駿
根岸裕子
江本聡
久保田守
吉岡遥
河合泰治
井上一郎
井上一郎
(21 rows)

sql

SELECT
 name
FROM
 quest
ORDER BY
 1 DESC;

result

name
和田駿
有木守
矢口一樹
南幸一
村井聡
西島泰治
清水一郎
森本雄二
上原遥
小野博美
山田太郎
根岸亜由美
江本修子
吉岡毅
河合太郎
井上茉莉
(16 rows)

sql

--join
SELECT
 l_name || f_name AS uqname
FROM
 usr u
INNER JOIN
 quest q
ON
 (u.l_name||u.f_name)=q.name
 ;

--where
SELECT
 l_name || f_name AS uqname
FROM
 usr u,
 quest q
WHERE
 (u.l_name||u.f_name)=q.name
;

result

両方にある名前

uqname
山田太郎
(1 rows)

--intersect
SELECT
 u.l_name || u.f_name name
FROM
 usr AS u
INTERSECT
SELECT
 name
FROM
 quest
;

sql

SELECT
 *
FROM
 usr x
WHERE EXISTS (
 SELECT
 FROM
  usr y
 WHERE
  (y.l_name || y.f_name)
　=
　(x.l_name || x.f_name)
 GROUP BY
  y.l_name,y.f_name
 HAVING
  COUNT(*) = 2
);

result

user_id	l_name	f_name	l_name_kana	f_name_kana	prefecture	city	o_address	tel	email
A200401	井上	一郎	イノウエ	イチロウ	千葉県	東千葉市北町	2-4-1	040-111-0001	inoue@wings.msn.to
A200506	井上	一郎	イノウエ	イチロウ	静岡県	南静岡市花町	4-2-1	040-666-0010	shimi@wings.msn.to
(2 rows)

sql

SELECT
 u.l_name || u.f_name AS name
FROM
 usr AS u
EXCEPT ALL
SELECT
 q.name
FROM
 quest AS q;

result

name
久保田守
児玉毅
松本博美
有木茉莉
井上一郎
井上一郎
西島文子
南沙恵
小野雄二
上原幸一
河合泰治
森本絵里
村井佐知子
木下一樹
坂口駿
吉岡遥
江本聡
和田修子
矢口亜由美
根岸裕子
(20 rows)

sql

SELECT
 u.l_name || u.f_name AS name
FROM
 usr AS u
EXCEPT
SELECT
 q.name
FROM
 quest AS q;

result

name
久保田守
児玉毅
松本博美
有木茉莉
井上一郎
西島文子
南沙恵
小野雄二
上原幸一
河合泰治
森本絵里
村井佐知子
木下一樹
坂口駿
吉岡遥
江本聡
和田修子
矢口亜由美
根岸裕子
(19 rows)

縦結合（INTERSECT）

両方のSELECT文で共通して存在する行のみを求めるため、より厳密なデータの共通部分をチェックする場合に利用します。

Q2-6-6-1:ユーザテーブル(usr)とアンケート回答テーブル(quest)からそれぞれに含まれる氏名を比較し双方に含まれるデータの氏名を取り出せ。

sql

SELECT
 u.l_name || u.f_name AS name
FROM
 usr AS u
INTERSECT
SELECT
 q.name
FROM
 quest AS q;

result

name
山田太郎
(1 row)

Q2-6-6-2:ユーザテーブル(usr)とアンケート回答テーブル(quest)からそれぞれに含まれる氏名を比較し双方に含まれるデータの氏名をサブクエリを使って書き直してみよう。

sql

SELECT
 a.name
FROM
 (
  SELECT
   u.l_name || u.f_name AS name
  FROM
   usr AS u
 ) a
 INNER JOIN quest AS q
  ON a.name = q.name;

result

name
山田太郎
(1 row)

Q2-6-6-3:ユーザテーブル(usr)とアンケート回答テーブル(quest)からそれぞれに含まれる氏名を比較し双方に含まれるデータの氏名をINNER JOINを使って書き直してみよう。

sql

SELECT
 u.l_name || u.f_name AS name
FROM
 usr AS u
 INNER JOIN quest AS q
  ON u.l_name || u.f_name = q.name;

result

name
山田太郎
(1 row)

データベース勉強会1 基礎編

この記事を書いた人

maftk

PostgreSQL 正規化 結合 副問合せ

正規化

正規形

内部結合(INNER JOIN ON句)

前の問題に別名をつけたり、WHEREでも書いてみる

社員テーブル(employee)とタイムカードテーブル(time_card)から2012年12月の氏名(l_name,f_name)と勤務時間(work_time)を、社員コード(s_id)、日付(r_date)の昇順で取り出すSQL文を完成させろ。

店舗テーブル(shop)と月間売り上げテーブル(sales)から2012年12月のs_nameごとのs_valueを売上高(s_value)の高い順で取り出すSQL文を完成させろ。

店舗テーブル(shop)と月間売り上げテーブル(sales)から店名ごとに2012年の売上高を売上高の低い順で取り出せ。

Q2-1-5:貸し出し記録テーブル(rental)とユーザテーブル(usr)からユーザについて、現在何冊の貸出を行っているかをユーザーID、氏名、貸し出し数を貸し出し数の多い順で取り出せ。

外部結合(RIGHT JOIN ON句／LEFT JOIN ON 句)

LEFT OUTER JOIN (左外部結合)

FULL OUTER JOIN (完全外部結合):

共通ポイント

使い分けの目安

Q2-2-1-1:注文明細テーブル(order_desc)と商品テーブル(product)から商品コード(p_id)毎に累計購入数(quantityの合計)と商品名(p_name),価格(price)を取得せよ。

Q2-2-2ユーザテーブル(usr)と貸し出し記録テーブル(rental)からユーザごとの貸し出し数が多い順で取得するSQL文を完成させろ。なお、貸し出しがないユーザの貸し出し数は「0」として出力せよ。

Q2-2-3:社員テーブル(employee)と所属部署テーブル(depart)を結合し所属社員が一人もいない「幽霊」部署を洗い出すSQL文を完成させろ。

Q2-2-4:書籍情報テーブル(books)と貸し出し記録テーブル(rental)を結合し、書籍毎の貸し出し累計数を多い順に取り出せ。この時貸し出しが1回もないものも取り出せ。

Q2-2-5:社員テーブル(employee)とタイムカードテーブル(time_card)を結合し、社員ごとの勤務時間平均を多い順に取り出せ。この時記録のない社員ないものも取り出せ。

自己結合(JOIN句＋別名定義)

Q2-3-1-1:書類目次テーブル(contents)からコンテンツコード(c_id)順に取得せよ。

Q2-3-3:メニューテーブル(menu)からそれぞれのメニュー名(title)と対応する親メニュー名(title)をページID(page_id)の昇順で取得するSQL文を完成せよ。

Q2-3-4: 社員テーブル(employee)からそれぞれ社員氏名(l_nameとf_name)と上司氏名(b_idが上司のs_id)、社員コード(s_id)の昇順で取得せよ。上司のいない社員についても情報を取り出せ。

３つ以上の結合(JOIN句の入れ子)

Q2-4-1: 書籍情報テーブル(books)と、著者情報テーブル(author_books)、著者テーブル(author)を結合して出版社(publish)が「山田出版」である、刊行日の新しい順で書名(title)、著者名(name)と刊行日(publish_date)を取得せよ。

Q2-4-3:書籍目次テーブル(contents)から前後のコンテンツを「前コンテンツ」「現コンテンツ」「次コンテンツ」として現ページのコンテンツコードの昇順で取り出すSQL文を完成させよ。

副問い合わせ/サブクエリ（SELECT文の入れ子）

EXISTS

Q2-5-1月間売上テーブル(sales)にて2012年12月分(2012-12)の売り上げが登録されていない店舗の店舗コード(s_id)と店舗名(s_name)を取得せよ。

Q2-5-2:アンケート回答テーブル(quest)から回答者平均年齢を超えている人の回答(answer1、answer2列)を評価の低い順に取得せよ。

Q2-5-3:所属部署テーブル(depart)から社員テーブル(employee)で使われていない部署コード部署名を取り出すSQL文を完成させよ。

Q2-5-4:店舗テーブル(shop)と月間売り上げテーブル2012年12月の売上高が店舗ごとの売り上げ平均(全ての月総平均)を下回っている店舗名とその売上高を取得せよ。

Q2-5-5:貸し出し記録テーブル(rental)を検索し、これまで一度も書籍の貸し出しを行ったことのないユーザの氏名をユーザテーブル(usr)から取得せよ

縦結合（UNION）

UNION (および UNION ALL) を使用する際の注意点

Q2-6-5著者情報テーブル(author)とユーザテーブル(usr)からそれぞれ氏名の情報を重複を含まない氏名（カナ）の降順で取り出すＳＱＬ文の誤りを正せ。

Q2-6-1-3:ユーザテーブル(usr)と社員テーブル(employee)の検索結果を足した氏名カナ(l_name_kanaとf_name_kana)を取得せよ。

Q2-6-2:アンケート回答テーブル(quest)と社員テーブル(employee)から女性の氏名（カナ）だけを、氏（カナ）名（カナ）昇順で重複データをそのまま取り出すSQL文を完成させよ。

縦結合（EXCEPT）

Q2-6-3ユーザテーブル(usr)とアンケート回答テーブル(quest)のユーザ名を比較しユーザテーブルにしか登録されていないもののみを取り出すSQL文を完成させよ

縦結合（INTERSECT）

Q2-6-6-1:ユーザテーブル(usr)とアンケート回答テーブル(quest)からそれぞれに含まれる氏名を比較し双方に含まれるデータの氏名を取り出せ。

この記事を書いた人

関連記事

PostgreSQL 正規化結合副問合せ