海外で話題のSQL実践練習サービス「SQL Murder Mystery(SQL マーダーミステリー)」をやってみた

2022-06-02 2023-08-22

今回の記事では「SQL Murder Mystery (SQL マーダーミステリー)」について
下記を中心に紹介したいと思います。

☑️　SQLを使ってどんなことができるのかイメージがつかない
☑️　SQLのチュートリアルを一通りやったが、どう使うのかよくわからない　
☑️　必要なデータに対してどのようなクエリを発行すべきかわからない

ベトナムのダナンで働いております竹内です。
最近海外のテック系YouTuberの方の動画を見ることにハマっています。
そんな中、とある方がSQLの学習者向けでSQL Murder Mystery（SQLマーダーミステリー）というSQL学習サービスを紹介していました。
面白そうと思い私もやってみたところ、SQLの実践練習教材として非常に良質だと思い今回記事にして読者の皆様にもシェアできればと思います。
本記事ではSQL マーダーミステリーの紹介のみでなく、どうすれば犯人をみつけられるかの攻略法にも焦点を当てて解説していきます。

SQL Murder Mystery(SQL マーダーミステリー)とは？

こちらのサービスはノースウェスタン大学のKnight Labコミュニティに所属していた研究員Joon ParkさんとCathy Heさんによって開発されたマーダーミステリー型SQL学習サービスです。
マーダーミステリーとは殺人事件の犯人をヒントを元に探し出していく推理ゲームのことです。

SQLマーダーミステリーでは各テーブル内に存在するヒントをSQLを通して取得し、その情報を元に犯人を推理していきます。

SQL Murder Mystery(SQL マーダーミステリー)で学べる内容とは？

殺人事件の犯人を特定するためにはクエリを発行（データベースに対して問合せ）し、犯人特定に繋がるヒントを取得する必要があります。
では実際にSQLマーダーミステリーを通じて学習できる内容について順番に解説していきます。

ER図から意図したデータの取得

ER図（Entity Relationship Diagram）とはデータベース上の各テーブルの関連図のことです。
ER図では下記情報が記載されています。

テーブル名
カラム名
主キー
外部キー

これらの情報を見て「どのようなデータが各テーブルに格納されているのか」を知ることができます。
SQLマーダーミステリーで犯人を見つけるためには、「犯人特定のための有力情報を取得する」必要があります。
ER図を確認して「どのテーブルのデータを取得する必要があるのか」を意識しながらクエリの発行ができるようになるかと思います。

テーブルの結合によるデータの取得

犯人の有力な情報を取得するためには、複数のテーブル情報を結合しデータを取得する必要があります。
複数のテーブルを結合し情報を取得する場合の参考例を下記に記載します。

インタビューを受けた人で且つ運転免許証を所持している人の一覧を取得したい場合
→interviewテーブルとpersonテーブルとdrivers_licenseテーブルを結合する

このように自分が欲しいデータに対して必要なテーブルの結合を考える癖をSQLマーダーミステリーを通じて身につけられるようになると思います。

主キー、外部キーの理解

テーブル結合によるデータ取得を行う場合には主キー・外部キーの理解も必要となります。

下記が主キー、外部キーそれぞれの概要説明になります。

主キー（Primary Key）: テーブル内の各レコードを一意に識別するための制約
外部キー (Foreign Key)：テーブル間のデータの整合性を保つための制約

実際にSQLマーダーミステリーで主キー、外部キーを意識するシーンとしては「運転免許証をもっている人の一覧を取得」したい場合です。

この場合drivers_licenseテーブルのidが主キー、personテーブルのlicense_idが外部キー扱いとなります。

また、テーブルを結合する場合は結合するテーブル同士の主キー、外部キーを結合対象として指定する必要があります。

そのため、SQLマーダーミステリーを通して「テーブル結合する場合の主キー、外部キーはこのカラムを指定すれば良いな」とクエリ作成時のイメージがつきやすくなると思います。

SQL Murder Mystery(SQL マーダーミステリー)をやってみよう！

それでは早速SQLマーダーミステリーに挑戦してみましょう。
もしもSQL未経験の場合はSQLのウォークスルーページにてSQLの復習ができます。

各テーブルの情報を整理する

どのようなテーブルがあるのかER図を元に整理していきましょう。

あまり細かく書くとネタバレになってしまうため、テーブル名と概要のみ羅列します。

interview : インタビュー時の回答
get_fit_now_check_in : フィットネスクラブへのチェックイン時間
get_fit_now_member : フィットネスクラブ会員情報
facebook_event_checkin : Facebookのイベント名
person : 住民情報
drivers_license : 免許情報
crime_scene_report : 犯罪レポート
income : 収入情報
solution : 犯人特定のためのヒント（一回以上Insertクエリを行った場合に閲覧可能）

犯人特定のためのコツとしては、「犯人特定に繋がる有力情報はどのテーブルを参照すれば良いか」を意識してみて下さい。

クエリを実行し取得したデータを分析する

では実際にクエリを実行してヒントを取得してみましょう。

クエリの実行を行うにはSQLエディタ部分に任意のクエリを入力し、Runボタンを押下すると実行できます。

クエリが正常に実行されると、SQLエディタ下にテーブル内のデータが表示されます。
このように他のテーブルにもクエリを実行し中身をみていくことで、少しずつ犯人の影が見えてくるようになると思います。

推理した犯人を報告する

犯人を特定できるのに十分な証拠が揃ったら、「Check your solution」下の赤下線部の値を容疑者の名前に変更し、INSERTクエリを実行しましょう。

正解かどうかはクエリの実行結果から知ることができます。

マーダーミステリーを攻略するためのコツ

実際にやってみると、犯人特定に繋がるヒントがなかなか出てこずネタバレを見たくなってしまいます。

ただ、マーダーミステリーは答えをみてしまうと推理ゲームの醍醐味が失われてしまうので、できれば自力で犯人を特定できた方が楽しめると思います。

ネタバレしない程度で犯人特定のヒントを順番に解説していきます。

犯罪発生時の情報を整理する

まずは殺人事件が発生した日の情報について確認しましょう。

まずはExperienced SQL Sleuths Start Here（熟練の探偵はこちらからスタートしよう）を読み今回の事件の発生日時と場所を確認します。

You vaguely remember that the crime was a murder that occurred sometime on Jan.15, 2018 and that it took place in SQL City

おぼろげに覚えているのは2018年1月15日あたりに殺人事件がSQL Cityで発生したということだ。

こちらのヒントを犯人特定のために非常に有力な情報となるためどこかにメモしておきましょう。

事件発生日時：2018年1月15日
事件の種類：殺人
事件発生場所：SQL City

こちらの情報を使って下記のようなクエリを実行すると何かしらのヒントを得られるかもしれません。

crime_scene_reportテーブルのdateを2018年1月15日に指定してみる
crime_scene_reportテーブルのtypeを殺人(murder)に指定してみる
crime_scene_reportテーブルのcityをSQL cityに指定してみる

結合すべきテーブルを整理する

二つ目の攻略のコツは結合が必要なテーブルを整理することです。
ER図を確認し、犯人特定のための情報はどのテーブルを組み合わせたら取得できるか考えてみましょう。

コツとしては自分が取得したい情報を考えてから、結合が必要なテーブルを羅列することです。

自動車免許を所有している住民情報 : personテーブル + drivers_licenseテーブル
フィットネスクラブに通っている住民情報 : personテーブル + get_fit_now_memberテーブル

以上が、「　SQL Murder Mystery(SQL マーダーミステリー)　」についての紹介でした。
当ブログの他の記事でもダナンの魅力や生活の様子などを発信していますので、興味がある方へぜひ参考になれば幸いです！

Enlytについて

株式会社Enlytはベトナムに開発拠点SupremeTechを持ち、これまで50以上の開発プロジェクトを行ってきました。ベトナムと日本のグローバルなチームで、数多くのプロジェクトを成功に導いてきました。

お客様の納得のいくまで、共に開発させていただき、アイデアを最高のかたちにサービス化いたします。

オフショア開発についてのお悩みやご相談がございましたら、下記ボタンより気軽にお問合せください！

システム開発

エンジニア