MySQL多表连接查询练习

一、笛卡尔集&内连接

1.查询90号部门员工的job_id和90号部门的location_id

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

2.选择所有有奖金的员工的last_name,department_name,location_id,city

SELECT last_name,department_name,l.location_id,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND e.`commission_pct` IS NOT NULL;

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

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

二、外连接

1.查询城市名包含a字符的哪个城市没有部门，并按城市名降序

SELECT city
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL
AND city LIKE '%a%'
ORDER BY city DESC;

2.查询部门名为SAL或IT的员工信息

SELECT e.*,d.`department_name`
FROM departments d
LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE d.`department_name` IN('SAL','IT');

三、子查询

1.返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);

2.查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT MIN(salary),department_id
FROM employees 
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees 
WHERE department_id=50
);

3.查询管理者是King的员工姓名和工资

SELECT last_name,salary
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name='K_ing'
);

4.查询在部门的location_id为1700的部门工作的员工的员工号

SELECT employee_id
FROM employees
WHERE department_id =ANY(
SELECT DISTINCT department_id
FROM departments
WHERE location_id =1700
);