2020-11-21

Oracle题库三

1)查询所有员工的平均工资，最高工资,最低工资，工资总和，还有有多少个员工?

SELECT
	avg( salary ),
	max( salary ),
	min( salary ),
	sum( salary ),
	count( id ) 
FROM
	s_emp;

2)查询每个部门的平均工资？对平均工资降序排序.平均工资大于1400.
SELECT

dept_id,
	avg( salary ) 
FROM
	s_emp 
GROUP BY
	dept_id 
HAVING
	avg( salary ) > 1400 
ORDER BY
	avg( salary ) DESC;

3)查看各个部门的最高工资

SELECT
	dept_id,
	max( salary ) 
FROM
	s_emp 
GROUP BY
	dept_id;

4)查看各个部门的员工数

SELECT
	dept_id,
	count( id ) 
FROM
	s_emp 
GROUP BY
	dept_id;

5)查询各个部门的平均薪水和最大薪水,并且平均薪水大于2000的部门id。

SELECT
	dept_id,
	avg( salary ),
	max( salary ) 
FROM
	s_emp 
GROUP BY
	dept_id 
HAVING
	avg( salary ) > 2000;

6)查询title中不包含vp字符串的每个职位的平均薪水，并对平均薪水进行降序排列，
并且每个职位的总薪水大于5000。

SELECT
	title,
	avg( salary ) 
FROM
	s_emp 
GROUP BY
	title 
HAVING
	SUM( salary ) > 5000 
	AND title NOT LIKE '%vp%' 
ORDER BY
	avg( salary ) DESC;

7)查看每个区域雇员的人数？

SELECT
	d.region_id,
	count( e.id ) 
FROM
	s_emp e,
	s_dept d 
WHERE
	e.dept_id = d.id 
GROUP BY
	d.region_id;

8)查看每个区域部门数？

SELECT
	region_id,
	count( id ) 
FROM
	s_dept 
GROUP BY
	region_id;

9)查询每个员工的名字以及员工对应的管理者的名字

SELECT
	e1.last_name,
	e2.last_name 
FROM
	s_emp e1,
	s_emp e2 
WHERE
	e1.manager_id = e2.id;

10)查询s_emp表中部门的总工资大于等于4000的部门

SELECT
	dept_id,
	sum( salary ) 
FROM
	s_emp 
GROUP BY
	dept_id 
HAVING
	sum( salary ) >= 4000;

11)查询s_emp表中部门的平均工资大于等于1400的部门,同时按照部门编号进行排序

SELECT
	dept_id,
	avg( salary ) 
FROM
	s_emp 
GROUP BY
	dept_id 
HAVING
	avg( salary ) >= 1400 
ORDER BY
	dept_id DESC;

12)查询s_emp表中最大的工资数,并且显示出这个最大工资的员工的名字

--子查询
SELECT
	last_name,
	salary 
FROM
	s_emp 
WHERE
	salary = ( SELECT max( salary ) FROM s_emp 
--多表连接
	SELECT
		e2.last_name,
		max( e1.salary ) 
	FROM
		s_emp e1,
		s_emp e2 
	GROUP BY
		e2.last_name,
		e2.salary 
	HAVING
		max( e1.salary ) = e2.salary;

13)查看和Ngao所在部门的区域id一样的所有员工id和名字
–获取Ngao所在部门的区域id

select d.region_id
from s_emp e,s_dept d
where e.dept_id = d.id and e.last_name='Ngao'
--获取Ngao所在部门的区域id 一样的所有员工id和名字
SELECT
	e.id,
	e.last_name 
FROM
	s_emp e,
	s_dept d 
WHERE
	e.dept_id = d.id 
	AND d.region_id IN ( SELECT d.region_id FROM s_emp e, s_dept d WHERE e.dept_id = d.id AND e.last_name = 'Ngao' );

14)查询每一个部门的员工小于该部门的平均工资的员工的信息？

--该部门的平均工资
SELECT
	dept_id,
	avg( salary ) 
FROM
	s_emp 
GROUP BY
	dept_id
--查询每一个部门的员工小于该部门的平均工资的员工的信息？
SELECT
	last_name,
	salary 
FROM
	s_emp e1,
	( SELECT dept_id, avg( salary ) avgSalary FROM s_emp GROUP BY dept_id ) e2 
WHERE
	e1.dept_id = e2.dept_id 
	AND e1.salary < e2.avgSalary;

15)查看职位和名字为Chang的员工一样的所有员工id和名字，查看名字为chang的员工的title

--多表级联
SELECT
	e1.title,
	e2.id,
	e2.last_name 
FROM
	s_emp e1,
	s_emp e2 
WHERE
	e1.last_name = 'Chang' 
	AND e1.title = e2.title
--子查询
SELECT
	id,
	last_name,
	title 
FROM
	s_emp 
WHERE
	title = ( SELECT title FROM s_emp WHERE last_name = 'Chang' );

16)查看员工工资小于平均工资的所有员工的id和名字

SELECT
	id,
	last_name,
	salary 
FROM
	s_emp 
WHERE
	salary < ( SELECT avg( salary ) FROM s_emp )
17)查看部门id和名字为Chang的部门相同或者区域ID为2的部门信息 
--名字为Chang的部门
SELECT
	dept_id 
FROM
	s_emp 
WHERE
	last_name = 'Chang'
--查看部门id和名字为Chang的部门相同或者区域ID为2的部门信息 
SELECT
	d.id,
	d.name 
FROM
	s_dept d 
WHERE
	d.id IN ( SELECT dept_id FROM s_emp WHERE last_name = 'Chang' ) 
	OR d.region_id =2;

18)查询员工所在区域为2的部门的员工的信息？

--所在区域为2的部门
select id
from s_dept
where region_id =2
--查询员工所在区域为2的部门的员工的信息？
--子查询
select id,last_name,dept_id
from s_emp where dept_id in (
	select id
	from s_dept
	where region_id =2
)
--多表连接
select se.id,se.last_name
from s_emp se,s_dept sd
where se.dept_id=sd.id and region_id=2;

19)查看部门平均工资大于32号部门平均工资的部门id
–32号部门平均工资

select avg(salary)
from s_emp
where dept_id = 32
--查看部门平均工资大于32号部门平均工资的部门id
select dept_id,avg(salary)
from s_emp
GROUP BY dept_id
having avg(salary) >(
	select avg(salary)
	from s_emp
	where dept_id = 32
);

20)查询工资大于Smith所在部门平均工资的员工的信息
–Smith所在部门平均工资

select avg(salary)
from s_emp where dept_id in(
	select dept_id
	from s_emp 
	where last_name = 'Smith'
);

–查询工资大于Smith所在部门平均工资的员工的信息

select last_name,salary
from s_emp
where salary>(
	select avg(salary)
	from s_emp where dept_id in(
		select dept_id
		from s_emp 
		where last_name = 'Smith'
	)
);

21)查看薪资高于Chang员工经理的经理薪资的员工信息
–Chang员工经理的经理薪资

select e3.salary
	from s_emp e1,s_emp e2,s_emp e3
	where e1.manager_id = e2.id and e1.last_name = 'Chang' and e2.manager_id = e3.id
--查看薪资高于Chang员工经理的经理薪资的员工信息
select last_name,salary 
from s_emp
where salary>(
	select e3.salary
	from s_emp e1,s_emp e2,s_emp e3
	where e1.manager_id = e2.id and e1.last_name = 'Chang' and e2.manager_id = e3.id
);

22)查看和Patel在同一个部门的员工的信息
–Patel的部门

select dept_id
from s_emp
where last_name = 'Patel';
--查看和Patel在同一个部门的员工的信息
select last_name
from s_emp
where dept_id in(
	select dept_id
	from s_emp
	where last_name = 'Patel'
);