Oracle day4

--4.查询平均工资最高的部门信息：
select * from dept where deptno = 
(select deptno from (select deptno,avg(sal) sal from emp group by deptno) e
where e.sal >= all(select sal from (select deptno,avg(sal) sal from emp group by deptno))) ;

with a as (select deptno,avg(sal) sal from emp group by deptno)
select  deptno, sal from 
a where sal = (select max(sal) from a);

--5.查询每个部门中薪水超过全体员工平均薪水的员工数量：
select deptno,count(*) from emp group by deptno having avg(sal) > (select avg(sal) from emp);

--6.查找最低工资大于1500元的各种工作：
select job,min(sal) from emp group by job having min(sal) > 1500;

--28.查询每个领导‘mgr’手下有多少人：
select mgr,count(*) from emp group by mgr;
--32.查询名字中包含字母‘E’且姓氏不以‘A’开头的员工信息：
select * from emp where ename like '%E%' and ename not like 'A%';
--36.显示与30号部门‘MARTIN’员工工资相同的员工的姓名和工资：
select ename,sal from emp where sal = 
(select sal from emp where ename = 'MARTIN' and deptno = 30);
--41.查询每个员工的员工编号，工资，奖金以及工资与奖金的和：
select empno,sal,comm,sal+nvl(comm,0) from emp;
--42.查询工资、奖金与10号部门某员工工资、奖金都相同的员工的信息：
select * from emp where (sal,nvl(comm,0)) in
(select sal,nvl(comm,0) from emp where deptno = 10);

select e.*,d.* from emp e inner join 
dept d on e.deptno = d.deptno;
select * from salgrade;
select e.*,s.grade from emp e inner join salgrade s 
on e.sal between s.losal and s.hisal;
select e.*,s.grade from emp e, salgrade s 
where e.sal between s.losal and s.hisal;
 
select e1.*,e2.ename from emp e1 inner join emp e2 on
e1.mgr = e2.empno;
select e1.*,e2.ename from emp e1 ,emp e2 where e1.mgr = e2.empno
 
select d.*,e.* from dept d left join emp e on d.deptno = e .deptno

select * from emp e cross join dept d cross join salgrade s;
select * from emp full join dept on emp.deptno = dept.deptno

select * from dept d where exists
(select * from emp where emp.deptno = d.deptno)

with a as (select deptno,count(*) peo_num from emp group by deptno)
select * from dept 
where deptno = (select deptno from a 
where peo_num =(select max(peo_num) from a));
select * from 
(select t1.*,rownum r from (select * from emp order by sal desc) t1)t2
where t2.r between 3 and 8;

--1.取得每个部门最高薪水的人员姓名：
select ename from emp where (deptno,sal) in
(select deptno,max(sal) from emp group by deptno);

--2.查询上司是‘king’的员工姓名和工资：
select ename,sal from emp where mgr = 
(select empno from emp where ename = 'KING');

--4.显示所有职员的姓名和工资及其所在部门的名称：
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno;

--5.查询各个部门的名称和员工人数：
select d.dname,count(*) from dept d left join
emp e on d.deptno = e. deptno group by d.dname;

--6.查询在研发部‘RESEARCH’工作人员的编号，姓名，部门名称，工作所在地：
select e.empno,e.ename,d.dname,d.loc from emp e join dept d
on e.deptno = d.deptno where d.dname = 'RESEARCH';

--7.查询工资最高的3名员工信息（不考虑重复数据）：
select * from (select e.*,row_number()over(order by sal desc) r from emp e) t where t.r<=3 ;

select e.*,rownum from (select * from emp order by sal desc) e where rownum <= 3;

--8.查询工资相同的员工的工资和姓名：
select sal,ename from emp where sal in 
(select sal from (select sal,count(*) num from emp group by sal having count(*) > 1));

select e1.sal,e1.ename from emp e1 join emp e2 on e1.sal = e2.sal
where e1.ename != e2.ename;

--9.查询每个员工的信息及工资级别，用到表‘Salgrade’：
select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

--10.查询每个部门，每个职位的最高工资：
select deptno,job, max(sal) from emp group by deptno,job;

--13.查询工资最高的第3-5名员工的员工信息：
select * from (select e.*,row_number()over(order by sal desc) r from emp e) t 
where t.r between 3 and 5;