Oracle特有函數 case when decode exists 分頁rownum

select * from EMP e
select * from dept d
select * from salgrade s
--Oracle特有函數 case when
select
case 2
when 1 then '一'
when 2 then '二'
when 3 then '三'
else '其他'
end
from dual;
--Oracle特有函數 decode
select decode(3,1,'一',2,'二',3,'三','其他')from dual;
--查詢員工的領導信息(內聯查詢)
select e.empno,e.ename,e.job,e.mgr,'||',m.empno,m.ename,m.job
from emp e,emp m
where e.mgr=m.empno(+)

select *
from emp e right outer join emp m on e.mgr=m.empno;

select *
from emp e full outer join emp m on e.mgr=m.empno;
--查詢員工的領導信息(內聯查詢)及員工部門名稱
select e.empno,e.ename,e.job,e.mgr,e.deptno,d.dname,'||',m.empno,m.ename,m.job
from emp e,emp m,dept d
where e.mgr=m.empno and e.deptno=d.deptno
--查詢員工的領導信息(內聯查詢)員工部門名稱,領導部門名稱
select e.empno,e.ename,e.job,e.mgr,e.deptno,d.dname,'||',m.empno,m.ename,m.job,p.dname
from emp e,emp m,dept d,dept p
where e.mgr=m.empno and e.deptno=d.deptno and m.deptno=p.deptno
--查詢員工的領導信息(內聯查詢)員工部門名稱,領導部門名稱,員工工資等級,領導工資等級
select e.empno,e.ename,e.job,e.mgr,e.deptno,d.dname,s.grade,
'||',m.empno,m.ename,m.job,p.dname,s1.grade
from emp e,emp m,dept d,dept p,salgrade s,salgrade s1
where e.mgr=m.empno
and e.deptno=d.deptno
and m.deptno=p.deptno
and e.sal between s.losal and s.hisal
and m.sal between s1.losal and s1.hisal
--級別不用數字表示,用文字表示
select e.empno,e.ename,e.job,e.mgr,e.deptno,d.dname,
decode(s.grade,1,'一級',2,'二級',3,'三級',4,'四級',5,'五級','沒級'),
'||',m.empno,m.ename,m.job,p.dname,
decode(s1.grade,1,'一級',2,'二級',3,'三級',4,'四級',5,'五級','沒級')
from emp e,emp m,dept d,dept p,salgrade s,salgrade s1
where e.mgr=m.empno
and e.deptno=d.deptno
and m.deptno=p.deptno
and e.sal between s.losal and s.hisal
and m.sal between s1.losal and s1.hisal
--查詢工資高於7369號員工工資的員工信息
select sal from emp where empno=7369
select * from emp where sal>(select sal from emp where empno=7369)
--查詢存在員工的部門信息
select distinct deptno from emp
select * from dept where deptno in(select distinct deptno from emp )
select * from dept where deptno =any(select distinct deptno from emp )
select * from dept where deptno =some(select distinct deptno from emp )
--使用exists
select * from dept d where exists (select * from emp e where e.deptno=d.deptno)
--查詢每個部門最低工資員工信息
select *
from emp e,(select min(sal) msal,deptno from emp group by deptno) t
where e.sal=t.msal
and e.deptno=t.deptno
--查詢工資最高的前三名 (分頁的感覺)
select * from
(select * from emp order by sal desc) t
where rownum <=3