PostgreSQL 練習 (2)

DB

↓の続きです。 utouto97.hatenablog.com

SQL の練習問題を解いていきます。
問題は↓になります。

qiita.com

準備

リンク先の Schema SQL を参考にテーブルの作成・データの挿入を行います。

CREATE TABLE employees (
  id SERIAL NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL,
  department varchar(255) NOT NULL,
  hobby1 varchar(255),
  hobby2 varchar(255),
  hobby3 varchar(255)
);

INSERT INTO employees
VALUES
  (1, '杉山 圭佑', '営業部', 'サッカー', 'ドライブ', '映画鑑賞'),
  (2, '佐藤 結菜', '人事部', '映画鑑賞', '旅行', 'インスタ'),
  (3, '高橋 絵里', '経理部', 'ゲーム', NULL, NULL),
  (4, '早川 良太', '人事部', 'ドライブ', '料理', NULL),
  (5, '佐藤 一弥', '経理部', NULL, NULL, NULL),
  (6, '佐藤 優穂', '営業部', 'インスタ', 'TikTok', NULL);

一つ注意すべき点は、PostgreSQL では、AUTO_INCREMENTではなく、SERIAL型を利用します。

問1

趣味に映画鑑賞が含まれる社員の名前を一覧で表示せよ。

SQL文はこんな感じ。

SELECT name
FROM employees
WHERE hobby1 = '映画鑑賞'
  OR hobby2 = '映画鑑賞'
  OR hobby3 = '映画鑑賞';

結果は以下のとおり。

   name    
-----------
 杉山 圭佑
 佐藤 結菜
(2 rows)

問2

趣味1～3を縦に表示せよ。

SQL文はこんな感じ。

SELECT name, hobby1 as hobby
FROM employees
UNION ALL
SELECT name, hobby2 as hobby
FROM employees
UNION ALL
SELECT name, hobby3 as hobby
FROM employees;

UNION ALL を使って、それぞれの和をとります。

結果は以下のとおり。

   name    |  hobby   
-----------+----------
 杉山 圭佑 | サッカー
 佐藤 結菜 | 映画鑑賞
 高橋 絵里 | ゲーム
 早川 良太 | ドライブ
 佐藤 一弥 | 
 佐藤 優穂 | インスタ
 杉山 圭佑 | ドライブ
 佐藤 結菜 | 旅行
 高橋 絵里 | 
 早川 良太 | 料理
 佐藤 一弥 | 
 佐藤 優穂 | TikTok
 杉山 圭佑 | 映画鑑賞
 佐藤 結菜 | インスタ
 高橋 絵里 | 
 早川 良太 | 
 佐藤 一弥 | 
 佐藤 優穂 | 
(18 rows)

参考: https://www.postgresql.jp/document/12/html/queries-union.html

問3

名字が佐藤である社員の、趣味の数を表示せよ。

SQL文はこんな感じ。

SELECT name, COUNT(hobby1) + COUNT(hobby2) + COUNT(hobby3) AS hobby_count
FROM employees
WHERE name LIKE '佐藤%'
GROUP BY name;

COUNTでは、NULLは数えられません。
参考: https://lightgauge.net/database/sqlserver/1675/

結果は以下のとおり。

   name    | hobby_count 
-----------+-------------
 佐藤 一弥 |           0
 佐藤 優穂 |           2
 佐藤 結菜 |           3
(3 rows)

問4

同じ趣味を持つ社員の一覧を表示せよ。 なお、氏名リストの並び順は社員番号の昇順で、区切り文字は「, 」とする。

SQL文はこんな感じ。

CREATE VIEW hobby_name AS
  SELECT name, hobby1 as hobby
  FROM employees
  UNION ALL
  SELECT name, hobby2 as hobby
  FROM employees
  UNION ALL
  SELECT name, hobby3 as hobby
  FROM employees;

SELECT hobby, ARRAY_TO_STRING(
  ARRAY_AGG(
    name
  ), ','
) AS name_list
FROM hobby_name
WHERE hobby IS NOT NULL
GROUP BY hobby
HAVING COUNT(*) > 1;

これはかなり難しかったです。
まず、hobby1,hobby2,hobby3と分かれていると扱いづらいので、問2の結果をそのままビューとします。
これで、人の名前と趣味の組み合わせのテーブルができました。

もうひとつポイントなのは、複数の結果をカンマでつなぐ必要があるということです。
これはARRAY_TO_STRINGを使いました。

結果は以下のとおり。

  hobby   |      name_list      
----------+---------------------
 ドライブ | 早川 良太,杉山 圭佑
 映画鑑賞 | 佐藤 結菜,杉山 圭佑
 インスタ | 佐藤 優穂,佐藤 結菜
(3 rows)

解答例を見てみると、一文で表現されていました。
また、ARRAY_TO_STRINGではなく、GROUP_CONCATというものを利用していました。

次

utouto97.hatenablog.com

終わり