Oracle精选面试题
1.显示10 号部门的所有经理('MANAGER')和20 号部门的所有职员('CLERK')的详细信息
答案:
Select * from emp where deptno=10 and job=’MANAGER’ or deptno=20 and job=’clerk’;
select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job ='CLERK';
2.显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
答案:
Select * from emp where ename note like ‘%L%’ or ename like ‘%SM%’;
select * from emp where ename not like '%L%' or ename like '%SM%';
3.显示各个部门经理('MANAGER')的工资
答案:
select deptno,emname, salary from emp_wqq where job='MANAGER';
4.显示佣金(COMM)收入比工资(SAL)高的员工的详细信息
答案:
select * from emp where comm > sal;
5.把hiredate 列看做是员工的生日,求本月过生日的员工
答案:
select * from emp where to_char(hiredate, 'mm') = to_char(sysdate , 'mm');
6.把hiredate 列看做是员工的生日,求下月过生日的员工
答案:
select * from emp where to_char(hiredate, 'mm') = to_char(add_months(sysdate,1) , 'mm');
7.求1982 年入职的员工
答案:
select * from emp where to_char(hiredate,'yyyy') = '1982';
8.求1981 年下半年入职的员工
答案:
select * from emp where hiredate
between to_date('1981-7-1','yyyy-mm-dd') and to_date('1982-1-1','yyyy-mm-dd') - 1;
9.求1981 年各个月入职的的员工个数
答案:
select count(*), to_char(trunc(hiredate,'month'),'yyyy-mm')