oracle 全部高级查询部分（二）
1.查询员工表所有数据, 并说明使用*的缺点select * from emp;select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;使用*时要先用转换成select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;2.查询职位(JOB)为'PRESIDENT'的员工的工资select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where job= 'PRESIDENT';3.查询佣金(COMM)为0或为NULL的员工信息select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where nvl(comm,0)=0 ;4.查询入职日期在 1981-5-1到1981-12-31之间的所有员工信息Select empno, ename,job,mgr,hiredate,sal,comm,deptno from emp where hiredate>to_date('1981-5-1','yyyy-MM-dd') and hiredate>to_date('1981-12-31','yyyy-MM-dd');5.查询所有名字长度为4的员工的员工编号,姓名select empno, ename,job,mgr,hiredate,sal,comm,deptno from emp where length(ename)=4;6.显示10号部门的所有经理('MANAGER')和20号部门的所有职员('CLERK')的详细信息select empno, ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK';7.显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息select empno, ename,job,mgr,hiredate,sal,comm,deptno from emp where ename not like '%L%';8.显示各个部门经理('MANAGER')的工资select sal from emp where job='MANAGER';9.显示佣金(COMM)收入比工资(SAL)高的员工的详细信息 select empno, ename,job,mgr,hiredate,sal,comm,deptno from emp where comm>sal;10.把hiredate列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数) select empno, ename,job,mgr,hiredate,sal,comm,deptno from emp where hiredate>last_day(add_months(sysdate,-1)) and hiredate<last_day(sysdate);11. 把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数) select empno, ename,job,mgr,hiredate,sal,comm,deptno from emp where hiredate<last_day(add_months(sysdate,1)) and hiredate>last_day(sysdate);12. 求1982年入职的员工(考察知识点:单行函数) select empno, ename,job,mgr,hiredate,sal,comm,deptno from emp where hiredate>=to_date('1982-01-01','yyyy-MM-dd') and hiredate<=to_date('1982-12-31','yyyy-MM-dd');13.求1981年下半年入职的员工(考察知识点:单行函数) select empno, ename,job,mgr,hiredate,sal,comm,deptno from emp where hiredate>=to_date('1982-06-01','yyyy-MM-dd') and hiredate<=to_date('1982-12-31','yyyy-MM-dd');14.求1981年各个月入职的的员工个数(考察知识点:组函数)select hiredate, to_char(hiredate,'MM'), count(*) from emp where hiredate>=to_date('1981-01-01','yyyy-MM-dd') and hiredate<=to_date('1981-12-31','yyyy-MM-dd') group by (to_char(hiredate,'MM'),hiredate);15. 查询当前的时间最后的月份 SELECT to_number(to_char(last_day(sysdate),'dd')) FROM dual; select last_day(sysdate)-last_day(add_months(sysdate,-1)) from dual;?