oracle题目

   1. /*1、选择在部门30中员工的所有信息*/ 
   2. select * from scott.emp where deptno = '30' 
   3. /*2、列出职位为（MANAGER）的员工的编号，姓名 */ 
   4. select empno, ename from scott.emp where job = 'MANAGER' 
   5. /*3、找出奖金高于工资的员工*/ 
   6. select * from scott.emp where comm > sal 
   7. /*4、找出每个员工奖金和工资的总和 */ 
   8. select ename, sal + nvl(comm, 0) from scott.emp 
   9. /*5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK) */ 
  10. select * 
  11.   from scott.emp 
  12.  where deptno = '10' 
  13.    and job = 'MANAGER' 
  14. union 
  15. select * 
  16.   from scott.emp 
  17.  where job = 'CLERK' 
  18.    and deptno = '20' 
  19. /*6、找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工 */ 
  20. select * 
  21.   from scott.emp 
  22.  where job != 'MANAGER' 
  23.    and job != 'CLERK' 
  24.    and sal > 2000 
  25. /*7、找出有奖金的员工的不同工作 */ 
  26. select distinct(job) from scott.emp where comm is not null 
  27. /*8、找出没有奖金或者奖金低于500的员工*/  
  28. select * 
  29.   from scott.emp 
  30.  where comm is not null 
  31.    and comm > 500 
  32. /*9、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 */ 
  33. select ename 
  34.   from scott.emp 
  35.  order by (months_between(sysdate, hiredate) / 12) desc 
  36.   
  37.  select ename,hiredate from scott.emp order by hiredate 
  38. /*10、找出每个月倒数第三天受雇的员工*/ 
  39. select * from scott.emp where hiredate = last_day(hiredate) - 2 
  40. /*11、分别用case和decode函数列出员工所在的部门，deptno=10显示'部门10', 
  41.  deptno=20显示'部门20' 
  42.  deptno=30显示'部门30' 
  43.  deptno=40显示'部门40' 
  44.  否则为'其他部门'*/ 
  45.  select ename, 
  46.         case deptno 
  47.           when 10 then 
  48.            '部门10' 
  49.           when 20 then 
  50.            '部门20' 
  51.           when 30 then 
  52.            '部门30' 
  53.           when 40 then 
  54.            '部门40' 
  55.           else 
  56.            '其他部门' 
  57.         end 工资情况 
  58.    from scott.emp 
  59.   
  60.  select ename, 
  61.         decode(deptno, 
  62.                10, 
  63.                '部门10', 
  64.                20, 
  65.                '部门20', 
  66.                30, 
  67.                '部门30', 
  68.                40, 
  69.                '部门40', 
  70.                '其他部门') 工资情况 
  71.    from scott.emp 
  72. /*12、分组统计各部门下工资>500的员工的平均工资*/ 
  73. select avg(sal) from scott.emp where sal > 500 group by deptno 
  74. /*13、统计各部门下平均工资大于500的部门*/ 
  75. select deptno from scott.emp group by deptno having avg(sal) > 500 
  76. /*14、算出部门30中得到最多奖金的员工奖金 */ 
  77. select max(comm) from scott.emp where deptno = 30 
  78. /*15、算出部门30中得到最多奖金的员工姓名*/ 
  79. select ename 
  80.   from scott.emp 
  81.  where deptno = 30 
  82.    and comm = (select max(comm) from scott.emp where deptno = 30) 
  83. /*16、算出每个职位的员工数和最低工资*/ 
  84. select count(ename), min(sal), job from scott.emp group by job 
  85. /*17、列出员工表中每个部门的员工数，和部门no */ 
  86. select count(ename), deptno from scott.emp group by deptno 
  87. /*18、得到工资大于自己部门平均工资的员工信息*/ 
  88. select * 
  89.   from scott.emp e 
  90.  where sal > (select avg(sal) from scott.emp where e.deptno = deptno) 
  91.   
  92.   select * 
  93.     from scott.emp e1, 
  94.          (select avg(sal) sals, deptno from scott.emp group by deptno) e2 
  95.    where sal > sals 
  96.      and e1.deptno = e2.deptno 
  97. /*19、分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金) */ 
  98. select avg(nvl(comm,0)), sum(sal + nvl(comm, 0)) 
  99.   from scott.emp 
100.  group by deptno,job 
101. /*20、笛卡尔集*/ 
102. select * from scott.emp, scott.dept 
103. /*21、显示员工ID,名字，直属主管ID，名字*/ 
104. select empno, 
105.        ename, 
106.        mgr, 
107.        (select ename from scott.emp e1 where e1.empno = e2.mgr) 直属主管名字 
108.   from scott.emp e2 
109. /*22、DEPT表按照部门跟EMP表左关联*/ 
110. select * 
111.   from scott.dept, scott.emp 
112.  where scott.dept.deptno = scott.emp.deptno(+) 
113. /*23、使用此语句重复的内容不再显示了*/ 
114. select distinct (job) from scott.emp 
115. /*24、重复的内容依然显示 */ 
116. select * 
117.   from scott.emp 
118. UNION ALL 
119. select * from scott.emp 
120. /*23和24题和22题是一样的 */ 
121.  
122. /*25、只显示了两个表中彼此重复的记录。*/ 
123. select * 
124.   from scott.dept, scott.emp 
125.  where scott.dept.deptno(+) = scott.emp.deptno 
126. /*26、只显示了两张表中的不同记录*/ 
127. select * from scott.emp union select * from scott.emp 
128. minus  
129. (select * from scott.emp intersect select * from scott.emp) 
130.  
131. (select * from scott.emp minus select * from scott.emp) 
132. union  
133. (select * from scott.emp minus select * from scott.emp) 
134.    表结构相同  先union 只能有 -  
135. /*27、列出员工表中每个部门的员工数，和部门no */ 
136. select count(ename), deptno from scott.emp group by deptno 
137. /*28、列出员工表中每个部门的员工数（员工数必须大于3），和部门名称*/  
138. select count(deptno), 
139.        deptno, 
140.        (select dname from scott.dept where scott.dept.deptno = e1.deptno) 
141.   from scott.emp e1 
142.  group by deptno having count(deptno)>3 
143. /*29、找出工资比jones多的员工*/ 
144. select * 
145.   from scott.emp 
146.  where sal > (select sal from scott.emp where ename = 'JONES') 
147. /*30、列出所有员工的姓名和其上级的姓名 */ 
148. select ename, 
149.        (select ename from scott.emp e1 where e1.empno = e2.mgr) 上级的姓名 
150.   from scott.emp e2 
151. /*31、以职位分组，找出平均工资最高的两种职位 */ 
152. select job 
153.   from scott.emp 
154.  group by job 
155. having avg(sal) in (select max(sal) from scott.emp group by job ) 
156.  
157. select job 
158.   from (select job, avg(sal) 
159.           from scott.emp 
160.          group by job 
161.          order by avg(sal) desc) 
162.  where rownum <= 2 
163.   
164.  最大的： 
165.  select max(max_sal) 
166.    from (select job, avg(sal) max_sal from scott.emp group by job) 
167. /*32、查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称*/ 
168.  
169. select ename, dname 
170.   from scott.emp e1, scott.dept e2 
171.  where e1.deptno = e2.deptno 
172.    and e1.deptno <> 20 
173.    and sal > (select max(sal) from scott.emp where deptno = '20') 
174.             
175. /*33、得到平均工资大于2000的工作职种 */ 
176. select job from scott.emp group by job having avg(sal) > 2000 
177. /*34、分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500 */ 
178. select avg(sal) 
179.   from scott.emp 
180.  where sal > 2000 
181.  group by deptno 
182. having avg(sal) > 2500 
183. /*35、得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 */ 
184. select deptno, dname, loc 
185.   from scott.dept 
186.  where deptno in (select deptno 
187.                    from scott.emp 
188.                   group by deptno 
189.                  having sum(sal) = (select min(sum(sal)) 
190.                                      from scott.emp 
191.                                     group by deptno)) 
192.  
193. select * from scott.dept 
194. /*36、分部门得到平均工资等级为2级（等级表）的部门编号 */ 
195. select deptno 
196.   from scott.emp 
197.  group by deptno 
198. having avg(sal) between (select losal from scott.salgrade where grade = 2) and (select hisal 
199.                                                                                   from scott.salgrade 
200.                                                                                where grade = 2) 
201.                                                                                 
202. select avg(sal) from scott.emp group by deptno 
203. select * from scott.salgrade 
204. /*37、查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置*/ 
205. select a.ename, dname, loc 
206.   from (select * 
207.           from (select rownum rn, deptno, empno, sal, ename 
208.                   from (select deptno, empno, sal, ename 
209.                           from scott.emp 
210.                          where deptno in (10, 20) 
211.                            and rownum <= 5 
212.                          order by sal desc)) 
213.          where rn between 3 and 5) a, 
214.        scott.dept b 
215.  where a.deptno = b.deptno 
216.             
217.             
218. select deptno, ename 
219.   from (select empno, deptno, ename 
220.           from (select rownum rn, deptno, empno, sal, ename 
221.                   from (select deptno, empno, sal, ename 
222.                           from scott.emp 
223.                          where deptno in (10, 20) 
224.                          order by sal desc)) 
225.          where rn between 3 and 5) 
226.    
227.  
228. /*38、查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入*/ 
229. select empno, ename, sal + nvl(comm, 0) 
230.   from scott.emp e1 
231.  where sal + nvl(comm, 0) > 
232.        (select sal + nvl(comm, 0) from scott.emp where empno = e1.mgr) 
233.  
234. select * from scott.emp 
235. select ename, sal + nvl(comm, 0) from scott.emp 
236. /*39、查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资 */ 
237. select avg(sal) 
238.   from scott.emp 
239.  where job in (select job 
240.                  from scott.emp 
241.                 where ename = 'MARTIN' 
242.                    or ename = 'SMITH') 
243. /*40、查找出不属于任何部门的员工 */ 
244. select * from scott.emp where deptno  is null 
245. select * from scott.emp where deptno not in (select deptno from scott.emp) 
246. /*41、按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）*/ 
247. select dname, loc 
248.   from (select * 
249.           from (select rownum rn, deptno 
250.                   from (select deptno, count(*) 
251.                           from scott.emp 
252.                          group by deptno 
253.                          order by count(*) desc)) 
254.          where rn between 2 and 5) a, 
255.        scott.dept b 
256.  where a.deptno = b.deptno 
257.    
258.   select count(*) from scott.emp group by deptno 
259. /*42、查询出king所在部门的部门号\部门名称\部门人数 （多种方法）*/ 
260. select sc.deptno, dname, count(*) 
261.   from scott.emp sc, scott.dept de 
262.  where sc.deptno = ((select deptno from scott.emp where ename = 'KING')) 
263.    and de.deptno = sc.deptno 
264.  group by sc.deptno, dname 
265.  
266.  
267. /*43、查询出king所在部门的工作年限最大的员工名字*/  
268. select * 
269.   from scott.emp 
270.  where hiredate = 
271.        (select min(hiredate) 
272.           from scott.emp 
273.          where deptno in (select deptno from scott.emp where ename = 'KING')) 
274.    and deptno = (select deptno from scott.emp where ename = 'KING') 
275. /*44、查询出工资成本最高的部门的部门号和部门名称 */ 
276. select deptno, dname 
277.   from scott.dept 
278.  where deptno = (select deptno 
279.                    from scott.emp 
280.                   group by deptno 
281.                  having sum(sal) = (select max(sum(sal)) 
282.                                      from scott.emp 
283.                                     group by deptno)) 
284.  
285. select * from scott.emp for update