MySQL-4日目(基本文法2演習) | ジョイタスネット

MySQLを使って以下の手順で作業を進めよ。

●sales_appデータベースを文字コードUTF-8で作成する。

CREATE DATABASE sales_app

DEFAULT CHARACTER SET utf8;

●sales_appにdepsテーブルを作成する。カラム構成は以下
id 整数　主キー　自動連番
dep 可変長文字列(30) NOT NULL

CREATE TABLE deps(
id INT PRIMARY KEY AUTO_INCREMENT,
dep VARCHAR(30) NOT NULL
);

●depsテーブルに以下の情報を追加する(コピペしてよい)

INSERT INTO deps(dep) VALUES

('営業1課'),('営業2課'),('経理部'),('総務部'),('人事部');

●sales_appにempsテーブルを作成する。カラム構成は以下
id 整数　主キー　自動連番
name 可変長文字列(30) NOT NULL,
age 整数
dep_id 整数

CREATE TABLE emps(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
age INT,
dep_id INT
);

●empsテーブルに以下のデータを挿入する。(コピペしてよい)

INSERT INTO emps(name,age,dep_id) VALUES
('佐藤',40,3),
('高橋',28,1),
('中川',20,2),
('渡辺',20,1),
('西澤',35,4),
('中村',26,2),
('田中',24,2),
('鈴木',42,5);

●sales_appにsalesテーブルを作成する。カラム構成は以下
id 整数　主キー　自動連番
emp_id INT,
sale INT,
s_date DATE

CREATE TABLE sales(
id INT PRIMARY KEY AUTO_INCREMENT,
emp_id INT,
sale INT,
s_date DATE
);

●salesテーブルに以下のデータを挿入する。(コピペしてよい)

INSERT INTO sales(emp_id,sale,s_date) VALUES
(3,100,'2017-09-01'),
(8,63,'2017-09-01'),
(2,82,'2017-09-01'),
(1,32,'2017-09-02'),
(5,98,'2017-09-02'),
(3,87,'2017-09-03'),
(3,66,'2017-09-03'),
(1,42,'2017-09-03'),
(6,164,'2017-09-04'),
(2,28,'2017-09-04'),
(3,35,'2017-09-04'),
(5,43,'2017-09-05'),
(4,26,'2017-09-05'),
(3,83,'2017-09-05'),
(8,55,'2017-09-05'),
(5,29,'2017-09-06');

上記で作成したテーブルにおいて以下の処理を実現するSQL文を記せ。
1.empsテーブルに、山田,22,1を追加せよ。

2.depsの全てのデータを抽出せよ。

3.empsテーブルidが5のデータの年齢を１加えよ。

4.salesテーブルidが16のデータを削除せよ。

5.salesテーブル。sale降順に抽出せよ。

6.empsのデータを年齢降順に抽出せよ。取得項目は、名前、年齢とする。

7.empsテーブル。年齢が30以上でdep_idが４でないデータを抽出。

8.empsテーブル。名前に[中]が含まれない人を抽出せよ。

9.salesテーブル。2017-09-01~2017-09-03のデータを抽出する。

10.salesとempsを内部結合。売上を記録した人の名前を抽出。重複は除外すること。

11.depsテーブル。idの1,2は本社ビル7F,3,4は本社ビル6F,その他は本社ビル5Fである。
以下の項目名で取得せよ。部署名、所在地

12.empsテーブル。全件抽出。ただし年齢が23歳以下の人には名前の前に(新)を付与すること。
取得項目は名前、年齢

13.salesテーブル。sale昇順に並び替え。saleに1000を掛け３桁毎にカンマを挿入し末尾に円を付与したものを売上高とする。
取得項目は日付、売上高

14.salesテーブル。売上の合計、最大、最小、平均、データ数を表示する。

15.salesテーブル。2017-09-03の日別売上を求めよ。項目名は日付、件数、日別売上とする。

16.depsとempsを内部結合し年齢昇順に抽出せよ。ただし年齢は30歳未満。取得項目は、名前、年齢、部署名とする。年齢が同じ場合は部署名を辞書順に並べること。

17.salesとempsとdepsを内部結合し、取得項目名を日付,名前,年齢,部署名,売上高として全件抽出せよ。

18.salesとempsとdepsを左結合し、総売上個人ランキングトップ３を降順で取得する。取得項目は名前、部署名、総売上とする。

19.まだ売上を上げていない社員名を抽出せよ。

—-解答例—-
1.

INSERT INTO emps(name,age,dep_id)

VALUES('山田',22,1)

SELECT * FROM deps;

UPDATE emps SET age=age+1

WHERE id=5;

DELETE FROM sales WHERE id=16;

SELECT * FROM sales

ORDER BY sale DESC;

SELECT name AS 名前,age AS 年齢 FROM emps

ORDER BY age DESC;

SELECT * FROM emps
WHERE age>=30
AND dep_id <> 4;

SELECT * FROM emps

WHERE name NOT LIKE '%中%';

SELECT * FROM sales

WHERE s_date BETWEEN '2017-09-01' AND '2017-09-03';

10.

SELECT DISTINCT e.name 
FROM sales AS s 
JOIN emps AS e 
ON s.emp_id=e.id

11.

SELECT dep AS 部署名, 
CASE WHEN id IN(1,2) THEN '本社ビル7F'
WHEN id IN(3,4) THEN '本社ビル6F' 
ELSE '本社ビル5F' 
END AS 所在地 FROM deps;

12.

SELECT 
CASE WHEN age<=23 THEN concat('(新)',name)
ELSE name
END AS 名前, 
age AS 年齢
FROM emps;

13.

SELECT s_date AS 日付,concat(format(sale*1000,0),'円') AS 売上高 
FROM sales
ORDER BY sale;

14.

SELECT sum(sale),max(sale),min(sale),avg(sale),count(*)

FROM sales;

15.

SELECT s_date AS 日付,
count(*) AS 件数,
sum(sale) AS 日別売上 
FROM sales 
GROUP BY s_date 
HAVING s_date='2017-09-03'

(whereで絞り込んでからGROPU BY でもOK)

SELECT s_date AS 日付, count(*) AS 件数, sum(sale) AS 日別売上 FROM sales

WHERE s_date='2017-09-03' GROPU BY s_date

(１日だったらwhereだけでもOK)

SELECT s_date AS 日付, count(*) AS 件数, sum(sale) AS 日別売上 FROM sales

WHERE s_date='2017-09-03'

16.

SELECT e.name AS 名前,e.age AS 年齢,d.dep AS 部署名
FROM deps AS d
JOIN emps AS e
ON d.id=e.dep_id
WHERE e.age<30
ORDER BY e.age ASC,d.dep ASC;

17.

SELECT s.s_date AS 日付,
e.name AS 名前,
e.age AS 年齢,
d.dep AS 部署名,
s.sale AS 売上高
FROM sales AS s
JOIN emps AS e
ON s.emp_id=e.id
JOIN deps AS d
ON e.dep_id = d.id;

18.

SELECT e.name AS 名前,
d.dep AS 部署名,
sum(s.sale) AS 総売上 
FROM sales AS s
LEFT JOIN emps AS e
ON s.emp_id=e.id
LEFT JOIN deps AS d
ON e.dep_id=d.id
GROUP BY s.emp_id
ORDER BY 総売上 DESC
LIMIT 3;

19.

SELECT name FROM emps

WHERE id NOT IN(SELECT emp_id FROM sales);

★★More★★
ここからは受講生の作成したお題からのピックアップです！
●部署毎に売上合計の高い人から並べて表示。取得項目は部署名,名前,売上合計。並び順は部署名昇順、売上降順とする。(W氏)

SELECT
d.dep AS 部署名,
e.name AS 名前,
sum(s.sale) AS 合計売上
FROM sales AS s
LEFT JOIN emps AS e
ON s.emp_id=e.id
LEFT JOIN deps AS d
ON e.dep_id=d.id
group by
部署名,名前
order by
部署名 ASC
,合計売上 DESC;

★GROUP BY に複数カラム指定することができる。

●2017年9月の個人売上高ランキングTOP5を取得せよ。取得項目は名前,売上。並びは売上降順とする。(F氏)

SELECT  
e.name AS 名前,
SUM(s.sale) AS 売上
FROM `sales` AS s
JOIN emps AS e
ON s.emp_Id=e.id
WHERE s.s_date BETWEEN '2017-09-01' AND '2017-09-30'
GROUP BY 名前
ORDER BY 売上 DESC
LIMIT 5

●20代、30代、40代の売上合計を取得せよ。取得項目は年代、売上。並びは年代昇順とする。

SELECT 
CASE 
WHEN e.age BETWEEN 20 AND 29 THEN '20-29'
WHEN e.age BETWEEN 30 AND 39 THEN '30-39'
WHEN e.age BETWEEN 40 AND 49 THEN '40-49'
END AS 年代,
SUM(s.sale) AS 売上
FROM sales AS s
JOIN emps AS e
ON s.emp_id=e.id
GROUP BY 年代
ORDER BY 年代 ASC

●まだ売り上げを上げていない社員を抽出し、一番年齢の高い社員のデータを削除せよ(K氏)
★この問題は1部難しい内容を含んでいるのでまずはSELECTしてみる

SELECT * FROM emps
WHERE id=
(SELECT id FROM emps
WHERE id NOT IN (SELECT emp_id FROM sales)
ORDER BY age DESC
LIMIT 1);

どうやら田中さんが対象者のようだ。ではSELECTをDELETEにして田中さんを首にしよう。

DELETE FROM emps
WHERE id=
(SELECT id FROM emps
WHERE id NOT IN (SELECT emp_id FROM sales)
ORDER BY age DESC
LIMIT 1);

がしかし。。。

1093エラーが出てしまう。このようにUPDATEやDELETEはサブクエリに同じテーブルを直接含むことができない。
ハック的なのだがこの場合以下のように対応する

DELETE FROM emps
WHERE id=
(SELECT id FROM 
  (SELECT * FROM emps
  WHERE id NOT IN (SELECT emp_id FROM sales)
  ORDER BY age DESC
  LIMIT 1) AS t
);

直接empsテーブルをサブクエリで対象にしてしまうとエラーになるのでこの部分をもう一段回サブクエリを作ってあげて別名をつけてあげるとうまくいく。1093エラーはたまに遭遇するので覚えておくと良いだろう。