回答は全てSQL文と実行結果を日報に添付しQ毎にPUSHしましょう
解答は事前課題4のデータがinsertされた状態で結果を取得しています
商品シャツの売り上げ合計金額を算出しましょう
解答
select sum(price * item_quontity)
from order_details inner join items on item_id = items.id
and items.name = "シャツ";
+----------------------------+
| sum(price * item_quontity) |
+----------------------------+
| 29000 |
+----------------------------+
1 row in set (0.00 sec)
itemsでランダムに1行取得しましょう
select * from items order by rand() limit 1;
+----+--------------+-------+---------------------+---------------------+
| id | name | price | created_at | updated_at |
+----+--------------+-------+---------------------+---------------------+
| 3 | マフラー | 1200 | 2016-08-21 15:23:06 | 0000-00-00 00:00:00 |
+----+--------------+-------+---------------------+---------------------+
1 row in set (0.00 sec)
商品シャツ、商品パンツを受注した受注番号を取得しましょう、受注番号は新しい(大きい)順にソートしましょう
解答
select distinct(order_details.order_no)
from order_details inner join items on order_details.item_id = items.id and items.name in ("シャツ","パンツ")
order by order_details.order_no desc;
+----------+
| order_no |
+----------+
| 5 |
| 3 |
| 2 |
| 1 |
+----------+
4 rows in set (0.00 sec)
全ての受注から平均受注金額を算出しましょう
解答
select avg(sum_price) from (
select orders.no ,sum(order_details.item_quontity * items.price) as sum_price from orders
inner join order_details on orders.no = order_details.order_no
inner join items on order_details.item_id = items.id
group by orders.no) a;
+----------------+
| avg(sum_price) |
+----------------+
| 34440.0000 |
+----------------+
1 row in set (0.00 sec)
受注金額の一番大きい受注番号と受注金額を求めましょう
解答
select orders.no,sum(order_details.item_quontity * items.price) sum_price
from orders
inner join order_details on orders.no = order_details.order_no
inner join items on order_details.item_id = items.id
group by orders.no
order by sum_price desc
limit 1;
新しい商品を1回のinsertで登録しましょう。商品タンクトップ 単価1300、商品ジャンパー 単価2500、商品ソックス 単価600
解答
insert into items(name,price) values
("タンクトップ",1300),
("ジャンパー",2500),
("ソックス",600);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
B商会の受注金額の合計を算出しましょう
解答
select sum(order_details.item_quontity * items.price) as sum_price
from orders
inner join order_details on orders.no = order_details.order_no
inner join items on order_details.item_id = items.id
inner join customers on customers.id = orders.customer_id
where customers.name = "B商会";
+-----------+
| sum_price |
+-----------+
| 98000 |
+-----------+
1 row in set (0.00 sec)
order_detailsで使われていない商品(items.id)を求めましょう)、3種類のSQLを作成しましょう(5分)
解答1
select items.id,items.name from items
where not exists (select 1 from order_details where order_details.item_id = items.id);
解答2
select items.id,items.name from items
left join order_details on order_details.item_id = items.id
where order_details.order_no is null;
解答3
select items.id,items.name from items
where items.id not in(select item_id from order_details);
一回のSQLでorder_detailsで商品全体の注文回数と商品id毎の注文回数を求めよ
回答例
| count(*) | 1 | 2 | 3 | 4 | 5 | 6 |
| 10 | 2 | 3 | 2 | 1 | 1 | 1 |
解答
select count(*) ,
sum(case when item_id = 1 then 1 else 0 end) as "1",
sum(case when item_id = 2 then 1 else 0 end) as "2",
sum(case when item_id = 3 then 1 else 0 end) as "3",
sum(case when item_id = 4 then 1 else 0 end) as "4",
sum(case when item_id = 5 then 1 else 0 end) as "5",
sum(case when item_id = 6 then 1 else 0 end) as "6"
from order_details;
+----------+------+------+------+------+------+------+
| count(*) | 1 | 2 | 3 | 4 | 5 | 6 |
+----------+------+------+------+------+------+------+
| 10 | 2 | 3 | 2 | 1 | 1 | 1 |
+----------+------+------+------+------+------+------+
1 row in set (0.00 sec)
items内で同じ商品で複数レコードがあるもので、その商品単価平均より高いitems.idとitems.nameとitem.priceを求めよ
解答
select id,name from items i1
where i1.price > (select avg(price) from items i2 where i2.name = i1.name);
+----+-----------+-------+
| id | name | price |
+----+-----------+-------+
| 5 | シャツ | 5000 |
+----+-----------+-------+
1 row in set (0.00 sec)