oracle的sql語句訓練

標籤：like   tab   函數   order   高薪   取出   分頁   比較   isa   

--查詢平均薪水最高部門的部門編號
select deptno,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno)--當做一張表，否則沒有表可以from
where avg_sal =
(select max(avg_sal)from (select deptno,avg(sal) 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)
where avg_sal =
(select max(avg(sal))from emp group by deptno ) );--組函數的嵌套，最多兩層，就是這種兩層（（））

--查詢部門平均薪水的等級
select deptno ,avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal;

--查詢平均薪水等級最低的部門的名稱--利用視圖可以簡化程式碼
select dname from dept where deptno=
( select deptno from
((select deptno ,avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal))
where grade=
(select min(grade) from
(select deptno ,avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal)));

--利用視圖簡化代碼
create view my_view as (select deptno ,avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal);
--建立視圖要用sys使用者來給scott使用者授權
用sys使用者登入成功後，
grant create table,create view to scott;
授權成功後才可以利用scott普通使用者建立視圖；
--用視圖來簡化上面的代碼
select dname from dept where deptno=
( select deptno from
(my_view )
where grade=
(select min(grade) from
my_view ));
--求比普通員工最高薪水還要高的經理人的名稱
普通員工：即不在mgr這個裡面的就是普通員工
經理人：在mgr這個裡面的就是經理人
--普通員工的最高薪水
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);--對於有null值的要進行這樣處理

--再求比這個最高工資還要高的經理人的名稱
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));

--求部門經理人中平均薪水最低的部門名稱
分析：
哪些部門中有哪些人是經理人，這些經理人所在部門，薪水是多少。
select dname from dept d join (
select deptno,avg(sal) avg_sal from (select ename,sal,deptno from emp where empno in(select mgr from emp )) group by deptno ) s
on d.deptno =s.deptno
where avg_sal=
(select min(avg_sal) from (select deptno,avg(sal) avg_sal from (select ename,sal,deptno from emp where empno in(select mgr from emp )) group by deptno));

--求薪水最高的前5名的僱員
select ename,sal from (select ename,sal from emp order by sal desc) where rownum <=5;

mysql的分頁是limit a,b oracle的分頁比較特殊，沒有limit只有rownum，這個是不顯示的一個偽欄位。這個欄位只能使用小於或者小於等於，不能使用大於或者大於等於。
如果分頁只能先查詢出rownum這列 select rownum from emp，然後從這裡面再rownum>或者<哪個數字。
select rownum ,ename from (select rownum no from emp) t where no>3 and no<10;

--求薪水最高的第6名到第10名的僱員
select ename,sal from (select ename,sal,rownum r from (select ename,sal from emp order by sal desc) ) where r>=6 and r<=10;

--求最後入職的5名員工(按照入職時間降序排列，取出前五個即可)

select ename,hiredate,rownum from(select ename,hiredate from emp order by hiredate desc ) where rownum<=5;

--比較效率
select * from emp where deptno=10 and ename like ‘%A%‘ --這個效率高些，跟java的短路與類似
select * from emp where ename like ‘%A% and deptno=10

oracle的sql語句訓練