子查询的相关例题

子查询的相关例题：

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

SELECT e1.last_name,e1.first_name,e1.salary
FROM employees e1
WHERE e1.department_id = (
	SELECT e2.department_id
	FROM employees e2
	WHERE e2.last_name='Zlotkey'
);

• 查询工资比公司平均工资高的员工号，姓名和工资

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

• 查询工资大于所有JOB_ID=‘SA_MAN’d的员工的工资 的 员工的last_name，job_id,salaty

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

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

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

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

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

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

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

• 查询工资最低的员工信息：last_name,salary

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

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

SELECT *
FROM departments
WHERE department_id = (
	SELECT e2.department_id
	FROM employees e2
	GROUP BY e2.department_id
	HAVING AVG(salary)=(

			SELECT MIN(avgsal)
			From(
				SELECT AVG( e1.salary) avgsal
				FROM employees e1
				GROUP BY e1.department_id
			) newtable
	)
);
 

SELECT *
FROM departments
WHERE department_id = (
	SELECT e2.department_id
	FROM employees e2
	GROUP BY e2.department_id
	HAVING AVG(salary)<= ALL(
				SELECT AVG( e1.salary) 
				FROM employees e1
				GROUP BY e1.department_id
			) 
	);

SELECT *
FROM departments
WHERE department_id = (
	SELECT e2.department_id
	FROM employees e2
	GROUP BY e2.department_id
	HAVING AVG(salary)=(
		SELECT AVG( e1.salary) avgsal
		FROM employees e1
		GROUP BY e1.department_id
		ORDER BY avgsal ASC
		LIMIT 1
		) 
);

SELECT d.*
FROM departments d,(
		SELECT department_id,AVG( e1.salary) avgsal
		FROM employees e1
		GROUP BY e1.department_id
		ORDER BY avgsal ASC
		LIMIT 1
) newtable
WHERE d.department_id=newtable.department_id;

• 查询平均工资最低的部门信息和该部门的平均工资

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id=d.department_id) avgsal
FROM departments d
WHERE department_id = (
	SELECT e2.department_id
	FROM employees e2
	GROUP BY e2.department_id
	HAVING AVG(salary)=(
		SELECT AVG( e1.salary) avgsal
		FROM employees e1
		GROUP BY e1.department_id
		ORDER BY avgsal ASC
		LIMIT 1
		) 
);

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

SELECT *
FROM jobs
WHERE job_id=(

SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) >= ALL (

SELECT AVG(salary)
FROM employees
GROUP BY job_id

)
);

• 查询平均工资高于公司平均工资的部门有那些

SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (

SELECT avg(salary)
FROM employees

);

• 查询公司中所有manager的详细信息

SELECT *
FROM employees
where employee_id IN (
	SELECT DISTINCT manager_id
	FROM employees
)

• 各个部门中，最高工资中最低的那个部门 最低工资是多少

SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)<= ALL(

SELECT MAX(salary)
FROM employees
GROUP BY department_id

)

• 查询平均工资最高的部门的manger的详细信息

SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
WHERE department_id IN (

SELECT department_id
FROM employees
GROUP BY department_id
HAVING avg(salary)>=ALL (
	SELECT avg(salary)
	FROM employees
	GROUP BY department_id
)
)
)

• 查询部门的部门号，其中不包括job_id是“ST_CLERK”的部门号

SELECT d.department_id
FROM departments d
WHERE NOT EXISTS(
	SELECT *
	FROM employees e
	WHERE d.department_id = e.department_id
	AND e.job_id = 'ST_CLERK'
);

SELECT department_id
FROM departments
WHERE department_id NOT IN(

SELECT DISTINCT department_id
FROM employees
WHERE job_id='ST_CLERK'
);

• 选择所有没有管理者的员工的last_name

SELECT last_name
FROM employees
WHERE manager_id IS NULL

SELECT last_name 
FROM employees emp
WHERE NOT EXISTS
(
	SELECT *
	FROM employees mgr
	WHERE emp.manager_id =mgr.employee_id
)

• 查询员工号、姓名、雇佣时间、工资，其中员工的管理者为“De Haan”

SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id IN (

SELECT employee_id
FROM employees
WHERE last_name='De Haan'

)



SELECT employee_id,last_name,hire_date,salary
FROM employees e1
WHERE EXISTS(
	SELECT *
	FROM employees e2
	WHERE e1.manager_id=e2.employee_id
	AND e2.last_name='De Haan'
)

• 查询每个部门下的部门人数大于5的部门名称

SELECT department_name
FROM departments d
WHERE 5<(
SELECT COUNT(1)
FROM employees e
where e.department_id=d.department_id
)

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

SELECT country_id
FROM locations l
WHERE 2<(
	SELECT COUNT(*)
	FROM departments d
	WHERE l.location_id=d.location_id
)

如果子查询相比较简单，建议从外往里写。一旦子查询结构比较复杂，建议从里往外写

如果是相关子查询，通常是从外往里写