MySQL练习

#显示员工的姓名，部门号，和部门名称
SELECT last_name 姓名,departments.department_id 部门号,department_name 部门名称
FROM departments,employees
WHERE departments.department_id=employees.department_id;

#查询90号部门员工的job_id 和90号部门的location_id
SELECT job_id,location_id
FROM employees,departments
WHERE departments.department_id=employees.department_id
AND departments.department_id=90;

#查询有奖金的员工的姓名，部门名，地区编号，城市
SELECT last_name,department_name,locations.location_id,city
FROM employees,departments,locations
WHERE departments.department_id=employees.department_id and departments.location_id=locations.location_id AND employees.commission_pct is not NULL;

#查询city在Toronto工作的员工的姓名，工种号，部门号，部门名
SELECT last_name,job_id,departments.department_id,department_name,city
FROM employees,departments,locations
WHERE departments.department_id=employees.department_id
AND departments.location_id=locations.location_id
AND city='Toronto';

#查询每个工种，每个部门的部门名，工种名和最低工资
SELECT department_name,job_title,MIN(salary)
FROM departments,jobs,employees
WHERE departments.department_id=employees.department_id
AND employees.job_id=jobs.job_id
GROUP BY jobs.job_id,departments.department_id;

#查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(department_id)
FROM departments,locations
WHERE departments.location_id=locations.location_id
GROUP BY country_id
HAVING COUNT(department_id)>2;