データベース・プログラミング - Exercise- SQL - Part2

データベースの作成と準備

  • my_rpgデータベースに接続してください。

テーブルの作成

  • 以下の仕様に従ってitem_categoryテーブルを作成してください。
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
  • 以下の仕様に従ってitemテーブルを再度、作成してください。
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | NO   | PRI | NULL    |       |
| name        | varchar(100) | YES  |     | NULL    |       |
| effect      | int(11)      | YES  |     | NULL    |       |
| price       | int(11)      | YES  |     | NULL    |       |
| category_id | int(11)      | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

category_idが追加されている点に注意してください。


レコードの作成

  • 以下の結果に示すとおり、item_categoryテーブルにレコードを作成してください。
+----+--------+
| id | name   |
+----+--------+
|  1 | Weapon |
|  2 | Armor  |
|  3 | Shield |
|  4 | Helmet |
+----+--------+
  • 以下の結果に示すとおり、itemテーブルにレコードを作成してください。
+----+--------------+--------+-------+-------------+
| id | name         | effect | price | category_id |
+----+--------------+--------+-------+-------------+
|  1 | Excalibur    |    100 | 10000 |           1 |
|  2 | Gungnir      |     90 |  9000 |           1 |
|  3 | Steel sword  |     30 |  1000 |           1 |
|  4 | Iron ax      |     20 |   500 |           1 |
|  5 | Silver mail  |     50 |  3000 |           2 |
|  6 | Steel armar  |     30 |  1000 |           2 |
|  7 | Ice shield   |     60 |  6000 |           3 |
|  8 | Steel shield |     40 |  1000 |           3 |
|  9 | Elixir       |    100 |  1000 |        NULL |
+----+--------------+--------+-------+-------------+

itemテーブルの検索

  • 1 category_id1のレコードのnamepriceを表示してください。
+-------------+-------+
| name        | price |
+-------------+-------+
| Excalibur   | 10000 |
| Gungnir     |  9000 |
| Steel sword |  1000 |
| Iron ax     |   500 |
+-------------+-------+
  • 2 price1000〜6000のレコードのnamepriceを表示してください。
+--------------+-------+
| name         | price |
+--------------+-------+
| Steel sword  |  1000 |
| Silver mail  |  3000 |
| Steel armar  |  1000 |
| Ice shield   |  6000 |
| Steel shield |  1000 |
| Elixir       |  1000 |
+--------------+-------+
  • 3 category_idnullのレコード表示してください。
+--------+
| name   |
+--------+
| Elixir |
+--------+
  • 4 nameSteelで始まるレコード表示してください。
+----+--------------+--------+-------+-------------+
| id | name         | effect | price | category_id |
+----+--------------+--------+-------+-------------+
|  3 | Steel sword  |     30 |  1000 |           1 |
|  6 | Steel armar  |     30 |  1000 |           2 |
|  8 | Steel shield |     40 |  1000 |           3 |
+----+--------------+--------+-------+-------------+
  • 5 category_id3のレコードのpriceの最大値を表示してください。
+------------+
| max(price) |
+------------+
|      6000 |
+------------+
  • 6 category_idごとのpriceの合計値を表示してください。ただし、category_idnullのものは対象外とします。
+-------------+------------+
| category_id | sum(price) |
+-------------+------------+
|           1 |      20500 |
|           2 |       4000 |
|           3 |       7000 |
+-------------+------------+
  • 7 priceの最大値であるレコードのnamepriceを出力してください。
+-----------+-------+
| name      | price |
+-----------+-------+
| Excalibur | 10000 |
+-----------+-------+
  • 8 priceの昇順で表示してください。
+----+--------------+--------+-------+-------------+
| id | name         | effect | price | category_id |
+----+--------------+--------+-------+-------------+
|  4 | Iron ax      |     20 |   500 |           1 |
|  3 | Steel sword  |     30 |  1000 |           1 |
|  6 | Steel armar  |     30 |  1000 |           2 |
|  8 | Steel shield |     40 |  1000 |           3 |
|  9 | Elixir       |    100 |  1000 |        NULL |
|  5 | Silver mail  |     50 |  3000 |           2 |
|  7 | Ice shield   |     60 |  6000 |           3 |
|  2 | Gungnir      |     90 |  9000 |           1 |
|  1 | Excalibur    |    100 | 10000 |           1 |
+----+--------------+--------+-------+-------------+
  • 9 effectの降順で表示してください。
+----+--------------+--------+-------+-------------+
| id | name         | effect | price | category_id |
+----+--------------+--------+-------+-------------+
|  1 | Excalibur    |    100 | 10000 |           1 |
|  9 | Elixir       |    100 |  1000 |        NULL |
|  2 | Gungnir      |     90 |  9000 |           1 |
|  7 | Ice shield   |     60 |  6000 |           3 |
|  5 | Silver mail  |     50 |  3000 |           2 |
|  8 | Steel shield |     40 |  1000 |           3 |
|  3 | Steel sword  |     30 |  1000 |           1 |
|  6 | Steel armar  |     30 |  1000 |           2 |
|  4 | Iron ax      |     20 |   500 |           1 |
+----+--------------+--------+-------+-------------+
  • 10 effect50以上のレコードをpriceの降順で表示してください。
+-------------+--------+-------+
| name        | effect | price |
+-------------+--------+-------+
| Excalibur   |    100 | 10000 |
| Gungnir     |     90 |  9000 |
| Ice shield  |     60 |  6000 |
| Silver mail |     50 |  3000 |
| Elixir      |    100 |  1000 |
+-------------+--------+-------+


テーブルの結合

  • 1 itemテーブルとitem_categoryテーブルを内部結合して、以下のとおり表示してください。
+----------+--------------+-------+
| category | name         | price |
+----------+--------------+-------+
| Weapon   | Excalibur    | 10000 |
| Weapon   | Gungnir      |  9000 |
| Weapon   | Steel sword  |  1000 |
| Weapon   | Iron ax      |   500 |
| Armor    | Silver mail  |  3000 |
| Armor    | Steel armar  |  1000 |
| Shield   | Ice shield   |  6000 |
| Shield   | Steel shield |  1000 |
+----------+--------------+-------+
  • 2 itemテーブルとitem_categoryテーブルを内部結合して、以下のとおりcategoryごとのpriceの合計を表示してください。
+----------+-------+
| category | price |
+----------+-------+
| Weapon   | 20500 |
| Armor    |  4000 |
| Shield   |  7000 |
+----------+-------+
  • 3 2の結果をpriceの昇順で表示してください。
+----------+-------+
| category | price |
+----------+-------+
| Armor    |  4000 |
| Shield   |  7000 |
| Weapon   | 20500 |
+----------+-------+