MySQL子查询

#列子查询
#返回 location_id是1400或1700的部门中所有员工姓名
SELECT last_name,location_id
FROM employees,departments
WHERE departments.department_id=employees.department_id
AND location_id IN
(SELECT location_id 
FROM departments
WHERE departments.location_id in(1400,1700)
);


SELECT last_name
FROM employees
WHERE  department_id IN 
(
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
);

#返回其他工种中比job-id为'IT_PROG'工种任一工资低的员工的员工号，姓名，job_id以及salary
SELECT employee_id,last_name,salary
FROM employees
WHERE salary<ANY
(
SELECT salary 
FROM employees
WHERE job_id='IT_PROG'
)
AND job_id<>'IT_PROG'

#行子查询
# 查询员工编号最小且工资最高的员工信息
SELECT *
FROM employees
WHERE employee_id=
(SELECT MIN(employee_id)
FROM employees)
AND salary= 
(SELECT MAX(salary)
FROM employees)

#SELECT 后面
#查询每个部门的员工个数
SELECT  department_id,IF(employee_id,COUNT(*),0) 个数
FROM employees
GROUP BY department_id;


SELECT department_id,(SELECT COUNT(*)	
											FROM employees
											WHERE employees.department_id=departments.department_id
											) 个数
											FROM departments
											ORDER BY department_id;
#查询员工号为102的部门名
SELECT 
(
SELECT department_name
FROM departments,employees
WHERE departments.department_id=employees.department_id
AND employees.employee_id=102
) 部门名;

SELECT department_name 部门名
FROM departments
WHERE department_id=
(SELECT department_id
FROM employees
WHERE employee_id=102
);

#FROM 后面
#查询每个部门的平均工资的工资等级
SELECT  ag_dep.*,job_grades.grade_level
FROM (
    SELECT avg(salary) ag,department_id
		FROM employees
		GROUP BY department_id
		) ag_dep
INNER JOIN job_grades
ON  ag_dep.ag BETWEEN lowest_sal AND highest_sal