第9章_子查询01

芳华梦落 2022-9-29 77 9/29

#1查询和Zlotkey相同部门的员工姓名和工资

SELECT last_name,salary
FROM employees
WHERE department_id=(SELECT department_id
FROM employees
WHERE last_name='Zlotkey'
);

#2查询工资比公司平均工资高的员工的员工号,姓名和工资

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees
);

#3选择工资大于所有job_id='SA_MAN'的员工的工资的员工last_name,job_id,salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary >ALL(SELECT salary
FROM employees
WHERE job_id='SA_MAN'
);

#4查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT employee_id,last_name
FROM employees
WHERE department_id IN (SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
);

#5查询在部门的location_id为1700的部门工作的员工的员工号

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

#6查询管理者是king的员工姓名和工资

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

#7查询工资最低的员工信息:last_name,salary

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

#8查询平均工资最低的部门信息

#方式1:
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(SELECT MIN(dep_avg_sal)
FROM (SELECT AVG(salary) dep_avg_sal
FROM employees
GROUP BY department_id) dep_avg_min_sal));
#方式2:
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<= ALL(SELECT AVG(salary) dep_min_sal
FROM employees
GROUP BY department_id));
#方式3:
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT AVG(salary) dep_avg_sal
FROM employees
GROUP BY department_id
ORDER BY dep_avg_sal
LIMIT 0,1));
#方式4:
SELECT d.*
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1) dept_avg_sal
WHERE d.department_id = dept_avg_sal.department_id

#9查询平均工资最低的部门信息和该部门的平均工资(相关子查询)

#方式1:
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id=d.`department_id`) avg_sal
FROM departments d
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(SELECT MIN(dep_avg_sal)
FROM (SELECT AVG(salary) dep_avg_sal
FROM employees
GROUP BY department_id) dep_avg_min_sal));
#方式2:
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id=d.`department_id`) avg_sal
FROM departments d
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<= ALL(SELECT AVG(salary) dep_min_sal
FROM employees
GROUP BY department_id));
#方式3:
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id=d.`department_id`) avg_sal
FROM departments d
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT AVG(salary) dep_avg_sal
FROM employees
GROUP BY department_id
ORDER BY dep_avg_sal
LIMIT 0,1));
#方式4:
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id=d.`department_id`) avg_sal
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 0,1) dept_avg_sal
WHERE d.department_id = dept_avg_sal.department_id

#10查询平均工资最高的job信息

#方式1:
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary)=(SELECT MAX(dep_avg_sal)
FROM (SELECT AVG(salary) dep_avg_sal
FROM employees
GROUP BY job_id) dep_avg_min_sal));
#方式2:
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary)>=ALL (SELECT AVG(salary) dep_avg_sal
FROM employees
GROUP BY job_id));
#方式3:
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary)=(SELECT AVG(salary) dep_avg_sal
FROM employees
GROUP BY job_id
ORDER BY dep_avg_sal DESC
LIMIT 0,1));
#方式4:
SELECT j.*
FROM jobs j,(
SELECT job_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal DESC
LIMIT 0,1) dept_avg_sal
WHERE j.`job_id` = dept_avg_sal.job_id
- THE END -

芳华梦落

10月08日15:49

最后修改:2022年10月8日
1

非特殊说明,本博所有文章均为博主原创。