日期:2014-05-16 浏览次数:20597 次
oracle的常见面试题,仅供大家参考
???? 前两天我做了一些oracle的面试题,觉得挺不错的,难易程度中等,今天总结了一下,仅供大家参考和学习。以下就是我做的面试题,大家可以看看。如果有什么不懂的给我留言,我们可以互相交流,互相讨论。共同进步,共同学习。
?????我给大家列一下emp和dept这两张表,表如下:
SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 14 rows selected SQL> select * from dept; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
??
01.查询员工表所有数据,并说明使用*的缺点
select * from emp;
??使用“*”效率不高
02.查询职位(JOB)为'PRESIDENT'的员工的工资
select sal from emp where job='PRESIDENT';
?03.查询佣金(COMM)为0或为NULL的员工信息
select * from emp where comm is null or comm=0;
?04.查询入职日期在1982-5-1到1981-12-31之间的所有员工信息
select * from emp where hiredate between to_date('1981-05-01','YYYY-MM-DD') and to_date('1981-12-31','YYYY-MM-DD');
?05.查询所有名字长度为4的员工的员工编号,姓名
select empno,ename from emp where length(ename)=4;
?06.显示10号部门的所有经理('MANAGER')和20号部门的所有职员('CLERK')的详细信息
select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK';
?07.显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
select * from emp where ename not like '%L%' or ename like '%SM%';
?08.显示各个部门经理('MANAGER')的工资
select sal from emp where job='MANAGER';
?09.显示佣金(COMM)收入比工资(SAL)高的员工的详细信息
select * from emp where comm>sal;
?10.把hiredate列看做是员工的生日,求本月过生日的员工(考察知识点:单行函数)
select * from emp where to_char(hiredate,'mm')=to_char(sysdate,'mm');
?11.把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)
select * from emp where to_char(hiredate,'mm')=to_char(add_months(sysdate,1),'mm');
?12.求1982年入职的员工(考察知识点:单行函数)
select * from emp where hiredate between to_date('1982-01-01','YYYY-MM-DD') and to_date('1982-12-31','YYYY-MM-DD');
?--------等效于---------->
select * from emp where to_char(hiredate,'yyyy')='1982';
?13.求1981年下半年入职的员工(考察知识点:单行函数)
select * from emp where hiredate between to_date('1981-07-01','YYYY-MM-DD') and to_date('1981-12-31','YYYY-MM-DD');
?14.求1981年各个月入职的员工个数(考察知识点:组函数)
select count(*),to_char(trunc(hiredate,'month'),'YYYY-MM-DD') from emp where to_char(hiredate,'yyyy')='1