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)
2.
SELECT * FROM deps;
3.
UPDATE emps SET age=age+1 WHERE id=5;
4.
DELETE FROM sales WHERE id=16;
5.
SELECT * FROM sales ORDER BY sale DESC;
6.
SELECT name AS 名前,age AS 年齢 FROM emps ORDER BY age DESC;
7.
SELECT * FROM emps WHERE age>=30 AND dep_id <> 4;
8.
SELECT * FROM emps WHERE name NOT LIKE '%中%';
9.
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で先に絞りんこんでからGROUP BYでもOK)
SELECT s_date AS 日付, count(*) AS 件数, sum(sale) AS 日別売上 FROM sales WHERE s_date='2017-09-03' GROUP BY s_date;
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エラーはたまに遭遇するので覚えておくと良いだろう。
コメント