oracle練習

- 在芝加哥工作的人中，誰的工資最高
select * from(select e.ename from emp e join dept d on(e.deptno = d.deptno)where d.loc='CHICAGO' order by sal desc) where rownum=1;

- 查詢每個部門下有多少員工
select dname,count(ename) from dept d left outer join emp e on d.deptno=e.deptno group by dname
- 查詢除去 salesman 所有平均工資超過 1500 的部門
select d.dname,avg(e.sal+nvl(e.comm,0)) from dept d join emp e on d.deptno=e.deptno where job!='salesman' group by dname having avg(e.sal+nvl(e.comm,0))>1500;

- 查詢在 new york 工作的所有員工的姓名，部門名稱和工資信息
select ename,d.dname,sal from emp e join dept d on d.deptno=e.deptno where d.loc='NEW YORK';
- 查詢姓名為 King 的員工的編號，名稱跟部門
select empno,ename,d.dname from emp e join dept d on d.deptno=e.deptno where e.ename='KING'
- 查詢各種工作的最低工資
select job,min(sal) from emp group by job;
- 查詢工齡大於10年的所有員工信息
select * from emp where (months_between(sysdate,hiredate)/12)>10;

- 查詢每個部門員工數量，平均工資和平均工作年限
select dname,count(ename),avg(sal+nvl(comm,0)),avg(to_char(hiredate,'yyyy')) from dept d left outer join emp e on d.deptno = e.deptno group by dname;

- 統計各部門每個工種的人數，平均工資。
select dname,count(ename),avg(sal+nvl(comm,0)) from dept d left join emp e on e.deptno=d.deptno group by dname;