Skip to content

Instantly share code, notes, and snippets.

@hironomiu

hironomiu/1.md Secret

Last active July 24, 2020 13:20
  • Star 16 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save hironomiu/963ef8f7b72b43c910fdff9dd5614e4d to your computer and use it in GitHub Desktop.
SQL実力アップセミナー

SQL実力アップセミナー

このセミナーは「リレーショナルデータベースの必須技術「正規化」を学ぼう」で学んだテーブル定義を用いてSQLの実践的な書き方を学びます。「リレーショナルデータベースの必須技術「正規化」を学ぼう」を視聴している方がテーブル定義の背景などを理解して臨めますのでベストですが視聴していなくても問題なくSQL文の勉強ができるセミナーになっています。

自己紹介

Twitter@hironomiu

セミナー受講対象

エンジニア志望学生であり、SQL初心者から中級者

初心者の定義

単一テーブルに対してSELECT文が書ける、条件句で絞り込みができる。UPDATE、INSERT、DELETE文が書ける、条件句で絞り込みができる。

中級者の定義

複数テーブルを結合してSELECT文が書ける。同じ結果となるSQL文を複数書き分けることができる。

上級者の定義

SQL文をパフォーマンス視点で最適なSQL文を導き出せる

対象データベース

このセミナーではMySQLをベースにSQL文について解説しています。

進め方

このセミナーはライブコーディング形式で進めます。セミナー中は自身でSQLを書く時間はありませんので事前に回答を元にSQLを書いてみるか、ライブコーディング後に書いてみることをおすすめします。

Step1 回答

Step2 回答

事前準備

MySQLが動作し、SQLが発行できる環境(ライブコーディングの視聴のみでも学べる構成にしてありますが実際に自分でSQLを書く方が学びが多いため推奨)

概要

今回のサンプル概要

テーブル定義

概要に基づいて制約など盛り込んだテーブル定義が以下です。以下のCREATE TABLE文を任意のMySQL DATABASEで実行し作成しましょう。

DROP TABLE IF EXISTS order_details;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
address VARCHAR(100),
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY(id)
);

CREATE TABLE items (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY(id)
);

CREATE TABLE orders (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
order_date DATE NOT NULL,
customer_id INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_details (
order_id INT UNSIGNED NOT NULL,
item_id INT UNSIGNED NOT NULL,
item_quantity INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY(order_id,item_id),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (item_id) REFERENCES items(id)
);

サンプルデータ

insert into customers(id,name,address,created_at,updated_at) values(1,'A商事','東京都',now(),now()),(2,'B商会','埼玉県',now(),now()),(3,'C商店','神奈川県',now(),now());

insert into items(id,name,price,created_at,updated_at) values(1,'シャツ',1000,now(),now()),(2,'パンツ',950,now(),now()),(3,'マフラー',1200,now(),now()),(4,'ブルゾン',1800,now(),now());

insert into orders(id,order_date,customer_id,created_at,updated_at) values(1 , '2013-10-01',1,now(),now()),(2 , '2013-10-01',2,now(),now()),(3 , '2013-10-02',2,now(),now()),(4 , '2013-10-02',3,now(),now());

insert into order_details(order_id,item_id,item_quantity,created_at,updated_at) values(1 , 1 ,3,now(),now()),(1 , 2 ,2,now(),now()),(2 , 1 ,1,now(),now()),(2 , 3 ,10,now(),now()),(2 , 4 ,5,now(),now()),(3 , 2 ,80,now(),now()),(4 , 3 ,25,now(),now());

Step1 Question

Q0 テーブル確認をしましょう

抽出結果

+----------------+
| Tables_in_test |
+----------------+
| customers      |
| items          |
| order_details  |
| orders         |
+----------------+
4 rows in set (0.00 sec)

+----+---------+--------------+---------------------+---------------------+
| id | name    | address      | created_at          | updated_at          |
+----+---------+--------------+---------------------+---------------------+
|  1 | A商事   | 東京都       | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|  2 | B商会   | 埼玉県       | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|  3 | C商店   | 神奈川県     | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
+----+---------+--------------+---------------------+---------------------+
3 rows in set (0.00 sec)

+----+--------------+-------+---------------------+---------------------+
| id | name         | price | created_at          | updated_at          |
+----+--------------+-------+---------------------+---------------------+
|  1 | シャツ       |  1000 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|  2 | パンツ       |   950 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|  3 | マフラー     |  1200 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|  4 | ブルゾン     |  1800 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
+----+--------------+-------+---------------------+---------------------+
4 rows in set (0.00 sec)

+----------+---------+---------------+---------------------+---------------------+
| order_id | item_id | item_quantity | created_at          | updated_at          |
+----------+---------+---------------+---------------------+---------------------+
|        1 |       1 |             3 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|        1 |       2 |             2 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|        2 |       1 |             1 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|        2 |       3 |            10 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|        2 |       4 |             5 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|        3 |       2 |            80 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|        4 |       3 |            25 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
+----------+---------+---------------+---------------------+---------------------+
7 rows in set (0.00 sec)

+----+------------+-------------+---------------------+---------------------+
| id | order_date | customer_id | created_at          | updated_at          |
+----+------------+-------------+---------------------+---------------------+
|  1 | 2013-10-01 |           1 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|  2 | 2013-10-01 |           2 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|  3 | 2013-10-02 |           2 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
|  4 | 2013-10-02 |           3 | 2020-04-14 08:30:09 | 2020-04-14 08:30:09 |
+----+------------+-------------+---------------------+---------------------+
4 rows in set (0.00 sec)

Q1 商品シャツの売り上げ合計金額を算出しましょう

抽出結果

+----------+
| proceeds |
+----------+
|     4000 |
+----------+
1 row in set (0.01 sec)

Q1 解答後補足問題

結合順序を変えてみましょう

Q2 商品をでランダムに1行求めましょう

抽出結果(ランダムに抽出するため同じになるとは限りません)

+----+-----------+-------+---------------------+------------+
| id | name      | price | created_at          | updated_at |
+----+-----------+-------+---------------------+------------+
|  2 | パンツ    |   950 | 2017-09-08 16:47:09 | NULL       |
+----+-----------+-------+---------------------+------------+
1 row in set (0.00 sec)

Q2 解答後補足問題1

ランダム抽出の内部の動作について調べましょう。調査を踏まえ注意すべき点などないかなど考えてみましょう

Q2 解答後補足問題2

SELECT文での「(アスタリスク」についてどのようなケースで利用して良いか考えてみましょう

Q3 商品「シャツ」「パンツ」を受注した受注idを求めましょう。受注idは新しい(大きい)順に並べましょう

抽出結果

+----------+
| order_id |
+----------+
|        3 |
|        2 |
|        1 |
+----------+
3 rows in set (0.00 sec)

Q3 解答後補足問題1

Q3のSQL文をunion句を用いて同じ結果になるSELECT文を構築しましょう
抽出結果

+----------+
| order_id |
+----------+
|        3 |
|        2 |
|        1 |
+----------+
3 rows in set (0.00 sec)

Q3 解答後補足問題2

Q3のSQL文をgroup by句を用いて同じ結果になるSELECT文を構築しましょう
抽出結果

+----------+
| order_id |
+----------+
|        3 |
|        2 |
|        1 |
+----------+
3 rows in set (0.00 sec)

Q4 受注全体から受注金額の平均を算出しましょう

抽出結果

+-----------------+
| avg_order_price |
+-----------------+
|      33225.0000 |
+-----------------+
1 row in set (0.00 sec)

Q4 解答後補足問題

受注の件数も一緒に取得しましょう

抽出結果

+-----------------+-------------+
| avg_order_price | order_count |
+-----------------+-------------+
|      33225.0000 |           4 |
+-----------------+-------------+
1 row in set (0.00 sec)

Q5 受注金額が一番大きい受注の受注idと受注金額を求めましょう

抽出結果

+----+-------------+
| id | order_price |
+----+-------------+
|  3 |       76000 |
+----+-------------+
1 row in set (0.00 sec)

Q5 解答後補足問題

今回の回答が正しいか考えてみましょう


Step2 Question

Q6 新しい商品を1回のinsertで登録しましょう。

商品 単価
タンクトップ 1300
ジャンパー 2500
ソックス 600

ヒント バルクインサートを検索しましょう

+----+--------------------+-------+---------------------+---------------------+
| id | name               | price | created_at          | updated_at          |
+----+--------------------+-------+---------------------+---------------------+
|  1 | シャツ             |  1000 | 2020-04-15 10:11:39 | 2020-04-15 10:11:39 |
|  2 | パンツ             |   950 | 2020-04-15 10:11:39 | 2020-04-15 10:11:39 |
|  3 | マフラー           |  1200 | 2020-04-15 10:11:39 | 2020-04-15 10:11:39 |
|  4 | ブルゾン           |  1800 | 2020-04-15 10:11:39 | 2020-04-15 10:11:39 |
|  5 | タンクトップ       |  1300 | 2020-05-06 01:25:01 | 2020-05-06 01:25:01 |
|  6 | ジャンパー         |  1500 | 2020-05-06 01:25:01 | 2020-05-06 01:25:01 |
|  7 | ソックス           |   600 | 2020-05-06 01:25:01 | 2020-05-06 01:25:01 |
+----+--------------------+-------+---------------------+---------------------+
7 rows in set (0.00 sec)

Q6 解答後補足問題

今回の回答に至ったinsert文のメリットを考えてみましょう

Q7 B商会の受注金額の合計を算出しましょう

抽出例

+-----------+
| sum_price |
+-----------+
|     98000 |
+-----------+
1 row in set (0.00 sec)

Q8 商品(itemss)から受注明細(order_details)で使われている商品(items.id,items.name)を求めましょう、3種類のSQLを作成しましょう

抽出例

+---------+--------------+
| item_id | name         |
+---------+--------------+
|       1 | シャツ       |
|       2 | パンツ       |
|       3 | マフラー     |
|       4 | ブルゾン     |
+---------+--------------+
4 rows in set (0.00 sec)

ヒント:「inner join」「exists」「in」で一つずつ構築しましょう

Q9 受注明細(order_details)で使われていない商品(items.id,items.name)を求めましょう、3種類のSQLを作成しましょう

抽出例

+----+--------------------+
| id | name               |
+----+--------------------+
|  5 | タンクトップ       |
|  6 | ジャンパー         |
|  7 | ソックス           |
+----+--------------------+
3 rows in set (0.00 sec)

Q10 全アイテム(items)に存在する商品を受注明細(order_details)から各商品ごとの注文回数(抽出例はitem_idが1~7固定のものとする)を求めよ、全注文数(all_order)も求めよ

抽出例

+-----------+------+------+------+------+------+------+------+
| all_order | 1    | 2    | 3    | 4    | 5    | 6    | 7    |
+-----------+------+------+------+------+------+------+------+
|         7 |    2 |    2 |    2 |    1 |    0 |    0 |    0 |
+-----------+------+------+------+------+------+------+------+
1 row in set (0.00 sec)

Q11 全アイテム(items)に存在する商品を受注明細(order_details)から各商品ごとの注文回数を求めよ(5分)

抽出例

+----+-------+
| id | count |
+----+-------+
|  1 |     2 |
|  2 |     2 |
|  3 |     2 |
|  4 |     1 |
|  5 |     0 |
|  6 |     0 |
|  7 |     0 |
+----+-------+
7 rows in set (0.00 sec)

Q12 全アイテム(items)に存在する商品を受注明細(order_details)から各商品ごとの注文回数を求めよ、全注文数(all_order)も求めよ(5分) 抽出例

抽出例

+-----------+-------+
| all_order | count |
+-----------+-------+
| all_order |     7 |
| 1         |     2 |
| 2         |     2 |
| 3         |     2 |
| 4         |     1 |
| 5         |     0 |
| 6         |     0 |
| 7         |     0 |
+-----------+-------+
8 rows in set (0.00 sec)

Q13 Q11,Q12で求めた全アイテム(items)に存在する商品を受注明細(order_details)から各商品ごとの注文(order_id)を求めよ、(5分) 抽出例

抽出例

+----+----------+
| id | order_id |
+----+----------+
|  1 | 1,2      |
|  2 | 1,3      |
|  3 | 2,4      |
|  4 | 2        |
|  5 | NULL     |
|  6 | NULL     |
|  7 | NULL     |
+----+----------+
7 rows in set (0.00 sec)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment