[打卡]多表查询练习题

SELECT last_name,e.department_id,department_name
from employees e
LEFT JOIN departments d
on e.department_id=d.department_id;
#把左表当成员工表
SELECT last_name,e.department_id,department_name
from departments d
RIGHT JOIN employees e
on e.department_id=d.department_id;
#把右表当成员工表
#1.显示所有员工的姓名，部门号和部门名称
SELECT job_id,location_id
from employees e,departments d
where e.department_id=d.department_id
and d.department_id=90;

SELECT job_id,location_id
from employees e join departments d
on e.department_id=d.department_id
WHERE d.department_id=90;
#2.查询90号部门员工的job_id和90号部门的location_id
SELECT last_name,e.department_id,department_name
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and city='Toronto';

SELECT last_name,e.department_id,department_name
from employees e join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
WHERE city='Toronto';
#3.选择city在Toronto工作的员工的last_name,job_id,department_id , department_name
SELECT department_name,street_address,last_name,job_id,salary
from employees e JOIN departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
and department_name='Executive';

#4.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为‘Executive’
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');
-- 错误示范-- 有歧义 ------------
SELECT employee_id,last_name,department_name
from employees e,departments d
where e.department_id=d.department_id
and d.department_name = 'Sales'
and d.department_name ='IT';
#5.查询部门名为sales或IT的员工信息