子查询练习1

1.查询工资最低的员工的信息,这个不难吧,工资最低员工的信息,看最低工资是多少,这作为一个内查询的结果,

返回给外层,那就select last_name,salary from employees,大家刚开始写的时候,不要太着急,你看我现在的时候

是从外层写的,然后where你看salary等于多少,这怎么写,你看一下最低工资是多少,然后看看谁的工资等于最低工资,

我们那会写过类似的了,select,最低工资,这个就是返回公司当中员工的最低工资,然后看看谁的最低工资等于最低的,

这个人的信息

select last_name,salary from employees where salary > (select min(salary) from employees)

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

查询平均工资最低的部门信息,平均工资最低的部门信息,我们说了,两种方式,一种是从内到外,一种是从外

到内,假设我们从内到外的话,平均工资最低的部门信息,最终肯定是求部门信息,这个肯定是外层了,我要是想

得到部门信息,你这个条件是什么,就是平均工资最低,第一步我是要先求出来,或者叫查询,查询公司的平均工资

是多少,查询公司的平均工资是多少,这个是不是得按照部门来,查询公司中各部门的,平均工资是多少,这一步查到

以后,下一步就是从各部门里面,有好多,我们看看谁的是最少的,找到平均工资中最少的那个,就是最少的平均工资,

最少的平均工资是多少,然后看看哪个部门的平均工资是最少的,这就是我们第三步要查的,哪个部门的平均工资等于

上一步第二步的结果,我这里就这样写了,大家想一想是这个意思不,我先查一下平均工资是多少,看看谁的平均工资

等于最少的,我们先做第一步,第一步不难,查询一下各部门的平均工资,按照部门来查,查平均工资,按照他查找平均工资,

然后找平均工资里面最少的

select avg(salary) from employees group by department_id

然后我直接一步往下放,找这里面最少的,我只需要这里面加一个min,现在求出来的就是最少的平均工资是多少

select min(avg(salary)) from employees group by department_id

然后我看看哪个部门的平均工资和这个一样呢,哪个部门的平均工资和这个一样呢,那你就要找这个部门,

那你就相当于select一下,哪个部门,平均工资,假设我需要找到这个id吧,from employees,看看哪个部门的

avg(salary)等于刚才算出来的这个,哪个部门等于它,哪个部门department_id,这里又有一个group by,你有

一个分组,按照部门来分,看看谁的平均工资等于刚才我们计算出来的这个,所以把它拿过来,这个题目我们就

做完了,先求出来平均工资最低的部门的信息,平均工资最低的部门信息,我先找到最低的平均工资是多少,

这是最低的平均工资,我看哪个部门的平均工资等于你的最低工资,然后得到结果,此处不允许使用分组函数,

我们这里要改成having,如果条件出现avg的话,出现了组函数的话,要把它改成having,来表示过滤,这个时候我们

再运行

select department_id from employees having avg(salary) = (select min(avg(salary)) from employees

group by department_id) group by department_id

50号部门,他想让你查询一下这个部门的信息,比如这个部门的位置在哪啊,或者你这个部门的名字叫什么啊,

这是不是相当于使用了另一个表,哪个部门的我们相当于还要再说一句,第四步,查询此部门的部门信息,这是我们

第四步要查询的,部门信息的话,那你就得是,select,我们就这样写吧,select * from departments,where department_id

等于我们刚才查询到的50号部门,所以你还得把它整个给扔进来,调整格式,这样吧,我们看一下

select * from departments where department_id = (select department_id from employees having 

avg(salary)=(select min(avg(salary)) from employees group by department_id) group by department_id)

50号部门,名字叫Shipping,manager_id,location_id,这是我们题目最终得到的结果,相当于我们通过四步查询,

分了四步,这个题目如果太复杂的话,我们可以考虑从内到外,一点点给他算出来,你看43题

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

select d.*, (select avg(salary) from employees where department_id = d.department_id)
from departments d
where d.department_id = (
      SELECT department_id
      FROM employees
      GROUP BY department_id 
      HAVING avg(salary) = (
			 SELECT min(avg(salary))
			 FROM employees
			 GROUP BY department_id
			  ) 
      )

跟这个题目有点像,但是还是有点不同,这个不仅查询部门信息还要查询一下平均工资,

那这个平均工资该怎么求,至少这个题目和这个有点像的,也得有这样几步,我们在这个题目的基础上我们来改一改,

现在我这个查询的效果我仅仅是把这个部门给列出来了,他除了让你找这个部门信息,还让你找这个部门的平均工资,

要求你这里输出了一个*,还得来一个avg,这个东西,输出这个部门的平均工资,这个部门的平均工资我们这里应该怎么去操作,

从这个结果看是50号部门,相当于找50号部门的平均工资,假设让大家输出50号部门的平均工资的话能不能输出来,这个题目稍微

有点难度的,这里我就直接这样来写了,我们这里再加入一个select,select什么啊,avg平均工资,from 当然还是employees这个表,

这个表你得有个条件吧,什么条件,就是你要查询的部门,相当于department_id得是50,这个语句表示的就是50号部门的平均工资,

他查询的是50号部门的平均工资,但是这个时候50号部门还不知道呢,最后我查询一下才知道是50,所以我们这里不能这样写,

我们这里写一个d,这是一个d,这里相当于是d.department_id来替代一下,你要查询的这个id,和我外层的这个d,它是一样的,

有一个连接,然后这个*是一个d点*

select d.*,(select avg(salary) from employees where department_id=d.department_id) from departments d

where department_id = (select department_id from employees having avg(salary)=(select min(avg(salary))

from employees group by department_id) group by department_id)

这个题目我们就这样操作完成了,这是有一些难度的,大家需要注意了,因为这里相当于后面这个信息,是用的

employees表,前面用的是departments这个表,那你要想他们两个在一个结果当中给他出现了,我们可以考虑

使用两个,他这个也相当于是一个子查询

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

	1). 按 job_id 分组, 查询最高的平均工资	
	SELECT max(avg(salary))
	FROM employees
	GROUP BY job_id
	
	2). 查询出平均工资等于 1) 的 job_id
	SELECT job_id
	FROM employees
	GROUP BY job_id
	HAVING avg(salary) = (
		SELECT max(avg(salary))
		FROM employees
		GROUP BY job_id
	)
	
	3). 查询出 2) 对应的 job 信息
	SELECT *
	FROM jobs
	WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING avg(salary) = (
			SELECT max(avg(salary))
			FROM employees
			GROUP BY job_id
		)
	)

查询平均工资最高的job的信息,平均工资最高的job的信息,第一步捋一下,是不是要查询一下平均工资里面最高的是

多少,就是按job来分类,或者叫分组,查询最高的 平均工资是多少,这是第一步,第二步,我们得看一下哪个job_id的

平均工资等于最高的,查询得到哪个job_id的平均工资等于上问等于1得到的值,哪个job_id等于1得到最高的,这是不

是这个job_id找到的,job_id找到了就好办了,我再根据另一个表,就jobs表,从jobs表中返回job_id他的对应项的信息,

大家可以试着写这个问题了,那我们先看第一步,按job来分组,查询到最高的平均工资,select先找到平均工资,得分组吧,

group by job_id,按他来分组,我们找到各个job_id的平均,里边来找最大的,就是他,最大的平均工资

select max(avg(salary)) from employees group by job_id

然后看看哪个平均工资等于这个,那就是select我要找到你这个id,from employees,然后group by job_id,

那么这个条件还得写在这,where,看看avg(salary),看看谁等于这个最大值,那相当于把这个结果作为一个值返回给我们,

这个外层的过滤条件这,谁的平均工资等于这个最大的,现在找到这个job_id了

select job_id from employees having avg(salary) = (select max(avg(salary)) 

from employees group by job_id) 

group by job_id

id找到了,然后看看他的基本信息,select * from jobs,where job_id等于,这里面有一点需要注意的是啥,我们这里面

是一个值,单行的,有可能平均工资等于这个的,有可能是有多个,恰好有两个是一样的,那你这里用等号有可能会出问题,

我们先拿过来演示看看

select * from jobs where job_id = (select job_id from employees having avg(salary) = 

(select max(avg(salary)) from employees group by job_id) group by job_id)

尽量把格式给他对齐一下,大家看到这个问题了吧,他说此处不允许使用组函数,是这儿错了,先给他改成having,因为这是一个

组函数

这个结果出来了,那是因为这里恰好返回的是一个部门,最大的就是他自己,假设要有多个的话,

大家是不是要给他改成in

select * from jobs where job_id in (select job_id from employees having avg(salary)=

(select max(avg(salary)) from employees group by job_id) group by job_id)

改成in肯定是没错的,等号有时候是会出问题的,这是我们讲的这个题目,通过这几个题目大家应该越来越

熟悉怎么去写这个子查询,尤其这个题目比较复杂的时候,建议大家从内到外

45. 查询平均工资高于公司平均工资的部门有哪些?

	1). 查询出公司的平均工资
	SELECT avg(salary)
	FROM employees
	
	2). 查询平均工资高于 1) 的部门 ID
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING avg(salary) > (
		SELECT avg(salary)
		FROM employees
	)

查询平均工资高于公司平均工资的有哪些,我就得到部门id就行,平均工资高于公司平均工资,

我们就直接来写吧,他不是要查一下department_id吗,我顺便给你列一下你的平均工资是多少,

from employees,因为有一个非组函数,要写在group by当中,然后需要注意,是不是要看看各个部门的

avg(salary),比公司的平均工资高的,大于公司的平均工资,不用where就搞定了,平均工资高于公司的

平均工资的,高于公司的平均工资的,有哪些部门,就这样呗

select department_id,avg(salary) from departments group by department_id having 

avg(salary)>(select avg(salary) from employees)

6. 查询出公司中所有 manager 的详细信息.
	1). 查询出所有的 manager_id
	SELECT distinct manager_id
	FROM employeess
	
	2). 查询出 employee_id 为 1) 查询结果的那些员工的信息
	SELECT employee_id, last_name
	FROM employees
	WHERE employee_id in (
		SELECT distinct manager_id
		FROM employees
	)

查询出所有manager的详细信息,所有Manager的详细信息,你看manager_id是多少,manager的employee_id

给他输出来,这不就是这样,select详细信息那我就select*,from employees,所有manager的详细信息,那employee_id

恰好我这里要查询到的,返回manager_id,那你这样写的话,就得写in了,因为你不止一个employee_id,select manager_id,

from employees,这里查询所有是老板,什么叫老板,是管理者的,id都是哪些,他们这些人本身不就是employee吗,把它们

的信息都给输出出来,多行没法返回给一行的

select * from employees where employee_id = (select manager_id from employees)

select * from employees where employee_id = (select manager_id from employees)

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

47. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
	1). 查询出各个部门的最高工资
	SELECT max(salary)
	FROM employees
	GROUP BY department_id
	
	2). 查询出 1) 对应的查询结果的最低值: 各个部门中最低的最高工资(无法查询对应的 department_id)
	SELECT min(max(salary))
	FROM employees
	GROUP BY department_id
	
	3). 查询出 2) 所对应的部门 id 是多少: 各个部门中最高工资等于 2) 的那个部门的 id
	SELECT department_id
	FROM employees
	GROUP BY department_id 
	HAVING max(salary) = (
		SELECT min(max(salary))
		FROM employees
		GROUP BY department_id
	)

我们一点点来理会这个题,他说各个部门中,最高工资中,各个部门都有一个最高工资,从最高工资里面找最低工资

的部门,那个部门里面的最低工资,看我举个例子,是这个意思,我们这里的各个部门,我们就以这个部门来说,假设

我这里有4个部门,A,B,C,D四个部门,这里有各个部门的公司员工,各个部门有最低的工资,也有最高的工资,还有其他的

乱七八糟的,然后你把最高的工资给拿出来,然后这个四个部门,把最高工资亮一亮,然后这个部门发现6500,这个7500,

这是8500,这个就是10000,这几个数,这几个最高工资里面,最低的就是6500,我找6500部门里面的,你这个部门里面的

最高的是6500,我找你这个部门里面最低的,我就想把你这里的2000给找到,这个题目大家看个明白,题目看懂以后,

我得找到这个最高工资,最高工资里面要看哪个最低,所以select最高max,from employees,得加上一个group by,

按照department_id来进行分组,分组以后我看看

select max(salary) from employees group by department_id

然后我看看这里面最小的那个

select min(max(salary)) from employees group by department_id

现在谁的最高工资等于4400,我想查询这个部门department_id,from employees,

where什么,having你的最高工资,等于我们刚才的这个值,然后你这里用的分组了,所以我们还得给你group by一下,

group by department_id,然后这个拿过来

select department_id,max(salary) from employees having max(salary) = (select min(max(salary)) 

from employees 

group by department_id) group by department_id

现在我就找到这个id了,我就看哪个部门是这么多

而且是这里面最少的一个

然后我看你10号部门的最低工资,那就好做了,select min(salary) from employees,

department_id等于10号部门,只不过我是通过查询的方式得到的

select min(salary) from employees where employee_id = (select department_id from employees 

having max(salary)=(select min(max(salary) from employees group by department_id))

group by department_id)

10部门的最高工资是4400,最低工资还是4400,我们写的没啥问题,我们看一下,这个部门就一个人,

select * from employees where department_id = 10

48. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
	
	1). 各个部门中, 查询平均工资最高的平均工资是多少
	SELECT max(avg(salary))
	FROM employees
	GROUP BY department_id
	
	
	2). 各个部门中, 平均工资等于 1) 的那个部门的部门号是多少
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING avg(salary) = (
		SELECT max(avg(salary))
		FROM employees
		GROUP BY department_id
	)
	
	
	
	3). 查询出 2) 对应的部门的 manager_id
	SELECT manager_id
	FROM departments
	WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id
		HAVING avg(salary) = (
			SELECT max(avg(salary))
			FROM employees
			GROUP BY department_id
		)	
	)
	
	
	4). 查询出 employee_id 为 3) 查询的 manager_id 的员工的 last_name, department_id, email, salary
	SELECT last_name, department_id, email, salary
	FROM employees
	WHERE employee_id = (
		SELECT manager_id
		FROM departments
		WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING avg(salary) = (
				SELECT max(avg(salary))
				FROM employees
				GROUP BY department_id
			)	
		)	
	)

查询平均工资最高部门的manager信息,平均工资最高的部门,找到平均工资最高的是多少,然后看哪个部门的平均工资等于

这个最高的,然后这个部门的manager有哪些,我们先找到平均工资最高的是多少,跟上面的题目是类似的,求平均工资,select 

avg(salary),按部门来

select avg(salary) from employees group by department_id

然后找这里面最大的

select max(avg(salary)) from employees group by department_id

然后我们要看看谁的额工资等于最高的,我们先看最高值是多少

我想查询一下部门id,group by department_id

select department_id from employees group by department_id having avg(salary) =

(select max(avg(salary)) from employees group by department_id)

哪个部门的平均工资等于这个值,哪个等于这个值,得到这个department_id

这个部门找到了,manager的信息,部门的manager信息,我先要看这个部门的manager_id有哪些,

where department_id等于刚才得到的90

select manager_id from employees where department_id = (select department_id from employees group by 

department_id having avg(salary)= (select max(avg(salary)) from employees group by department_id))

这个越写越长了,现在我是得到90号部门的manager_id,有哪些

manager_id两个,这两个都是100,相当于这里有两个员工,两个员工的manager_id都是他,你可以加上一个distinct,

select distinct manager_id from employees where department_id = (select department_id from employees

group by department_id having avg(salary) = (select max(avg(salary)) from employees 

group by department_id))

这不就是一个,我现在就想看这个人的这些信息,那就再加一层,看看他要求的这些信息,from employees,

where employee_id,因为你manager_id等于100,是不是这个100也是员工的employee_id,所以这里写100

就行,然后呢我给他整合在一起,这个大家要一层一层的写,每一层写好以后最好验证一下,运行一下看他成不成功,

你别最后写完以后再去验证,可能这个代码太多了,放在一块难免会出错,现在我看看这个人,他说单行子查询返回

多行的值,那我把这个等号改成in

select last_name,department_id,email,salary from employees where employee_id in (select distinct

manager_id from employees group by department_id having avg(salary)=(select max(avg(salary)) from 

employees group by department_id))

department_id90号部门,就是这个人,这是这个人的相信信息,我们这个题就搞定了,所以刚才你也看到了,

如果你真的是等号的话,如果真的是等号,还有大于,小于,不等于等等,如果是等号的话,你不凡给他改成in

更保险,因为八把不准里面就返回多行,这是我们的这个题,大家看最后一个

49. 查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.
 		
		1). 查询出 1999 年来公司的所有的员工的 salary
		SELECT salary
		FROM employees
		WHERE to_char(hire_date, 'yyyy') = '1999'
		
		2). 查询出 1) 对应的结果的最大值
		SELECT max(salary)
		FROM employees
		WHERE to_char(hire_date, 'yyyy') = '1999'
		
		3). 查询工资等于 2) 对应的结果且 1999 年入职的员工信息		
		SELECT *
		FROM employees
		WHERE to_char(hire_date, 'yyyy') = '1999' AND salary = (
			SELECT max(salary)
			FROM employees
			WHERE to_char(hire_date, 'yyyy') = '1999'
		)

查询1999年来公司的人,1999年来公司的员工,最高工资中员工的信息,我找到99年来公司的人,这里面肯定有工资最高的,

我找到工资最高的那个人的信息,这个我们怎么写,他想查询这个人的信息,select,我写个*吧,我们从外层到内层,select 

* from employees,其实你从内到外也行,然后有一个限制条件,首先他的工资是不是要等于一个值,工资等于一个值的可能

有很多,而且这个人还是99年来公司的,99年来的我们已经有这个经验了,我们把hire_date转成一个char,转成以后,它是1999,

是这样吧,先这样来看的话就是,我想查询一下这个信息,什么信息,工资等于某个值,并且是99年来公司的,工资等于某个值的,

工资等于多少,这里面就是我们内层需要做的事了,内层需要做的事,这个你要返回给外层什么结果,就是99年来的最高工资,

找最高工资,select max(salary),from employees,这里成了员工当中最高的工资了,你得找99年来的,是不是还是这个,

你看看这里面是不是我们所想要的,99年来公司的员工当中的,最高工资,谁的工资等于这个最高工资,并且也是99年来的,

这就搞定了

select * from employees where salary = (select max(salary) from employees 

to_char(hire_date,'yyyy')='1999')

and to_char(hire_date,'yyyy')='1999'

你看看这个信息,99年来的,大家你写的时候尤其需要注意,最后这个别给丢了,很多人从内层往外层写的

时候,我说一下,很多人内层往外层写的时候,就这样操作写完,也就得了,这个是错的,你要这样写的话,

因为你内层是99年来的,他返回给外层的只返回一个公司的值,这个99年来的这个信息,并没有同步的返回

出去,所以还是的显示的再加上一个

你看你要是不加的话,你看这个结果

就是多个了,看一下他们的时间,其中有两个,所以你一定要给他加上,这是我们讲子查询比较容易出错的地方,

关于这里面的这些练习,我们就说完了