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;