#第06章_多表查询练习题
# 1.显示所有员工的姓名,部门号和部门名称。 看见所有的,外连接问题
SELECT e.last_name,d.department_id,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` =d.`department_id`;
# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT e.job_id,d.location_id
FROM employees e JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_id`=90;
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city(外连接)
SELECT e.last_name, d.department_name , d.location_id , l.city
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
LEFT JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE e.commission_pct IS NOT NULL; #少了一个,因为有一个人没有部门,但是有奖金
DESC departments
DESC locations
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL; # 有35条
# 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
ON e.`department_id`=d.`department_id`
JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE l.`city`='Toronto';
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资,其中员工所在部门的部门名称为’Executive’
SELECT d.department_name,l.street_address,e.last_name,e.job_id,e.salary
FROM departments d LEFT JOIN employees e
ON e.`department_id`=d.`department_id`
LEFT JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_name`='Executive';
DESC departments
DESC locations
# 6.选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
#错误写法:
SELECT e.last_name "employees",e.employee_id "Emp",mgr.last_name "manager",mgr.employee_id "Mgr"
FROM employees e JOIN employees mgr
ON e.manager_id = mgr.employee_id; #查出106条,因为有个人没有mgr
SELECT e.last_name "employees",e.employee_id "Emp",mgr.last_name "manager",mgr.employee_id "Mgr"
FROM employees e LEFT JOIN employees mgr
ON e.manager_id = mgr.employee_id;
DESC employees
# 7.查询哪些部门没有员工
SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;
# 8. 查询哪个城市没有部门 (说明城市比部门多)
SELECT d.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.location_id = d.`location_id`
WHERE d.`location_id` IS NULL;
SELECT department_id
FROM departments
WHERE department_id IN (1000,1100,1200,1300,1600);
# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.employee_id,e.last_name,d.department_id
FROM employees e JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_name` IN('Sales','IT');
第06章_多表查询练习题#第06章_多表查询练习题# 1.显示所有员工的姓名,部门号和部门名称。 看见所有的,外连接问题SELECT e.last_name,d.department_id,d.department_nameFROM employees e LEFT OUTER JOIN departments dON e.`department_id` =d.`department_id`;# 2.查询90号部门员工的job_id和90号部门的location_idSELECT e.j