|
尚硅谷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;
|