MySQL|多表查询-练习

尚硅谷21.11，shk，MySQL基础课程，多表查询练习题

题目见课件

-- 多表查询-1

#1
SELECT e.last_name,e.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;

#2

SELECT e.job_id,d.department_id,d.location_id
FROM departments d,employees e
WHERE d.department_id = 90;

SELECT d.department_id,e.job_id,d.location_id
FROM departments d JOIN employees e
ON d.department_id = 90; 

# 3.选择所有有奖金的员工的 last_name , department_name , 
# location_id , city (三表查询)

SELECT*
FROM locations;

SELECT e.last_name , d.department_name ,  l.location_id , l.city
FROM employees e,departments d,locations l
WHERE e.commission_pct>0
AND e.department_id = d.department_id
AND d.location_id=l.location_id;

# 4.选择city在Toronto工作的员工的 last_name , job_id ,
# department_id , department_name

SELECT e.last_name, e.job_id, d.department_name,
d.department_id,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';


# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，
# 其中员工所在部门的部门名称为’Executive’

SELECT e.last_name,l.city,d.department_name,e.job_id,e.salary
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND d.department_name = 'Executive';


# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，
# 结果类似于下面的格式

# employees Emp# manager Mgr#
# kochhar   101   king   100

SELECT emp.last_name,mgr.employee_id 'Emp#',
mgr.last_name,mgr.employee_id 'Mgr#'
FROM employees emp,employees mgr
WHERE emp.manager_id = mgr.employee_id;


# 7.查询哪些部门没有员工

SELECT *
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE employee_id IS NULL;

# 8.查询哪个城市没有部门

SELECT *
FROM departments d RIGHT JOIN locations l
ON d.location_id = l.location_id
WHERE department_id IS NULL;

# 9. 查询部门名为 Sales 或 IT 的员工信息


SELECT *
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'Sales' OR d.department_name = 'IT';

-- 多表查询-2

#1 笨办法，等下看高效算法
SELECT *
FROM t_emp e,t_dept d
WHERE e.deptId = d.id;


#2
SELECT e.id,e.name,d.deptName
FROM t_emp e LEFT JOIN t_dept d
ON e.deptId = d.id;

#3
SELECT *
FROM  t_dept d;

#4
SELECT *
FROM t_emp
WHERE deptId IS NULL;

#5 不会

#思路很简单，先把B和A的偏B全集搞到，然后取A中没有的部分，就是B独有的
SELECT *
FROM t_dept b LEFT JOIN t_emp a
ON a.deptId = b.id
WHERE a.id IS NULL;


#6 
SELECT *
FROM t_emp e RIGHT JOIN t_dept d
ON e.deptId = d.id
UNION 
SELECT *
FROM t_emp e LEFT JOIN t_dept d
ON e.deptId = d.id;


#7 不会
-- 选出门派中没人的选项
SELECT *
FROM t_emp e RIGHT JOIN t_dept d
ON e.deptId = d.id
-- where中可以用上面表里已经出现的列名做选择，但是必须是原表拥有的列名
WHERE e.age IS NULL
-- 做两次选择，用UNION联合起来
UNION 
-- 选出没有门派的人
SELECT *
FROM t_emp e LEFT JOIN t_dept d
ON e.deptId = d.id
WHERE e.deptId IS NULL;