Oracle面试题汇总--2

 SQL> --10.把hiredate列看做是员工的生日，求本月过生日的员工SQL> select * from emp where to_char(hiredate,'mm')=to_char(sysdate,'mm');SQL> --11.把hiredate列看做是员工的生日，求下一个月过生日的员工SQL> select * from emp where to_char(hiredate,'mm')=to_char(add_months(sysdate,1),'mm'); SQL> --12.求1982年入职的员工SQL> select * from emp where to_char(hiredate,'yyyy')='1982';                                                                              SQL> --13.求1981年下半年入职的员工SQL>select * from emp where hiredate between to_date('1981-7-1','yyyy-mm-dd')and to_date('1982-1-1','yyyy-mm-dd')-1;SQL> --14.求1981年个月入职的员工个数SQL>select count(*),to_char(trunc(hiredate,'month'),'yyyy-mm') from emp         where to_char(hiredate,'yyyy')='1981' group by trunc(hiredate,'month')         order by trunc(hiredate,'month') ;SQL> --02.显示各种职位的最低工资SQL> select job ,min(sal) from emp group by job;SQL> --03.按入职日期由新到旧排列员工信息SQL> select * from emp order by hiredate desc;SQL> --04.查询员工的基本信息，附加其上级的姓名SQL> select e1.*,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno; SQL> --05.显示工资比‘ALLEN’高的所有员工的姓名和工资SQL> select ename ,sal from emp where sal >( select sal from emp where ename='ALLEN');SQL> --06.显示与‘SCOTT'从事相同工作的员工的详细信息SQL> select * from emp where job=(select job from emp where ename='SCOTT');SQL> --07.显示销售部（‘SALES’）员工的姓名SQL>select ename from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';SQL> --08.显示与30号部门‘MARTIN’员工工资相同的员工的姓名和工资SQL> select ename,sal from emp where sal=(select sal from emp where deptno=30 and ename='MARTIN');