MySQL_尚硅谷第六章_多表查询l练习

最新推荐文章于 2025-03-07 16:30:53 发布

售前小肖

最新推荐文章于 2025-03-07 16:30:53 发布

阅读量244

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.youkuaiyun.com/qq_45846968/article/details/127723526

版权

本文提供了一系列SQL查询案例，包括员工信息的复杂联表查询、特定条件筛选等，帮助读者掌握SQL在实际场景中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

#答案
SELECT last_name, e.department_id, 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,d.department_id
FROM departments d, employees e
where d.department_id = e.department_id
and d.department_id = 90;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name,d.department_name,d.location_id,l.city,e.commission_pct
FROM employees e,departments d,locations l
WHERE e.commission_pct is not null;

SELECT e.last_name,d.department_name,d.location_id,l.city,e.commission_pct
FROM employees e
left OUTER JOIN departments d
on e.department_id = d.department_id
LEFT OUTER JOIN locations l

ON d.location_id = l.location_id
WHERE commission_pct IS not NULL;

#答案
SELECT e.last_name , d.department_name , d.location_id , l.city,e.commission_pct
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE 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,city
FROM employees e
LEFT JOIN departments d
on e.department_id = d.department_id

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

#答案
SELECT last_name, e.department_id, 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,d.department_id
FROM departments d, employees e
where d.department_id = e.department_id
and d.department_id = 90;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name,d.department_name,d.location_id,l.city,e.commission_pct
FROM employees e,departments d,locations l
WHERE e.commission_pct is not null;

SELECT e.last_name,d.department_name,d.location_id,l.city,e.commission_pct
FROM employees e
left OUTER JOIN departments d

on e.department_id = d.department_id
LEFT OUTER JOIN locations l

ON d.location_id = l.location_id
WHERE commission_pct IS not NULL;

#答案
SELECT e.last_name , d.department_name , d.location_id , l.city,e.commission_pct
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE 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,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 l.city in ('Toronto');

#答案
SELECT last_name , job_id , e.department_id , department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';
#或
SELECT last_name , job_id , e.department_id , department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON l.`location_id` = d.`location_id`
WHERE l.`city` = 'Toronto';

# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT d.department_name,l.street_address,e.last_name,e.job_id,e.salary
FROM employees e
JOIN departments d
on e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
AND d.department_name = 'Executive';

#答案
SELECT department_name, street_address, last_name, job_id, salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE department_name = 'Executive'



# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
-- employees Emp# manager Mgr#
-- kochhar   101  king    100
SELECT e1.last_name as employees,e1.employee_id as Emp,e2.last_name as manager,e2.employee_id as Mgr
FROM employees e1
JOIN employees e2
ON e1.manager_id = e2.employee_id;

#答案
SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

# 7.查询哪些部门没有员工
-- SELECT department_name
-- FROM departments 
-- LEFT OUTER JOIN  employees
-- ON employees.department_id = departments.department_id;
-- 

#答案
#方式1：
SELECT d.department_id,d.department_name
FROM departments d 
LEFT JOIN employees e
ON e.department_id = d.`department_id`
WHERE e.department_id IS NULL

#方式2：
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
							SELECT *
							FROM employees e
							WHERE e.`department_id` = d.`department_id`
							)


# 8. 查询哪个城市没有部门
SELECT l.city
FROM locations l
LEFT JOIN departments d
ON d.location_id = l.location_id
WHERE d.location_id is NULL;

#答案
SELECT l.location_id,l.city
FROM locations l 
LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL


# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT employee_id,last_name,d.department_id,salary
FROM employees e
JOIN departments d
on e.department_id = d.department_id
WHERE d.department_name = 'Sales' OR d.department_name = 'IT';

#答案
SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');

LEFT JOIN locations l
ON d.location_id = l.location_id
WHERE l.city in ('Toronto');

#答案
SELECT last_name , job_id , e.department_id , department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';
#或
SELECT last_name , job_id , e.department_id , department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON l.`location_id` = d.`location_id`
WHERE l.`city` = 'Toronto';

# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT d.department_name,l.street_address,e.last_name,e.job_id,e.salary
FROM employees e
JOIN departments d on e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
AND d.department_name = 'Executive';

#答案
SELECT department_name, street_address, last_name, job_id, salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE department_name = 'Executive'

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
-- employees Emp# manager Mgr#
-- kochhar 101 king 100
SELECT e1.last_name as employees,e1.employee_id as Emp,e2.last_name as manager,e2.employee_id as Mgr
FROM employees e1
JOIN employees e2
ON e1.manager_id = e2.employee_id;

#答案
SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

# 7.查询哪些部门没有员工
-- SELECT department_name
-- FROM departments
-- LEFT OUTER JOIN employees
-- ON employees.department_id = departments.department_id;
--

#答案
#方式1：
SELECT d.department_id,d.department_name
FROM departments d
LEFT JOIN employees e
ON e.department_id = d.`department_id`
WHERE e.department_id IS NULL

#方式2：
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
                           SELECT *
                           FROM employees e
                           WHERE e.`department_id` = d.`department_id`
                           )

# 8. 查询哪个城市没有部门
SELECT l.city
FROM locations l
LEFT JOIN departments d
ON d.location_id = l.location_id
WHERE d.location_id is NULL;

#答案
SELECT l.location_id,l.city
FROM locations l
LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL

# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT employee_id,last_name,d.department_id,salary
FROM employees e
JOIN departments d
on e.department_id = d.department_id
WHERE d.department_name = 'Sales' OR d.department_name = 'IT';

#答案
SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');