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

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


## 環境
- M1 MacBook Air
- Docker for Desktop
- postgresql
- dbver (SQLクライアントツール使用)
- ↓実践環境は以下の通りです
![スクリーンショット 2023-02-18 17.04.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/949345/6b07c488-8105-c0a9-eb35-48223de30d5f.png)

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

## 問１

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

```sql
select * from receipt limit 10;
```

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

## 問2

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

```sql
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に項目名を変更しながら抽出すること。
> 

```sql
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"
> 

```sql
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以上

```sql
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以上

```sql
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以下

```sql
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"以外
> 

```sql
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)`
> 

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

## 問10

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

```sql
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件表示せよ。
> 

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

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

## 問12

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

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

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

## 問13

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

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

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

## 問14

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

```sql
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件表示せよ。
> 

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

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

### 問16

---

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

```sql
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件表示せよ。
> 

```sql
select *
from customer
order by birth_day
limit 10;
```

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

## 問18

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

```sql
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）が等しい場合は同一順位を付与するものとする。
> 

```sql
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）が等しい場合でも別順位を付与すること。
> 

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

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


## 参考資料
https://github.com/The-Japan-DataScientist-Society/100knocks-preprocess

https://ken-info.com/datascience-100knocks/

https://qiita.com/Ryo-Nakano/items/8961c956babaf0976b4a