|
|
Posted on
2015-08-29 16:03
勤奋积累
阅读( 266)
评论()
收藏
举报
1 /********************************SCOTT Database*******************************/
2 select * from EMP;
3 select * from DEPT;
4 select * from SALGRADE;
5
6 select ename, sal*12 from emp;
7
8 /*system empty table*/
9 select * from dual;
10 select 2*3 from dual;
11
12 select sysdate from dual;
13 /*alias*/
14 select ename, sal*12 annual_sal from emp;
15 /*alias with space*/
16 select ename, sal*12 "annual sal" from emp;
17
18 /*0 not equal null value*/
19 select ename, comm from emp;
20
21 /*year salary + comm, any contains empty's math operation result is empty*/
22 select ename, sal*12 + comm from emp;
23
24 select ename||sal from emp;
25
26 /*concat string*/
27 select ename || 'abcdefg' from emp;
28
29 /*2 ' instead of 1 '*/
30 select ename || 'abcdefg''abcd' from emp;
31
32 select deptno from emp;
33
34 /*remove duplicate*/
35 select distinct deptno from emp;
36
37 select distinct deptno, job from emp;
38
39 select * from emp where deptno = 10;
40
41 select * from EMP where ename = 'CLARK';
42
43 select ename, sal from emp where SAL > 1500;
44
45 select ename, sal from emp where deptno <> 10;
46
47 select ename, sal from emp where ename > 'CBA';
48
49 select ename, sal from emp where sal between 800 and 1500;
50
51 select ename, sal from emp where sal >= 800 and sal <= 1500;
52
53 /*empty value*/
54 select ename, sal, comm from emp where comm = null;
55
56 select ename, sal, comm from emp where comm is null;
57
58 select ename, sal, comm from emp where comm is not null;
59
60 select ename, sal, comm from emp where sal in (800, 1500, 2000);
61
62 select ename, sal, comm from emp where ename in ('SMITH', 'KING', 'ABC');
63
64 select ename, sal from emp where DEPTNO = 10 and sal > 1000;
65
66 select ename, sal from emp where DEPTNO = 10 or sal > 1000;
67
68 select ename, sal from emp where DEPTNO = 10 and sal not in (800, 1500);
69
70 /*% means 0 or more*/
71 select ename from emp where ENAME like '%ALL%';
72
73 select ename from emp where ename like '_A%';
74
75 /*contains % in the selected rows need escape sequence*/
76
77 select ename from emp where ENAME like '%\%%';
78
79 /*default escapse sequence is \ but we can defintion by use <escape> '$' */
80 select ename from emp where ename like '%$%%' escape '$';
81
82 select * from DEPT;
83
84 /*order by asc desc defalut is asc*/
85 select * from DEPT order by DEPTNO desc;
86
87 select empno, ename from emp;
88
89 select empno, ename from emp order by EMPNO asc;
90
91 select empno, ename from emp order by EMPNO desc;
92
93 select empno, ename from emp where deptno <> 10 order by empno asc;
94
95 select ename, sal, deptno from emp order by deptno asc;
96
97 select ename, sal, deptno from emp order by deptno asc, ename desc;
98
99 select ename, sal*12 annual_sal from emp where ename not like '_A%' and sal > 800 order by sal desc;
100
101 select lower(ename) from emp
102
103 select ename from emp where lower(ename) like '_a%';
104
105 select ename from emp where ename like '_a%' or ename like '_A%';
106
107 select substr(ename, 1, 3) from emp;
108
109 select substr(ename, 2, 3) from emp;
110
111 select chr(65) from dual;
112
113 select ascii('A') from dual;
114
115 select round(23.652) from dual;
116
117 select round(23.652, 2) from dual;
118
119 select round(23.652, -1) from dual;
120
121 /* 9 means number, is this position has no number, it will not be displayed
122 '$99,999.9999' --> $800.0000
123 '$99,999.9999' --> $1,500.0000
124 'L99,999.9999' --> Y1,500.0000
125 '$00000.0000' --> $00800.0000
126 '$00000.0000' --> $01500.0000
127
128 to_char
129 */
130
131 select to_char(sal, '$99,999.9999') from emp;
132
133 select to_char(sal, '$00000.0000') from emp;
134
135 select hiredate from emp;
136
137 /* Date format
138 YYYY -- year
139 MM -- month
140 DD -- day
141 HH -- 12 hour
142 HH24 -- 24 hour
143 MI -- minute
144 SS -- second
145 */
146 select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') joindate from emp;
147
148 /*to_date*/
149 select ename, hiredate from emp where HIREDATE > to_date('1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');
150
151 /*to_number*/
152 select sal from EMP where sal > to_number('$1,250.00', '$9,999.99');
153
154 select ename, sal*12 + comm from emp;
155
156
157 /*
158 function nvl to deal with null value to math operation
159 if comm is null then o instead of null or it will be comm
160 */
161 select ename, sal*12 + nvl(comm, 0) from emp;
162
163
164 /*
165 group function : min, max, avg, count, sum
166 */
167 select max(sal) maxsal from emp;
168
169 select min(sal) minsal from emp;
170
171 select avg(sal) avgsal from emp;
172
173 select to_char(avg(sal),'999999999.99') avgsal from emp;
174
175 select round(avg(sal),2) avgsal from emp;
176
177 select sum(sal) from emp;
178
179 select count(*) records from emp;
180
181 select count(*) from emp where deptno = 10;
182
183 select count(ename) from emp;
184
185 /*count the field which is not null*/
186 select count(comm) from emp;
187
188 select count(deptno) from emp;
189
190 select count(distinct deptno) from emp;
191
192 /*
193 function : group by
194 */
195
196 /* which department's avg salary is highest*/
197 select sal, deptno from emp;
198
199
200 /*department avg salary*/
201 select deptno, to_char(avg(sal),'99999999.99') depavgsal from emp group by deptno;
202
203 select deptno, job, max(sal) maxsal from emp group by deptno, job;
204
205 /*the highest salary in the company*/
206 select max(sal) from emp;
207
208 /*who is the highest salary in the company*/
209 select ename from emp where sal = (select max(sal) from emp);
210
211 /*the highest salary in the department*/
212 select deptno, max(sal) from emp group by deptno;
213
214 select to_char(avg(sal), '99999999.99') avgsal, deptno from emp group by deptno;
215
216 /* having is limited with grouping*/
217 select to_char(avg(sal), '99999999.99') avgsal, deptno from emp group by deptno having avg(sal) > 2000;
218
219 select deptno, max(sal) from emp where SAL > 1000 group by deptno having avg(sal) > 2000 order by deptno desc;
220
221 select deptno, to_char(avg(sal), '99999999.99') from emp where sal > 1200 group by deptno having avg(sal) > 1500 order by avg(sal) desc;
222
223
224 select ename, sal from emp where SAL = (select max(sal) from emp);
225
226 select ename, sal from emp where sal > (select avg(sal) from emp);
227
228 select ename, sal from emp join (select max(sal) maxsal, deptno from emp group by deptno) t on (emp.sal = t.maxsal and emp.deptno = t.deptno);
229
230 select * from SALGRADE;
231
232 select deptno, to_char(avg(sal), '999999999.99') s from emp group by deptno;
233
234 SELECT dname,grade,to_char(S, '999999.999') FROM dept,(SELECT deptno,grade,s FROM (SELECT deptno,grade,s FROM (SELECT deptno,avg(sal) s FROM emp GROUP BY deptno),salgrade WHERE s BETWEEN losal AND hisal ORDER BY grade DESC) t WHERE ROWNUM=1) t WHERE dept.deptno=t.deptno;
235
236 SELECT dname,grade,to_char(S, '999999.999') FROM dept,(SELECT deptno,grade,s FROM (SELECT deptno,grade,s FROM (SELECT deptno,avg(sal) s FROM emp GROUP BY deptno),salgrade WHERE s BETWEEN losal AND hisal ORDER BY grade DESC) t WHERE ROWNUM=1) t WHERE dept.deptno=t.deptno;
237
238
239 /*self table connection*/
240
241 select empno, ename, mgr from emp;
242
243 select e1.ename, e2.ename from emp e1, emp e2 where e1.MGR = e2.EMPNO;
244
245 select ename, dname from EMP, DEPT where emp.DEPTNO = dept.DEPTNO;
246
247 select ename, dname from EMP join DEPT on (emp.DEPTNO = dept.DEPTNO);
248
249 select ename, dname from EMP join DEPT using (deptno);
250
251 select ename, grade from emp e join salgrade s on (e.SAL between s.LOSAL and s.HISAL);
252
253 select ename, dname, grade from emp e join dept d on (e.DEPTNO = d.DEPTNO) join salgrade s on (e.SAL between s.LOSAL and s.HISAL) where ename not like '_A%';
254
255 /*left join == left outer join*/
256 select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
257
258 select e1.ename, e2.ename from emp e1 left outer join emp e2 on (e1.mgr = e2.empno);
259
260 select * from dept;
261
262 select ename, dname from emp e right join DEPT d on (e.DEPTNO = d.DEPTNO);
263
264 select ename, dname from emp e right outer join DEPT d on (e.DEPTNO = d.DEPTNO);
265
266 /* full join */
267 select ename, dname from emp e full join DEPT d on (e.DEPTNO = d.DEPTNO);
268
269 /*department avg salary*/
270 select deptno, to_char(avg(sal), '9999999.99') from emp group by deptno;
271
272 /*department avg salary's grade*/
273 select deptno, avg_sal, grade
274 from (select deptno, to_char(avg(sal), '999999.99') avg_sal from emp group by deptno) t
275 join SALGRADE s
276 on (t.avg_sal between s.LOSAL and s.HISAL);
277
278 /*salary grade for every employee*/
279 select ename, deptno, grade from emp e join SALGRADE s on (e.SAL between s.LOSAL and s.HISAL);
280
281 select deptno, to_char(avg(grade), '9.9') avg_grade
282 from (select ename, deptno, grade from emp e join SALGRADE s on (e.SAL between s.LOSAL and s.HISAL)) t
283 group by t.deptno;
284
285 /* who is manager*/
286 select ename from emp where EMPNO in (select mgr from emp);
287
288 select ename from emp where EMPNO in (select distinct mgr from emp);
289
290 select * from emp;
291
292 /*select highest salary*/
293 select max(sal) from emp;
294
295 select distinct e1.sal from emp e1 join emp e2 on (e1.SAL < e2.SAL);
296
297 /* please don't use group function to find the max salary*/
298 select distinct sal from emp where SAL not in (select distinct e1.sal from emp e1 join emp e2 on (e1.SAL < e2.SAL));
299
300 select deptno, avg_sal from (select to_char(avg(sal), '99999.99') avg_sal, deptno from emp group by deptno)
301 where avg_sal = (select max(avg_sal) from (select to_char(avg(sal),'999999.99') avg_sal, deptno from emp group by deptno));
302
303
304 select max(avg_sal) from (select to_char(avg(sal),'999999.99') avg_sal, deptno from emp group by deptno);
305 select to_char(avg(sal), '99999.99') avg_sal, deptno from emp group by deptno;
306
307
308 /*avg salary is highest*/
309 select deptno, avg_sal from (select to_char(avg(sal), '99999.99') avg_sal, deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select to_char(avg(sal),'99999.99') avg_sal, deptno from emp group by deptno));
310
311 /*the depatname of the highest avg salary*/
312 select dname from dept where deptno =
313 (select deptno from (select to_char(avg(sal), '99999.99') avg_sal, deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select to_char(avg(sal),'99999.99') avg_sal, deptno from emp group by deptno)));
314
315
316 select avg(sal) sal_avg from emp group by deptno;
317
318 select deptno, avg(sal) sal_avg from emp group by deptno;
319
320 select deptno, to_char(sal_avg, '999999.99') sal_avg, grade
321 from (select deptno, avg(sal) sal_avg from emp group by deptno) t
322 join salgrade s
323 on (t.sal_avg between s.LOSAL and s.HISAL);
324
325
326 select min(grade) from (
327 select deptno, to_char(sal_avg, '999999.99') sal_avg, grade
328 from (select deptno, avg(sal) sal_avg from emp group by deptno) t
329 join salgrade s
330 on (t.sal_avg between s.LOSAL and s.HISAL)
331 );
332
333
334 select * from dept;
335
336 /*selected the lowest avg salary's deptment's deptname*/
337 select t1.deptno, dname, sal_avg, grade from
338 (
339 select deptno, to_char(sal_avg, '999999.99') sal_avg, grade
340 from (select deptno, avg(sal) sal_avg from emp group by deptno) t
341 join salgrade s
342 on (t.sal_avg between s.LOSAL and s.HISAL)
343 ) t1
344 join
345 dept
346 on
347 t1.deptno = dept.DEPTNO
348 where
349 t1.grade =
350 (
351 select min(grade) from (
352 select deptno, to_char(sal_avg, '999999.99') sal_avg, grade
353 from (select deptno, avg(sal) sal_avg from emp group by deptno) t
354 join salgrade s
355 on (t.sal_avg between s.LOSAL and s.HISAL)
356 )
357 );
358
359 /*create view*/
360 create or replace view v$_dept_avg_sal_info as
361 select deptno, to_char(sal_avg, '999999.99') sal_avg, grade
362 from (select deptno, avg(sal) sal_avg from emp group by deptno) t
363 join salgrade s
364 on (t.sal_avg between s.LOSAL and s.HISAL)
365
366 select * from V$_DEPT_AVG_SAL_INFO;
367
368
369 /*selected the lowest avg salary's deptment's deptname by view*/
370
371 select t1.deptno, dname, sal_avg, grade from
372 V$_DEPT_AVG_SAL_INFO t1
373 join
374 dept
375 on
376 t1.deptno = dept.DEPTNO
377 where
378 t1.grade =
379 (
380 select min(grade) from V$_DEPT_AVG_SAL_INFO
381 );
382
383 select max(sal)
384 from
385 emp
386 where
387 EMPNO not in (select distinct mgr from emp where MGR is not null);
388
389
390 select ename
391 from
392 emp
393 where
394 empno in (select distinct mgr from emp where MGR is not null)
395 and
396 SAL > (
397 select max(sal)
398 from
399 emp
400 where
401 EMPNO not in (select distinct mgr from emp where MGR is not null)
402 )
403
404
405 select * from user_views;
406
407 select empno, ename, rownum from emp order by empno desc;
408
409 select empno, ename from emp where rownum <= 5;
410
411 select rownum r, ename from emp;
412
413 select ename from (select rownum r, ename from emp) where r > 10;
414
415 select ename, sal from emp order by sal desc;
416
417 select ename, sal from (select ename, sal from EMP order by sal desc) where rownum <= 5;
418
419 select ename,sal
420 from (select ename, sal, rownum r from (select ename, sal from emp order by SAL desc))
421 where r >= 6 and r <= 10;
422
423
424
425 select * from PRESENT;
1 /********************************SCOTT Database*******************************/
2 select * from EMP;
3 select * from DEPT;
4 select * from SALGRADE;
5
6 select ename, sal*12 from emp;
7
8 /*system empty table*/
9 select * from dual;
10 select 2*3 from dual;
11
12 select sysdate from dual;
13 /*alias*/
14 select ename, sal*12 annual_sal from emp;
15 /*alias with space*/
16 select ename, sal*12 "annual sal" from emp;
17
18 /*0 not equal null value*/
19 select ename, comm from emp;
20
21 /*year salary + comm, any contains empty's math operation result is empty*/
22 select ename, sal*12 + comm from emp;
23
24 select ename||sal from emp;
25
26 /*concat string*/
27 select ename || 'abcdefg' from emp;
28
29 /*2 ' instead of 1 '*/
30 select ename || 'abcdefg''abcd' from emp;
31
32 select deptno from emp;
33
34 /*remove duplicate*/
35 select distinct deptno from emp;
36
37 select distinct deptno, job from emp;
38
39 select * from emp where deptno = 10;
40
41 select * from EMP where ename = 'CLARK';
42
43 select ename, sal from emp where SAL > 1500;
44
45 select ename, sal from emp where deptno <> 10;
46
47 select ename, sal from emp where ename > 'CBA';
48
49 select ename, sal from emp where sal between 800 and 1500;
50
51 select ename, sal from emp where sal >= 800 and sal <= 1500;
52
53 /*empty value*/
54 select ename, sal, comm from emp where comm = null;
55
56 select ename, sal, comm from emp where comm is null;
57
58 select ename, sal, comm from emp where comm is not null;
59
60 select ename, sal, comm from emp where sal in (800, 1500, 2000);
61
62 select ename, sal, comm from emp where ename in ('SMITH', 'KING', 'ABC');
63
64 select ename, sal from emp where DEPTNO = 10 and sal > 1000;
65
66 select ename, sal from emp where DEPTNO = 10 or sal > 1000;
67
68 select ename, sal from emp where DEPTNO = 10 and sal not in (800, 1500);
69
70 /*% means 0 or more*/
71 select ename from emp where ENAME like '%ALL%';
72
73 select ename from emp where ename like '_A%';
74
75 /*contains % in the selected rows need escape sequence*/
76
77 select ename from emp where ENAME like '%\%%';
78
79 /*default escapse sequence is \ but we can defintion by use <escape> '$' */
80 select ename from emp where ename like '%$%%' escape '$';
81
82 select * from DEPT;
83
84 /*order by asc desc defalut is asc*/
85 select * from DEPT order by DEPTNO desc;
86
87 select empno, ename from emp;
88
89 select empno, ename from emp order by EMPNO asc;
90
91 select empno, ename from emp order by EMPNO desc;
92
93 select empno, ename from emp where deptno <> 10 order by empno asc;
94
95 select ename, sal, deptno from emp order by deptno asc;
96
97 select ename, sal, deptno from emp order by deptno asc, ename desc;
98
99 select ename, sal*12 annual_sal from emp where ename not like '_A%' and sal > 800 order by sal desc;
100
101 select lower(ename) from emp
102
103 select ename from emp where lower(ename) like '_a%';
104
105 select ename from emp where ename like '_a%' or ename like '_A%';
106
107 select substr(ename, 1, 3) from emp;
108
109 select substr(ename, 2, 3) from emp;
110
111 select chr(65) from dual;
112
113 select ascii('A') from dual;
114
115 select round(23.652) from dual;
116
117 select round(23.652, 2) from dual;
118
119 select round(23.652, -1) from dual;
120
121 /* 9 means number, is this position has no number, it will not be displayed
122 '$99,999.9999' --> $800.0000
123 '$99,999.9999' --> $1,500.0000
124 'L99,999.9999' --> Y1,500.0000
125 '$00000.0000' --> $00800.0000
126 '$00000.0000' --> $01500.0000
127
128 to_char
129 */
130
131 select to_char(sal, '$99,999.9999') from emp;
132
133 select to_char(sal, '$00000.0000') from emp;
134
135 select hiredate from emp;
136
137 /* Date format
138 YYYY -- year
139 MM -- month
140 DD -- day
141 HH -- 12 hour
142 HH24 -- 24 hour
143 MI -- minute
144 SS -- second
145 */
146 select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') joindate from emp;
147
148 /*to_date*/
149 select ename, hiredate from emp where HIREDATE > to_date('1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');
150
151 /*to_number*/
152 select sal from EMP where sal > to_number('$1,250.00', '$9,999.99');
153
154 select ename, sal*12 + comm from emp;
155
156
157 /*
158 function nvl to deal with null value to math operation
159 if comm is null then o instead of null or it will be comm
160 */
161 select ename, sal*12 + nvl(comm, 0) from emp;
162
163
164 /*
165 group function : min, max, avg, count, sum
166 */
167 select max(sal) maxsal from emp;
168
169 select min(sal) minsal from emp;
170
171 select avg(sal) avgsal from emp;
172
173 select to_char(avg(sal),'999999999.99') avgsal from emp;
174
175 select round(avg(sal),2) avgsal from emp;
176
177 select sum(sal) from emp;
178
179 select count(*) records from emp;
180
181 select count(*) from emp where deptno = 10;
182
183 select count(ename) from emp;
184
185 /*count the field which is not null*/
186 select count(comm) from emp;
187
188 select count(deptno) from emp;
189
190 select count(distinct deptno) from emp;
191
192 /*
193 function : group by
194 */
195
196 /* which department's avg salary is highest*/
197 select sal, deptno from emp;
198
199
200 /*department avg salary*/
201 select deptno, to_char(avg(sal),'99999999.99') depavgsal from emp group by deptno;
202
203 select deptno, job, max(sal) maxsal from emp group by deptno, job;
204
205 /*the highest salary in the company*/
206 select max(sal) from emp;
207
208 /*who is the highest salary in the company*/
209 select ename from emp where sal = (select max(sal) from emp);
210
211 /*the highest salary in the department*/
212 select deptno, max(sal) from emp group by deptno;
213
214 select to_char(avg(sal), '99999999.99') avgsal, deptno from emp group by deptno;
215
216 /* having is limited with grouping*/
217 select to_char(avg(sal), '99999999.99') avgsal, deptno from emp group by deptno having avg(sal) > 2000;
218
219 select deptno, max(sal) from emp where SAL > 1000 group by deptno having avg(sal) > 2000 order by deptno desc;
220
221 select deptno, to_char(avg(sal), '99999999.99') from emp where sal > 1200 group by deptno having avg(sal) > 1500 order by avg(sal) desc;
222
223
224 select ename, sal from emp where SAL = (select max(sal) from emp);
225
226 select ename, sal from emp where sal > (select avg(sal) from emp);
227
228 select ename, sal from emp join (select max(sal) maxsal, deptno from emp group by deptno) t on (emp.sal = t.maxsal and emp.deptno = t.deptno);
229
230 select * from SALGRADE;
231
232 select deptno, to_char(avg(sal), '999999999.99') s from emp group by deptno;
233
234 SELECT dname,grade,to_char(S, '999999.999') FROM dept,(SELECT deptno,grade,s FROM (SELECT deptno,grade,s FROM (SELECT deptno,avg(sal) s FROM emp GROUP BY deptno),salgrade WHERE s BETWEEN losal AND hisal ORDER BY grade DESC) t WHERE ROWNUM=1) t WHERE dept.deptno=t.deptno;
235
236 SELECT dname,grade,to_char(S, '999999.999') FROM dept,(SELECT deptno,grade,s FROM (SELECT deptno,grade,s FROM (SELECT deptno,avg(sal) s FROM emp GROUP BY deptno),salgrade WHERE s BETWEEN losal AND hisal ORDER BY grade DESC) t WHERE ROWNUM=1) t WHERE dept.deptno=t.deptno;
237
238
239 /*self table connection*/
240
241 select empno, ename, mgr from emp;
242
243 select e1.ename, e2.ename from emp e1, emp e2 where e1.MGR = e2.EMPNO;
244
245 select ename, dname from EMP, DEPT where emp.DEPTNO = dept.DEPTNO;
246
247 select ename, dname from EMP join DEPT on (emp.DEPTNO = dept.DEPTNO);
248
249 select ename, dname from EMP join DEPT using (deptno);
250
251 select ename, grade from emp e join salgrade s on (e.SAL between s.LOSAL and s.HISAL);
252
253 select ename, dname, grade from emp e join dept d on (e.DEPTNO = d.DEPTNO) join salgrade s on (e.SAL between s.LOSAL and s.HISAL) where ename not like '_A%';
254
255 /*left join == left outer join*/
256 select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
257
258 select e1.ename, e2.ename from emp e1 left outer join emp e2 on (e1.mgr = e2.empno);
259
260 select * from dept;
261
262 select ename, dname from emp e right join DEPT d on (e.DEPTNO = d.DEPTNO);
263
264 select ename, dname from emp e right outer join DEPT d on (e.DEPTNO = d.DEPTNO);
265
266 /* full join */
267 select ename, dname from emp e full join DEPT d on (e.DEPTNO = d.DEPTNO);
268
269 /*department avg salary*/
270 select deptno, to_char(avg(sal), '9999999.99') from emp group by deptno;
271
272 /*department avg salary's grade*/
273 select deptno, avg_sal, grade
274 from (select deptno, to_char(avg(sal), '999999.99') avg_sal from emp group by deptno) t
275 join SALGRADE s
276 on (t.avg_sal between s.LOSAL and s.HISAL);
277
278 /*salary grade for every employee*/
279 select ename, deptno, grade from emp e join SALGRADE s on (e.SAL between s.LOSAL and s.HISAL);
280
281 select deptno, to_char(avg(grade), '9.9') avg_grade
282 from (select ename, deptno, grade from emp e join SALGRADE s on (e.SAL between s.LOSAL and s.HISAL)) t
283 group by t.deptno;
284
285 /* who is manager*/
286 select ename from emp where EMPNO in (select mgr from emp);
287
288 select ename from emp where EMPNO in (select distinct mgr from emp);
289
290 select * from emp;
291
292 /*select highest salary*/
293 select max(sal) from emp;
294
295 select distinct e1.sal from emp e1 join emp e2 on (e1.SAL < e2.SAL);
296
297 /* please don't use group function to find the max salary*/
298 select distinct sal from emp where SAL not in (select distinct e1.sal from emp e1 join emp e2 on (e1.SAL < e2.SAL));
299
300 select deptno, avg_sal from (select to_char(avg(sal), '99999.99') avg_sal, deptno from emp group by deptno)
301 where avg_sal = (select max(avg_sal) from (select to_char(avg(sal),'999999.99') avg_sal, deptno from emp group by deptno));
302
303
304 select max(avg_sal) from (select to_char(avg(sal),'999999.99') avg_sal, deptno from emp group by deptno);
305 select to_char(avg(sal), '99999.99') avg_sal, deptno from emp group by deptno;
306
307
308 /*avg salary is highest*/
309 select deptno, avg_sal from (select to_char(avg(sal), '99999.99') avg_sal, deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select to_char(avg(sal),'99999.99') avg_sal, deptno from emp group by deptno));
310
311 /*the depatname of the highest avg salary*/
312 select dname from dept where deptno =
313 (select deptno from (select to_char(avg(sal), '99999.99') avg_sal, deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select to_char(avg(sal),'99999.99') avg_sal, deptno from emp group by deptno)));
314
315
316 select avg(sal) sal_avg from emp group by deptno;
317
318 select deptno, avg(sal) sal_avg from emp group by deptno;
319
320 select deptno, to_char(sal_avg, '999999.99') sal_avg, grade
321 from (select deptno, avg(sal) sal_avg from emp group by deptno) t
322 join salgrade s
323 on (t.sal_avg between s.LOSAL and s.HISAL);
324
325
326 select min(grade) from (
327 select deptno, to_char(sal_avg, '999999.99') sal_avg, grade
328 from (select deptno, avg(sal) sal_avg from emp group by deptno) t
329 join salgrade s
330 on (t.sal_avg between s.LOSAL and s.HISAL)
331 );
332
333
334 select * from dept;
335
336 /*selected the lowest avg salary's deptment's deptname*/
337 select t1.deptno, dname, sal_avg, grade from
338 (
339 select deptno, to_char(sal_avg, '999999.99') sal_avg, grade
340 from (select deptno, avg(sal) sal_avg from emp group by deptno) t
341 join salgrade s
342 on (t.sal_avg between s.LOSAL and s.HISAL)
343 ) t1
344 join
345 dept
346 on
347 t1.deptno = dept.DEPTNO
348 where
349 t1.grade =
350 (
351 select min(grade) from (
352 select deptno, to_char(sal_avg, '999999.99') sal_avg, grade
353 from (select deptno, avg(sal) sal_avg from emp group by deptno) t
354 join salgrade s
355 on (t.sal_avg between s.LOSAL and s.HISAL)
356 )
357 );
358
359 /*create view*/
360 create or replace view v$_dept_avg_sal_info as
361 select deptno, to_char(sal_avg, '999999.99') sal_avg, grade
362 from (select deptno, avg(sal) sal_avg from emp group by deptno) t
363 join salgrade s
364 on (t.sal_avg between s.LOSAL and s.HISAL)
365
366 select * from V$_DEPT_AVG_SAL_INFO;
367
368
369 /*selected the lowest avg salary's deptment's deptname by view*/
370
371 select t1.deptno, dname, sal_avg, grade from
372 V$_DEPT_AVG_SAL_INFO t1
373 join
374 dept
375 on
376 t1.deptno = dept.DEPTNO
377 where
378 t1.grade =
379 (
380 select min(grade) from V$_DEPT_AVG_SAL_INFO
381 );
382
383 select max(sal)
384 from
385 emp
386 where
387 EMPNO not in (select distinct mgr from emp where MGR is not null);
388
389
390 select ename
391 from
392 emp
393 where
394 empno in (select distinct mgr from emp where MGR is not null)
395 and
396 SAL > (
397 select max(sal)
398 from
399 emp
400 where
401 EMPNO not in (select distinct mgr from emp where MGR is not null)
402 )
403
404
405 select * from user_views;
406
407 select empno, ename, rownum from emp order by empno desc;
408
409 select empno, ename from emp where rownum <= 5;
410
411 select rownum r, ename from emp;
412
413 select ename from (select rownum r, ename from emp) where r > 10;
414
415 select ename, sal from emp order by sal desc;
416
417 select ename, sal from (select ename, sal from EMP order by sal desc) where rownum <= 5;
418
419 select ename,sal
420 from (select ename, sal, rownum r from (select ename, sal from emp order by SAL desc))
421 where r >= 6 and r <= 10;
422
423
424
425 select * from PRESENT;
|
浙公网安备 33010602011771号