ICTSC9 予選問題 問7

私は問3と問7を担当しました。
問7は問題数が5問しか無く、特に苦労せず出来た印象です。
以下、各問題の感想と私の回答です。
DBはMySQLでした。

1. 現在の総社員数は何人ですか

> SELECT count(*) FROM employee WHERE deleted_at IS NULL;

+----------+
| count(*) |
+----------+
|       29 |
+----------+
1 row in set (0.01 sec)

2. ユニークな名前の一覧と、そのそれぞれの名前の人は何人いるでしょうか

> SELECT name, COUNT(name) FROM employee WHERE deleted_id IS NULL GROUP BY name;

+----------+-------------+
| name     | count(name) |
+----------+-------------+
| Ito      |           5 |
| Kato     |           6 |
| Nakamura |           3 |
| Suzuki   |           7 |
| Tanaka   |           3 |
| Yamada   |           5 |
+----------+-------------+
6 rows in set (0.00 sec)

3. 男性の人数、女性の人数はそれぞれ何人ですか

SQLでcase文が使えることを知らなかったので、解説を見た時は唸りました。

> SELECT sex, COUNT(sex) FROM employee WHERE deleted_at IS NULL GROUP BY sex;

+-----+------------+
| sex | COUNT(sex) |
+-----+------------+
| F   |         16 |
| M   |         13 |
+-----+------------+
2 rows in set (0.00 sec)

4. 月ごとにこの会社が支払った賃金の合計はいくらでしょうか。また、上から金額が高い順に並べてください

問題文に金額の高い順とあるのでORDER BY句でDESCを指定したですが、提出してから「ORDER BY」を書き忘れていたことに気づく痛恨のミス。後から書き忘れさえ無ければ満点だったと聞かされてとても悔しかったです。

> SELECT month, SUM(amount) FROM monthly_salary m, employee e 
        WHERE m.employee_id = e.id AND deleted_at IS NULL GROUP BY month DESC;

+-------+-------------+
| month | sum(amount) |
+-------+-------------+
| Sep   |    14350000 |
| Oct   |    14450000 |
| Nov   |    14450000 |
| May   |    13865000 |
| Mar   |    12670000 |
| Jun   |    13770000 |
| Jul   |    13870000 |
| Jan   |    12435000 |
| Feb   |    13045000 |
| Dec   |    12370000 |
| Aug   |    11850000 |
| Apr   |    12455000 |
+-------+-------------+
12 rows in set (0.00 sec)

5. この会社の平均年収はいくらでしょうか

問題4、問題5と連続して解説に書かれていた「結果は合ってるけどパフォーマンスが悪いSQL文」に該当した解答でした。 もっとパフォーマンスまで目が行くようになりたいです。

> SELECT AVG(sala) FROM (SELECT SUM(amount) sala FROM monthly_salary m, employee e 
        WHERE m.employee_id = e.id AND deleted_at IS NULL GROUP BY e.id) AS sub;

+--------------+
| AVG(sala)    |
+--------------+
| 5502758.6207 |
+--------------+
1 row in set (0.00 sec)