プログラミング超初心者がSQL入門編を学び、次へ進もうとすると忘れるため備忘録目的で記事を書きました。その第2弾です。
具体的な取り組み方法1。
取り組むときの注意2。
練習帳で使うテーブル
《問題①》 asuka_purchasesテーブルから重複するデータを除いたnameカラムのデータを取得する
《答え①》
ex.sql
SELECT DISTINCT name
FROM asuka_purchases;
| name |
|---|
| 煙草 |
| お菓子 |
| 芋焼酎 |
| 焼酎 |
| みかん |
《問題②》 asuka_purchasesテーブルからnameとpriceカラム、そしてpriceカラムの価格データに消費税(10％)をかけた値を取得する
《答え②》
ex.sql
SELECT name, price*1.1
FROM asuka_purchases;
| name | price | price*1.1 |
|---|---|---|
| 煙草 | 600 | 660 |
| お菓子 | 150 | 165 |
| 芋焼酎 | 300 | 330 |
| お菓子 | 200 | 220 |
| 焼酎 | 1000 | 1100 |
| みかん | NULL | NULL |
数値とNULLを四則演算するとNULLになる
《問題③》 asuka_purchasesテーブルからお菓子の合計金額を取得する
《答え③》
ex.sql
SELECT SUM(price)
FROM asuka_purchases
WHERE name = "お菓子";
| SUM(price) |
|---|
| 350 |
《問題④》 asuka_purchasesテーブル内のお菓子の平均金額を取得する
《答え④》
ex.sql
SELECT AVG(price)
FROM asuka_purchases
WHERE name = "お菓子";
| AVG(price) |
|---|
| 175 |
《問題⑤》asuka_purchasesテーブルのレコードの数とpriceカラムのデータ数、そしてお菓子のデータ数をそれぞれ取得する
《答え⑤》
ex.sql
SELECT COUNT(*)
FROM asuka_purchases;
/* 『price』カラムのデータ数 */
SELECT COUNT(price)
FROM asuka_purchases;
/* 『price』カラムのデータ数 */
SELECT COUNT(*)
FROM asuka_purchases;
WHERE name = "お菓子";
| COUNT(*) |
|---|
| 6 |
| COUNT(price) |
|---|
| 5 |
| COUNT(*) |
|---|
| 2 |
COUNT関数でカラム名を指定した場合、nullになっているデータの数は計算に含まれない
《問題⑥》priceカラムのデータの内、一番高い価格は何円？また、お菓子の中で一番安い金額は？
《答え⑥》
ex.sql
SELECT MAX(price)
FROM asuka_purchases;
/* 『お菓子』の中で一番安い価格は？ */
SELECT MIN(price)
FROM asuka_purchases
WHERE name = "お菓子";
| MAX(price) |
|---|
| 1000 |
| MIN(お菓子) |
|---|
| 150 |
《問題⑦》日付毎にグループ化して、各グループごとの合計金額を集計する
《答え⑦》
ex.sql
SELECT SUM(price), date
FROM asuka_purchases
GROUP BY date;
| SUM(price) | date |
|---|---|
| 1050 | 2023-05-01 |
| 200 | 2023-05-16 |
| 1000 | 2023-05-17 |
| 2023-05-18 |
GROUP BYについて
① GROUP BYを用いる場合SELECTで使えるのは、集計関数とGROUP BYに指定しているカラム名のみ!!
② 今回『2023-05-18』のグループでNULL(みかん)のみだったが、この日付に購入した項目が他にもあり、例えば焼酎1000円もあるとすると、みかんのNULLの値は無視されて合計金額は「1000」で出力される。
《問題⑧》日付毎にグループ化して、各グループごとの購入数を取得
《答え⑧》
ex.sql
SELECT COUNT(name), date
FROM asuka_purchases
GROUP BY date;
| COUNT(name) | date |
|---|---|
| 3 | 2023-05-01 |
| 1 | 2023-05-16 |
| 1 | 2023-05-17 |
| 1 | 2023-05-18 |
《問題⑨》日付と購入品ごとにグループ化した合計金額を取得
《答え⑨》
ex.sql
SELECT SUM(price), date, name
FROM asuka_purchases
GROUP BY date, name;
今回、同日中に複数の同じ項目を購入していないため、元データ(asuka_purchasesテーブル)と同じ結果がでる。
《問題⑩》nameがお菓子であるレコードを検索し、日付毎にグループ化し、各グループのprice合計とdateカラムを取得する。
《答え⑩》
ex.sql
SELECT SUM(price), date
FROM asuka_purchases
WHERE name = "お菓子"
GROUP BY date;
| SUM(price) | date |
|---|---|
| 150 | 2023-05-01 |
| 200 | 2023-05-16 |
《問題⑪》 ⑨でWHEREを使ってcategoryが『飲み物』であるデータの場合の日付と合計価格、購入品(name)を取得する
《答え⑪》
ex.sql
SELECT SUM(price), date, name
FROM asuka_purchases
WHERE category = "飲み物"
GROUP BY date, name;
| SUM(price) | date | name |
|---|---|---|
| 300 | 2023-05-01 | 芋焼酎 |
| 1000 | 2023-05-17 | 焼酎 |
《問題⑫》日付と購入品毎の合計金額のうち、1000円以上の合計データのみを取得する
《答え⑫》
ex.sql
SELECT SUM(price), date, name
FROM asuka_purchases
GROUP BY date, name
HAVING SUM(price) >= 1000;
| SUM(price) | date | name |
|---|---|---|
| 1000 | 2023-05-17 | 焼酎 |
【実行される順番】
《問題⑬》categoryでグループ化し、各カテゴリー毎の金額合計とcategoryカラムのデータを取得
《答え⑬》
ex.sql
SELECT SUM(price),category
FROM asuka_purchases
GROUP BY category;
| SUM(price) | category |
|---|---|
| 600 | 嗜好品 |
| 350 | 食べ物 |
| 1300 | 飲み物 |
《問題⑭》WHEREでcategoryが『飲み物』であるレコードを検索し、商品(name)毎にグループ化し、priceカラムの合計と商品(name)を取得する
《答え⑭》
ex.sql
SELECT SUM(price), name
FROM asuka_purchases
WHERE category = "飲み物"
GROUP BY name;
| SUM(price) | name |
|---|---|
| 300 | 芋焼酎 |
| 1000 | 焼酎 |
-
回答用のファイル(mdファイル)を別途用意して練習帳(この記事)のコードをコピーする。回答用の中にある《答え》の部分を《回答欄》に置き換え、すべてのコードを削除。回答用のファイルをVSコードで開き、コードを書く。やり方はQiitaの【Markdown記法 チートシート】を活用してMarkdownをマスターする！《答え編》と同じ要領です。 ↩
-
mdファイルに書いているのでMarkdownと異なり、コードを間違えても気づきにくいです。 ↩