演習課題dbxx_1104(20点，クエリーごとに２点)
 　課題ごとに課題番号と内容を含め，wineShopAplusへのクエリー命令およびその実行結果を収めたテキストファイルdbxx_1104.txtをsningping@kumamoto-nct.ac.jへ添付で提出ください．

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
課題：【group by 属性名　having その属性の一部による条件】の利用演習． 

1.セットIDは’s-3’とするワインセットの価格を示せ．

mysql> select wineSet.setID, wineSet.name as 'セット名',sum(price*quantity) as '価格' from wineSet inner join setDetail using(setID) inner join wine using(wineID) group by setDetail.setID having wineSet.setID='s-3';
+-------+-----------------------+--------+
| setID | セット名              | 価格   |
+-------+-----------------------+--------+
| s-3   | 白ワインセット        |   6400 |
+-------+-----------------------+--------+
1 row in set (0.00 sec)

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
2.顧客IDは１番とする注文されたワインの総本数と金額を示せ．

①まずは、客別の注文されたワインの総本数と金額を求めてみる．
mysql> select customerID,concat(lastName,' ', firstName) as '顧客名前', sum(amount) as 'ワイン購入総数',sum(price*amount) as '金額' from cusOrder inner join orderWine on cusOrder.orderID=orderWine.ordID inner join customer on cusOrder.customerID=customer.cID inner join wine on orderWine.wID = wine.wineID group by customerID;
+------------+---------------+-----------------------+--------+
| customerID | 顧客名前      | ワイン購入総数        | 金額   |
+------------+---------------+-----------------------+--------+
|          1 | 東山 桜       |                     6 |  13913 |
|          2 | 山村 健       |                     4 |  18600 |
|          3 | 伊集院 紫     |                     2 |   4000 |
|          5 | 浅倉 彬       |                    32 |  55245 |
|         15 | Osaka Mio     |                     1 |   4000 |
+------------+---------------+-----------------------+--------+
5 rows in set (0.01 sec)

②次に、having を追加する。

mysql> select customerID,concat(lastName,' ', firstName) as '顧客名前', sum(amount) as 'ワイン購入総数',sum(price*amount) as '金額' from cusOrder inner join orderWine on cusOrder.orderID=orderWine.ordID inner join customer on cusOrder.customerID=customer.cID inner join wine on orderWine.wID = wine.wineID group by customerID having customerID=1;
+------------+--------------+-----------------------+--------+
| customerID | 顧客名前     | ワイン購入総数        | 金額   |
+------------+--------------+-----------------------+--------+
|          1 | 東山 桜      |                     6 |  13913 |
+------------+--------------+-----------------------+--------+
1 row in set (0.01 sec)


＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
3. 6000 円～10000 円のワインセットの名前と価格を示せ． 

　この課題は，一つのクエリで、幾つかの部分集合を作り，その後複合クエリで探すことはできるが，集合演算の考えを正しくまとめるのは簡単ではない．実用的かつ簡単な解決方法として，最初にcreate viewを利用して，新たにワインセットのID,価格,ワインの総本数を含むビユー（仮テーブル）wSetを作っておくことを薦める．
　ワインセットの価格のようなワインの値段と関係する属性はワインセットのテーブルに入れると,ワインの値段を更新するたびに，ワイン(wine)とワインセット(wineSet)両方更新しなければならない．その一方，例え元の表のデータが更新されても，ビユーはその表のデータを連動的に表示できるので，十分に機能が果たされるデータベースの設計になる．

また，ビユーwSetはテーブルと同様にクエリを作られ，6000 円～10000 円の価格を探せば，わかりやすく，ミスが起こるのを防げる． 

　drop view ordWine; で作ったviewを削除できる．

①mysql> create view wSet as select wineSet.setID,sum(price*quantity) as setPrice, sum(quantity) as qtySet from wineSet inner join setDetail using(setID) inner join wine using(wineID) group by setDetail.setID;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wSet;
+-------+----------+--------+
| setID | setPrice | qtySet |
+-------+----------+--------+
| KW-1  |     6100 |      3 |
| KW-2  |     4900 |      2 |
| s-1   |     5400 |      2 |
| s-2   |    11400 |      3 |
| s-3   |     6400 |      2 |
| s-4   |    17400 |      4 |
+-------+----------+--------+
6 rows in set (0.00 sec)

②mysql>select name, setPrice from wSet inner join wineSet using(setID) where setPrice >=6000 and setPrice<=10000;
+-----------------------+----------+
| name                  | setPrice |
+-----------------------+----------+
| 甲州白セット          |     6100 |
| 白ワインセット        |     6400 |
+-----------------------+----------+
2 rows in set (0.00 sec)


＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
4. 10000円以上のワインセットの名前, 価格を示せ．

課題３で作ったwSetは，引き続きに使える．

mysql>  select name, setPrice from wSet inner join wineSet using(setID) where setPrice >=10000;
+-----------------------+----------+
| name                  | setPrice |
+-----------------------+----------+
| ボルドーセット        |    11400 |
| 赤ワインセット        |    17400 |
+-----------------------+----------+
2 rows in set (0.00 sec)


＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
5.ワイン本数の和が３本以上のワインセットの名前, 価格, 本数の和を示せ．
課題３で作ったwSetは，引き続きに使える．
mysql>  select name, qtySet, setPrice from wSet inner join wineSet using(setID)
where qtySet >=3;
+-----------------------+--------+----------+
| name                  | qtySet | setPrice |
+-----------------------+--------+----------+
| 甲州白セット          |      3 |     6100 |
| ボルドーセット        |      3 |    11400 |
| 赤ワインセット        |      4 |    17400 |
+-----------------------+--------+----------+
3 rows in set (0.01 sec)
　

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
6.注文IDは１番とする注文されたワインの総数と金額を示せ．

mysql> select customerID,concat(lastName,' ', firstName) as '顧客名前', sum(amount) as 'ワイン購入総数',sum(price*amount) as '金額' from cusOrder inner join orderWine on cusOrder.orderID=orderWine.ordID inner join customer on cusOrder.customerID=customer.cID inner join wine on orderWine.wID = wine.wineID group by orderID having orderID=1;
+------------+--------------+-----------------------+--------+
| customerID | 顧客名前     | ワイン購入総数        | 金額   |
+------------+--------------+-----------------------+--------+
|          2 | 山村 健      |                     2 |  11600 |
+------------+--------------+-----------------------+--------+
1 row in set (0.00 sec)

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
7.注文ID別に注文されたワインの金額は3000円以上となる顧客名，ワインの金額を示せ．

viewを利用する方法で，課題２①クエリで仮テーブルordWineを作っておく．
mysql> create view ordWine as select orderID,concat(lastName,' ', firstName) as cusName, sum(amount) as wineQty,sum(price*amount) as  wineAmount from cusOrder inner join orderWine on cusOrder.orderID=orderWine.ordID inner join customer on cusOrder.customerID=customer.cID inner join wine on orderWine.wID = wine.wineID group by orderID;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from ordWine;
+---------+---------------+---------+------------+
| orderID | cusName       | wineQty | wineAmount |
+---------+---------------+---------+------------+
|       1 | 山村 健       |       2 |      11600 |
|       2 | 東山 桜       |       1 |       1800 |
|       4 | 東山 桜       |       2 |       4900 |
|       5 | 伊集院 紫     |       2 |       4000 |
|       7 | 浅倉 彬       |       4 |       7200 |
|       8 | 浅倉 彬       |       1 |       1800 |
|       9 | 浅倉 彬       |       3 |       5400 |
|      14 | 浅倉 彬       |       3 |       2925 |
|      15 | 浅倉 彬       |       3 |       5400 |
|      18 | 浅倉 彬       |       3 |       5400 |
|      21 | 浅倉 彬       |       1 |        975 |
|      22 | 浅倉 彬       |       1 |        975 |
|      23 | 浅倉 彬       |       1 |        975 |
|      25 | 浅倉 彬       |       1 |        975 |
|      26 | 浅倉 彬       |       4 |       3900 |
|      27 | 浅倉 彬       |       4 |       3900 |
|      28 | 浅倉 彬       |       3 |      15420 |
|      29 | 東山 桜       |       2 |       4300 |
|      31 | 山村 健       |       2 |       7000 |
|      32 | Osaka Mio     |       1 |       4000 |
|      33 | 東山 桜       |       1 |       2913 |
+---------+---------------+---------+------------+
21 rows in set (0.01 sec)

②課題７のクエリは以下の通りになる．
mysql> select cusName,wineAmount from ordWine where wineAmount>=3000;
+---------------+------------+
| cusName       | wineAmount |
+---------------+------------+
| 山村 健       |      11600 |
| 東山 桜       |       4900 |
| 伊集院 紫     |       4000 |
| 浅倉 彬       |       7200 |
| 浅倉 彬       |       5400 |
| 浅倉 彬       |       5400 |
| 浅倉 彬       |       5400 |
| 浅倉 彬       |       3900 |
| 浅倉 彬       |       3900 |
| 浅倉 彬       |      15420 |
| 東山 桜       |       4300 |
| 山村 健       |       7000 |
| Osaka Mio     |       4000 |
+---------------+------------+
13 rows in set (0.00 sec)


＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
8.注文IDは３番とする注文された顧客名，ワインセットの総数を示せ。
having を利用する．

mysql> select orderID,concat(lastName,' ', firstName) as '顧客名前', sum(amount)as 'ワインセット注文総数' from cusOrder inner join orderWineSet on cusOrder.orderID=orderWineSet.ordID inner join customer on cusOrder.customerID=customer.cID group by orderID having orderID=3;
+---------+---------------+--------------------------------+
| orderID | 顧客名前      | ワインセット注文総数           |
+---------+---------------+--------------------------------+
|       3 | 伊集院 紫     |                              1 |
+---------+---------------+--------------------------------+
1 row in set (0.00 sec)

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
9.注文ID別に注文されたワインセット総数が2以上となる顧客名，セットの総数を示せ．

①課題８のクエリをビユー(view)のallSetにする．

mysql> create view ordSet as select orderID,concat(lastName,' ', firstName) as cusName, sum(amount)as wsetQty from cusOrder inner join orderWineSet on cusOrder.orderID=orderWineSet.ordID inner join customer on cusOrder.customerID=customer.cID group by orderID;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from ordSet;
+---------+---------------+---------+
| orderID | cusName       | wsetQty |
+---------+---------------+---------+
|       1 | 山村 健       |       1 |
|       2 | 東山 桜       |       1 |
|       3 | 伊集院 紫     |       1 |
|       4 | 東山 桜       |       1 |
|       5 | 伊集院 紫     |       1 |
|      29 | 東山 桜       |       2 |
|      32 | Osaka Mio     |       1 |
+---------+---------------+---------+
7 rows in set (0.00 sec)

②課題８のクエリ
mysql> select cusName as '顧客名',wsetQty as '注文セット総数' from ordSet where wsetQty>=2;
+------------+-----------------------+
| 顧客名     | 注文セット総数        |
+------------+-----------------------+
| 東山 桜    |                     2 |
+------------+-----------------------+
1 row in set (0.00 sec)

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
10.指定された日の店のワインの売り上げ金額を示せ．

ビユーordWineとcusOrderを結合する．
mysql> select sum(wineAmount) from ordWine inner join cusOrder using(orderID) where date(orderTime)='2021-08-02';
+-----------------+
| sum(wineAmount) |
+-----------------+
|            6913 |
+-----------------+
1 row in set (0.00 sec)

また，今まで店のワインの売り上げは，次のように求める．
mysql> select sum(wineAmount) from ordWine;
+-----------------+
| sum(wineAmount) |
+-----------------+
|           95758 |
+-----------------+
1 row in set (0.00 sec)