經常涉及的Oracle面試題

（1）查詢20號部門的所有員工信息。 select?*?from?emp?where?deptno?=?20; （2）查詢所有工種為CLERK的員工的工號、員工名和部門名。 select?empno,ename,deptno?from?emp?where?job?like?'CLERK'; （3）查詢獎金（COMM）高于工資（SAL）的員工信息。 select?*?from?emp?where?comm?>?sal; （4）查詢獎金高于工資的20%的員工信息。 select?*?from?emp?where?comm?>?(sal*0.2); （5）查詢10號部門中工種為MANAGER和20號部門中工種為CLERK的員工的信息。 select?*?from?emp? where?(deptno?=?10?and?job?like?'MANAGER')?or?(deptno?=?20?and?job?like?'CLERK'); （6）查詢所有工種不是MANAGER和CLERK，且工資大于或等于2000的員工的詳細信息。 select?*?from?emp? where?job?not?in?('MANAGER','CLERK')?and?sal?>=?2000?; （7）查詢有獎金的員工的不同工種。 select?distinct?job?from?emp?where?comm?is?not?null; （8）查詢所有員工工資和獎金的和。 select?ename,(sal+nvl(comm,0))?salcomm?from?emp; （9）查詢沒有獎金或獎金低于100的員工信息。 select?*?from?emp?where?(comm?is?null?or?comm?=?10?; （12）查詢員工信息，要求以首字母大寫的方式顯示所有員工的姓名。 select?upper(substr(ename,1,1))?||?lower(substr(ename,2,length(ename)-1))?from?emp; （13）查詢員工名正好為6個字符的員工的信息。 select?*?from?emp?where?length（ename）=?6?; （14）查詢員工名字中不包含字母“S”員工。 select?*?from?emp?where?ename?not?in?(select?ename?from?emp?where?ename?like?'%S%')?; select?*?from?emp?where?ename?not?like?‘%S%’; （15）查詢員工姓名的第2個字母為“M”的員工信息。 select?*?from?emp?where?ename?like?'_M%'; （16）查詢所有員工姓名的前3個字符。 select?substr(ename,1,3)?from?emp?; （17）查詢所有員工的姓名，如果包含字母“s”，則用“S”替換。 select?replace(ename,'s','S')?from?emp?; （18）查詢員工的姓名和入職日期，并按入職日期從先到后進行排列。 select?ename,hiredate?from?emp?order?by?hiredate?asc?; （19）顯示所有的姓名、工種、工資和獎金，按工種降序排列，若工種相同則按工資升序排列。 select?ename,job,sal,comm?from?emp?order?by?job?desc,sal?asc?; （20）顯示所有員工的姓名、入職的年份和月份，若入職日期所在的月份排序，若月份相同則按入職的年份排序。 select?ename,to_char(hiredate,'yyyy')||'-'||to_char(hiredate,'mm')?from?emp?order?by?to_char(hiredate,'mm'),to_char(hiredate,'yyyy'); （21）查詢在2月份入職的所有員工信息。 select?*?from?emp?where?to_char(hiredate,'mm')?=?2?; （22）查詢所有員工入職以來的工作期限，用“**年**月**日”的形式表示。 select?ename,floor((sysdate-hiredate)/365)||'年'||floor(mod((sysdate-hiredate),365)/30)||'月'||cell(mod(mod((sysdate-hiredate),365),30))||'天'?from?emp?; （23）查詢至少有一個員工的部門信息。 select?*?from?dept?where?deptno?in?(select?distinct?deptno?from?emp?where?mgr?is?not?null)?; （24）查詢工資比SMITH員工工資高的所有員工信息。 select?*?from?emp?where?sal?>?(select?sal?from?emp?where?ename?like?'SMITH')?; （25）查詢所有員工的姓名及其直接上級的姓名。 select?staname,ename?supname?from?(select?ename?staname,mgr?from?emp)?t?join?emp?on?t.mgr=emp.empno?; （26）查詢入職日期早于其直接上級領導的所有員工信息。 select?*?from?emp?where?empno?in?(select?staempno?from?(select?empno?staempno,hiredate?stahiredate,mgr?from?emp)?t?join?emp?on?t.mgr=emp.empno?and?stahiredate??2500?; （31）查詢最低工資低于2000的部門及其員工信息。 select?*?from?emp?where?deptno?in?(select?deptno?from?(select?min(sal)?min_sal,deptno?from?emp?group?by?deptno)?where?min_sal??(select?avg(sal)?from?emp)?; （34）查詢與SMITH員工從事相同工作的所有員工信息。 select?*?from?emp?where?job?in?(select?job?from?emp?where?ename?like?'SMITH')?and?ename?not?like?'SMITH'?; （35）列出工資等于30號部門中某個員工工資的所有員工的姓名和工資。 select?ename,sal?from?emp?where?sal?=any?(select?sal?from?emp?where?deptno?=?30)?; （36）查詢工資高于30號部門中工作的所有員工的工資的員工姓名和工資。 select?ename,sal?from?emp?where?sal?>all?(select?sal?from?emp?where?deptno?=?30)?; （37）查詢每個部門中的員工數量、平均工資和平均工作年限。 select?dname,count,avg_sal,avg_date?from?dept?join?(select?count(*)?count,avg(sal)?avg_sal,avg((sysdate-hiredate)/365)?avg_date,deptno?from?emp?group?by?deptno)?t?on?dept.deptno?=?t.deptno?; （38）查詢從事同一種工作但不屬于同一部門的員工信息。 select?distinct?t1.empno,t1.ename,t1.deptno?from?emp?t1?join?emp?t2?on?t1.job?like?t2.job?and?t1.deptno??t2.deptno?; （39）查詢各個部門的詳細信息以及部門人數、部門平均工資。 Select?dept.*,person_num,avg_sal?from?dept,(select?count(*)?person_num,avg(sal)?avg_sal,deptno?from?emp?group?by?deptno)?t?where?dept.deptno?=?t.deptno?; （40）查詢各種工作的最低工資。 select?job,min(sal)?from?emp?group?by?job?; （41）查詢各個部門中的不同工種的最高工資。 select?max(sal),job,deptno?from?emp?group?by?deptno,job?order?by?deptno,job?； （42）查詢10號部門員工以及領導的信息。 select?*?from?emp?where?empno?in?(select?mgr?from?emp?where?deptno=10)?or?deptno?=?10?； （43）查詢各個部門的人數及平均工資。 select?deptno,count(*),avg(sal)?from?emp?group?by?deptno?； （44）查詢工資為某個部門平均工資的員工信息。 select?*?from?emp?where?sal?in?(select?avg(sal)?avg_sal?from?emp?group?by?deptno)?; （45）查詢工資高于本部門平均工資的員工的信息。 select?emp.*?from?emp?join?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)?t?on?emp.deptno=t.deptno?and?sal>avg_sal?; （46）查詢工資高于本部門平均工資的員工的信息及其部門的平均工資。 select?emp.*,	?from?emp?join?(select?deptno,avg(sal)?avg_sal?from?emp?group?by?deptno)?t?on?emp.deptno=t.deptno?and?sal>avg_sal?; （47）查詢工資高于20號部門某個員工工資的員工的信息。 select?*?from?emp?where?sal?>any(select?sal?from?emp?where?deptno=20); （48）統計各個工種的人數與平均工資。 select?job,count(*),avg(sal)?from?emp?group?by?job?; （49）統計每個部門中各個工種的人數與平均工資。 select?deptno,job,count(*),avg(sal)?from?emp?group?by?deptno,job?order?by?deptno,job; （50）查詢工資、獎金與10?號部門某個員工工資、獎金都相同的員工的信息。 select?emp.*?from?emp?join?(select?sal,comm?from?emp?where?deptno?=?10)?t?on?emp.sal=t.sal?and?nvl(emp.comm,0)=nvl(t.comm,0)?and?emp.deptno?!=?10; （51）查詢部門人數大于5的部門的員工的信息。 select?*?from?emp?where?deptno?in?(select?deptno?from?emp?group?by?deptno?having?count(*)>5); （52）查詢所有員工工資都大于1000的部門的信息。 select?*?from?dept?where?deptno?in?(select?distinct?deptno?from?emp?where?deptno?not?in?(select?distinct?deptno?from?emp?where?sal??(select?max(sal)?from?(select?e2.sal?from?emp?e1?right?join?emp?e2?on?e1.mgr?=?e2.empno?where?e1.ename?is?null)?t?);?  select?ename?from?emp?where?empno?in?(select?mgr?from?emp)?and?sal?>? (select?max(sal)?from?emp?where?empno?not?in?(select?distinct?mgr?from?emp?where?mgr?is?not?null)?); //NOT?IN遇到NULL則返回NULL，必須排除NULL值 ?11.?求薪水最高的前5名雇員? select?empno,ename?from?(select?*?from?emp?order?by?sal?desc)?where?rownum=6?and?r(select?count(*)-5?from?emp);