changed 6 years ago  
Published Linked with GitHub

ストアドプロシージャってやつでなんとかして！！！！

問題文

あなたはA社に今年入社した新入社員です。

A社は人手不足でAさんの他に上司のBさんしか情報システム部門にいません。
ある日突然Bさんが人事のCさんに、「簡単に指定のデータを取り出せるようにしてほしい」と言われました。
ですが、Bさんは次の日からバカンスに行く予定があったため、あなたに任せて出国してしまいました。
Bさんの代わりにCさんを助けてあげてください。

問1

性別名が"男"の従業員のみを取得してください。
カラムは(employee_id, name, birthday, sex_name)を表示してください。

CREATE PROCEDURE q01()
BEGIN
SELECT employee_id, name, birthday, sex_name FROM Employees INNER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name = '男';
END

問2

性別名が"女"で年齢が女性従業員の平均年齢以上の従業員のみを取得してください。
カラムは(employee_id, name, age, sex_name)を表示してください。
age(年齢)に関してはbirthdayから求めて答えてください。

CREATE PROCEDURE q02()
BEGIN
SELECT employee_id, name, TIMESTAMPDIFF(YEAR, `birthday`, CURDATE()) AS age, sex_name FROM Employees INNER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name = '女' AND TIMESTAMPDIFF(YEAR, `birthday`, CURDATE()) >= (SELECT AVG(TIMESTAMPDIFF(YEAR, `birthday`, CURDATE())) FROM Employees INNER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name = '女'); 
END

問3

部署も権限もすでに決まっている(NULL以外である)従業員のデータのみを取得してください。
カラムは(employee_id,name,age,department_name,permit_name)
age(年齢)に関してはbirthdayから求めて答えてください。

CREATE PROCEDURE q03()
BEGIN
	SELECT employee_id, name, TIMESTAMPDIFF(YEAR, `birthday`, CURDATE()) AS age, department_name, permit_name FROM Employees INNER JOIN Departments ON Departments.department_id = Employees.department_id INNER JOIN Permit ON Employees.permit_id = Permit.permit_id WHERE Employees.department_id IS NOT NULL AND Employees.permit_id IS NOT NULL;
END

条件

Employees以外のテーブル内にあるカラム"〇〇_id"は今後内容が変更されることがあるので注意すること。

ゴール

問1, 2, 3で指定したデータを取得するクエリを、q01, q02, q03という名前でストアドプロシージャとして登録する。

情報

IPアドレス: 192.168.0.1
ユーザー名: admin
パスワード: 6pfmqje365Ed
DBユーザー名: root
DBパスワード: なし

テーブル

Department

select * FROM Departments;

+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|             1 | 総務            |
|             2 | デザイン        |
|             3 | 開発            |
+---------------+-----------------+

Employees

select * FROM Employees;

+-------------+---------------------+------------+--------+---------------+-----------+
| employee_id | name                | birthday   | sex_id | department_id | permit_id |
+-------------+---------------------+------------+--------+---------------+-----------+
|        1001 | 東京 太郎           | 1984-10-02 |      0 |             2 |         2 |
|        1002 | 桜庭 花男           | 1980-05-06 |      0 |             1 |         3 |
|        1003 | 神奈川 太郎         | 2001-04-05 |      0 |          NULL |         1 |
|        1004 | 埼玉 小太郎         | 1972-12-22 |      0 |             2 |         2 |
|        1005 | 滝本 宗次郎         | 2000-02-13 |      0 |             1 |         1 |
|        1006 | 千葉 太郎坊         | 1966-03-02 |      0 |             1 |         1 |
|        1007 | 飯島 れん           | 1995-04-01 |      0 |             3 |         3 |
|        1008 | 葉月 もずく         | 1995-11-03 |      0 |             2 |         3 |
|        1009 | 大和 和子           | 1996-10-01 |      1 |             1 |         3 |
|        1010 | 涼風 青葉           | 2001-02-02 |      1 |             2 |         2 |
|        1011 | 望月 紅葉           | 2000-01-17 |      1 |          NULL |         1 |
|        1012 | 阿波根 うみこ       | 1989-07-20 |      1 |             3 |         3 |
|        1013 | 鳴海 ツバメ         | 1999-05-16 |      1 |          NULL |         1 |
|        1014 | 飯島 ゆん           | 1997-12-06 |      1 |             2 |         2 |
|        1015 | 滝本 ひふみ         | 1995-01-23 |      1 |             2 |         2 |
|        1016 | 桜 ねね             | 2000-05-05 |      1 |          NULL |         1 |
|        1017 | 遠山 りん           | 1993-12-03 |      1 |             2 |         2 |
|        1018 | 葉月 しずく         | 1983-05-22 |      1 |             1 |         3 |
|        1019 | 八神 コウ           | 1993-08-02 |      1 |             2 |         3 |
|        1020 | 篠田 はじめ         | 1998-01-01 |      1 |             2 |         2 |
+-------------+---------------------+------------+--------+---------------+-----------+

Permit

SELECT * FROM Permit;

+-----------+----------------+
| permit_id | permit_name    |
+-----------+----------------+
|         1 | none           |
|         2 | read only      |
|         3 | read and write |
+-----------+----------------+

Sex

SELECT * FROM Sex;

+--------+----------+
| sex_id | sex_name |
+--------+----------+
|      0 | 男       |
|      1 | 女       |
+--------+----------+

解答

お疲れ様です。:thonk_spin.ex-large.rotate.parrot:です。
問題「ストアドプロシージャってやつでなんとかして！！！！」の解答を送らせていただきます。

問1, 2, 3で指定されたデータを取得するクエリを、それぞれq01, q02, q03という名前のストアドプロシージャとして登録いたしました。ストアドプロシージャ登録クエリは以下の通りになります。

問1

CREATE PROCEDURE q01()
BEGIN
SELECT employee_id, name, birthday, sex_name FROM Employees INNER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name = '男';
END

問2

CREATE PROCEDURE q02()
BEGIN
SELECT employee_id, name, TIMESTAMPDIFF(YEAR, `birthday`, CURDATE()) AS age, sex_name FROM Employees INNER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name = '女' AND TIMESTAMPDIFF(YEAR, `birthday`, CURDATE()) >= (SELECT AVG(TIMESTAMPDIFF(YEAR, `birthday`, CURDATE())) FROM Employees INNER JOIN Sex ON Employees.sex_id = Sex.sex_id WHERE sex_name = '女'); 
END

問3

CREATE PROCEDURE q03()
BEGIN
SELECT employee_id, name, TIMESTAMPDIFF(YEAR, `birthday`, CURDATE()) AS age, department_name, permit_name FROM Employees INNER JOIN Departments ON Departments.department_id = Employees.department_id INNER JOIN Permit ON Employees.permit_id = Permit.permit_id WHERE Employees.department_id IS NOT NULL AND Employees.permit_id IS NOT NULL;
END
Select a repo