Oracle常见面试题整理
01 .查询各个部门的平均工资
SQL> select deptno,avg(nvl(sal,0)) from emp group by deptno;
02.显示各种职位的最低工资
SQL> select job,min(sal) from emp group by job;
03.按照入职日期由新到旧排列员工信息
SQL> select hiredate from emp order by hiredate desc;
04.查询员工的基本信息,附加其上级的姓名(自关联)
SQL> select e.*,e1.ename from emp e,emp e1 where e.mgr=e1.empno;
05.显示工资比’ALLEN’高的所有员工的姓名和工作
SQL> select ename,sal from emp where sal>(
select sal from emp where ename='ALLEN');
06.显示与scott从事相同工作的员工的信息(子查询)
SQL> select * from emp where job=(
select job from emp where ename='SCOTT');
07.显示销售部(‘SALES’)员工的姓名
SQL> select e.ename from emp e inner join dept d on e.deptno=d.deptno where d.dname='SALES';
08.显示与30号门’MARTIN’员工工资相同的员工的姓名和工资
SQL> select ename,sal from emp where sal=(select sal from emp where deptno=30 and ename='MARTIN');
09.查询所有工资高于平均工资(包括所有员工)的销售人员
SQL> select * from emp where sal>(
select avg(sal) from emp) and job='SALESMAN';
或
SQL> select * from emp where job='SALESMAN' and sal>(
select avg(sal) from emp);
10.显示所有职员的姓名及其所在部门的名称和工资(表连接)
SQL> select e.*,e.sal,d.dname from emp e inner join dept d on e.deptno=d.deptno;
11.查询在研发部(RESEARCH)工作人员的编号,姓名,工作部门,工作所在地
SQL> select e.empno,e.ename,d.dname,d.loc from emp e inner join dept d on e.deptno=d.deptno where dname='RESEARCH';
12.查询各个部门的名称和员工人数
select e.deptno,d.dname,count(*) from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname
分析:
SQL> select d.dname from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
SQL> select e.deptno,d.dname from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname;
DEPTNO DNAME
------ --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
SQL> select e.deptno,d.dname,count(*) from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname;
DEPTNO DNAME COUNT(*)
------ -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
13.查询各个部门员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位(子查询)
SQL> select count(*),job from emp where sal>(select avg(sal) from emp) group by job;
14.查询工资相同的员工的工资和姓名(子查询)
SQL> select sal,ename from emp e where(select count(*) from emp where sal=e.sal group by sal)>1;
或
SQL> select e.sal,e.ename from emp e,emp e1 where e.sal=e1.sal and e.ename<>e1.ename;