# 1.显示所有员工的姓名,部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT 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,departments d
WHERE e.department_id = d.department_id && e.department_id = 90;
SELECT job_id,location_id
FROM employees e JOIN departments d ON e.department_id = d.department_id
WHERE e.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 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;
# 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,departments d,locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.city = 'Toronto';
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.salary,j.job_title
FROM employees e JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
JOIN jobs j ON e.job_id = j.job_id
WHERE d.department_name = 'Executive';
SELECT* FROM employees WHERE department_id = 90;
# 6.选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式 employees Emp# manager Mgr# kochhar 101 king 100
SELECT e.last_name AS employees, e.employee_id AS "Emp#", m.last_name AS manger,m.employee_id AS "Mgr#"
FROM employees e ,employees m
WHERE e.last_name = 'kochhar' AND e.manager_id = m.employee_id;
# 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 l.city,l.location_id
FROM departments d RIGHT JOIN locations l ON d.location_id = l.location_id
WHERE d.location_id IS NULL;
# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.last_name,e.employee_id,e.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id AND (d.department_name = 'Sales' OR d.department_name = 'IT')
数据库上篇-04:多表查询(练习)
最新推荐文章于 2022-03-17 23:17:30 发布