MySQL练习

#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");