子查询的相关例题:
- 查询和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
)
如果子查询相比较简单,建议从外往里写。一旦子查询结构比较复杂,建议从里往外写
如果是相关子查询,通常是从外往里写