Open4

達人に学ぶSQL徹底指南書演習

しんとみしんとみ

演習問題1-1

テーブル作成とデータ挿入
カラム名にkeyは利用できなかったのでmykeyに変更した（おそらくkeyが予約語だから）

CREATE TABLE greatests (mykey VARCHAR(10) NOT NULL, x INTEGER NOT NULL, y INTEGER NOT NULL, z INTEGER NOT NULL);
INSERT INTO greatests (mykey, x, y, z) VALUES ('A', 1,2,3);
INSERT INTO greatests (mykey, x, y, z) VALUES ('B', 5,5, 2);
INSERT INTO greatests (mykey, x, y, z) VALUES ('C', 4,7,1);
INSERT INTO greatests (mykey, x, y, z) VALUES ('D',3,3,8);

mysql> SELECT mykey, CASE WHEN x > y THEN x ELSE y END AS greatest FROM greatests;
+-------+----------+
| mykey | greatest |
+-------+----------+
| A     |        2 |
| B     |        5 |
| C     |        7 |
| D     |        3 |
+-------+----------+
4 rows in set (0.00 sec)

mysql> SELECT mykey, CASE WHEN x > y THEN x WHEN y > z THEN y ELSE z END AS greatest FROM greatests;
+-------+----------+
| mykey | greatest |
+-------+----------+
| A     |        3 |
| B     |        5 |
| C     |        7 |
| D     |        8 |
+-------+----------+
4 rows in set (0.00 sec)
しんとみしんとみ

演習問題1-2

INSERT INTO PopTbl2 VALUES 
('tokushima', '1', '60'),
('tokushima', '2', '40'),
('kagawa', '1', '100'),
('kagawa', '2', '100'),
('ehime', '1', '100'),
('ehime', '2', '50'),
('kouti', '1', '100'),
('kouti', '2', '100'),
('hukuoka', '1', '100'),
('hukuoka', '2', '200'),
('saga', '1', '20'),
('saga', '2', '80'),
('nagasaki', '1', '125'),
('nagasaki', '2', '125'),
('tokyo', '1', '250'),
('tokyo', '2', '150');
CREATE TABLE PopTbl2
(pref_name VARCHAR(32),
 sex CHAR(1) NOT NULL,
 population INTEGER NOT NULL,
    PRIMARY KEY(pref_name, sex));

自力解

mysql> SELECT CASE WHEN sex='1' THEN 'man' ELSE 'woman' END AS 'sex',
    SUM(population) AS total,
    SUM(CASE WHEN pref_name='tokushima' THEN population ELSE 0 END) AS tokushima,
    SUM(CASE WHEN pref_name='kagawa' THEN population ELSE 0 END) AS kagawa,
    SUM(CASE WHEN pref_name='ehime' THEN population ELSE 0 END) AS ehime,
    SUM(CASE WHEN pref_name='kouti' THEN population ELSE 0 END) as kouti,
    SUM(CASE WHEN pref_name IN ('tokushima', 'kagawa', 'ehime', 'kouti') THEN population ELSE 0 END) AS sikoku
FROM PopTbl2 GROUP BY sex;
+-------+-------+-----------+--------+-------+-------+--------+
| sex   | total | tokushima | kagawa | ehime | kouti | sikoku |
+-------+-------+-----------+--------+-------+-------+--------+
| man   |   855 |        60 |    100 |   100 |   100 |    360 |
| woman |   845 |        40 |    100 |    50 |   100 |    290 |
+-------+-------+-----------+--------+-------+-------+--------+
2 rows in set, 1 warning (0.00 sec)

ChatGPT解

SELECT 
    IF(sex='1', 'man', 'woman') AS sex,
    SUM(population) AS total,
    SUM(population * (pref_name = 'tokushima')) AS tokushima,
    SUM(population * (pref_name = 'kagawa')) AS kagawa,
    SUM(population * (pref_name = 'ehime')) AS ehime,
    SUM(population * (pref_name = 'kouti')) AS kouti,
    SUM(population * (pref_name IN ('tokushima', 'kagawa', 'ehime', 'kouti'))) AS sikoku
FROM 
    PopTbl2
GROUP BY 
    sex;
しんとみしんとみ

演習問題1-3

mysql> SELECT mykey, CASE WHEN x > y THEN x ELSE y END AS greatest FROM greatests ORDER BY CASE mykey
    -> WHEN 'B' THEN 1
    -> WHEN 'A' THEN 2
    -> WHEN 'C' THEN 3
    -> WHEN 'D' THEN 4
    -> ELSE NULL END;
+-------+----------+
| mykey | greatest |
+-------+----------+
| B     |        5 |
| A     |        2 |
| C     |        7 |
| D     |        3 |
+-------+----------+
4 rows in set (0.00 sec)