データーサイエンス SQL100本ノック編(SQL1~20問)

はじめに

• SQLの勉強をしないといけないと思っていたところTwitterでバズっていたのでやってみることにしました！
• 実務でSQLは必須のためやって損はないと思ったため
• 何か質問やご指摘あればコメント頂けると嬉しいです！

環境

• M1 MacBook Air
• Docker for Desktop
• postgresql
• dbver (SQLクライアントツール使用)
• ↓実践環境は以下の通りです
  

問題

• 早速問題を解いていきます。
• 100問あるので順番に投稿していきます。

問１

S-001: レシート明細データ（receipt）から全項目の先頭10件を表示し、どのようなデータを保有しているか目視で確認せよ。

select * from receipt limit 10;

• select：カラムを選択する。*でカラム全選択。
• from：選択したカラムがあるテーブルを選択する。
• limit：表示するデーターの行数を指定する

問2

S-002: レシート明細データ（receipt）から売上年月日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示せよ。

select sales_ymd, customer_id, product_cd, amount
from receipt
limit 10;

• select：カラムを選択する
• from：テーブルを選択
• limit：表示するデーターの行数を指定する

問3

S-003: レシート明細データ（receipt）から売上年月日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、10件表示せよ。ただし、sales_ymdsales_dateに項目名を変更しながら抽出すること。

select 
sales_ymd as sales_ymdsales_date,
customer_id, 
product_cd, 
amount
from receipt
limit 10;

• select：カラムを選択する
• as：カラム名の変更
• from：テーブルを選択
• limit：表示するデーターの行数を指定する

問4

S-004: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の条件を満たすデータを抽出せよ。• 顧客ID（customer_id）が"CS018205000001"

select 
sales_ymd as sales_ymdsales_date,
customer_id,
product_cd,
amount
from receipt
where customer_id = 'CS018205000001'

• select：カラムを選択する
• as：カラム名の変更
• from：テーブルの指定
• limit：表示するデーターの行数を指定
• where：カラムの条件を指定

問5

S-005: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

• 顧客ID（customer_id）が"CS018205000001"
• 売上金額（amount）が1,000以上

select 
sales_ymd,
customer_id,
product_cd,
amount
from receipt
where customer_id = 'CS018205000001'
and amount >= 1000;

• select：カラムを選択
• from：テーブルを指定
• where：カラムの条件を指定(customer_idが'CS018205000001'のみ)
• and：条件が複数になる場合

問6

S-006: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上数量（quantity）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

• 顧客ID（customer_id）が"CS018205000001"
• 売上金額（amount）が1,000以上または売上数量（quantity）が5以上

select 
sales_ymd, 
customer_id, 
product_cd,
amount
from receipt
where customer_id = 'CS018205000001'
and (
		amount >= 1000 or quantity >= 5
	);

• select：カラムを指定
• from：：テーブルを指定
• where：カラムの条件を指定(where句では()やorが使用できる)

問7

S-007: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。

• 顧客ID（customer_id）が"CS018205000001"
• 売上金額（amount）が1,000以上2,000以下

select 
sales_ymd,
customer_id,
product_cd,
amount
from receipt
where customer_id = 'CS018205000001'
and amount between 1000 and 20000;

• select：カラムを指定
• from：テーブルを指定
• where：カラムの条件式を設定(andや()も使用可能)
• between：数値などの範囲を指定できる

問8

S-008: レシート明細データ（receipt）から売上日（sales_ymd）、顧客ID（customer_id）、商品コード（product_cd）、売上金額（amount）の順に列を指定し、以下の全ての条件を満たすデータを抽出せよ。
• 顧客ID（customer_id）が"CS018205000001"
• 商品コード（product_cd）が"P071401019"以外

select 
sales_ymd,
customer_id,
product_cd,
amount
from receipt
where customer_id = 'CS018205000001'
and product_cd != 'P071401019';

• select：カラム指定
• from：テーブルを指定
• where：条件式を設定
• ！=：否定の条件

問9

S-009: 以下の処理において、出力結果を変えずにORをANDに書き換えよ。
SELECT * FROM store WHERE NOT (prefecture_cd = '13' OR floor_area > 900)

select * from store where prefecture_cd != '13' and floor_area < 900;

問10

S-010: 店舗データ（store）から、店舗コード（store_cd）が"S14"で始まるものだけ全項目抽出し、10件表示せよ。

select store_cd
from store
where store_cd like 'S14%'
limit 10;

# like '文字列%'： 前方一致で検索
# limit： 行数の指定

• where like ‘文字列%’：前方一致で検索
• %：0文字以上の任意の文字列を取得
• _：任意の1文字表す

問11

S-011: 顧客データ（customer）から顧客ID（customer_id）の末尾が1のものだけ全項目抽出し、10件表示せよ。

select customer_id
from customer
where customer_id like '%1'
limit 10;

• where lile ‘%文字列’：後方一致で検索
• %：0文字以上のに任意の文字列を取得
• _：任意の1文字表す

問12

S-012: 店舗データ（store）から、住所 (address) に"横浜市"が含まれるものだけ全項目表示せよ。

select address
from store
where  address like '%横浜市%';

• where カラム like ‘%文字列%’：部分検索

問13

S-013: 顧客データ（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まるデータを全項目抽出し、10件表示せよ。

select *
from customer
where status_cd ~'^[A-F]'
limit 10;

• ~：正規表現の時に使用する
• ^：文字列の先頭に一致(AからFの先頭)

問14

S-014: 顧客データ（customer）から、ステータスコード（status_cd）の末尾が数字の1〜9で終わるデータを全項目抽出し、10件表示せよ。

select *
from customer
where status_cd ~ '[1-9]$'
limit 10;

• $：末尾で終わるデーターを取得[1~9]の末尾を取得
• limit：件数を取得
• $：文字列の末尾に一致(1から9)

問15

S-015: 顧客データ（customer）から、ステータスコード（status_cd）の先頭がアルファベットのA〜Fで始まり、末尾が数字の1〜9で終わるデータを全項目抽出し、10件表示せよ。

select *
from customer
where status_cd ~ '^[A-F].*[1-9]$'
limit 10;

• ^：文字列の先頭に一致
• $：文字列の末尾に一致
• .*：0文字以上の任意の文字列

問16

---

S-016: 店舗データ（store）から、電話番号（tel_no）が3桁-3桁-4桁のデータを全項目表示せよ。

select *
from store
where tel_no ~ '[0-9]{3}-[0-9]{3}-[0-9]{4}';

• [0-9]{3}-[0-9]{3}-[0-9]{4}
  ↓
  「(0~9)の文字列を3回繰り返す」-「(0~9)の文字列を3回繰り返す」-「(0~9)の文字列を4回繰り返す」

問17

S-017: 顧客データ（customer）を生年月日（birth_day）で高齢順にソートし、先頭から全項目を10件表示せよ。

select *
from customer
order by birth_day
limit 10;

• order by：指定したカラムをソート順に並べる
• limit：行選択した分取得する

問18

S-018: 顧客データ（customer）を生年月日（birth_day）で若い順にソートし、先頭から全項目を10件表示せよ。

select *
from customer
order by birth_day desc
limit 10;

• order by カラム名 desc：ソート順に並べる(降順)
• limit：行選択指定

問19

S-019: レシート明細データ（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭から10件表示せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合は同一順位を付与するものとする。

select customer_id, amount,
rank() over(
	order by amount desc
) as ranking
from receipt
limit 10;

• rank() over：rank関数は同順位に対して順位をつける
• order by：ソート順にする(降順)
• as：名前をつける
• limit：取得件数を選択

問20

S-020: レシート明細データ（receipt）に対し、1件あたりの売上金額（amount）が高い順にランクを付与し、先頭から10件表示せよ。項目は顧客ID（customer_id）、売上金額（amount）、付与したランクを表示させること。なお、売上金額（amount）が等しい場合でも別順位を付与すること。

SELECT
    customer_id,
    amount,
    ROW_NUMBER() OVER(ORDER BY amount DESC) AS ranking 
FROM receipt
LIMIT 10;

• row_number() over：全てのデーターに一意に番号をつける
• order by desc：降順に並び替える
• limit：取得件数を選択

参考資料