子查询的相关例题

子查询的相关例题：

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

SELECT e1.last_name,e1.first_name,e1.salary
FROM employees e1
WHERE e1.department_id = (SELECT e2.department_idFROM employees e2WHERE 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 salaryFROM employees WHERE job_id= 'SA_MAN'
);

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

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

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

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

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

SELECT e1.last_name,e1.salary
FROM employees e1
WHERE e1.manager_id IN
(SELECT employee_idFROM employeesWHERE 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_idFROM employees e2GROUP BY e2.department_idHAVING AVG(salary)=(SELECT MIN(avgsal)From(SELECT AVG( e1.salary) avgsalFROM employees e1GROUP BY e1.department_id) newtable)
);

SELECT *
FROM departments
WHERE department_id = (SELECT e2.department_idFROM employees e2GROUP BY e2.department_idHAVING AVG(salary)<= ALL(SELECT AVG( e1.salary) FROM employees e1GROUP BY e1.department_id) );

SELECT *
FROM departments
WHERE department_id = (SELECT e2.department_idFROM employees e2GROUP BY e2.department_idHAVING AVG(salary)=(SELECT AVG( e1.salary) avgsalFROM employees e1GROUP BY e1.department_idORDER BY avgsal ASCLIMIT 1) 
);

SELECT d.*
FROM departments d,(SELECT department_id,AVG( e1.salary) avgsalFROM employees e1GROUP BY e1.department_idORDER BY avgsal ASCLIMIT 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_idFROM employees e2GROUP BY e2.department_idHAVING AVG(salary)=(SELECT AVG( e1.salary) avgsalFROM employees e1GROUP BY e1.department_idORDER BY avgsal ASCLIMIT 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_idFROM 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 employeesGROUP BY department_id
)
)
)

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

SELECT d.department_id
FROM departments d
WHERE NOT EXISTS(SELECT *FROM employees eWHERE d.department_id = e.department_idAND 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 mgrWHERE 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 e2WHERE e1.manager_id=e2.employee_idAND 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 dWHERE l.location_id=d.location_id
)

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

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