多表查询练习

多表查询练习

1.显示所有员工的姓名，部门号和部门名称。

SELECT e.last_name,d.department_id,d.department_name ,d.location_id 
from employees e LEFT JOIN departments d 
USING (department_id);

2.查询90号部门员工的job_id和90号部门的location_id

SELECT e.job_id,d.location_id from employees e,departments d 
WHERE e.department_id= d.department_id and d.department_id = 90;

3.选择所有有奖金的员工的 last_name ，department_name ，location_id ，city

SELECT e.last_name ,d.department_name ,l.location_id ,l.city ,e.commission_pct
 from (employees e LEFT JOIN departments d using(department_id)) LEFT JOIN locations l USING (location_id)
 WHERE e.commission_pct IS not null;

4.选择city在Toronto工作的员工的 last_name ，job_id ，department_id ，department_name

 SELECT  e.last_name, e.job_id, d.department_id,  d.department_name ,l.city
 from (employees e JOIN departments d USING (department_id)) JOIN locations l USING (location_id)
 WHERE l.city LIKE 'Toronto';

5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

 SELECT d.department_name , l.street_address, e.last_name, e.job_id, e.salary
 from (employees e JOIN departments d USING (department_id)) JOIN locations l USING (location_id)
 WHERE d.department_name LIKE 'Executive';

6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号.(自连接)

 SELECT e.last_name, e.employee_id,m.last_name,m.manager_id
 from employees e LEFT join employees m
 on e.manager_id = m.employee_id;

7.查询哪些部门没有员工

 SELECT e.last_name, d.department_name,d.department_id 
 from employees e RIGHT JOIN departments d USING (department_id) 
 WHERE e.department_id IS NULL;

8.查询哪个城市没有部门

 SELECT * FROM  locations l  LEFT JOIN  departments d USING(location_id)
 where d.location_id IS null;

9. 查询部门名为 Sales 或 IT 的员工信息

 SELECT * FROM employees e JOIN departments d USING(department_id)
 WHERE d.department_name IN ('Sales','IT');