/
SQL Commands3.txt
526 lines (355 loc) · 14.8 KB
/
SQL Commands3.txt
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
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
DDL statements
connect system/Newuser123
create table person(id number(3) primary key, name varchar2(10), city varchar2(10)); -- creating table
desc person; -- showing table
rename person to person1; -- renaming table
alter table person1 add salary number(10,100); -- adding column
alter table person1 modify salary number(6,3); -- to change column width
alter table person1 rename column salary to sal; --to change column name
alter table person1 drop primary key; -- to drop primary key
select * from tab; -- show all tables
alter table person1 add primary key(id);--add primary key
------Answer to question given-------
create table employee(id number(3), name varchar2(10) not null, city varchar2(10), mgrid number(10), des varchar2(30), dob date, sal number(10,3) default 20000, dept varchar2(10), gender varchar2(3) check (gender in('m', 'f')), constraint e_pk primary key(id));
-----Insert Values to a table-----
insert into employee values(&id, '&name', '&city', &mgrid, '&des', '&dob', &sal, '&dept', '&gender');
----entering values------------
Enter value for id: 1
Enter value for name: Karan
Enter value for city: goa
Enter value for mgrid: 2
Enter value for des: engg
Enter value for dob: 10-may-1994
Enter value for sal: 60000
Enter value for dept: it
Enter value for gender: m
old 1: insert into employee values(&id, '&name', '&city', &mgrid, '&des', '&dob', &sal, '&dept', '&gender')
new 1: insert into employee values(1, 'Karan', 'goa', 2, 'engg', '10-may-1994', 60000, 'it', 'm')
SQL> insert into employee values(&id, '&name', '&city', &mgrid, '&des', '&dob', &sal, '&dept', '&gender');
Enter value for id: 2
Enter value for name: Suman
Enter value for city: pune
Enter value for mgrid: 1
Enter value for des: engg
Enter value for dob: 20-nov-1996
Enter value for sal: 200000
Enter value for dept: hr
Enter value for gender: f
old 1: insert into employee values(&id, '&name', '&city', &mgrid, '&des', '&dob', &sal, '&dept', '&gender')
new 1: insert into employee values(2, 'Suman', 'pune', 1, 'engg', '20-nov-1996', 200000, 'hr', 'f')
1 row created.
SQL> /
Enter value for id: 3
Enter value for name: kunal
Enter value for city: hyderabad
Enter value for mgrid: 3
Enter value for des: doc
Enter value for dob: 20-nov-1996
Enter value for sal: 1000000
Enter value for dept: it
Enter value for gender: m
old 1: insert into employee values(&id, '&name', '&city', &mgrid, '&des', '&dob', &sal, '&dept', '&gender')
new 1: insert into employee values(3, 'kunal', 'hyderabad', 3, 'doc', '20-nov-1996', 1000000, 'it', 'm')
1 row created.
SQL> /
Enter value for id: 4
Enter value for name: Roshni
Enter value for city: Mumbai
Enter value for mgrid: 4
Enter value for des: lawyer
Enter value for dob: 10-may-1994
Enter value for sal: 320000
Enter value for dept: hr
Enter value for gender: f
old 1: insert into employee values(&id, '&name', '&city', &mgrid, '&des', '&dob', &sal, '&dept', '&gender')
new 1: insert into employee values(4, 'Roshni', 'Mumbai', 4, 'lawyer', '10-may-1994', 320000, 'hr', 'f')
1 row created.
SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(3)
NAME NOT NULL VARCHAR2(10)
CITY VARCHAR2(10)
MGRID NUMBER(10)
DES VARCHAR2(30)
DOB DATE
SAL NUMBER(10,3)
DEPT VARCHAR2(10)
GENDER VARCHAR2(3)
SQL> set linesize 32000
SQL> set pagesize 40000
SQL> select * from employee;
ID NAME CITY MGRID DES DOB SAL DEPT GEN
---------- ---------- ---------- ---------- ------------------------------ --------- ---------- ---------- ---
1 Karan goa 2 engg 10-MAY-94 60000 it m
2 Suman pune 1 engg 20-NOV-96 200000 hr f
3 kunal hyderabad 3 doc 20-NOV-96 1000000 it m
4 Roshni Mumbai 4 lawyer 10-MAY-94 320000 hr f
select * from employee;
ID NAME CITY MGRID DES DOB SAL DEPT GEN
---------- ---------- ---------- ---------- ------------------------------ --------- ---------- ---------- ---
1 Karan goa 2 engg 10-MAY-94 60000 it m
2 Suman pune 1 engg 20-NOV-96 200000 hr f
3 kunal hyderabad 3 doc 20-NOV-96 1000000 it m
4 Roshni Mumbai 4 lawyer 10-MAY-94 320000 hr f
SQL> insert into employee values(&id, '&name', '&city', &mgrid, '&des', '&dob', &sal, '&dept', '&gender');
Enter value for id: 5
Enter value for name: kunali
Enter value for city: chandigarh
Enter value for mgrid: 5
Enter value for des: pilot
Enter value for dob: 21-may-2007
Enter value for sal: 1000000
Enter value for dept: it
Enter value for gender: f
old 1: insert into employee values(&id, '&name', '&city', &mgrid, '&des', '&dob', &sal, '&dept', '&gender')
new 1: insert into employee values(5, 'kunali', 'chandigarh', 5, 'pilot', '21-may-2007', 1000000, 'it', 'f')
SQL> select * from employee;
ID NAME CITY MGRID DES DOB SAL DEPT GEN
---------- ---------- ---------- ---------- ------------------------------ --------- ---------- ---------- ---
1 Karan goa 2 engg 10-MAY-94 60000 it m
2 Suman pune 1 engg 20-NOV-96 200000 hr f
3 kunal hyderabad 3 doc 20-NOV-96 1000000 it m
4 Roshni Mumbai 4 lawyer 10-MAY-94 320000 hr f
5 kunali chandigarh 5 pilot 21-MAY-07 1000000 it f
update employee set sal = null where id = 2;
1 row updated.
SQL> select * from employee;
ID NAME CITY MGRID DES DOB SAL DEPT GEN
---------- ---------- ---------- ---------- ------------------------------ --------- ---------- ---------- ---
1 Karan goa 2 engg 10-MAY-94 60000 it m
2 Suman pune 1 engg 20-NOV-96 hr f
3 kunal hyderabad 3 doc 20-NOV-96 1000000 it m
4 Roshni Mumbai 4 lawyer 10-MAY-94 320000 hr f
5 kunali chandigarh 5 pilot 21-MAY-07 1000000 it f
SQL> commit;
Commit complete.
SQL> select * from employee;
ID NAME CITY MGRID DES DOB SAL DEPT GEN
---------- ---------- ---------- ---------- ------------------------------ --------- ---------- ---------- ---
1 Karan goa 2 engg 10-MAY-94 60000 it m
2 Suman pune 1 engg 20-NOV-96 hr f
3 kunal hyderabad 3 doc 20-NOV-96 1000000 it m
4 Roshni Mumbai 4 lawyer 10-MAY-94 320000 hr f
5 kunali chandigarh 5 pilot 21-MAY-07 1000000 it f
select name,sal from employee where sal between 10000 and 65000;
NAME SAL
---------- ----------
Karan 60000
SQL> select name from employee where city in ('goa', 'pune');
NAME
----------
Karan
Suman
SQL> select name from employee where city like 'pune' ;
NAME
----------
Suman
SQL> select name from employee where name like 'k%';
NAME
----------
kunal
kunali
SQL> select name from employee where name like 'k_';
no rows selected
SQL> select name from employee where name like 'k____';
NAME
----------
kunal
SQL> select name from employee where name like '__r%';
NAME
----------
Karan
select name from employee where sal is null;
NAME
----------
Suman
select name from employee where des='engg' and sal>30000;
NAME
----------
Karan
SQL> select name from employee where not des='engg';
NAME
----------
kunal
Roshni
kunali
SQL> select name from employee where des not in 'engg';
NAME
----------
kunal
Roshni
kunali
SQL>
SQL> select upper('pune') from dual;
UPPE
----
PUNE
SQL> select instr('pune', 'p') from dual;
INSTR('PUNE','P')
-----------------
1
SQL> select instr('pune', 'r') from dual;
INSTR('PUNE','R')
-----------------
0
SQL> select rtrim('good morning', 'morning') from dual;
RTRIM
-----
good
SQL> select lpad('lti', 6, '***') from dual;
LPAD('
------
***lti
select substr('helloworld', 3,6) from dual;
SUBSTR
------
llowor
> select round(23.889, 2) from dual;
ROUND(23.889,2)
---------------
23.89
SQL> select round(23.88876879, 2) from dual;
ROUND(23.88876879,2)
--------------------
23.89
SQL> select floor(23.88876879) from dual;
FLOOR(23.88876879)
------------------
23
SQL> select ceil(23.88876879) from dual;
CEIL(23.88876879)
-----------------
24
SQL> select add_months('20-mar-2006',5) from dual;
ADD_MONTH
---------
20-AUG-06
SQL> select months_between('20-mar-2006','15-march-2009') from dual;
MONTHS_BETWEEN('20-MAR-2006','15-MARCH-2009')
---------------------------------------------
-35.83871
SQL> select months_between('20-mar-2016','15-march-2009') from dual;
MONTHS_BETWEEN('20-MAR-2016','15-MARCH-2009')
---------------------------------------------
84.1612903
SQL> select next_day('10-june-2019','sunday') from dual;
NEXT_DAY(
---------
16-JUN-19
select last_day('10-june-2019') from dual;
LAST_DAY(
---------
30-JUN-19
SELECT TO_CHAR
2 (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"
3 FROM DUAL;
NOW
-------------------
06-10-2019 16:39:50
SELECT TO_CHAR
2 (SYSDATE, 'yy') "NOW"
3 FROM DUAL;
NO
--
19
SQL>
SQL> select name from employee where dob like '%96';
NAME
----------
Suman
kunal
SQL> select * from employee;
ID NAME CITY MGRID DES DOB SAL DEPT GEN
---------- ---------- ---------- ---------- ------------------------------ --------- ---------- ---------- ---
1 Karan goa 2 engg 10-MAY-94 60000 it m
2 Suman pune 1 engg 20-NOV-96 hr f
3 kunal hyderabad 3 doc 20-NOV-96 1000000 it m
4 Roshni Mumbai 4 lawyer 10-MAY-94 320000 hr f
5 kunali chandigarh 5 pilot 21-MAY-07 1000000 it f
SQL> select name from employee where to_char(dob, 'yyyy')=1996;
NAME
----------
Suman
kunal
SQL> select nvl(null,20) from dual;
NVL(NULL,20)
------------
20
SQL> select nvl(21,20) from dual;
NVL(21,20)
----------
21
SQL> select nvl2(21,20,24) from dual;
NVL2(21,20,24)
--------------
20
SQL> select nvl2(null,20,24) from dual;
NVL2(NULL,20,24)
----------------
24
SQL> select coalesce(null,20,24) from dual;
COALESCE(NULL,20,24)
--------------------
20
SQL> select coalesce(null,null) from dual;
C
-
select greatest(12,13,1,-4) from dual;
GREATEST(12,13,1,-4)
--------------------
13
SQL> select count(*) from employee where des='engg';
COUNT(*)
----------
2
SQL> select count(*) engineers from employee where des='engg'; //here engineers is alias
ENGINEERS
----------
2
SQL> select sal from employee order by sal;
SAL
----------
60000
320000
1000000
1000000
SQL> select * from employee order by sal;
ID NAME CITY MGRID DES DOB SAL DEPT GEN
---------- ---------- ---------- ---------- ------------------------------ --------- ---------- ---------- ---
1 Karan goa 2 engg 10-MAY-94 60000 it m
4 Roshni Mumbai 4 lawyer 10-MAY-94 320000 hr f
5 kunali chandigarh 5 pilot 21-MAY-07 1000000 it f
3 kunal hyderabad 3 doc 20-NOV-96 1000000 it m
2 Suman pune 1 engg 20-NOV-96 hr f
select distinct des, name from employee order by name;
DES NAME
------------------------------ ----------
engg Karan
lawyer Roshni
engg Suman
doc kunal
pilot kunali
SQL> select distinct des from employee order by des;
DES
------------------------------
doc
engg
lawyer
pilot
SQL> select distinct des from employee order by des desc;
DES
------------------------------
pilot
lawyer
engg
doc
SQL> select dept,sum(sal) as total_sal from employee group by dept;
DEPT TOTAL_SAL
---------- ----------
hr 320000
it 2060000
select dept,count(dept) from employee group by dept having count(dept)<=2;
DEPT COUNT(DEPT)
---------- -----------
hr 2