SQL演習

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文について，問い合わせ結果が変わらないようORをANDに書き換えよ．

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
Contents