--1.找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK);
select * from emp where
(job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20);
--2.分别用case和decode函数列出员工所在的部门,例如:deptno=10显示'部门10';
select case deptno when 10 then '部门10' when 20 then '部门20'
when 30 then '部门30' when 40 then '部门40' end 部门
from emp
--------------------------
select decode(deptno,10,'部门10',20,'部门20',30,'部门30',40,'部门40') 部门 from emp;
--3.得到工资大于自己部门平均工资的员工信息;
select s.*,e.empno,e.ename from
(select deptno,trunc(avg(sal)) avgsal from emp group by deptno) s,emp e
where s.deptno=e.deptno and e.sal>s.avgsal;
select e.empno,e.ename from emp e
where e.sal>
(select trunc(avg(sal)) avgsal from emp s
where s.deptno=e.deptno group by deptno )
--4.列出所有员工的姓名和其上级的姓名;
select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;
--5.得到每个月工资总数最少的那个部门的部门编号,部门名称,部门位置;
select s.*,d.* from
(select rownum rn,e.* from
(select deptno,sum(sal) sumsal from emp group by deptno order by sumsal) e) s,
dept d
where s.rn=1 and s.deptno=d.deptno;
--6.查找出部门10和部门20中,工资最高第3名到工资第5名的员工的员工名字,
-- 部门名字,部门位置;
select s.*,d.* from
(select e.*,row_number() over(partition by deptno order by sal ) rn
from emp e where deptno in (10,20)) s,dept d
where s.rn between 3 and 5 and d.deptno=s.deptno;
-----------------------
select s.*,d.* from dept d,
select * from emp where
(job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20);
--2.分别用case和decode函数列出员工所在的部门,例如:deptno=10显示'部门10';
select case deptno when 10 then '部门10' when 20 then '部门20'
when 30 then '部门30' when 40 then '部门40' end 部门
from emp
--------------------------
select decode(deptno,10,'部门10',20,'部门20',30,'部门30',40,'部门40') 部门 from emp;
--3.得到工资大于自己部门平均工资的员工信息;
select s.*,e.empno,e.ename from
(select deptno,trunc(avg(sal)) avgsal from emp group by deptno) s,emp e
where s.deptno=e.deptno and e.sal>s.avgsal;
select e.empno,e.ename from emp e
where e.sal>
(select trunc(avg(sal)) avgsal from emp s
where s.deptno=e.deptno group by deptno )
--4.列出所有员工的姓名和其上级的姓名;
select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;
--5.得到每个月工资总数最少的那个部门的部门编号,部门名称,部门位置;
select s.*,d.* from
(select rownum rn,e.* from
(select deptno,sum(sal) sumsal from emp group by deptno order by sumsal) e) s,
dept d
where s.rn=1 and s.deptno=d.deptno;
--6.查找出部门10和部门20中,工资最高第3名到工资第5名的员工的员工名字,
-- 部门名字,部门位置;
select s.*,d.* from
(select e.*,row_number() over(partition by deptno order by sal ) rn
from emp e where deptno in (10,20)) s,dept d
where s.rn between 3 and 5 and d.deptno=s.deptno;
-----------------------
select s.*,d.* from dept d,