06多表查询SQL练习

题目

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

select last_name,employees.department_id,department_name
from employees left outer join departments
on employees.department_id = departments.department_id;

注：遇到所有字眼时需要考虑外连接查询

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

select job_id,location_id
from departments join employees
on departments.department_id = employees.department_id
where departments.department_id = 90;

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

select last_name,department_name,locations.location_id,city
from employees join departments
on employees.department_id = departments.department_id
join locations
on departments.location_id = locations.location_id
where employees.commission_pct is not NULL;

4.选择city在Totonto工作的员工last_name,job_id,department_id,department_name

select last_name,job_id,employees.department_id,department_name
from employees join departments
on employees.department_id = departments.department_id
join locations
on departments.location_id = locations.location_id
where city = 'Toronto';

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

select department_name,city,last_name,job_id,salary
from employees join departments
on employees.department_id = departments.department_id
join locations
on departments.location_id = locations.location_id
where department_name = 'Executive';

6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于以下形式

select worker.last_name "employees",worker.employee_id "Emp#",manager.last_name "manager",manager.employee_id "man#"
from employees worker join employees manager
on worker.manager_id = manager.employee_id;

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

select departments.department_id,department_name
from employees right outer join departments
on employees.department_id = departments.department_id
where employees.employee_id is null;

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

select city
from departments right outer join locations
on departments.location_id = locations.location_id
where departments.department_id is null;

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

select last_name,employees.employee_id
from employees join departments
on employees.department_id = departments.department_id
where department_name = 'Sales' or department_name = 'IT';