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.depton group  by dname
查询除去 salesman 所有平均工资超过 1500 的部门
select d.dname,e.job,avg(e.sal) from emp e join dept d on d.deptno=e.deptno where e.job!='SALESMAN'group by d.dname,e.job having avg(e.sal)>=0;

查询在 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 left join dept d on d.deptno = e.deptno where e.ename='KING';

查询各种工作的最低工资
查询工龄大于10年的所有员工信息

select * from emp where (months_between(sysdate,hiredate)/12)>10; 

查询每个部门员工数量，平均工资和平均工作年限

select dname,count(ename),avg(sal),avg(to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')) from  dept d left join emp e on e.deptno=d.deptno group by dname;