これであなたも SQL マスター 超特訓講座|研修コースに参加してみた
今回参加したコースは これであなたも SQL マスター 超特訓講座 です。
「 SQLマスター」で「超特訓講座」とは、いきなり強そうなタイトルですね。
そのタイトルに相応しく SQL の基本的な文法や、テーブルのスキーマ、正規化、 ER 図などは学習済みの、中級〜上級レベル向けのコースで、複雑で大規模なデータベースでも見通しをたてて SQL を書けることを目指すコースでした。
そこで出題されたのはちょっと角度を変えてアプローチする必要がある課題ばかりでした。 果たして私は SQL マスターになれたのか … ?
では、どのような内容だったのか、レポートします!
もくじ
コース情報
想定している受講者 |
|
---|---|
受講目標 | SQL でさらなる高みを目指したい方 |
講師紹介
このコースの講師は前回、「 SQL 練習 ドリル 2 ~高度で複雑な SQL に挑戦」でも登壇された宮崎 克也さんです。
「インターネットの登場で人生が変わった。とんがった技術を教えたい」
まずはこのコースで目指すことを簡単に紹介いただきました。
- 複雑でも、こわくない
- 大規模でも、こわくない
- 仕様書がなくても、こわくない
「大規模でも、こわくない」「仕様書がなくても、こわくない」とりあえず、どっちも怖いんですが …
複雑で大規模なデータベースとは
まずは中級・上級レベルが扱う複雑で大規模なデータベースとはどのようなものか、解説されました。
- エンティティ(テーブル)数で数百以上
- 列数はさまざま
- 金融機関のシステムであれば、範囲にもよるが数千テーブルも
- レコード(データ)件数は青天井
- 数万件、数十万件程度はザラ
- 昨今はさらに上のオーダーもあり
- RDB 以外(ビッグテーブルなど)であればなお多い
さらに複雑・大規模なデータベースでは、構造がわかりにくい場合があります。 その要因としては次のようなものが考えられます。
- ドキュメントの不備(大規模でもたまにある)
- 構造的な問題
- 正規化が不十分
- カラム数が異常に多い(用途によってはやむをえないことも)
- よく似た名前のカラムが散在している
- 「汚れた( dirty )」データの存在
- プログラムのバグでありえない値が入っている
- 入力チェック(バリデーション)の不備によるもの
- いわゆる「直接修正」によるもの
- 通常のシステム運用では発生しえない値
- そして、その記録が残っていない……
- 多数のテーブルとの JOIN が必要な構造
- ある意味 RDB の宿命
このコースでは、複雑で大規模なデータベースの例として、 MySQL が用意しているサンプルデータベースを使用します。
今回はこの中から sakira のデータベースを使います。
- 16 テーブル(標準か、むしろ少なめ)
- 平均すると 1000 件以内のレコード
ドキュメント不備でもデータベースを見通すコツ
こうしたデータベースを見通すには、通常なら仕様書や ER 図などの設計書を確認しますが、上記の通り、そういったドキュメントがない、もしくは不正確な場合があります。
そんなデータベースでも見通すコツがあります。
- ER 図を生成する
- 複数あるが MySQL Workbench の「 Reverse Engineer 」の機能が便利
- 今あるデータベースから ER 図を生成できる
- 業務知識をもとに推測する
- テーブル名や列名などからデータの作られ方や使われ方などをイメージする
今回のサンプルデータベースの Sakila でも ER 図を生成したところ、リレーションの組み方が独特でした。
- エンティティ間を結ぶ線がテーブル数にしては多い
- つまりリレーションが複雑
- 例: customer テーブルの address_id 列
- 住所を ID で持っている
- つまり住所を address テーブルで管理している
ちなみに Sakila はレンタルビデオ業のデータベースです。
演習問題
ここからは Sakira データベースで、生成した ER 図と英語の仕様書を頼りに演習問題を解いていきます。 このレポートでは 3 問だけ紹介いたします。 全問解きたい方はぜひコースにご参加くださいませ!
どの問題も、一見簡単そうですが、簡単ではないという問題設定になっていました。
なお、上記の仕様書には環境構築手順も記載されているので、ぜひお試しください。
ウォーミングアップ
最初のウォーミングアップ問題は、まず例として宮崎さんが解き方を説明いただきながら、演習しました。
スタッフ一覧として、次の列を出力する( 2 行)
スタッフ ID, スタッフ名, 住所, 郵便番号, 電話番号, 都市名, 国名, 店舗 ID
info必要に応じて、 JOIN 、計算、集計などを駆使して取得する(以降も同様)
ER 図をもとにデータベースの構造を探っていきます。
- スタッフ一覧なので、スタッフを管理する staff テーブルをメインにする
- staff テーブルを見ると、必要な列が含まれていない
- 結合が必要
- スタッフ ID 、スタッフ名は相当する列がある
- 住所は address テーブルから
- 結合が必要
- 郵便番号、電話番号、都市名、国家、店舗 ID もない
解答と解説
- staff テーブルを元にする
- address テーブルを結合する
- 対応する行があるはずなので内部結合でよさそう
- 都市名は city テーブルを結合する
- 国名は country テーブルを結合する
SELECT
s.staff_id AS ID,
CONCAT(s.first_name,
_utf8mb4 ' ',
s.last_name) AS name,
a.address AS address,
a.postal_code AS `zip code`,
a.phone AS phone,
city.city AS city,
country.country AS country,
s.store_id AS SID
FROM
(((staff s
JOIN address a ON ((s.address_id = a.address_id)))
JOIN city ON ((a.city_id = city.city_id)))
JOIN country ON ((city.country_id = country.country_id)))
レベル 1
レベル 1 からは自力で解きます。ここから厄介になります。問題に書かれている結果の行数をヒントにするとよいでしょう。
カテゴリ別の売上一覧として、次の列を出力する( 16 行)
カテゴリ, 売上合計額
解答と解説
一見複雑ではなさそうですが、 JOIN がいくつも重なっています。 ER 図から、目的のデータをどうやって辿るか考えます。
- 売上に関するテーブルがない
- rental か payment か
- payment に amount というカラムがある
- payment にはカテゴリを示すカラムはない
- E-R 図から辿る
- category テーブルから film_category という中間テーブルを経て、 film テーブルとリレーションがある
- film から inventory にリレーションがある
- payment テーブルから逆に辿る payment -> rental -> inventory -> film テーブルにリレーションがある
これは ER 図がないと、なかなかわかりませんね。
SELECT
c.name AS category, SUM(p.amount) AS total_sales
FROM
(((((payment p
JOIN rental r ON ((p.rental_id = r.rental_id)))
JOIN inventory i ON ((r.inventory_id = i.inventory_id)))
JOIN film f ON ((i.film_id = f.film_id)))
JOIN film_category fc ON ((f.film_id = fc.film_id)))
JOIN category c ON ((fc.category_id = c.category_id)))
GROUP BY c.name
ORDER BY total_sales DESC
レベル 2
次はレベル 2 の問題です。 本当に問題だけを見ると、そんな大変そうには見えないんですが … 。 テーブル設計からやり直して欲しい。
顧客一覧として、次の列を出力する( 599 行)
顧客 ID, 顧客名, 住所, 郵便番号, 電話番号, 都市名, 国名, notes( active かどうか), SID
解答と解説
- 顧客一覧なので customer テーブルがメイン
- 住所などはこれまでと同様に結合でとる
- 問題文の「 notes( active かどうか)」 の処理
- 解答例では if 関数を使って ‘active’ という文字列を返している
SELECT
cu.customer_id AS ID,
concat(cu.first_name,
_utf8mb4' ',
cu.last_name) AS name,
a.address AS address,
a.postal_code AS `zip code`,
a.phone AS phone,
city.city AS city,
country.country AS country,
if(cu.active,
_utf8mb4'active',_utf8mb4'') AS notes,
cu.store_id AS SID
FROM (((
customer cu
JOIN address a ON((cu.address_id = a.address_id)))
JOIN city ON((a.city_id = city.city_id)))
JOIN country ON((city.country_id = country.country_id)))
このあと、レベル 3 の GROUP_CONCAT / CONCAT とサブクエリを使った問題を演習 -> 解説いただき、このコースは修了しました。
まとめ
このコースでは SQL の習得の総仕上げとして、 3 つの「こわい」を克服することを目指しました。
- 複雑でも、こわくない
- 大規模でも、こわくない
- 仕様書がなくても、こわくない
ER 図と業務知識を頼りに推測しながら、目的のデータを辿るのは、なかなか探偵のような気分を味わえ「面白い」と思ったのもつかの間、レベル 2 からは複雑性が増し、 JOIN する順番やテーブルのリレーション、さらに正確には JOIN なのか、 LEFT JOIN なのか知識がおぼつかなくなり、私は SQL マスターにはなれませんでした … 。
ただ DBA が登場するのは、ほとんどが大規模データベースなので、これぐらいができて当たり前の世界か、とそのレベル感を体感できました。
「 SQL を完全に理解した!」という方にはぜひチャレンジいただきたいコースでした!
label SEカレッジを詳しく知りたいという方はこちらから !!
IT専門の定額制研修 月額28,000円 ~/ 1社 で IT研修 制度を導入できます。
年間 670 講座をほぼ毎日開催中!!
SEプラスにしかないコンテンツや、研修サービスの運営情報を発信しています。