2023年Oracle面试题附答案及分析

精选面试题Oracle

1.显示10号部门的所有经理MANAGER和20号部门aI所有职工CLERK1aI详细信息答案Select*from emp where deptno=10and job=MANAGER ordeptno=20and job=clerk;select*from empwhere deptno=10and job=MANAGER ordeptno=20and job=CLERK;显示姓名中没有字的员工时详细信息或具有字的员工信息

2.L答案Select*from empwhere enamenote like%L%or enamelike%SM%;select*from empwhere enamenot like%L%or enamelike%SM%;显示各个部门经理日勺工资

3.MANAGER答案11select deptnoemname salaryfrom emp_w wherej ob=MANAGER;f A显示佣金收入比工资高的员工的详细信息

4.COMM SAL答案select*from empwhere commsal;把列看做是员工的生日，求本月过生日的员工

5.hiredate答案select*from empwhere to_charhiredate mm=to_charsysdate,mm;z把列看做是员工股生日,求下月过生日的员工

6.hiredate I答案select*from empwhere to_charhiredate,mm=to_charadd_monthssysdate,l,mm;求年入职的员工

7.1982答案select*from empwhere to_charhiredate/yyyy,=1982;求年下六个月入职的员工

8.1981答案select*from empwhere hiredatebetweento_date,1981-7-l,,yyyy-mm-dd,and to_date1982-l-l「yyyy-mm-dcT-1;求年各个月入职时的员工个数

9.1981答案select count*,to_chartrunchiredate/month,,,yyyy-mm,「from empwhere to_charhiredate yyyy=‘1981’group bytrunchiredate,monthorder bytrunchiredate,month;查询各个部门的平均工资

10.答案select deptno,avgsal from emp group by deptno;显示多种职位的最低工资

11.答案select job,minsal from emp group by job;按照入职日期由新到旧排列员工信息

12.答案select*from emporder byhiredate desc;查询员工日勺基本信息，附加其上级的姓名select e.*,e

2.ename from emp e,emp e2where e.mgr=e

2.empno;显示工资比，，高的所有员工日勺姓名和工资

13.ALLEN答案select*from empwhere salselect sal from empwhere ename=ALLEN;显示与从事相似工作的员工时详细信息

14.SCOTT答案select*from empwhere job=select*from empwhere ename=,SCOTT,;显示销售部员工肚|姓名

15.SALES答案select enamefrom emp e,dept dwhere e.deptno=d.deptno andd.dname=SALES;显示与号部门，员工工资相似的员工的姓名和工资

16.30MARTIN答案select ename,salfrom empwhere sal=select salfrom empwhere deptno=30and ename=MARTIN;查询所有工资高于平均工资平均工资包括所有员工日勺销售人员

17.SALESMAN答案select*from empwhere job=SALESMAN andsalselect avgsal from emp;显示所有职工的姓名及其所在部门的名称和工资

18.答案select ename,job,dname from emp e,dept dwhere e.deptno=d.deptno;查询在研发部工作员工的编号，姓名，工作部门，工作所在地RESEARCH答案select empno,ename,dname,loc from emp e,dept dwhere e.deptno=d.deptno anddanme=RESEARCH,;查询各个部门的名称和员工人数

19.答案select*from select count*c,deptno from emp group by deptnoeinner joindept don e.deptno=d.deptno;查询各个职位员工工资不小于平均工资平均工资包括所有员工的人数和员工职位

20.答案select job,countf*from empwhere salselect avgsal from emp group byjob;查询工资相似的员工的工资和姓名

21.答案select*from emp e whereselect count*from empwhere sal=e.sal group by sal1;查询工资最高欧名员工信息

22.I3答案select*from select*from emporder bysal descwhere rownum=3;按工资进行排名,排名从开始，工资相似排名相似假如两人并列第则没有第名，从第三名继

23.112续排答案select e.*,select count*from empwhere sale.sal+l rankfrom emp e order by rank;求入职日期相似於年月日相似的员工

24.1答案select*from empe whereselectcount*from empwhere e.hiredate=hiredatel;查询每个部门的最高工资

25.select deptno,maxsal maxsal from emp groupby deptno order by deptno;查询每个部门，每种职位的最高工资

26.答案select deptno,job,maxsalfrom emp groupby deptno,job orderby deptno,job;查询每个员工的信息及工资级别

27.答案select e.*,sg.grade from empe,salgrade sgwhere salbetween losal and hisal;查询工资最高的第名员工

28.6-10答案select*from select e.*,rownum rnfromselect*from emporderbysal descewhere rownum=10where rn5;查询各部门工资最高的员工信息

29.答案select*from empe wheree.sal=select maxsalfromempwhere deptno=e.deptno;查询每个部门工资最高的前名员工

30.J2答案select*fromempe whereselectcount*fromempwhere sale.salande.deptno=deptno2orderby deptno,sal desc;答案select*fromempe whereselectcount*fromempwheree.empno=mgr2;查询所有不小于本部门平均工资的员工信息

32.答案select*fromempe wheresal select avgsalfromempwheredeptno=e.deptnoorder by deptno;查询平均工资最高日勺部门信息

33.答案select d.*,avgsalfrom dept dselect avgsalavgsal,deptno fromemp groupbydeptnosewhere avgsal=select maxavgsalfromempgroupbydeptno andd.deptno=se.deptno;查询不小于各部门总工资日勺平均值的部门信息

34.答案select d.*,sumsal fromdept d,select sumsalsumsal,deptno fromempgroupbydeptnose wheresumsalselect avgsumsal fromempgroupbydeptno andse.deptno=d.deptno;查询不小于各部门总工资的平均值的部门下的员工信息

35.答案selecte.*,sumsalfromempe,select sumsalsumsal,deptno fromempgroupbydeptnosewhere sumsalselect avgsumsalfromempgroupbydeptno andse.deptno=e.deptno;查询没有员工的部门信息

36.答案select d.*fromdept d left join empe one.deptno=d.deptno whereempno isnull;查询目前月有多少天

37.答案select truncadd_monthssysdate,l/month-truncsysdate,month fromdual;列出最低薪金不小于的多种工作及此从事此工作的|所有雇员人数

38.1500答案SELECT job,COUNTempnoFROM empGROUPBY jobHAVING MINsal1500;列出薪金高于企业平均薪金的所有员工，所在部门，上级领导，企业日勺工资等级答案SELECT e.empno,e.ename,d.dname,m.ename,s.gradeFROM empe,deptd,emp m^algrade sWHEREsalSELECT AVGsal FROM empAND e.mgr=m.empno ANDd.deptno=e.deptno+AND e.salBETWEEN s.losal ANDs.hisal;列出薪金高于在部门工作的所有员工的薪金的员工姓名和薪金、部门名称

39.30答案SELECT e.ename,e.sal,d.dname FROMempe,dept dWHEREsalALL SELECTsalFROMemp WHEREdeptno=30AND e.deptno=d.deptno;列出所有部门欧详细信息和部门人数

40.I答案SELECT d.dname,d.loc’dt.countFROM deptd,SELECT deptno,COUNT*count FROMemp GROUPBY deptnodtWHERE d.deptno=dt.deptno;显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计不小于输出成果按月工资的合计升序排列$5000,答案SELECT job,SUMsal sumFROMempWHERE joboSALESMANGROUPBY jobHAVING sum5000ORDER BYsum;客户表登陆流水表购物流水表

41.aid nameaddress bidtime cidtime productidproductnum.求每个客户日勺最新登陆时间姓名落户1time,name id答案select a.id,a.name,d.time astimefrom aleftjoinselect id,maxtime astime fromb groupby iddon a.id=d.id;.查最新登陆并且已经购置商品的客户登陆的时间一条语句2id,name,time sql答案selecta.id,a.name,d.time astimefrom a,select id,maxtime astime fromb groupby iddwhere a.id=d.idand existsselect*from cwhere id=a.id;。