演習: 式と関数
bookstore データベースで練習します。最初に USE bookstore; で切り替えてください。
3-a. 在庫金額を計算する
Section titled “3-a. 在庫金額を計算する”books テーブルから、価格が 3000 円以上の書籍について、タイトル、価格、在庫、在庫金額（price * stock）を、在庫金額の多い順に表示してください。在庫金額のカラム名は inventory_value とします。
期待される結果:
| title | price | stock | inventory_value |
|---|---|---|---|
| 達人プログラマー(第2版) | 3520 | 25 | 88000 |
| リファクタリング(第2版) | 4840 | 18 | 87120 |
| プログラミング言語Go | 4180 | 16 | 66880 |
| Clean Code | 4104 | 15 | 61560 |
| 人月の神話 新装版 | 3520 | 12 | 42240 |
| Googleのソフトウェアエンジニアリング | 4840 | 5 | 24200 |
| データ指向アプリケーションデザイン | 5060 | 3 | 15180 |
| ファインマン物理学 I 力学 | 3740 | 4 | 14960 |
| SRE サイトリライアビリティエンジニアリング | 5280 | 2 | 10560 |
ヒント
SELECT 句では列同士の計算結果を取り出せます。AS で結果に名前を付け、ORDER BY でその名前を使えます。
3-b. 著者情報を整形する
Section titled “3-b. 著者情報を整形する”authors テーブルから、国が「イギリス」の著者について、「名前 (国)」の形式で 1 つの文字列として表示してください。カラム名は display、著者 ID の昇順に並べてください。
期待される結果:
| display |
|---|
| アガサ・クリスティー (イギリス) |
| リンダ・グラットン (イギリス) |
| マーチン・ファウラー (イギリス) |
| J.K.ローリング (イギリス) |
| マーティン・クレップマン (イギリス) |
ヒント
CONCAT(文字列1, 文字列2, ...) で文字列を連結できます。
3-c. 売り切りまでの日数を計算する
Section titled “3-c. 売り切りまでの日数を計算する”books テーブルから、在庫が 80 冊以上の書籍について、タイトル、在庫、1 日に 7 冊売れるとした場合の売り切り日数（stock / 7）を小数第 1 位で四捨五入した値を、日数の短い順に表示してください。日数のカラム名は days_to_sell_out とします。
期待される結果:
| title | stock | days_to_sell_out |
|---|---|---|
| 進撃の巨人 1 | 85 | 12.1 |
| ONE PIECE 2 | 100 | 14.3 |
| ONE PIECE 1 | 120 | 17.1 |
ヒント
ROUND(値, 桁数) で四捨五入できます。第 2 引数に 1 を指定すると小数第 1 位まで残ります。
3-d. 発売年を取り出す
Section titled “3-d. 発売年を取り出す”books テーブルから、価格が 3000 円以上の書籍について、タイトル、発売日（published_at）、発売年を、発売年の新しい順、同じ年の中では書籍 ID の昇順に表示してください。発売年のカラム名は published_year とします。
期待される結果:
| title | published_at | published_year |
|---|---|---|
| Googleのソフトウェアエンジニアリング | 2021-11-29 | 2021 |
| 達人プログラマー(第2版) | 2020-11-21 | 2020 |
| リファクタリング(第2版) | 2019-12-01 | 2019 |
| データ指向アプリケーションデザイン | 2019-07-18 | 2019 |
| Clean Code | 2017-12-18 | 2017 |
| SRE サイトリライアビリティエンジニアリング | 2017-08-12 | 2017 |
| プログラミング言語Go | 2016-06-20 | 2016 |
| 人月の神話 新装版 | 2014-04-22 | 2014 |
| ファインマン物理学 I 力学 | 1986-01-08 | 1986 |
ヒント
YEAR(日付) で年を取り出せます。同様に MONTH、DAY で月・日を取り出せます。
3-e. 売上日時を年月形式で表示する
Section titled “3-e. 売上日時を年月形式で表示する”sales テーブルから、customer_id が 1 の顧客の売上について、book_id、売上日時(sold_at)、年月を、売上日時の古い順に表示してください。年月は「YYYY年MM月」形式で、カラム名は year_month とします。
期待される結果:
| book_id | sold_at | year_month |
|---|---|---|
| 50 | 2024-04-25 11:20:00 | 2024年04月 |
| 8 | 2024-08-10 15:50:00 | 2024年08月 |
| 29 | 2024-08-16 16:30:00 | 2024年08月 |
| 44 | 2024-10-11 09:10:00 | 2024年10月 |
| 10 | 2024-11-09 11:45:00 | 2024年11月 |
| 6 | 2025-01-08 10:00:00 | 2025年01月 |
ヒント
DATE_FORMAT(日付, 書式) で日付を指定の書式の文字列に変換できます。%Y は 4 桁の年、%m は 2 桁の月です。
3-f. ISBN から出版者範囲を抽出する
Section titled “3-f. ISBN から出版者範囲を抽出する”ISBN の 5〜8 桁目は出版者を識別する範囲です（実際の長さは可変）。技術書カテゴリ（category_id = 3）の書籍について、タイトル、ISBN、ISBN の 5 文字目から 4 文字を抽出した値を、抽出値の昇順、同じ抽出値の中では書籍 ID の昇順に表示してください。抽出値のカラム名は publisher_code とします。
期待される結果:
| title | isbn | publisher_code |
|---|---|---|
| ファインマン物理学 I 力学 | 9784000077118 | 0000 |
| Clean Code | 9784048930598 | 0489 |
| リファクタリング(第2版) | 9784274224546 | 2742 |
| 達人プログラマー(第2版) | 9784274226298 | 2742 |
| 人月の神話 新装版 | 9784621066089 | 6210 |
| プログラミング言語Go | 9784621300251 | 6213 |
| SQL 第2版 ゼロからはじめるデータベース操作 | 9784798144450 | 7981 |
| アルゴリズム図鑑 | 9784798149776 | 7981 |
| リーダブルコード | 9784873115658 | 8731 |
| データ指向アプリケーションデザイン | 9784873118703 | 8731 |
| Googleのソフトウェアエンジニアリング | 9784873119656 | 8731 |
| SRE サイトリライアビリティエンジニアリング | 9784873117911 | 8731 |
ヒント
SUBSTRING(文字列, 開始位置, 長さ) で文字列の一部を切り出せます。開始位置は 1 から数えます。
3-g. タイトルが長い書籍を抽出する
Section titled “3-g. タイトルが長い書籍を抽出する”一覧画面でタイトルが長いと折り返しが発生する。タイトルの文字数が 20 を超える書籍を、タイトルとその文字数で、文字数の多い順、同じ文字数の場合は書籍 ID の昇順に表示してください。文字数のカラム名は title_length とします。
期待される結果:
| title | title_length |
|---|---|
| ローマ人の物語 1 ローマは一日にして成らず(上) | 25 |
| ローマ人の物語 2 ローマは一日にして成らず(下) | 25 |
| SQL 第2版 ゼロからはじめるデータベース操作 | 24 |
| SRE サイトリライアビリティエンジニアリング | 23 |
| Googleのソフトウェアエンジニアリング | 21 |
ヒント
CHAR_LENGTH(文字列) で文字数を返します。似た LENGTH はバイト数を返すので、日本語を数える場合は CHAR_LENGTH を使います。
3-h. 生年が欠けている場合の代替表示
Section titled “3-h. 生年が欠けている場合の代替表示”authors テーブルから、id が 20 以上の著者について、名前と、生年（NULL の場合は '不明'）を、id の昇順に表示してください。カラム名は birth_year_display とします。
期待される結果:
| name | birth_year_display |
|---|---|
| フレデリック・P・ブルックス Jr. | 1931 |
| アラン・ドノバン | 不明 |
| 石田保輝 | 1988 |
| J.K.ローリング | 1965 |
| ミヒャエル・エンデ | 1929 |
| スティーブン・R・コヴィー | 1932 |
| 岸見一郎 | 1956 |
| ユヴァル・ノア・ハラリ | 1976 |
| ジャレド・ダイアモンド | 1937 |
| 塩野七生 | 1937 |
| 尾田栄一郎 | 1975 |
| 諫山創 | 1986 |
| 鳥山明 | 1955 |
| D・カーネギー | 1888 |
| ナポレオン・ヒル | 1883 |
| マーティン・クレップマン | 不明 |
| タイタス・ウィンタース | 不明 |
| リチャード・P・ファインマン | 1918 |
| ベッツィ・ベイヤー | 不明 |
ヒント
COALESCE(値1, 値2, ...) は、先頭から評価して最初に NULL でない値を返します。IFNULL の上位互換で、引数を 3 つ以上渡せます。
3-i. 著者を世代で分類する
Section titled “3-i. 著者を世代で分類する”authors テーブルから、id が 10 以下の著者について、名前、生年、世代区分を、生年の古い順に表示してください。世代区分は次のルールです。
- 生年が 1900 年未満:
'19世紀以前' - 生年が 1900 年以上 1950 年未満:
'20世紀前半' - 生年が 1950 年以上:
'20世紀後半以降'
世代区分のカラム名は generation とします。
期待される結果:
| name | birth_year | generation |
|---|---|---|
| 松尾芭蕉 | 1644 | 19世紀以前 |
| 夏目漱石 | 1867 | 19世紀以前 |
| アガサ・クリスティー | 1890 | 19世紀以前 |
| ヘミングウェイ | 1899 | 19世紀以前 |
| ピーター・ドラッカー | 1909 | 20世紀前半 |
| 稲盛和夫 | 1932 | 20世紀前半 |
| 村上春樹 | 1949 | 20世紀前半 |
| 東野圭吾 | 1958 | 20世紀後半以降 |
| 伊坂幸太郎 | 1971 | 20世紀後半以降 |
| 又吉直樹 | 1980 | 20世紀後半以降 |
ヒント
CASE WHEN 条件 THEN 値 ... ELSE 値 END で条件分岐できます。WHEN は上から順に評価されるので、範囲が広い条件を後ろに書くと漏れなく分類できます。
3-j. 顧客を地域に分類する
Section titled “3-j. 顧客を地域に分類する”地域別の集計レポートの準備として、都道府県を地域区分に変換します。customers テーブルから、id が 10 以下の顧客について、名前、都道府県、地域区分を、地域区分の昇順、同じ地域区分の中では id の昇順に表示してください。地域区分は次のルールです。
'東京都','神奈川県','千葉県','埼玉県'→'関東''大阪府','京都府','兵庫県'→'関西'- その他 →
'その他'
地域区分のカラム名は region とします。
期待される結果:
| name | prefecture | region |
|---|---|---|
| 山本 玲奈 | 愛知県 | その他 |
| 中村 拓海 | 福岡県 | その他 |
| 加藤 誠 | 北海道 | その他 |
| 田中 健 | 東京都 | 関東 |
| 鈴木 美咲 | 東京都 | 関東 |
| 佐藤 雅子 | 東京都 | 関東 |
| 高橋 翔 | 東京都 | 関東 |
| 渡辺 直樹 | 神奈川県 | 関東 |
| 小林 彩花 | 東京都 | 関東 |
| 伊藤 優奈 | 大阪府 | 関西 |
ヒント
CASE 列 WHEN 値 THEN 結果 ... ELSE 既定値 END の形式では、列が各 WHEN の値と等しいときの結果を返します。等値比較を複数並べるときはこの書き方が簡潔です。