7. SQL演習#

SQLの基本を体に染みこませるために,本演習では細かな課題「ノック」を大量に解く. 架空のショッピングサイトVirtualStoreにおける商品管理,購買履歴管理のために運用されている関係データベースにアクセスできるとの想定の下,様々なデータ要求に応えるためのSQL文を書く練習(ノック)を行う.

本演習では,関係データベースの管理システム(DBMS)の中でも環境構築がカンタンで軽量なDBMSであるSQLiteを用いる. 演習で用いる関係データベース(virtual_store)には,下記テーブルが格納されている:

  • receipt: レシート明細に関するテーブル

  • store: 店舗情報に関するテーブル

  • product: 商品情報に関するテーブル

  • category: 商品カテゴリに関するテーブル

これらテーブルに対してSQL文を実行し,データベースに対する問い合わせの練習を行う. ノックの数は合計25である. どのノックも前章までに解説した内容に即したものになっている. 適宜過去資料を振り返りながら,各ノックに取り組むこと.

なお,演習を始める前に,必ずJupyter NotebookもしくはGoogle Colaboratory上で以下のコードを実行すること. 演習で用いるSQLiteデータ(virtual_store.db)のダウンロード,およびSQLiteの実行環境設定を自動で済ませることができる.

import os

# 演習で用いるデータのダウンロード
if not os.path.isfile('data/virtual_store.db'):
    os.makedirs('data', exist_ok=True)
    !wget -P data https://github.com/hontolab-courses/database-lecturenote/raw/main/content/exercise/data/virtual_store.db

!pip install jupysql

%load_ext sql
%config SqlMagic.feedback = 0
%sql sqlite:///data/virtual_store.db

7.1. 演習で用いるデータおよび課題の出典について#

本演習課題で用いるデータは,データサイエンティスト協会スキル定義委員の「データサイエンス100本ノック(構造化データ加工編)」で配布されているデータを,わたし山本がSQLite形式に変換したものである. また,演習内容(ノック)はデータサイエンス100本ノック(構造化データ加工編)に収録されている演習課題を,本講義向けにアレンジしたものである.

下記のとおり,元データの著作権やライセンスは,MITライセンスに従う.

Copyright (c) 2020 The Japan DataScientist Society
Released under the MIT license
https://raw.githubusercontent.com/The-Japan-DataScientist-Society/100knocks-preprocess/master/LICENSE

Important

レポート課題1(SQL)

本ページで出題されているすべての課題を解きなさい. 解答期日,解答方法等については下記を参照すること.

  • 設問数:25

  • 成績評価における本課題の割合:20%(20点)

  • 評価方法:SQL文の実行結果をもとに,1問1点で採点します.ただし,20点以上のスコアは20点とカウントします

  • 解答〆切り:2024年6月16日 (日)23:59( 解答は締め切りました

  • 解答提出方法:解答用サイトSQL Autograder経由

  • 備考:〆切り以降に提出された解答は,本人の責めに帰さない限り,別途受理します.

7.2. SQL Knock 25連発#

7.2.1. Knock 1: 選択#

レシート明細のテーブル(receipt)から先頭10件を表示するSQL文を書け.なお,表示する列はreceiptテーブルがもつ全列(sales_ymd, sales_epoch, …)とする.また,列の表示順はreceiptテーブルで定義された列の順序に従うこと.

%%sql

7.2.2. Knock 2: 射影1#

レシート明細のテーブル(receipt)から先頭10件を表示するSQL文を書け.ただし,表示する列およびその表示順序は売上日(sales_ymd),顧客ID(customer_id),商品コード(product_cd),売上金額(amount)とする.

%%sql

7.2.3. Knock 3: 射影2#

レシート明細のテーブル(receipt)から先頭10件を表示するSQL文を書け.ただし,表示する列およびその表示順序は売上日(sales_ymd),顧客ID(customer_id),商品コード(product_cd),売上金額(amount)とする.また,列sales_ymdは列名をsales_dateに変更すること.

%%sql

7.2.4. Knock 4: 選択2#

レシート明細のテーブル(receipt)から以下の条件を満たすレコードをすべて抽出するSQL文を書け.

  • 顧客IDが”CS018205000001”

ただし,表示する列およびその表示順序は売上日(sales_ymd),顧客ID(customer_id),商品コード(product_cd),売上金額(amount)とする.

%%sql

7.2.5. Knock 5: 選択3#

レシート明細のテーブル(receipt)から以下の条件を満たすレコードをすべて抽出するSQL文を書け.

  • 顧客IDが”CS018205000001”

  • 売上金額が1000以上

ただし,表示する列およびその表示順序は売上日(sales_ymd),顧客ID(customer_id),商品コード(product_cd),売上金額(amount)とする.

%%sql

7.2.6. Knock 6: 選択4#

レシート明細のテーブル(receipt)から以下の条件を満たすレコードをすべて抽出するSQL文を書け.

  • 顧客IDが”CS018205000001”

  • 売上金額が1000以上2000以下

ただし,表示する列およびその表示順序は売上日(sales_ymd),顧客ID(customer_id),商品コード(product_cd),売上金額(amount)とする.

%%sql

7.2.7. Knock 7: 選択5#

レシート明細のテーブル(receipt)から以下の条件を満たすレコードをすべて抽出するSQL文を書け.

  • 顧客IDが”CS018205000001”

  • 売上金額が1000以上または売上数量が5以上

ただし,表示する列およびその表示順序は売上日(sales_ymd),顧客ID(customer_id),商品コード(product_cd),売上数量(quantity),売上金額(amount)とする.

%%sql

7.2.8. Knock 8: 選択6#

レシート明細のテーブル(receipt)から以下の条件を満たすレコードをすべて抽出するSQL文を書け.

  • 顧客IDが”CS018205000001”

  • 商品コードが”P071401020”以外

ただし,表示する列およびその表示順序は売上日(sales_ymd),顧客ID(customer_id),商品コード(product_cd),売上金額(amount)とする.

%%sql

7.2.9. Knock 9: 選択7#

下記SQL文について,問い合わせ結果が変わらないようORANDに書き換えよ.

SELECT 
    *
FROM
    store
WHERE
    NOT (prefecture_cd = 13 OR floor_area > 900);
%%sql

7.2.10. Knock 10: LIKE句1#

店舗テーブル(store)から店舗コード(store_cd)が”S14”で始まるものレコードのみを抽出し,その先頭10件を表示するSQL文を書け.ただし,表示する列はstoreテーブルにある全項目とし,その表示順序もstoreテーブルに定義された順に従うとする(つまり,store_cd, store_name, …).

%%sql

7.2.11. Knock 11: LIKE句2#

店舗テーブル(store)から横浜市にある店舗に関するレコードのみを抽出し,その先頭10件を表示するSQL文を書け.ただし,表示する列はstoreテーブルにある全項目とし,その表示順序もstoreテーブルに定義された順に従うとする(つまり,store_cd, store_name, …).

%%sql

7.2.12. Knock 12: ソート1#

顧客テーブル(customer)のレコードを高年齢順にソートし,先頭10レコードを表示するSQL文を書け.ただし,表示する列はcustomerテーブルにある全項目とし,その表示順序もcustomerテーブルに定義された順に従うとする(つまり,customer_id, customer_name, …).

%%sql

7.2.13. Knock 13: ソート2#

顧客テーブル(customer)のレコードを若い順(同じ年齢の場合は誕生日が遅い順)にソートし,先頭10レコードを表示するSQL文を書け.ただし,表示する列はcustomerテーブルにある全項目とし,その表示順序もcustomerテーブルに定義された順に従うとする(つまり,customer_id, customer_name, …).

※ヒント: ORDER BYにはソートの軸となる属性を複数指定できる

%%sql

7.2.14. Knock 14: 集計1#

レシート明細テーブル(receipt)のレコード数を求めるSQL文を書け.

%%sql

7.2.15. Knock 15: 集計2#

レシート明細テーブル(receipt)中の顧客ID(customer_id)の総数(重複を除く)を求めるSQL文を書け.

%%sql

7.2.16. Knock 16: 集約演算1#

レシート明細テーブル(receipt)に対し,店舗コード(store_cd)ごとに売上金額の合計(total_amount)と売上数量の合計(total_quantity)を計算するSQL文を書け.なお,結果のレコードは売上金額の合計値の降順で並べ替え,先頭から10件を表示せよ.また,表示する列の順序は,店舗コード,売上金額の合計,売上数量の合計の順とする.

%%sql

7.2.17. Knock 17: 集約演算2(やや難)#

レシート明細テーブル(receipt)に対し,顧客ID(customer_id)ごとに最も新しい売上日と最も古い売り上げ日を求め,それらが異なる顧客IDを求めるSQL文を書け.なお,結果レコードは顧客IDの昇順に並びかえ,先頭の10件を表示するようにせよ.また,表示する項目とその表示順序は,顧客ID,最も新しい売り上げ日(latest_sales_ymd),最も古い売り上げ日(oldest_sales_ymd)とする.

%%sql

7.2.18. Knock 18: 集約演算3#

レシート明細テーブル(receipt)に対し,店舗コード(store_cd)ごとにレシート1つあたりの売上金額(amount)の平均を計算し,その平均売り上げ金額の上位5件の店舗を表示するSQL文を書け.なお,表示する項目とその表示順序は,店舗コード,平均売上金額とする.

%%sql

7.2.19. Knock 19: 集約演算4#

レシート明細テーブル(receipt)に対し,店舗コード(store_cd)ごとに売上金額(amount)の総額を計算し,売り上げ総額が800,000以上の店舗を売り上げ総額順に表示するSQL文を書け.なお,表示する項目とその表示順序は,店舗コード,売り上げ総額とする.

%%sql

7.2.20. Knock 20: 結合1#

レシート明細テーブル(receipt)と店舗テーブル(store)を内部結合し,店舗テーブルの店舗名(store_name)とレシート明細テーブルの全項目を表示するSQL文を書け.なお,結果レコードはsales_ymdの昇順に並び替え,その先頭の10件を表示するようにせよ.また,列の表示順序は,店舗名,レシート明細テーブルで定義された列の順序とする(つまり,store_name, sales_ymd, sales_epoch…).

%%sql

7.2.21. Knock 21: 結合2#

商品テーブル(product)とカテゴリテーブル(category)を内部結合し,カテゴリテーブルの小区分名(category_small_name)および商品テーブルの全項目を表示するSQL文を書け.なお,結果レコードはproduct_cdの昇順で並び替え,その先頭の10件を表示するようにせよ.また,列の表示順序は,カテゴリテーブルの小区分名,商品テーブルで定義された列の順序とする(つまり,category_small_name, product_cd, category_major_cd…).

%%sql

7.2.22. Knock 22: 結合3(難)#

顧客テーブル(customer)とレシート明細テーブル(receipt)から,各顧客ごとの売上金額合計を求め,先頭10件を表示するSQL文を書け.ただし,以下の条件を満たすること:

  • 売上実績がない顧客については売上金額を0として表示させること.

  • 顧客は性別コード(gender_cd)が女性(1)であるものを対象とし,非会員(顧客IDが”Z”から始まるもの)は除外する

  • 表示項目とその表示順は顧客ID(customer_id),顧客名(customer_name),売上金額合計とする

  • 表示するレコードは顧客IDの昇順とする

※ヒント: IFNULL(変数1, 0)関数は,変数1が空値(None)のとき0に置き換える

%%sql

7.2.23. Knock 23: 四則演算と集約演算#

レシート明細テーブル(receipt)と顧客テーブル(customer)を結合し,年代(ageから計算)ごとの売上金額(amount)の合計値を表示するSQL文を書け.ただし,表示するレコードは年代の昇順に並び替えること.また表示する項目およびその表示順は年代,売上総額とする.

なお,年代は10歳ごとの階級とすること.

%%sql

7.2.24. Knock 24: 副問い合わせ1#

レシート明細テーブル(receipt)を利用して顧客ID(customer_id)ごとの売上総額を計算し,その平均(avg_total_amount)を求めるSQL文を書け.ただし,顧客IDが”Z”から始まるのものは非会員を表すため,除外して計算すること.

%%sql

7.2.25. Knock 25: 副問い合わせ2(難)#

レシート明細テーブル(receipt)を利用して全商品の中で売上総額が上位3位の商品コード(product_cd)を求め,各店舗(store_cd)における3つの商品の売上総額を求めるSQL文を書け.ただし,結果はstore_cd列(昇順)を第1キー,product_cd列(昇順)を第2キーとしてソートし,その先頭の30件を表示するようにせよ.また,表示する項目とその表示順序は,店舗コード,商品コード,売上総額とする.

%%sql