はじめに
SQL初心者が入門書を1冊やった後にどうやって学習していくか、というのは意外と難しい問題だと思います。
世の中にはSQLやDBの優れた技術書はたくさんあります。 ただ、ひとまず基礎的なSQLの文法は学んだ、という段階で読むにはレベルが高すぎるし、技術的な解説が中心になりがちです。
そこで、初心者のSQLへの理解を助ける事を目的としてこの記事を書いてみようと思います。
対象者
この記事は下記のような人を対象にしています。
- SQLの基礎は学んだので、次のステップに進みたい人
- より実践的なSQLの使い方を学びたい人
- 色んな問い合わせの引き出しを増やしたい人
前提
- PostgreSQLをインストールしていること（Ver 16.2）
→最近のバージョンであれば問題ないと思います。 - pgAdmin
本編
この記事では、通販サイトの注文管理DBをモデルに3つのテーブルを作成します。
本DBに対して様々な問い合わせ文を実際に書くことで、より実務に近い形でのSQLスキルを身につけていきましょう。
「こういうデータが欲しい」という希望（要件）を、ロジックを考えてSQL文で表現（設計・製造）するのがエンジニアの仕事です。
この記事で作るテーブルとサンプルデータは、様々な要件を考えてそれを実現するSQL文を書く練習に使えるようになっています。
ぜひ活用してください。
1. DB、テーブル作成
まず、データベース（sandbox）を作成します。
DB作成
CREATE DATABASE sandbox;
次に、3つのテーブル（customers, products, orders）を作成します。
テーブル作成
-- 顧客テーブルの作成
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY, -- 顧客ID
name VARCHAR(100) NOT NULL, -- 顧客名
birthday DATE NOT NULL, -- 誕生日
address VARCHAR(255) NOT NULL, -- 住所
email VARCHAR(255), -- メールアドレス
insert_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL -- 作成日
);
-- 商品テーブルの作成
CREATE TABLE products (
product_id SERIAL PRIMARY KEY, -- 製品ID
name VARCHAR(100) NOT NULL, -- 製品名
genre VARCHAR(50) NOT NULL, -- 製品ジャンル
price INTEGER NOT NULL, -- 単価
insert_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL -- 作成日
);
-- 注文テーブルの作成
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY, -- 注文ID
customer_id INTEGER NOT NULL, -- 顧客ID
product_id INTEGER NOT NULL, -- 製品ID
quantity INTEGER CHECK (quantity > 0) NOT NULL, -- 数量
order_date DATE NOT NULL, -- 注文日
insert_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 作成日
FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
FOREIGN KEY (product_id) REFERENCES products (product_id)
);
2. サンプルデータのインサート
それでは、先ほど作成したテーブルにデータを入れていきましょう。
全INSERT文を1度に実行可能です。
データインサート
-- 顧客データ
INSERT INTO customers (customer_id, name, birthday, address, email, insert_timestamp) VALUES
(1, '山田 一郎', '1980-01-15', '東京都新宿区1-1-1', 'ichiro.yamada@example.com', '2022-05-01 14:23:45'),
(2, '佐藤 花子', '1990-02-20', '神奈川県横浜市2-2-2', 'hanako.sato@example.com', '2021-11-12 08:15:30'),
(3, '鈴木 武史', '1985-03-25', '埼玉県さいたま市3-3-3', 'takeshi.suzuki@example.com', '2020-03-20 11:05:50'),
(4, '高橋 佳奈', '1995-04-10', '千葉県千葉市4-4-4', 'kana.takahashi@example.com', '2023-07-23 19:45:00'),
(5, '伊藤 直樹', '1982-05-15', '大阪府大阪市5-5-5', 'naoki.ito@example.com', '2021-02-14 09:30:25'),
(6, '中村 麻衣', '1991-06-30', '愛知県名古屋市6-6-6', 'mai.nakamura@example.com', '2024-01-15 17:20:40'),
(7, '小林 健太', '1988-07-25', '福岡県福岡市7-7-7', 'kenta.kobayashi@example.com', '2022-08-08 13:50:55'),
(8, '加藤 さくら', '1993-08-20', '北海道札幌市8-8-8', 'sakura.kato@example.com', '2020-07-07 15:45:35'),
(9, '吉田 俊介', '1986-09-10', '広島県広島市9-9-9', 'shunsuke.yoshida@example.com', '2023-05-05 10:40:45'),
(10, '山口 美咲', '1992-10-05', '宮城県仙台市10-10-10', 'misaki.yamaguchi@example.com', '2024-04-04 14:55:50'),
(11, '斎藤 亮介', '1983-11-15', '京都府京都市11-11-11', 'ryosuke.saito@example.com', '2021-01-01 12:00:00'),
(12, '松本 由美', '1994-12-25', '兵庫県神戸市12-12-12', 'yumi.matsumoto@example.com', '2020-11-11 16:30:15'),
(13, '井上 大輔', '1981-01-05', '静岡県静岡市13-13-13', 'daisuke.inoue@example.com', '2023-08-08 19:10:05'),
(14, '渡辺 あかり', '1989-02-14', '岡山県岡山市14-14-14', 'akari.watanabe@example.com', '2022-03-03 07:25:30'),
(15, '石井 勇気', '1996-03-15', '群馬県前橋市15-15-15', 'yuki.ishii@example.com', '2021-09-09 22:20:40'),
(16, '木村 千尋', '1990-04-30', '熊本県熊本市16-16-16', 'chihiro.kimura@example.com', '2024-07-07 18:40:55'),
(17, '林 真一', '1984-05-05', '新潟県新潟市17-17-17', 'shinichi.hayashi@example.com', '2020-02-02 08:10:20'),
(18, '森 杏奈', '1993-06-25', '長崎県長崎市18-18-18', 'anna.mori@example.com', '2023-10-10 20:30:45'),
(19, '池田 智也', '1987-07-15', '長野県長野市19-19-19', 'tomoya.ikeda@example.com', '2022-06-06 10:50:30'),
(20, '橋本 理恵', '1991-08-05', '沖縄県那覇市20-20-20', 'rie.hashimoto@example.com', '2021-12-12 12:30:45');
-- 商品データ
INSERT INTO products (product_id, name, genre, price, insert_timestamp) VALUES
(1, 'ノートパソコン', '電子機器', 120000, '2020-05-01 14:23:45'),
(2, 'スマートフォン', '電子機器', 90000, '2021-06-12 08:15:30'),
(3, 'タブレット', '電子機器', 60000, '2022-03-20 11:05:50'),
(4, 'ワイヤレスイヤホン', '電子機器', 15000, '2020-07-23 19:45:00'),
(5, 'デジタルカメラ', '電子機器', 80000, '2023-01-14 09:30:25'),
(6, 'テレビ', '家電', 50000, '2024-05-08 17:20:40'),
(7, '冷蔵庫', '家電', 100000, '2021-04-08 13:50:55'),
(8, '洗濯機', '家電', 70000, '2023-09-09 15:45:35'),
(9, '電子レンジ', '家電', 20000, '2020-08-08 10:40:45'),
(10, '掃除機', '家電', 15000, '2022-04-04 14:55:50'),
(11, '炊飯器', '家電', 25000, '2023-11-11 12:00:00'),
(12, 'オーブントースター', '家電', 10000, '2021-05-11 16:30:15'),
(13, 'コーヒーメーカー', '家電', 12000, '2022-07-08 19:10:05'),
(14, '電動歯ブラシ', '家電', 8000, '2024-06-03 07:25:30'),
(15, '扇風機', '家電', 5000, '2020-01-09 22:20:40'),
(16, 'エアコン', '家電', 80000, '2021-08-15 18:40:55'),
(17, 'ドライヤー', '家電', 4000, '2023-02-02 08:10:20'),
(18, '加湿器', '家電', 6000, '2022-10-10 20:30:45'),
(19, '空気清浄機', '家電', 20000, '2020-09-06 10:50:30'),
(20, 'ヒーター', '家電', 15000, '2021-11-12 12:30:45');
-- 注文データ
INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date, insert_timestamp) VALUES
(1, 2, 3, 3, '2020-01-01', '2020-01-01 14:23:45'),
(2, 5, 1, 2, '2020-03-15', '2020-03-15 08:15:30'),
(3, 7, 4, 1, '2020-05-10', '2020-05-10 11:05:50'),
(4, 9, 2, 4, '2020-07-20', '2020-07-20 19:45:00'),
(5, 11, 5, 2, '2020-09-25', '2020-09-25 09:30:25'),
(6, 1, 7, 3, '2020-12-05', '2020-12-05 17:20:40'),
(7, 4, 6, 2, '2021-02-28', '2021-02-28 13:50:55'),
(8, 6, 8, 1, '2021-05-10', '2021-05-10 15:45:35'),
(9, 8, 9, 4, '2021-08-01', '2021-08-01 10:40:45'),
(10, 10, 11, 2, '2021-10-15', '2021-10-15 14:55:50'),
(11, 12, 10, 1, '2022-01-01', '2022-01-01 12:00:00'),
(12, 3, 12, 3, '2022-03-20', '2022-03-20 16:30:15'),
(13, 5, 11, 2, '2022-06-10', '2022-06-10 19:10:05'),
(14, 7, 13, 5, '2022-08-25', '2022-08-25 07:25:30'),
(15, 9, 14, 1, '2022-11-05', '2022-11-05 22:20:40'),
(16, 11, 15, 2, '2023-01-10', '2023-01-10 18:40:55'),
(17, 2, 16, 4, '2023-03-01', '2023-03-01 08:10:20'),
(18, 4, 17, 3, '2023-05-20', '2023-05-20 20:30:45'),
(19, 6, 18, 2, '2023-08-05', '2023-08-05 10:50:30'),
(20, 8, 19, 1, '2023-10-10', '2023-10-10 12:30:45'),
(21, 10, 20, 1, '2024-01-01', '2024-01-01 18:25:30'),
(22, 12, 1, 3, '2024-02-15', '2024-02-15 15:20:35'),
(23, 13, 2, 2, '2024-04-01', '2024-04-01 16:15:30'),
(24, 19, 3, 1, '2024-05-15', '2024-05-15 20:10:20'),
(25, 5, 4, 2, '2024-06-20', '2024-06-20 12:45:30'),
(26, 7, 5, 1, '2024-07-10', '2024-07-10 17:30:40'),
(27, 9, 6, 3, '2024-08-01', '2024-08-01 08:20:30'),
(28, 11, 7, 2, '2024-08-02', '2024-08-02 11:15:20'),
(29, 2, 8, 1, '2024-08-03', '2024-08-03 13:50:35'),
(30, 18, 9, 2, '2024-08-04', '2024-08-04 10:45:30'),
(31, 16, 10, 3, '2024-08-05', '2024-08-05 18:30:20'),
(32, 8, 2, 4, '2024-08-06', '2024-08-06 15:25:30'),
(33, 10, 5, 1, '2024-08-07', '2024-08-07 12:40:50'),
(34, 12, 19, 2, '2024-08-08', '2024-08-08 11:35:30'),
(35, 1, 12, 3, '2024-08-09', '2024-08-09 19:45:25'),
(36, 15, 1, 1, '2024-08-10', '2024-08-10 14:30:20'),
(37, 5, 13, 2, '2024-08-11', '2024-08-11 16:15:30'),
(38, 17, 14, 1, '2024-08-12', '2024-08-12 14:20:40'),
(39, 9, 18, 3, '2024-08-13', '2024-08-13 09:25:50'),
(40, 11, 20, 2, '2024-08-14', '2024-08-14 10:20:40'),
(41, 2, 7, 1, '2024-08-15', '2024-08-15 15:10:30'),
(42, 4, 4, 4, '2024-08-16', '2024-08-16 20:50:25'),
(43, 6, 9, 2, '2024-08-17', '2024-08-17 11:30:15'),
(44, 8, 19, 3, '2024-08-18', '2024-08-18 19:15:20'),
(45, 13, 20, 1, '2024-08-19', '2024-08-19 14:40:35'),
(46, 12, 11, 2, '2024-08-20', '2024-08-20 13:55:30'),
(47, 1, 10, 1, '2024-08-21', '2024-08-21 12:20:25'),
(48, 14, 20, 2, '2024-08-22', '2024-08-22 17:50:30'),
(49, 5, 17, 3, '2024-08-23', '2024-08-23 16:05:45'),
(50, 7, 5, 1, '2024-08-24', '2024-08-24 18:30:55');
3. SQLトレーニング問題
問題1: 顧客一覧の取得 (難易度: ★☆☆☆☆)
問題文:customersテーブルから全ての顧客の名前と住所を取得してください。
解答1:
SELECT name, address FROM customers;
問題2: 特定のジャンルの商品 (難易度: ★☆☆☆☆)
問題文: productsテーブルからジャンルが「電子機器」の商品名と価格を取得してください。
解答2:
SELECT name, price FROM products WHERE genre = '電子機器';
問題3: 特定の期間内の注文 (難易度: ★★☆☆☆)
問題文: ordersテーブルから、注文日が2022年1月1日から2023年12月31日までの注文を全て取得してください。
解答3:
SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2023-12-31';
問題4: 顧客ごとの注文数 (難易度: ★★☆☆☆)
問題文: 各顧客が行った注文の数をカウントし、customer_idと注文数を取得してください。
解答4:
SELECT customer_id, COUNT(*) as order_count FROM orders GROUP BY customer_id;
問題5: 特定の商品の注文 (難易度: ★★★☆☆)
問題文: ordersテーブルから、product_idが5の注文のcustomer_idと注文日を取得してください。
解答5:
SELECT customer_id, order_date FROM orders WHERE product_id = 5;
問題6: 顧客の詳細と注文 (難易度: ★★★☆☆)
問題文: 全ての注文について、顧客の名前、住所、商品名、注文日を取得してください。
解答6:
SELECT c.name, c.address, p.name AS product_name, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;
問題7: 最も高価な商品を購入した顧客 (難易度: ★★★★☆)
問題文: 最も高価な商品を購入した顧客の名前とその商品の価格を取得してください。
解答7:
SELECT c.name, p.price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE p.price = (SELECT MAX(price) FROM products);
問題8: 注文数が5以上の顧客 (難易度: ★★★★☆)
問題文: 注文数が5回以上の顧客のcustomer_idとその注文数を取得してください。
解答8:
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5;
問題9: 顧客の最新注文 (難易度: ★★★★★)
問題文: 各顧客の最新の注文日とその注文の詳細（customer_id、product_id、quantity、order_date）を取得してください。
解答9:
SELECT o.customer_id, o.product_id, o.quantity, o.order_date
FROM orders o
JOIN (
SELECT customer_id, MAX(order_date) as latest_order_date
FROM orders
GROUP BY customer_id
) lo ON o.customer_id = lo.customer_id AND o.order_date = lo.latest_order_date;
問題10: 最も多く注文された商品 (難易度: ★★★★★)
問題文: 注文数が最も多い商品の商品名とその注文数を取得してください。
解答10:
SELECT p.name, COUNT(o.product_id) as order_count
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.name
ORDER BY order_count DESC
LIMIT 1;
おわりに
以上、注文管理DBをモデルにしたSQLハンズオンでした。
JOIN、GROUP BY、サブクエリなどの実践的な使い方に慣れると、必要なデータを取るためにどんな手があるか自分で考えられるようになってきます。
今回のトレーニング問題以外にも、自分でデータ取得条件を考えて、それを実現するSQLを作り上げてみてください。
色々試しているうちに、いつの間にかリレーショナルDBの扱い方に習熟していると思います。
この記事が皆さんのSQL学習の助けになれば幸いです。
最後まで読んでいただきありがとうございました。