多表查询练习
7种join操作
#中图
select employee_id,department_name
from employees e join departments d
on e.department_id=d.department_id
#左上
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
#右上
SELECT employee_id,department_name
FROM employees e right OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
#左中
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_id` IS NULL
#右中
SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`department_id` IS NULL
#左下 满连接
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`department_id` IS NULL
#右下
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`department_id` IS NULL
多表查询练习
表结构
题目
1.显示所有员工的姓名，部门号和部门名称。
select e.last_name,e.department_id,d.department_name
from employees e left join departments d
on e.department_id=d.department_id
2.查询90号部门员工的job_id和90号部门的location_id
select job_id,location_id
from employees e join department_id d
on e.department_id=d.department_id
where e.department_id=90
3.选择所有有奖金的员工的 last_name , department_name , location_id , city
select last_name,department_name,location_id,city
from employees e left join departments d
on e.department_id=d.department_id
left join locations l
on d.location_id=l.location_id
where e.commission_pct is not null
SELECT e.last_name ,e.`commission_pct`, d.department_name , d.location_id , l.city
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE e.`commission_pct` IS NOT NULL; #也应该是35条记录
4.选择city在Toronto工作的员工的 last_name , job_id ,
select e.last_name,e.job_id
from employees e join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
where l.city='Toronto'
5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
select d.department_name,l.street_address,e.last_name,e.job_id,e.salary
from employees e left join departments d
on e.department_id=d.department_id
left join location_id
on d.location_id=l.location_id
where department_name='Executive'
6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
select e.last_name "employees",e.employee_id "Emp",l.last_name "mageger" ,l.employee_id "Mgr"
from employees e left join employees l
on e.manager_id =l.employee_id
7.查询哪些部门没有员工(重点)
SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`department_id` IS NULL;
8. 查询哪个城市没有部门
SELECT l.location_id,l.city
FROM locations l left JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;
9. 查询部门名为 Sales 或 IT 的员工信息
select e.employee_id,e.last_name,e.department_id
from employees e join departments d
on e.`department_id`=d.`department_id`
where d.`department_name` in('Sales','IT')
