#1.显示所有员工的姓名，部门号和部门名称
SELECt emp.first_name,emp.last_name,emp.department_id,dep.department_name FROM employees emp
LEFT JOIN departments dep ON emp.department_id = dep.department_id;
#2.查询90号部门员工的job_id(employees表中)和90号部门的location_id(departments表中)
SELECt emp.department_id,emp.first_name,emp.last_name,emp.job_id,dep.location_id FROM employees emp
LEFT JOIN departments dep ON emp.department_id = dep.department_id WHERe emp.department_id = 90;
#3.选择所有有奖金员工的last_name,department_name(departments表中),location_id(locations表中),city(locations表中)
SELECt emp.commission_pct,emp.first_name,emp.last_name,dep.department_name,loc.location_id,loc.city FROM employees emp
LEFT JOIN departments dep ON emp.department_id = dep.department_id
LEFT JOIN locations loc ON dep.location_id = loc.location_id WHERe emp.commission_pct IS NOT NULL;
#4.选择city在Toronto工作的员工last_name,job_id(employees表中),department_id(employees表中和departments表中),department_name(departments表中)
SELECt loc.city,emp.first_name,emp.last_name,job_id,emp.department_id,dep.department_name FROM employees emp
LEFT JOIN departments dep ON emp.department_id = dep.department_id
LEFT JOIN locations loc ON dep.location_id = loc.location_id WHERe loc.city = "Toronto";
#5.查询员工所在的部门名称(departments表中)，部门地址(locations表中)，姓名，工作，工资其中员工所在部门的部门名称为"Executive"
SELECt emp.first_name,emp.last_name,dep.department_name,loc.street_address,emp.job_id,emp.salary FROM employees emp
LEFT JOIN departments dep ON emp.department_id = dep.department_id
LEFT JOIN locations loc ON dep.location_id = loc.location_id WHERe dep.department_name = "Executive";
#6.选择指定员工的姓名，员工号以及他的管理者姓名和员工号，结果类似于下面格式
#employees Emp# manager Mgr#
#kochhar(last_name) 101(employee_id) king(last_name) 100
SELECt emp.first_name,emp.last_name,emp.employee_id,mgr.first_name,mgr.last_name,mgr.employee_id FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.employee_id WHERe mgr.employee_id IS NOT NULL;
#7.查询哪些部门没有员工
SELECt dep.department_name,dep.department_id,emp.first_name,emp.last_name FROM departments dep
LEFT JOIN employees emp ON emp.department_id = dep.department_id WHERe emp.first_name IS NULL;
#8.查询哪个城市没有部门
SELECt loc.city,dep.department_id,dep.department_name FROM locations loc
LEFT JOIN departments dep ON loc.location_id = dep.location_id WHERe dep.location_id IS NULL;
#9.查询部门名为Sales或IT的员工信息
SELECt emp.first_name,emp.last_name,dep.department_name FROM employees emp
LEFT JOIN departments dep ON emp.department_id = dep.department_id WHERe dep.department_name IN("Sales","IT");
