--求部门中哪些人的薪水最高
select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
--求部门平均薪水的等级
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal);
--求部门平均的薪水等级
select deptno, avg(grade) avg_grade from emp
Join salgrade s on(emp.sal between s,losal and s.hisal)
Group by deptno;
--雇员中有哪些是经理人
select ename from emp where empno in
(select distinct mgr from emp);
--不准用组函数,求薪水的最高值(面试题)
select distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal));
--求平均薪水最高的部门和部门编号
select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp
group by deptno));
--求平均薪水最高的部门和部门名称
select dname from dept where deptno =
(
select deptno from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal ,deptno from emp group by deptno)
)
);
--求平均薪水的等级最低的部门的部门名称
select dname ,t1.deptno,grade,avg_sal from
( select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and hisal)
)t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
);
方法二:(使用视图)
--求部门经理人中平均薪水最低的部门名称(思考题)
--求比普通员工的最高薪水还要高的经理人名称
--->1.先求普通员工的最高薪水
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null);
--->2.
select ename from emp
where empno in
(select distinct mgr from emp where mgr is not null)
and sal >
(select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null)
);
--求薪水最高的前5名雇员
select ename, sal from
(select ename, sal from emp order by sal desc) where rownum <= 5;
--求薪水最高的第六到第十名雇员
select ename, sal
from (
) where r >=6 and r <= 10;
--面试题:比较效率(第一个效率比较高)
select * from emp where deptno = 10 and ename like '%A%';
select * from emp where ename like '%A%' and deptno = 10;