hanqingwxf2008
- 浏览: 50643 次
- 性别:
- 来自: 青岛
-
文章分类
最新评论
2./*1、选择在部门30中员工的所有信息*/
3.select * from scott.emp where deptno = '30'
4./*2、列出职位为（MANAGER）的员工的编号，姓名 */
5.select empno, ename from scott.emp where job = 'MANAGER'
6./*3、找出奖金高于工资的员工*/
7.select * from scott.emp where comm > sal
8./*4、找出每个员工奖金和工资的总和 */
9.select ename, sal + nvl(comm, 0) from scott.emp
10./*5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK) */
11.select *
12. from scott.emp
13. where deptno = '10'
14. and job = 'MANAGER'
15.union
16.select *
17. from scott.emp
18. where job = 'CLERK'
19. and deptno = '20'
20./*6、找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工 */
21.select *
22. from scott.emp
23. where job != 'MANAGER'
24. and job != 'CLERK'
25. and sal > 2000
26./*7、找出有奖金的员工的不同工作 */
27.select distinct(job) from scott.emp where comm is not null
28./*8、找出没有奖金或者奖金低于500的员工*/
29.select *
30. from scott.emp
31. where comm is not null
32. and comm > 500
33./*9、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 */
34.select ename
35. from scott.emp
36. order by (months_between(sysdate, hiredate) / 12) desc
37.
38. select ename,hiredate from scott.emp order by hiredate
39./*10、找出每个月倒数第三天受雇的员工*/
40.select * from scott.emp where hiredate = last_day(hiredate) - 2
41./*11、分别用case和decode函数列出员工所在的部门，deptno=10显示'部门10',
42. deptno=20显示'部门20'
43. deptno=30显示'部门30'
44. deptno=40显示'部门40'
45. 否则为'其他部门'*/
46. select ename,
47. case deptno
48. when 10 then
49. '部门10'
50. when 20 then
51. '部门20'
52. when 30 then
53. '部门30'
54. when 40 then
55. '部门40'
56. else
57. '其他部门'
58. end 工资情况
59. from scott.emp
60.
61. select ename,
62. decode(deptno,
63. 10,
64. '部门10',
65. 20,
66. '部门20',
67. 30,
68. '部门30',
69. 40,
70. '部门40',
71. '其他部门') 工资情况
72. from scott.emp
73./*12、分组统计各部门下工资>500的员工的平均工资*/
74.select avg(sal) from scott.emp where sal > 500 group by deptno
75./*13、统计各部门下平均工资大于500的部门*/
76.select deptno from scott.emp group by deptno having avg(sal) > 500
77./*14、算出部门30中得到最多奖金的员工奖金 */
78.select max(comm) from scott.emp where deptno = 30
79./*15、算出部门30中得到最多奖金的员工姓名*/
80.select ename
81. from scott.emp
82. where deptno = 30
83. and comm = (select max(comm) from scott.emp where deptno = 30)
84./*16、算出每个职位的员工数和最低工资*/
85.select count(ename), min(sal), job from scott.emp group by job
86./*17、列出员工表中每个部门的员工数，和部门no */
87.select count(ename), deptno from scott.emp group by deptno
88./*18、得到工资大于自己部门平均工资的员工信息*/
89.select *
90. from scott.emp e
91. where sal > (select avg(sal) from scott.emp where e.deptno = deptno)
92.
93. select *
94. from scott.emp e1,
95. (select avg(sal) sals, deptno from scott.emp group by deptno) e2
96. where sal > sals
97. and e1.deptno = e2.deptno
98./*19、分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金) */
99.select avg(nvl(comm,0)), sum(sal + nvl(comm, 0))
100. from scott.emp
101. group by deptno,job
102./*20、笛卡尔集*/
103.select * from scott.emp, scott.dept
104./*21、显示员工ID,名字，直属主管ID，名字*/
105.select empno,
106. ename,
107. mgr,
108. (select ename from scott.emp e1 where e1.empno = e2.mgr) 直属主管名字
109. from scott.emp e2
110./*22、DEPT表按照部门跟EMP表左关联*/
111.select *
112. from scott.dept, scott.emp
113. where scott.dept.deptno = scott.emp.deptno(+)
114./*23、使用此语句重复的内容不再显示了*/
115.select distinct (job) from scott.emp
116./*24、重复的内容依然显示 */
117.select *
118. from scott.emp
119.UNION ALL
120.select * from scott.emp
121./*23和24题和22题是一样的 */
122.
123./*25、只显示了两个表中彼此重复的记录。*/
124.select *
125. from scott.dept, scott.emp
126. where scott.dept.deptno(+) = scott.emp.deptno
127./*26、只显示了两张表中的不同记录*/
128.select * from scott.emp union select * from scott.emp
129.minus
130.(select * from scott.emp intersect select * from scott.emp)
131.
132.(select * from scott.emp minus select * from scott.emp)
133.union
134.(select * from scott.emp minus select * from scott.emp)
135. 表结构相同 先union 只能有 -
136./*27、列出员工表中每个部门的员工数，和部门no */
137.select count(ename), deptno from scott.emp group by deptno
138./*28、列出员工表中每个部门的员工数（员工数必须大于3），和部门名称*/
139.select count(deptno),
140. deptno,
141. (select dname from scott.dept where scott.dept.deptno = e1.deptno)
142. from scott.emp e1
143. group by deptno having count(deptno)>3
144./*29、找出工资比jones多的员工*/
145.select *
146. from scott.emp
147. where sal > (select sal from scott.emp where ename = 'JONES')
148./*30、列出所有员工的姓名和其上级的姓名 */
149.select ename,
150. (select ename from scott.emp e1 where e1.empno = e2.mgr) 上级的姓名
151. from scott.emp e2
152./*31、以职位分组，找出平均工资最高的两种职位 */
153.select job
154. from scott.emp
155. group by job
156.having avg(sal) in (select max(sal) from scott.emp group by job )
157.
158.select job
159. from (select job, avg(sal)
160. from scott.emp
161. group by job
162. order by avg(sal) desc)
163. where rownum <= 2
164.
165. 最大的：
166. select max(max_sal)
167. from (select job, avg(sal) max_sal from scott.emp group by job)
168./*32、查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称*/
169.
170.select ename, dname
171. from scott.emp e1, scott.dept e2
172. where e1.deptno = e2.deptno
173. and e1.deptno <> 20
174. and sal > (select max(sal) from scott.emp where deptno = '20')
175.
176./*33、得到平均工资大于2000的工作职种 */
177.select job from scott.emp group by job having avg(sal) > 2000
178./*34、分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500 */
179.select avg(sal)
180. from scott.emp
181. where sal > 2000
182. group by deptno
183.having avg(sal) > 2500
184./*35、得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 */
185.select deptno, dname, loc
186. from scott.dept
187. where deptno in (select deptno
188. from scott.emp
189. group by deptno
190. having sum(sal) = (select min(sum(sal))
191. from scott.emp
192. group by deptno))
193.
194.select * from scott.dept
195./*36、分部门得到平均工资等级为2级（等级表）的部门编号 */
196.select deptno
197. from scott.emp
198. group by deptno
199.having avg(sal) between (select losal from scott.salgrade where grade = 2) and (select hisal
200. from scott.salgrade
201. where grade = 2)
202.
203.select avg(sal) from scott.emp group by deptno
204.select * from scott.salgrade
205./*37、查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置*/
206.select a.ename, dname, loc
207. from (select *
208. from (select rownum rn, deptno, empno, sal, ename
209. from (select deptno, empno, sal, ename
210. from scott.emp
211. where deptno in (10, 20)
212. and rownum <= 5
213. order by sal desc))
214. where rn between 3 and 5) a,
215. scott.dept b
216. where a.deptno = b.deptno
217.
218.
219.select deptno, ename
220. from (select empno, deptno, ename
221. from (select rownum rn, deptno, empno, sal, ename
222. from (select deptno, empno, sal, ename
223. from scott.emp
224. where deptno in (10, 20)
225. order by sal desc))
226. where rn between 3 and 5)
227.
228.
229./*38、查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入*/
230.select empno, ename, sal + nvl(comm, 0)
231. from scott.emp e1
232. where sal + nvl(comm, 0) >
233. (select sal + nvl(comm, 0) from scott.emp where empno = e1.mgr)
234.
235.select * from scott.emp
236.select ename, sal + nvl(comm, 0) from scott.emp
237./*39、查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资 */
238.select avg(sal)
239. from scott.emp
240. where job in (select job
241. from scott.emp
242. where ename = 'MARTIN'
243. or ename = 'SMITH')
244./*40、查找出不属于任何部门的员工 */
245.select * from scott.emp where deptno is null
246.select * from scott.emp where deptno not in (select deptno from scott.emp)
247./*41、按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）*/
248.select dname, loc
249. from (select *
250. from (select rownum rn, deptno
251. from (select deptno, count(*)
252. from scott.emp
253. group by deptno
254. order by count(*) desc))
255. where rn between 2 and 5) a,
256. scott.dept b
257. where a.deptno = b.deptno
258.
259. select count(*) from scott.emp group by deptno
260./*42、查询出king所在部门的部门号\部门名称\部门人数 （多种方法）*/
261.select sc.deptno, dname, count(*)
262. from scott.emp sc, scott.dept de
263. where sc.deptno = ((select deptno from scott.emp where ename = 'KING'))
264. and de.deptno = sc.deptno
265. group by sc.deptno, dname
266.
267.
268./*43、查询出king所在部门的工作年限最大的员工名字*/
269.select *
270. from scott.emp
271. where hiredate =
272. (select min(hiredate)
273. from scott.emp
274. where deptno in (select deptno from scott.emp where ename = 'KING'))
275. and deptno = (select deptno from scott.emp where ename = 'KING')
276./*44、查询出工资成本最高的部门的部门号和部门名称 */
277.select deptno, dname
278. from scott.dept
279. where deptno = (select deptno
280. from scott.emp
281. group by deptno
282. having sum(sal) = (select max(sum(sal))
283. from scott.emp
284. group by deptno))
285.
286.select * from scott.emp for update
/*1、选择在部门30中员工的所有信息*/
select * from scott.emp where deptno = '30'
/*2、列出职位为（MANAGER）的员工的编号，姓名 */
select empno, ename from scott.emp where job = 'MANAGER'
/*3、找出奖金高于工资的员工*/
select * from scott.emp where comm > sal
/*4、找出每个员工奖金和工资的总和 */
select ename, sal + nvl(comm, 0) from scott.emp
/*5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK) */
select *
from scott.emp
where deptno = '10'
and job = 'MANAGER'
union
select *
from scott.emp
where job = 'CLERK'
and deptno = '20'
/*6、找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工 */
select *
from scott.emp
where job != 'MANAGER'
and job != 'CLERK'
and sal > 2000
/*7、找出有奖金的员工的不同工作 */
select distinct(job) from scott.emp where comm is not null
/*8、找出没有奖金或者奖金低于500的员工*/
select *
from scott.emp
where comm is not null
and comm > 500
/*9、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 */
select ename
from scott.emp
order by (months_between(sysdate, hiredate) / 12) desc
select ename,hiredate from scott.emp order by hiredate
/*10、找出每个月倒数第三天受雇的员工*/
select * from scott.emp where hiredate = last_day(hiredate) - 2
/*11、分别用case和decode函数列出员工所在的部门，deptno=10显示'部门10',
deptno=20显示'部门20'
deptno=30显示'部门30'
deptno=40显示'部门40'
否则为'其他部门'*/
select ename,
case deptno
when 10 then
'部门10'
when 20 then
'部门20'
when 30 then
'部门30'
when 40 then
'部门40'
else
'其他部门'
end 工资情况
from scott.emp
select ename,
decode(deptno,
10,
'部门10',
20,
'部门20',
30,
'部门30',
40,
'部门40',
'其他部门') 工资情况
from scott.emp
/*12、分组统计各部门下工资>500的员工的平均工资*/
select avg(sal) from scott.emp where sal > 500 group by deptno
/*13、统计各部门下平均工资大于500的部门*/
select deptno from scott.emp group by deptno having avg(sal) > 500
/*14、算出部门30中得到最多奖金的员工奖金 */
select max(comm) from scott.emp where deptno = 30
/*15、算出部门30中得到最多奖金的员工姓名*/
select ename
from scott.emp
where deptno = 30
and comm = (select max(comm) from scott.emp where deptno = 30)
/*16、算出每个职位的员工数和最低工资*/
select count(ename), min(sal), job from scott.emp group by job
/*17、列出员工表中每个部门的员工数，和部门no */
select count(ename), deptno from scott.emp group by deptno
/*18、得到工资大于自己部门平均工资的员工信息*/
select *
from scott.emp e
where sal > (select avg(sal) from scott.emp where e.deptno = deptno)
select *
from scott.emp e1,
(select avg(sal) sals, deptno from scott.emp group by deptno) e2
where sal > sals
and e1.deptno = e2.deptno
/*19、分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金) */
select avg(nvl(comm,0)), sum(sal + nvl(comm, 0))
from scott.emp
group by deptno,job
/*20、笛卡尔集*/
select * from scott.emp, scott.dept
/*21、显示员工ID,名字，直属主管ID，名字*/
select empno,
ename,
mgr,
(select ename from scott.emp e1 where e1.empno = e2.mgr) 直属主管名字
from scott.emp e2
/*22、DEPT表按照部门跟EMP表左关联*/
select *
from scott.dept, scott.emp
where scott.dept.deptno = scott.emp.deptno(+)
/*23、使用此语句重复的内容不再显示了*/
select distinct (job) from scott.emp
/*24、重复的内容依然显示 */
select *
from scott.emp
UNION ALL
select * from scott.emp
/*23和24题和22题是一样的 */
/*25、只显示了两个表中彼此重复的记录。*/
select *
from scott.dept, scott.emp
where scott.dept.deptno(+) = scott.emp.deptno
/*26、只显示了两张表中的不同记录*/
select * from scott.emp union select * from scott.emp
minus
(select * from scott.emp intersect select * from scott.emp)
(select * from scott.emp minus select * from scott.emp)
union
(select * from scott.emp minus select * from scott.emp)
表结构相同 先union 只能有 -
/*27、列出员工表中每个部门的员工数，和部门no */
select count(ename), deptno from scott.emp group by deptno
/*28、列出员工表中每个部门的员工数（员工数必须大于3），和部门名称*/
select count(deptno),
deptno,
(select dname from scott.dept where scott.dept.deptno = e1.deptno)
from scott.emp e1
group by deptno having count(deptno)>3
/*29、找出工资比jones多的员工*/
select *
from scott.emp
where sal > (select sal from scott.emp where ename = 'JONES')
/*30、列出所有员工的姓名和其上级的姓名 */
select ename,
(select ename from scott.emp e1 where e1.empno = e2.mgr) 上级的姓名
from scott.emp e2
/*31、以职位分组，找出平均工资最高的两种职位 */
select job
from scott.emp
group by job
having avg(sal) in (select max(sal) from scott.emp group by job )
select job
from (select job, avg(sal)
from scott.emp
group by job
order by avg(sal) desc)
where rownum <= 2
最大的：
select max(max_sal)
from (select job, avg(sal) max_sal from scott.emp group by job)
/*32、查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称*/
select ename, dname
from scott.emp e1, scott.dept e2
where e1.deptno = e2.deptno
and e1.deptno <> 20
and sal > (select max(sal) from scott.emp where deptno = '20')
/*33、得到平均工资大于2000的工作职种 */
select job from scott.emp group by job having avg(sal) > 2000
/*34、分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500 */
select avg(sal)
from scott.emp
where sal > 2000
group by deptno
having avg(sal) > 2500
/*35、得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 */
select deptno, dname, loc
from scott.dept
where deptno in (select deptno
from scott.emp
group by deptno
having sum(sal) = (select min(sum(sal))
from scott.emp
group by deptno))
select * from scott.dept
/*36、分部门得到平均工资等级为2级（等级表）的部门编号 */
select deptno
from scott.emp
group by deptno
having avg(sal) between (select losal from scott.salgrade where grade = 2) and (select hisal
from scott.salgrade
where grade = 2)
select avg(sal) from scott.emp group by deptno
select * from scott.salgrade
/*37、查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置*/
select a.ename, dname, loc
from (select *
from (select rownum rn, deptno, empno, sal, ename
from (select deptno, empno, sal, ename
from scott.emp
where deptno in (10, 20)
and rownum <= 5
order by sal desc))
where rn between 3 and 5) a,
scott.dept b
where a.deptno = b.deptno
select deptno, ename
from (select empno, deptno, ename
from (select rownum rn, deptno, empno, sal, ename
from (select deptno, empno, sal, ename
from scott.emp
where deptno in (10, 20)
order by sal desc))
where rn between 3 and 5)
/*38、查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入*/
select empno, ename, sal + nvl(comm, 0)
from scott.emp e1
where sal + nvl(comm, 0) >
(select sal + nvl(comm, 0) from scott.emp where empno = e1.mgr)
select * from scott.emp
select ename, sal + nvl(comm, 0) from scott.emp
/*39、查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资 */
select avg(sal)
from scott.emp
where job in (select job
from scott.emp
where ename = 'MARTIN'
or ename = 'SMITH')
/*40、查找出不属于任何部门的员工 */
select * from scott.emp where deptno is null
select * from scott.emp where deptno not in (select deptno from scott.emp)
/*41、按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）*/
select dname, loc
from (select *
from (select rownum rn, deptno
from (select deptno, count(*)
from scott.emp
group by deptno
order by count(*) desc))
where rn between 2 and 5) a,
scott.dept b
where a.deptno = b.deptno
select count(*) from scott.emp group by deptno
/*42、查询出king所在部门的部门号\部门名称\部门人数 （多种方法）*/
select sc.deptno, dname, count(*)
from scott.emp sc, scott.dept de
where sc.deptno = ((select deptno from scott.emp where ename = 'KING'))
and de.deptno = sc.deptno
group by sc.deptno, dname
/*43、查询出king所在部门的工作年限最大的员工名字*/
select *
from scott.emp
where hiredate =
(select min(hiredate)
from scott.emp
where deptno in (select deptno from scott.emp where ename = 'KING'))
and deptno = (select deptno from scott.emp where ename = 'KING')
/*44、查询出工资成本最高的部门的部门号和部门名称 */
select deptno, dname
from scott.dept
where deptno = (select deptno
from scott.emp
group by deptno
having sum(sal) = (select max(sum(sal))
from scott.emp
group by deptno))
select * from scott.emp for update
3.select * from scott.emp where deptno = '30'
4./*2、列出职位为（MANAGER）的员工的编号，姓名 */
5.select empno, ename from scott.emp where job = 'MANAGER'
6./*3、找出奖金高于工资的员工*/
7.select * from scott.emp where comm > sal
8./*4、找出每个员工奖金和工资的总和 */
9.select ename, sal + nvl(comm, 0) from scott.emp
10./*5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK) */
11.select *
12. from scott.emp
13. where deptno = '10'
14. and job = 'MANAGER'
15.union
16.select *
17. from scott.emp
18. where job = 'CLERK'
19. and deptno = '20'
20./*6、找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工 */
21.select *
22. from scott.emp
23. where job != 'MANAGER'
24. and job != 'CLERK'
25. and sal > 2000
26./*7、找出有奖金的员工的不同工作 */
27.select distinct(job) from scott.emp where comm is not null
28./*8、找出没有奖金或者奖金低于500的员工*/
29.select *
30. from scott.emp
31. where comm is not null
32. and comm > 500
33./*9、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 */
34.select ename
35. from scott.emp
36. order by (months_between(sysdate, hiredate) / 12) desc
37.
38. select ename,hiredate from scott.emp order by hiredate
39./*10、找出每个月倒数第三天受雇的员工*/
40.select * from scott.emp where hiredate = last_day(hiredate) - 2
41./*11、分别用case和decode函数列出员工所在的部门，deptno=10显示'部门10',
42. deptno=20显示'部门20'
43. deptno=30显示'部门30'
44. deptno=40显示'部门40'
45. 否则为'其他部门'*/
46. select ename,
47. case deptno
48. when 10 then
49. '部门10'
50. when 20 then
51. '部门20'
52. when 30 then
53. '部门30'
54. when 40 then
55. '部门40'
56. else
57. '其他部门'
58. end 工资情况
59. from scott.emp
60.
61. select ename,
62. decode(deptno,
63. 10,
64. '部门10',
65. 20,
66. '部门20',
67. 30,
68. '部门30',
69. 40,
70. '部门40',
71. '其他部门') 工资情况
72. from scott.emp
73./*12、分组统计各部门下工资>500的员工的平均工资*/
74.select avg(sal) from scott.emp where sal > 500 group by deptno
75./*13、统计各部门下平均工资大于500的部门*/
76.select deptno from scott.emp group by deptno having avg(sal) > 500
77./*14、算出部门30中得到最多奖金的员工奖金 */
78.select max(comm) from scott.emp where deptno = 30
79./*15、算出部门30中得到最多奖金的员工姓名*/
80.select ename
81. from scott.emp
82. where deptno = 30
83. and comm = (select max(comm) from scott.emp where deptno = 30)
84./*16、算出每个职位的员工数和最低工资*/
85.select count(ename), min(sal), job from scott.emp group by job
86./*17、列出员工表中每个部门的员工数，和部门no */
87.select count(ename), deptno from scott.emp group by deptno
88./*18、得到工资大于自己部门平均工资的员工信息*/
89.select *
90. from scott.emp e
91. where sal > (select avg(sal) from scott.emp where e.deptno = deptno)
92.
93. select *
94. from scott.emp e1,
95. (select avg(sal) sals, deptno from scott.emp group by deptno) e2
96. where sal > sals
97. and e1.deptno = e2.deptno
98./*19、分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金) */
99.select avg(nvl(comm,0)), sum(sal + nvl(comm, 0))
100. from scott.emp
101. group by deptno,job
102./*20、笛卡尔集*/
103.select * from scott.emp, scott.dept
104./*21、显示员工ID,名字，直属主管ID，名字*/
105.select empno,
106. ename,
107. mgr,
108. (select ename from scott.emp e1 where e1.empno = e2.mgr) 直属主管名字
109. from scott.emp e2
110./*22、DEPT表按照部门跟EMP表左关联*/
111.select *
112. from scott.dept, scott.emp
113. where scott.dept.deptno = scott.emp.deptno(+)
114./*23、使用此语句重复的内容不再显示了*/
115.select distinct (job) from scott.emp
116./*24、重复的内容依然显示 */
117.select *
118. from scott.emp
119.UNION ALL
120.select * from scott.emp
121./*23和24题和22题是一样的 */
122.
123./*25、只显示了两个表中彼此重复的记录。*/
124.select *
125. from scott.dept, scott.emp
126. where scott.dept.deptno(+) = scott.emp.deptno
127./*26、只显示了两张表中的不同记录*/
128.select * from scott.emp union select * from scott.emp
129.minus
130.(select * from scott.emp intersect select * from scott.emp)
131.
132.(select * from scott.emp minus select * from scott.emp)
133.union
134.(select * from scott.emp minus select * from scott.emp)
135. 表结构相同 先union 只能有 -
136./*27、列出员工表中每个部门的员工数，和部门no */
137.select count(ename), deptno from scott.emp group by deptno
138./*28、列出员工表中每个部门的员工数（员工数必须大于3），和部门名称*/
139.select count(deptno),
140. deptno,
141. (select dname from scott.dept where scott.dept.deptno = e1.deptno)
142. from scott.emp e1
143. group by deptno having count(deptno)>3
144./*29、找出工资比jones多的员工*/
145.select *
146. from scott.emp
147. where sal > (select sal from scott.emp where ename = 'JONES')
148./*30、列出所有员工的姓名和其上级的姓名 */
149.select ename,
150. (select ename from scott.emp e1 where e1.empno = e2.mgr) 上级的姓名
151. from scott.emp e2
152./*31、以职位分组，找出平均工资最高的两种职位 */
153.select job
154. from scott.emp
155. group by job
156.having avg(sal) in (select max(sal) from scott.emp group by job )
157.
158.select job
159. from (select job, avg(sal)
160. from scott.emp
161. group by job
162. order by avg(sal) desc)
163. where rownum <= 2
164.
165. 最大的：
166. select max(max_sal)
167. from (select job, avg(sal) max_sal from scott.emp group by job)
168./*32、查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称*/
169.
170.select ename, dname
171. from scott.emp e1, scott.dept e2
172. where e1.deptno = e2.deptno
173. and e1.deptno <> 20
174. and sal > (select max(sal) from scott.emp where deptno = '20')
175.
176./*33、得到平均工资大于2000的工作职种 */
177.select job from scott.emp group by job having avg(sal) > 2000
178./*34、分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500 */
179.select avg(sal)
180. from scott.emp
181. where sal > 2000
182. group by deptno
183.having avg(sal) > 2500
184./*35、得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 */
185.select deptno, dname, loc
186. from scott.dept
187. where deptno in (select deptno
188. from scott.emp
189. group by deptno
190. having sum(sal) = (select min(sum(sal))
191. from scott.emp
192. group by deptno))
193.
194.select * from scott.dept
195./*36、分部门得到平均工资等级为2级（等级表）的部门编号 */
196.select deptno
197. from scott.emp
198. group by deptno
199.having avg(sal) between (select losal from scott.salgrade where grade = 2) and (select hisal
200. from scott.salgrade
201. where grade = 2)
202.
203.select avg(sal) from scott.emp group by deptno
204.select * from scott.salgrade
205./*37、查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置*/
206.select a.ename, dname, loc
207. from (select *
208. from (select rownum rn, deptno, empno, sal, ename
209. from (select deptno, empno, sal, ename
210. from scott.emp
211. where deptno in (10, 20)
212. and rownum <= 5
213. order by sal desc))
214. where rn between 3 and 5) a,
215. scott.dept b
216. where a.deptno = b.deptno
217.
218.
219.select deptno, ename
220. from (select empno, deptno, ename
221. from (select rownum rn, deptno, empno, sal, ename
222. from (select deptno, empno, sal, ename
223. from scott.emp
224. where deptno in (10, 20)
225. order by sal desc))
226. where rn between 3 and 5)
227.
228.
229./*38、查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入*/
230.select empno, ename, sal + nvl(comm, 0)
231. from scott.emp e1
232. where sal + nvl(comm, 0) >
233. (select sal + nvl(comm, 0) from scott.emp where empno = e1.mgr)
234.
235.select * from scott.emp
236.select ename, sal + nvl(comm, 0) from scott.emp
237./*39、查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资 */
238.select avg(sal)
239. from scott.emp
240. where job in (select job
241. from scott.emp
242. where ename = 'MARTIN'
243. or ename = 'SMITH')
244./*40、查找出不属于任何部门的员工 */
245.select * from scott.emp where deptno is null
246.select * from scott.emp where deptno not in (select deptno from scott.emp)
247./*41、按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）*/
248.select dname, loc
249. from (select *
250. from (select rownum rn, deptno
251. from (select deptno, count(*)
252. from scott.emp
253. group by deptno
254. order by count(*) desc))
255. where rn between 2 and 5) a,
256. scott.dept b
257. where a.deptno = b.deptno
258.
259. select count(*) from scott.emp group by deptno
260./*42、查询出king所在部门的部门号\部门名称\部门人数 （多种方法）*/
261.select sc.deptno, dname, count(*)
262. from scott.emp sc, scott.dept de
263. where sc.deptno = ((select deptno from scott.emp where ename = 'KING'))
264. and de.deptno = sc.deptno
265. group by sc.deptno, dname
266.
267.
268./*43、查询出king所在部门的工作年限最大的员工名字*/
269.select *
270. from scott.emp
271. where hiredate =
272. (select min(hiredate)
273. from scott.emp
274. where deptno in (select deptno from scott.emp where ename = 'KING'))
275. and deptno = (select deptno from scott.emp where ename = 'KING')
276./*44、查询出工资成本最高的部门的部门号和部门名称 */
277.select deptno, dname
278. from scott.dept
279. where deptno = (select deptno
280. from scott.emp
281. group by deptno
282. having sum(sal) = (select max(sum(sal))
283. from scott.emp
284. group by deptno))
285.
286.select * from scott.emp for update
/*1、选择在部门30中员工的所有信息*/
select * from scott.emp where deptno = '30'
/*2、列出职位为（MANAGER）的员工的编号，姓名 */
select empno, ename from scott.emp where job = 'MANAGER'
/*3、找出奖金高于工资的员工*/
select * from scott.emp where comm > sal
/*4、找出每个员工奖金和工资的总和 */
select ename, sal + nvl(comm, 0) from scott.emp
/*5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK) */
select *
from scott.emp
where deptno = '10'
and job = 'MANAGER'
union
select *
from scott.emp
where job = 'CLERK'
and deptno = '20'
/*6、找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工 */
select *
from scott.emp
where job != 'MANAGER'
and job != 'CLERK'
and sal > 2000
/*7、找出有奖金的员工的不同工作 */
select distinct(job) from scott.emp where comm is not null
/*8、找出没有奖金或者奖金低于500的员工*/
select *
from scott.emp
where comm is not null
and comm > 500
/*9、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 */
select ename
from scott.emp
order by (months_between(sysdate, hiredate) / 12) desc
select ename,hiredate from scott.emp order by hiredate
/*10、找出每个月倒数第三天受雇的员工*/
select * from scott.emp where hiredate = last_day(hiredate) - 2
/*11、分别用case和decode函数列出员工所在的部门，deptno=10显示'部门10',
deptno=20显示'部门20'
deptno=30显示'部门30'
deptno=40显示'部门40'
否则为'其他部门'*/
select ename,
case deptno
when 10 then
'部门10'
when 20 then
'部门20'
when 30 then
'部门30'
when 40 then
'部门40'
else
'其他部门'
end 工资情况
from scott.emp
select ename,
decode(deptno,
10,
'部门10',
20,
'部门20',
30,
'部门30',
40,
'部门40',
'其他部门') 工资情况
from scott.emp
/*12、分组统计各部门下工资>500的员工的平均工资*/
select avg(sal) from scott.emp where sal > 500 group by deptno
/*13、统计各部门下平均工资大于500的部门*/
select deptno from scott.emp group by deptno having avg(sal) > 500
/*14、算出部门30中得到最多奖金的员工奖金 */
select max(comm) from scott.emp where deptno = 30
/*15、算出部门30中得到最多奖金的员工姓名*/
select ename
from scott.emp
where deptno = 30
and comm = (select max(comm) from scott.emp where deptno = 30)
/*16、算出每个职位的员工数和最低工资*/
select count(ename), min(sal), job from scott.emp group by job
/*17、列出员工表中每个部门的员工数，和部门no */
select count(ename), deptno from scott.emp group by deptno
/*18、得到工资大于自己部门平均工资的员工信息*/
select *
from scott.emp e
where sal > (select avg(sal) from scott.emp where e.deptno = deptno)
select *
from scott.emp e1,
(select avg(sal) sals, deptno from scott.emp group by deptno) e2
where sal > sals
and e1.deptno = e2.deptno
/*19、分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金) */
select avg(nvl(comm,0)), sum(sal + nvl(comm, 0))
from scott.emp
group by deptno,job
/*20、笛卡尔集*/
select * from scott.emp, scott.dept
/*21、显示员工ID,名字，直属主管ID，名字*/
select empno,
ename,
mgr,
(select ename from scott.emp e1 where e1.empno = e2.mgr) 直属主管名字
from scott.emp e2
/*22、DEPT表按照部门跟EMP表左关联*/
select *
from scott.dept, scott.emp
where scott.dept.deptno = scott.emp.deptno(+)
/*23、使用此语句重复的内容不再显示了*/
select distinct (job) from scott.emp
/*24、重复的内容依然显示 */
select *
from scott.emp
UNION ALL
select * from scott.emp
/*23和24题和22题是一样的 */
/*25、只显示了两个表中彼此重复的记录。*/
select *
from scott.dept, scott.emp
where scott.dept.deptno(+) = scott.emp.deptno
/*26、只显示了两张表中的不同记录*/
select * from scott.emp union select * from scott.emp
minus
(select * from scott.emp intersect select * from scott.emp)
(select * from scott.emp minus select * from scott.emp)
union
(select * from scott.emp minus select * from scott.emp)
表结构相同 先union 只能有 -
/*27、列出员工表中每个部门的员工数，和部门no */
select count(ename), deptno from scott.emp group by deptno
/*28、列出员工表中每个部门的员工数（员工数必须大于3），和部门名称*/
select count(deptno),
deptno,
(select dname from scott.dept where scott.dept.deptno = e1.deptno)
from scott.emp e1
group by deptno having count(deptno)>3
/*29、找出工资比jones多的员工*/
select *
from scott.emp
where sal > (select sal from scott.emp where ename = 'JONES')
/*30、列出所有员工的姓名和其上级的姓名 */
select ename,
(select ename from scott.emp e1 where e1.empno = e2.mgr) 上级的姓名
from scott.emp e2
/*31、以职位分组，找出平均工资最高的两种职位 */
select job
from scott.emp
group by job
having avg(sal) in (select max(sal) from scott.emp group by job )
select job
from (select job, avg(sal)
from scott.emp
group by job
order by avg(sal) desc)
where rownum <= 2
最大的：
select max(max_sal)
from (select job, avg(sal) max_sal from scott.emp group by job)
/*32、查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称*/
select ename, dname
from scott.emp e1, scott.dept e2
where e1.deptno = e2.deptno
and e1.deptno <> 20
and sal > (select max(sal) from scott.emp where deptno = '20')
/*33、得到平均工资大于2000的工作职种 */
select job from scott.emp group by job having avg(sal) > 2000
/*34、分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500 */
select avg(sal)
from scott.emp
where sal > 2000
group by deptno
having avg(sal) > 2500
/*35、得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 */
select deptno, dname, loc
from scott.dept
where deptno in (select deptno
from scott.emp
group by deptno
having sum(sal) = (select min(sum(sal))
from scott.emp
group by deptno))
select * from scott.dept
/*36、分部门得到平均工资等级为2级（等级表）的部门编号 */
select deptno
from scott.emp
group by deptno
having avg(sal) between (select losal from scott.salgrade where grade = 2) and (select hisal
from scott.salgrade
where grade = 2)
select avg(sal) from scott.emp group by deptno
select * from scott.salgrade
/*37、查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置*/
select a.ename, dname, loc
from (select *
from (select rownum rn, deptno, empno, sal, ename
from (select deptno, empno, sal, ename
from scott.emp
where deptno in (10, 20)
and rownum <= 5
order by sal desc))
where rn between 3 and 5) a,
scott.dept b
where a.deptno = b.deptno
select deptno, ename
from (select empno, deptno, ename
from (select rownum rn, deptno, empno, sal, ename
from (select deptno, empno, sal, ename
from scott.emp
where deptno in (10, 20)
order by sal desc))
where rn between 3 and 5)
/*38、查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入*/
select empno, ename, sal + nvl(comm, 0)
from scott.emp e1
where sal + nvl(comm, 0) >
(select sal + nvl(comm, 0) from scott.emp where empno = e1.mgr)
select * from scott.emp
select ename, sal + nvl(comm, 0) from scott.emp
/*39、查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资 */
select avg(sal)
from scott.emp
where job in (select job
from scott.emp
where ename = 'MARTIN'
or ename = 'SMITH')
/*40、查找出不属于任何部门的员工 */
select * from scott.emp where deptno is null
select * from scott.emp where deptno not in (select deptno from scott.emp)
/*41、按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）*/
select dname, loc
from (select *
from (select rownum rn, deptno
from (select deptno, count(*)
from scott.emp
group by deptno
order by count(*) desc))
where rn between 2 and 5) a,
scott.dept b
where a.deptno = b.deptno
select count(*) from scott.emp group by deptno
/*42、查询出king所在部门的部门号\部门名称\部门人数 （多种方法）*/
select sc.deptno, dname, count(*)
from scott.emp sc, scott.dept de
where sc.deptno = ((select deptno from scott.emp where ename = 'KING'))
and de.deptno = sc.deptno
group by sc.deptno, dname
/*43、查询出king所在部门的工作年限最大的员工名字*/
select *
from scott.emp
where hiredate =
(select min(hiredate)
from scott.emp
where deptno in (select deptno from scott.emp where ename = 'KING'))
and deptno = (select deptno from scott.emp where ename = 'KING')
/*44、查询出工资成本最高的部门的部门号和部门名称 */
select deptno, dname
from scott.dept
where deptno = (select deptno
from scott.emp
group by deptno
having sum(sal) = (select max(sum(sal))
from scott.emp
group by deptno))
select * from scott.emp for update
分享到：
发表评论
相关推荐
最全sql查询语句练习题汇总(面试必备) 最全sql查询语句练习题汇总(面试必备) 最全sql查询语句练习题汇总(面试必备) 最全sql查询语句练习题汇总(面试必备) 最全sql查询语句练习题汇总(面试必备) 最全sql查询语句练习...
对于开发人员来说，尤其是需要频繁进行数据查询时，可以快速构建和测试SQL语句，节省了大量的时间。同时，它降低了错误率，因为大多数工具会自动检查语法和逻辑错误，确保生成的SQL语句是有效的。 另外，可视化界面...
SQL语句通常分为SELECT（查询）、INSERT（插入）、UPDATE（更新）和DELETE（删除）四大类。 2. SQL查询语句生成器原理： 生成器通过用户友好的界面，让用户选择所需的操作（如查询、更新等），然后根据用户的输入...
4. 图片和SQL语句：虽然SQL主要用于处理文本数据，但在实际应用中，我们可能需要与图像数据交互。这通常涉及到BLOB（Binary Large Object）类型的字段，用于存储非文本数据。查询这类数据时，可能需要用到`SELECT ...
FROM 子句指定 SELECT 语句查询及与查询相关的表或视图。在 FROM 子句中最多可指定 256 个表或视图，它们之间用逗号分隔。 * 指定多个表或视图：在 FROM 子句同时指定多个表或视图时，如果选择列表中存在同名列，...
为了使SQL语句更加清晰，通常会给表起别名。例如： ```sql SELECT username, b.cityid FROM usertable a, citytable b WHERE a.cityid = b.cityid; ``` 此外，`SELECT`语句不仅可以从表或视图中检索数据，还可以从...
14. **存储过程和函数**：预编译的SQL语句集合，可以接受参数，执行一系列操作并返回结果。 在"查询2.sql"中，你可能会找到这些概念的实际应用示例，每个示例都是一个学习和理解SQL的好机会。通过实践和研究这些...
SQL查询语句的性能优化与索引，写得还不错，可以看看了
SQL查询语句转换成图结构的算法设计与实现 本文旨在设计并实现将SQL查询语句转换成图结构的算法，利用图神经网络对SQL查询语句进行分析和处理。该算法可以将SQL查询语句中的字段、表名、函数、操作符、值和关键字...
- 一组SQL语句的集合，要么全部执行成功，要么全部回滚。常用命令：`BEGIN TRANSACTION`, `COMMIT`, `ROLLBACK`. 9. **存储过程（Stored Procedure）** - 包含一组预编译的SQL语句，可多次调用。创建存储过程：`...
sql查询语句集合sql查询语句集合sql查询语句集合sql查询语句集合sql查询语句集合sql查询语句集合sql查询语句集合sql查询语句集合
"数据库系统原理实验报告-SQL查询语句" 数据库系统原理实验报告-SQL查询语句是关系数据库管理系统的核心组件之一。实验报告的主要目的是让学生熟悉关系数据库标准语言 SQL，並且掌握基本的 SQL 查询语句。 一、...
在ACCESS中，通过“模块”可以输入和执行SQL语句，实现更复杂的数据处理。这对于需要自动化或批量处理数据的场景尤其有用。 5. **示例应用**：例如，假设我们有一个名为"Employees"的表，包含"ID"、"Name"和"Salary...
7. **存储过程**：预编译的SQL语句集合，可以接受参数，执行多次，提高性能，并提供封装和安全性的优点。 8. **触发器**：当满足特定条件时自动执行的SQL代码，常用于实现复杂的业务规则或审计功能。 9. **视图**...
存储过程是一组预编译的SQL语句，可以封装在一起并多次调用，提高代码复用性和安全性。触发器则在特定的数据库事件发生时自动执行，如INSERT、UPDATE或DELETE操作。 8. **事务管理** SQL支持事务，确保数据的一致...
数据库中 SQL 查询语句习题含答案 本资源主要讲述了数据库中 SQL 查询语句的习题和答案，涵盖了数据库的基本概念和 SQL 查询语句的应用。通过这份资源，读者可以学习和掌握数据库中 SQL 查询语句的基础知识和高级...
经典的常用SQL查询语句，比较实用，一些常用的组合语句一一收集
SQL查询语句用法及实例资料 SQL查询语句是数据库管理系统中最基本也是最重要的一部分，掌握SQL查询语句的使用是每个IT从业者必备的技能。本文将对SQL查询语句的用法进行详细的介绍，并提供实际的实例资料，帮助读者...
本文将详细介绍如何通过特定的SQL查询来找出这些消耗资源的SQL语句，并对其进行优化。 #### 监控SQL语句的方法 1. **使用V$SQLAREA视图** V$SQLAREA视图提供了关于SQL语句执行计划的信息，包括磁盘读取次数、...