--把雇员按部门分组, 求最高薪水, 部门号, 过滤掉名字中第二个字母是'A'的, 要求分组后的平均薪水
>1500, 按照部门编号倒序排列
--求每个人的名字和他的经理人的名字,用外连接把KING也取出来
--哪些人的薪水在部门的平均薪水之上
--求部门中哪些人的薪水最高
--求部门平均薪水的等级
--求部门平均的薪水等级
--雇员中有哪些人是经理人
--不准用组函数,求薪水的最高值(面试题)
--求平均薪水最高的部门的部门编号
--求平均薪水最高的部门的部门名称
--求平均薪水的等级最低的部门的部门名称
--求部门经理人中平均薪水最低的部门名称 (思考题)
--求比普通员工的最高薪水还要高的经理人名称
--求薪水最高的前5名雇员
--求薪水最高的第6到第10名雇员(重点掌握)
--练习: 求最后入职的5名员工
--求每个部门中薪水最高的前两名雇员
--面试题: 比较效率
--求分段显示薪水的个数
如:
scale total
<800 0
800-1000 2
1001-2000 3
2001-5000 6
>5000 8
或者显示成为
800-1000 1001-2000 2000-5000
2 3 6
或者显示成为
DEPTNO 800-2000 2001-5000
------ ---------- ----------
30 5 1
20 2 3
10 1 2
--
========================第一份答案=======================
--把雇员按部门分组, 求最高薪水, 部门号, 过滤掉名字中第二个字母是'A'的,
要求分组后的平均薪水>1500, 按照部门编号倒序排列
- select e1.ename, e2.ename
- from emp e1 left join emp e2 on (e1.mgr = e2.empno)
--哪些人的薪水在部门的平均薪水之上
- select ename, sal
- from emp join
- (select deptno, max(sal) max_sal
- from emp
- group by deptno) t
- on (emp.deptno = t.deptno and emp.sal = t.max_sal)
--求部门平均薪水的等级
- select deptno, avg_sal, grade
- from salgrade s join
- (select deptno, avg(sal) avg_sal
- from emp
- group by deptno) t
- on (t.avg_sal between s.losal and s.hisal)
--求部门平均的薪水等级
- select ename from emp where empno in (select distinct mgr from emp)
--不准用组函数,求薪水的最高值(面试题)
- select ename from emp where empno not in
- (select distinct e1.empno from emp e1 join emp e2 on (e1.sal < e2.sal))
排序,取第一个
--求平均薪水最高的部门的部门编号
- select deptno from
- (select deptno, avg(sal) avg_sal from emp group by deptno) t
- where avg_sal =
- (select max(avg_sal) from
- (select deptno, avg(sal) avg_sal from emp group by deptno) t)
- select max(avg(sal)) from emp group by deptno --组函数嵌套
--求平均薪水最高的部门的部门名称
- select dname from dept where deptno =
- (select deptno from
- (select deptno, avg(sal) avg_sal from emp group by deptno) t
- where avg_sal =
- (select max(avg_sal) from
- (select deptno, avg(sal) avg_sal from emp group by deptno) t)
- )
--求平均薪水的等级最低的部门的部门名称
- select dept.deptno,dname from
- (
- select deptno, avg_sal, grade from salgrade s join
- (select deptno, avg(sal) avg_sal from emp group by deptno) t
- on (t.avg_sal between s.losal and s.hisal)
- ) t1 join dept on
- (t1.deptno = dept.deptno)
- where grade =
- (select min(grade) from
- (
- select deptno, avg_sal, grade from salgrade s join
- (select deptno, avg(sal) avg_sal from emp group by deptno) t
- on (t.avg_sal between s.losal and s.hisal)
- )
- )
- reate view v$_dept_info as
- select deptno, avg_sal, grade from salgrade s join
- (select deptno, avg(sal) avg_sal from emp group by deptno) t
- on (t.avg_sal between s.losal and s.hisal)
- elect deptno from v$_dept_info where grade = (select min(grade) from v$_dept_info);
--求部门经理人中平均薪水最低的部门名称 (思考题)
//....
--求比普通员工的最高薪水还要高的经理人名称
- select empno, ename, sal from emp where sal >
- (
- select max(sal) from
- (select empno,ename, sal
- from emp where empno not in (select distinct nvl(mgr, -1) from emp ))
- ) and empno in (select distinct mgr from emp)
- select distinct mgr from emp where mgr is not null
--求薪水最高的前5名雇员
--求薪水最高的第6到第10名雇员(重点掌握)
- select ename, sal from
- (
- select ename, sal, rownum r from
- (
- select ename, sal from emp order by sal desc
- )
- where rownum <=10
- )
- where r > 5 and r <= 10
--求每个部门中薪水最高的前两名雇员
- select deptno, ename, sal from emp order by deptno, sal desc
- select deptno, ename, sal , rownum r from
- (select deptno, ename, sal from emp order by deptno, sal desc);
- create view v as select deptno, ename, sal , rownum r from
- (select deptno, ename, sal from emp order by deptno, sal desc);
- select deptno, min(r) min_r from v group by deptno;
- select v.deptno, ename, sal , r from v join
- (
- select deptno, min(r) min_r from v group by deptno
- ) t
- on (v.deptno = t.deptno and v.r >= t.min_r and v.r <= t.min_r + 1)
- order by v.deptno, sal desc;
--练习: 求最后入职的5名员工
- select ename, hiredate from
- (
- select ename ,hiredate from emp order by hiredate desc
- )
- where rownum <= 5
--面试题: 比较效率
- select * from emp where deptno = 10 and ename like '%A%';--这种是最高的
- select * from emp where ename like '%A%' and deptno = 10;
--求分段显示薪水的个数
如:
- scale total
- <800 0
- 801-1000 2
- 1001-2000 3
- 2001-5000 6
- >5000 8
- select '<800' as scale , count(*) as total from emp where sal < 800
- union
- select '800-1000' as scale , count(*) as total from emp where sal between 800 and 1000
- union
- select '2000-5000' as scale , count(*) as total from emp where sal between 2000 and 5000;
- ...
或者显示成为
800-1000 1001-2000 2000-5000
2 3 6
- select * from
- ( select count(*) as "800-1000" from emp where sal between 800 and 1000 )
- ,
- ( select count(*) as "1001-2000" from emp where sal between 1001 and 2000 );
- ...
或者显示成为
DEPTNO 800-2000 2001-5000
------ ---------- ----------
30 5 1
20 2 3
10 1 2
- select t1.deptno, "800-2000", "2001-5000" from
- (
- select deptno , count(*) as "800-2000" from emp where sal between 800 and 2000
- group by deptno
- ) t1
- join
- (
- select deptno , count(*) as "2001-5000" from emp where sal between 2001 and 5000
- group by deptno
- ) t2
- on
- t1.deptno = t2.deptno
- select ename, sal
- from
- (select ename, sal from emp order by sal desc)
- where rownum <=5;
- select deptno, avg(grade) from
- (select deptno, sal , grade
- from emp e join salgrade s on
- (e.sal between s.losal and s.hisal)
- ) t
- group by deptno
--雇员中有哪些人是经理人
- select ename, sal
- from emp join
- (select deptno, avg(sal) avg_sal
- from emp
- group by deptno) t
- on (emp.deptno = t.deptno and emp.sal > t.avg_sal)
--求部门中哪些人的薪水最高
- select max(sal), deptno from emp
- where ename not like '_A%'
- group by deptno
- having avg(sal) > 1500
- order by deptno desc;
--求每个人的名字和他的经理人的名字,用外连接把KING也取出来