sql練習　

Topic

すべての従業員とそれに対応するマネージャーを取得します。
従業員自身がマネージャーの場合は表示しません。

従業員テーブル dept_emp

+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d001    | 1986-06-26 | 9999-01-01 |
|  10002 | d001    | 1996-08-03 | 9999-01-01 |
|  10003 | d002    | 1995-12-03 | 9999-01-01 |
+--------+---------+------------+------------+

説明：
一行目は、従業員番号 10001 の部門が部門 d001 です。

部門マネージャ テーブル dept_manager

+---------+--------+------------+------------+
| dept_no | emp_no | from_date  | to_date    |
+---------+--------+------------+------------+
| d001    |  10002 | 1996-08-03 | 9999-01-01 |
| d002    |  10003 | 1990-08-05 | 9999-01-01 |
+---------+--------+------------+------------+

説明：
一行目は、部門 d001 のマネージャーが番号 10002 の従業員です。

質問

思考整理

• まず、すべてのマネージャーの従業員番号を見つける
• 次は、すべての一般従業員番号を見つける

順番にやて行きます。

解答

すべてのマネージャーの従業員番号

select emp_no from dept_manager;

すべての一般従業員番号

select emp_no, dept_no 
from dept_emp 
where emp_no not in 
(select emp_no from dept_manager);

最後に

select
    distinct employee.emp_no,　dept_manager.emp_no as manager
from
    (
        select
            emp_no,　dept_no
        from
            dept_emp
        where
            emp_no not in ( select emp_no from dept_manager )
    ) as employee,
    dept_manager
where
    employee.dept_no = dept_manager.dept_no;

結果は以下の通り

+--------+---------+
| emp_no | manager |
+--------+---------+
|  10001 |   10002 |
+--------+---------+

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

drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');

SQL