-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL_PRACTICE3.text
504 lines (424 loc) · 65.5 KB
/
SQL_PRACTICE3.text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
SQL> SELECT ENAME,JOB,SALARY + 1000 AS SAL
2 FROM EMP;
SELECT ENAME,JOB,SALARY + 1000 AS SAL
*
ERROR at line 1:
ORA-00904: "SALARY": invalid identifier
SQL> SELECT ENAME,JOB,SAL + 1000 AS SAL
2 FROM EMP;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 1800
ALLEN SALESMAN 2600
WARD SALESMAN 2250
JONES MANAGER 3975
MARTIN SALESMAN 2250
BLAKE MANAGER 3850
CLARK MANAGER 3450
SCOTT ANALYST 4000
KING PRESIDENT 6000
TURNER SALESMAN 2500
ADAMS CLERK 2100
JAMES CLERK 1950
FORD ANALYST 4000
MILLER CLERK 2300
14 rows selected.
SQL> SELECT SAL*12
2 FROM EMP;
SAL*12
----------
9600
19200
15000
35700
15000
34200
29400
36000
60000
18000
13200
11400
36000
15600
14 rows selected.
SQL> SELECT ENAME,SAL+(SAL*10/100)
2 FROM EMP;
ENAME SAL+(SAL*10/100)
---------- ----------------
SMITH 880
ALLEN 1760
WARD 1375
JONES 3272.5
MARTIN 1375
BLAKE 3135
CLARK 2695
SCOTT 3300
KING 5500
TURNER 1650
ADAMS 1210
JAMES 1045
FORD 3300
MILLER 1430
14 rows selected.
SQL> SELECT ENAME,JOB,SAL-200 AS SALARY
2 FROM EMP;
ENAME JOB SALARY
---------- --------- ----------
SMITH CLERK 600
ALLEN SALESMAN 1400
WARD SALESMAN 1050
JONES MANAGER 2775
MARTIN SALESMAN 1050
BLAKE MANAGER 2650
CLARK MANAGER 2250
SCOTT ANALYST 2800
KING PRESIDENT 4800
TURNER SALESMAN 1300
ADAMS CLERK 900
JAMES CLERK 750
FORD ANALYST 2800
MILLER CLERK 1100
14 rows selected.
SQL> DESC EMP;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> SELECT ENAME||' '||JOB
2 FROM EMP;
ENAME||''||JOB
--------------------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
14 rows selected.
SQL> SELECT ENAME||' WORKING AS A '||JOB
2 FROM EMP;
ENAME||'WORKINGASA'||JOB
---------------------------------
SMITH WORKING AS A CLERK
ALLEN WORKING AS A SALESMAN
WARD WORKING AS A SALESMAN
JONES WORKING AS A MANAGER
MARTIN WORKING AS A SALESMAN
BLAKE WORKING AS A MANAGER
CLARK WORKING AS A MANAGER
SCOTT WORKING AS A ANALYST
KING WORKING AS A PRESIDENT
TURNER WORKING AS A SALESMAN
ADAMS WORKING AS A CLERK
JAMES WORKING AS A CLERK
FORD WORKING AS A ANALYST
MILLER WORKING AS A CLERK
14 rows selected.
SQL> SELECT ENAME||' GETTING A SALARY OF '||SAL
2 FROM EMP;
ENAME||'GETTINGASALARYOF'||SAL
-----------------------------------------------------------------------
SMITH GETTING A SALARY OF 800
ALLEN GETTING A SALARY OF 1600
WARD GETTING A SALARY OF 1250
JONES GETTING A SALARY OF 2975
MARTIN GETTING A SALARY OF 1250
BLAKE GETTING A SALARY OF 2850
CLARK GETTING A SALARY OF 2450
SCOTT GETTING A SALARY OF 3000
KING GETTING A SALARY OF 5000
TURNER GETTING A SALARY OF 1500
ADAMS GETTING A SALARY OF 1100
JAMES GETTING A SALARY OF 950
FORD GETTING A SALARY OF 3000
MILLER GETTING A SALARY OF 1300
14 rows selected.
SQL> SELECT 'HI GOOD MORNING '||ENAME
2 FROM EMP;
'HIGOODMORNING'||ENAME
--------------------------
HI GOOD MORNING SMITH
HI GOOD MORNING ALLEN
HI GOOD MORNING WARD
HI GOOD MORNING JONES
HI GOOD MORNING MARTIN
HI GOOD MORNING BLAKE
HI GOOD MORNING CLARK
HI GOOD MORNING SCOTT
HI GOOD MORNING KING
HI GOOD MORNING TURNER
HI GOOD MORNING ADAMS
HI GOOD MORNING JAMES
HI GOOD MORNING FORD
HI GOOD MORNING MILLER
14 rows selected.
SQL> SELECT ENAME||' WORKING IN A DEPTNO '||DEPTNO||' AND HIS EMPLOYEE ID IS '||EMPNO
2 FROM EMP;
ENAME||'WORKINGINADEPTNO'||DEPTNO||'ANDHISEMPLOYEEIDIS'||EMPNO
---------------------------------------------------------------------------------------------------------------------------------------
SMITH WORKING IN A DEPTNO 20 AND HIS EMPLOYEE ID IS 7369
ALLEN WORKING IN A DEPTNO 30 AND HIS EMPLOYEE ID IS 7499
WARD WORKING IN A DEPTNO 30 AND HIS EMPLOYEE ID IS 7521
JONES WORKING IN A DEPTNO 20 AND HIS EMPLOYEE ID IS 7566
MARTIN WORKING IN A DEPTNO 30 AND HIS EMPLOYEE ID IS 7654
BLAKE WORKING IN A DEPTNO 30 AND HIS EMPLOYEE ID IS 7698
CLARK WORKING IN A DEPTNO 10 AND HIS EMPLOYEE ID IS 7782
SCOTT WORKING IN A DEPTNO 20 AND HIS EMPLOYEE ID IS 7788
KING WORKING IN A DEPTNO 10 AND HIS EMPLOYEE ID IS 7839
TURNER WORKING IN A DEPTNO 30 AND HIS EMPLOYEE ID IS 7844
ADAMS WORKING IN A DEPTNO 20 AND HIS EMPLOYEE ID IS 7876
JAMES WORKING IN A DEPTNO 30 AND HIS EMPLOYEE ID IS 7900
FORD WORKING IN A DEPTNO 20 AND HIS EMPLOYEE ID IS 7902
MILLER WORKING IN A DEPTNO 10 AND HIS EMPLOYEE ID IS 7934
14 rows selected.
SQL> SELECT ENAME||' JOIN THE COMPANY '||HIREDATE||' AND GETTING SALARY OF '||SAL
2 FROM EMP;
ENAME||'JOINTHECOMPANY'||HIREDATE||'ANDGETTINGSALARYOF'||SAL
----------------------------------------------------------------------------------------------------
SMITH JOIN THE COMPANY 17-DEC-80 AND GETTING SALARY OF 800
ALLEN JOIN THE COMPANY 20-FEB-81 AND GETTING SALARY OF 1600
WARD JOIN THE COMPANY 22-FEB-81 AND GETTING SALARY OF 1250
JONES JOIN THE COMPANY 02-APR-81 AND GETTING SALARY OF 2975
MARTIN JOIN THE COMPANY 28-SEP-81 AND GETTING SALARY OF 1250
BLAKE JOIN THE COMPANY 01-MAY-81 AND GETTING SALARY OF 2850
CLARK JOIN THE COMPANY 09-JUN-81 AND GETTING SALARY OF 2450
SCOTT JOIN THE COMPANY 19-APR-87 AND GETTING SALARY OF 3000
KING JOIN THE COMPANY 17-NOV-81 AND GETTING SALARY OF 5000
TURNER JOIN THE COMPANY 08-SEP-81 AND GETTING SALARY OF 1500
ADAMS JOIN THE COMPANY 23-MAY-87 AND GETTING SALARY OF 1100
JAMES JOIN THE COMPANY 03-DEC-81 AND GETTING SALARY OF 950
FORD JOIN THE COMPANY 03-DEC-81 AND GETTING SALARY OF 3000
MILLER JOIN THE COMPANY 23-JAN-82 AND GETTING SALARY OF 1300
14 rows selected.
SQL> SELECT ENAME NAME||' WORKING AS A '||JOB AS PROPER
2 FROM EMP;
SELECT ENAME NAME||' WORKING AS A '||JOB AS PROPER
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> SELECT ENAME NAME,' WORKING AS A '||JOB AS PROPER
2 FROM EMP;
NAME PROPER
---------- -----------------------
SMITH WORKING AS A CLERK
ALLEN WORKING AS A SALESMAN
WARD WORKING AS A SALESMAN
JONES WORKING AS A MANAGER
MARTIN WORKING AS A SALESMAN
BLAKE WORKING AS A MANAGER
CLARK WORKING AS A MANAGER
SCOTT WORKING AS A ANALYST
KING WORKING AS A PRESIDENT
TURNER WORKING AS A SALESMAN
ADAMS WORKING AS A CLERK
JAMES WORKING AS A CLERK
FORD WORKING AS A ANALYST
MILLER WORKING AS A CLERK
14 rows selected.
SQL> SELECT ENAME NAME,' GETTING A SALARY OF '||SAL
2 FROM EMP;
NAME 'GETTINGASALARYOF'||SAL
---------- -------------------------------------------------------------
SMITH GETTING A SALARY OF 800
ALLEN GETTING A SALARY OF 1600
WARD GETTING A SALARY OF 1250
JONES GETTING A SALARY OF 2975
MARTIN GETTING A SALARY OF 1250
BLAKE GETTING A SALARY OF 2850
CLARK GETTING A SALARY OF 2450
SCOTT GETTING A SALARY OF 3000
KING GETTING A SALARY OF 5000
TURNER GETTING A SALARY OF 1500
ADAMS GETTING A SALARY OF 1100
JAMES GETTING A SALARY OF 950
FORD GETTING A SALARY OF 3000
MILLER GETTING A SALARY OF 1300
14 rows selected.
SQL> SELECT * FROM EMP
2 WHERE ENAME='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
SQL> SELECT * FROM EMP
2 WHERE ENAME='Smith';
no rows selected
SQL> SELECT * FROM EMP
2 WHERE DEPTNO=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> SELECT * FROM EMP
2 WHERE JOB='SALESMAN';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SQL> SELECT ENAME,SAL,DEPTNO
2 FROM EMP
3 WHERE JOB != 'CLERK';
ENAME SAL DEPTNO
---------- ---------- ----------
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 30
FORD 3000 20
10 rows selected.
SQL> SELECT * FROM EMP
2 WHERE SAL <= 3000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
SQL> SELECT *
2 FROM EMP
3 WHERE JOB='MANAGER' AND SALARY > 2900;
WHERE JOB='MANAGER' AND SALARY > 2900
*
ERROR at line 3:
ORA-00904: "SALARY": invalid identifier
SQL> SELECT *
2 FROM EMP
3 WHERE JOB='MANAGER' AND SAL>2900;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
SQL> SELECT * FROM EMP
2 WHERE ENAME='SMITH' OR ENAME='ALLEN';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
SQL> SELECT ENAME,MGR,DEPTNO
2 FROM EMP
3 WHERE SAL>=1500 AND SAL <= 2500;
ENAME MGR DEPTNO
---------- ---------- ----------
ALLEN 7698 30
CLARK 7839 10
TURNER 7698 30
SQL> SELECT *
2 FROM EMP
3 WHERE JOB='MANAGER' OR JOB='SALESMAN' OR JOB='CLERK';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
11 rows selected.
SQL> SELECT ENAME,EMPNO,SAL,HIREDATE
2 FROM EMP
3 WHERE HIREDATE >= '01-JAN-81' AND HIREDATE <= '31-DEC-81';
ENAME EMPNO SAL HIREDATE
---------- ---------- ---------- ---------
ALLEN 7499 1600 20-FEB-81
WARD 7521 1250 22-FEB-81
JONES 7566 2975 02-APR-81
MARTIN 7654 1250 28-SEP-81
BLAKE 7698 2850 01-MAY-81
CLARK 7782 2450 09-JUN-81
KING 7839 5000 17-NOV-81
TURNER 7844 1500 08-SEP-81
JAMES 7900 950 03-DEC-81
FORD 7902 3000 03-DEC-81
10 rows selected.
SQL> SELECT *
2 FROM EMP
3 WHERE NOT JOB = 'MANAGER';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
11 rows selected.
SQL> SELECT *
2 FROM EMP
3 WHERE NOT JOB = 'CLERK' AND (JOB='MANAGER' OR JOB='SALESMAN') AND (SAL>=2000 AND SAL<=3000);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
SQL> SELECT *
2 FROM EMP
3 WHERE (DEPTNO=20 OR DEPTNO=30) AND SAL<2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7 rows selected.
SQL> SELECT *
2 FROM EMP
3 WHERE JOB IN('MANAGER','SALESMAN','ANALYST');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
9 rows selected.
SQL> SPOOL OFF;