子查询的相关例题

子查询的相关例题:

  • 查询和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
)

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

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值