表数据：https://www.cnblogs.com/zhishu/p/16452950.html
1.显示所有员工的姓名，部门号和部门名称。
所有员工，用left join。
left outer join和left join的效果是一样的。
#107条数据，有的员工没有部门
SELECT a.last_name,a.department_id,b.department_name
FROM employees a
LEFT JOIN departments b ON a.department_id = b.department_id;
#错误的，106条数据
SELECT a.last_name,a.department_id,b.department_name
FROM employees a,departments b
WHERE a.department_id = b.department_id;
2.查询90号部门员工的job_id和90号部门的location_id
SELECT a.job_id,b.location_id
FROM employees a
JOIN departments b ON a.department_id = b.department_id
WHERE b.department_id = '90';
#不用join的方式
SELECT a.job_id,b.location_id
FROM employees a,departments b
WHERE a.department_id = b.department_id AND b.department_id = '90';
3.选择所有有奖金的员工的 last_name , department_name , location_id , city
所有员工，使用left join
SELECT a.last_name,b.department_name,b.location_id,c.city
FROM employees a
LEFT JOIN departments b ON a.department_id = b.department_id
LEFT JOIN locations c ON b.location_id = c.location_id
WHERE a.commission_pct IS NOT NULL;
4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT a.last_name,a.job_id,a.department_id,b.department_name
FROM employees a
JOIN departments b ON a.department_id = b.department_id
JOIN locations c ON b.location_id = c.location_id
WHERE c.city = 'Toronto';
#或
SELECT a.last_name,a.job_id,b.department_id,b.department_name
FROM employees a,departments b,locations c
WHERE a.department_id = b.department_id AND b.location_id = c.location_id;
5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT b.department_name,CONCAT(c.state_province,c.street_address) AS address,a.last_name,d.job_title,a.salary
FROM employees a
JOIN departments b ON a.department_id = b.department_id
JOIN locations c ON b.location_id = c.location_id
JOIN jobs d ON a.job_id = d.job_id
WHERE b.department_name = 'Executive';
6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
SELECT emp.last_name AS "employees",emp.employee_id AS "Emp",mgr.last_name AS "manager",mgr.employee_id AS "Mgr"
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.employee_id;
7.查询哪些部门没有员工
b表的哪个字段为空都行，一般写b.department_id IS NULL
#第一种方式
SELECT a.department_id,a.department_name
FROM departments a
LEFT JOIN employees b ON a.department_id = b.department_id
WHERE b.employee_id IS NULL;
#第二种方式：查询不存在相等的，即为没有部门的员工
SELECT a.department_id,a.department_name
FROM departments a
WHERE NOT EXISTS(
SELECT *
FROM employees b
WHERE a.department_id =b.department_id
);
8. 查询哪个城市没有部门
SELECT a.city
FROM locations a
LEFT JOIN departments b ON a.location_id = b.location_id
WHERE b.department_id IS NULL;
9. 查询部门名为 Sales 或 IT 的员工信息
SELECT a.employee_id,a.last_name,b.department_name
FROM employees a
JOIN departments b ON a.department_id = b.department_id
WHERE b.department_name = 'Sales' OR b.department_name = 'IT';
#或
SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');