黑猴子的家：mysql 多表连接（等值连接）练习

1、显示所有员工的姓名，部门号和部门名称

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

2、查询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;

3、选择所有有奖金的员工的 last_name , department_name , location_id , city

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

4、选择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';

5、查询每个工种、每个部门的部门名、工种名和最低工资

SELECT department_name,job_title,MIN(salary) 最低工资
FROM departments d ,employees e,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY e.`department_id`,e.`job_id`;

6、查询每个国家下的部门个数大于2的国家编号

SELECT COUNT(*) 部门个数,country_id
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`country_id`
HAVING 部门个数>2;

7、表数据查看

SELECT * FROM departments;
SELECT * FROM locations;