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