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');