SQL練習　

概要

今回のテーマは、各役職タイプに対応する従業員の給与に対応する平均給与 avg を計算してください。

従業員役職テーブル titles

+--------+-----------------+------------+------------+
| emp_no | title           | from_date  | to_date    |
+--------+-----------------+------------+------------+
|  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  10003 | Senior Engineer | 2001-12-01 | 9999-01-01 |
|  10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
|  10006 | Senior Engineer | 2001-08-02 | 9999-01-01 |
|  10007 | Senior Staff    | 1996-02-11 | 9999-01-01 |
+--------+-----------------+------------+------------+

給与テーブル salaries

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 1986-06-26 | 9999-01-01 |
|  10003 |  43311 | 2001-12-01 | 9999-01-01 |
|  10004 |  74057 | 1995-12-01 | 9999-01-01 |
|  10006 |  43311 | 2001-08-02 | 9999-01-01 |
|  10007 |  88070 | 2002-02-07 | 9999-01-01 |
+--------+--------+------------+------------+

質問

各役職タイプに対応する従業員の給与に対応する平均給与 avg を計算してください。

解答

社員の役職表 titles と給与表 salaries を社員番号で結合し、役職別に分類する。

inner join と group by を使います。

select t.title, avg(s.salary)
from titles t inner join salaries s 
on t.emp_no = s.emp_no
where t.to_date = "9999-01-01"
and s.to_date = "9999-01-01"
group by t.title;

結果は以下の通り

+-----------------+---------------+
| title           | avg(s.salary) |
+-----------------+---------------+
| Senior Engineer |    62409.2500 |
| Senior Staff    |    88070.0000 |
+-----------------+---------------+

DDLとDMLは以下に添付しますので、好きな方はご利用ください。

drop table if exists  `salaries` ; 
drop table if exists  titles;
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'1995-12-01','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','2001-12-01','9999-01-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10006,'Senior Engineer','2001-08-02','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');