1.求出每个岗位的总工资、平均工资、最高工资、最低工资以及人数 select job, sum(sal), avg(sal), max(sal), min(sal), count(*) from emp group by job; 2.按部门统计工资大于1000的人数 select deptno, count(*) from emp where sal > 1000 group by deptno; 3.求岗位平均工资大于1500的岗位及平均工资,按平均工资从小到大排序 select job, avg(sal) from emp --where group by job having avg(sal)>1500 order by avg(sal); 4.求有提成的员工按部门统计总工资 select deptno, sum(sal) from emp where comm is not null group by deptno; 1.找出名字拼写长度为4的员工 select * from emp where length(ename)=4; 2.找出1981-4-1后入职的员工 select * from emp where hiredate>to_date('1981-4-1','yyyy-mm-dd'); 3.显示员工在公司工作了几个月(取整数,不足一个月按一个月计算) select ename, ceil(months_between(sysdate, hiredate)) from emp; 4.显示所有员工姓名,用‘a’替换所有‘A’ select replace(ename, 'A', 'a') from emp; 5.显示员工的年薪(基本工资+提成) select ename, (nvl(comm,0)+sal)*12 from emp;