DDL COMMANDS

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 16:53:49 2020

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> create table stud(rollno number(5),name char(10),mno number(10));

Table created.

SQL> describe stud

Name Null? Type
----------------------------------------- -------- --------------------------
ROLLNO NUMBER(5)
NAME CHAR(10)
MNO NUMBER(10)

SQL> alter table stud add(address varchar2(25));

Table altered.

SQL> describe stud

Name Null? Type
----------------------------------------- -------- ---------------------------
ROLLNO NUMBER(5)
NAME CHAR(10)
MNO NUMBER(10)
ADDRESS VARCHAR2(25)

SQL> alter table stud drop(mno);

Table altered.

SQL> desc stud

Name Null? Type
----------------------------------------- -------- ----------------------------

ROLLNO NUMBER(5)
NAME CHAR(10)
ADDRESS VARCHAR2(25)

SQL> alter table stud modify (name char(15));

Table altered.

SQL> desc stud

Name Null? Type
----------------------------------------- -------- ----------------------------
 ROLLNO NUMBER(5)
NAME CHAR(15)
ADDRESS VARCHAR2(25)

SQL> alter table stud modify (name char(12));

Table altered.

SQL> desc stud

Name Null? Type
----------------------------------------- -------- ----------------------------

ROLLNO NUMBER(5)
NAME CHAR(12)
ADDRESS VARCHAR2(25)

SQL> alter table stud modify (rollno char(7));

Table altered.

SQL> desc stud

Name Null? Type
----------------------------------------- -------- ----------------------------

ROLLNO CHAR(7)
NAME CHAR(12)
ADDRESS VARCHAR2(25)

SQL> drop table stud;

Table dropped.
DML COMMANDS

SQL> create table stud(rollno char(4), sname varchar2(10), mobno number(10),

marks number(2));

Table created.

SQL> desc stud

Name Null? Type
----------------------------------------- -------- ----------------------------

ROLLNO CHAR(4)
SNAME VARCHAR2(10)
MOBNO NUMBER(10)
MARKS NUMBER(2)

SQL> insert into stud values('E500','avinash',9898987900,80);

1 row created.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
E500 avinash 9898987900 80

SQL> insert into stud(rollno,sname,mobno) values('E600','bala',9876543210);

1 row created.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
E500 avinash 9898987900 80
E600 bala 9876543210

SQL> insert into stud values('E700','keerthy',984487900,NULL);

1 row created.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
E500 avinash 9898987900 80
E600 bala 9876543210
E700 keerthy 984487900

SQL> insert into stud values(&rno,&sname,&mobileno,&m);

Enter value for rno: e800
Enter value for sname: kumar
Enter value for mobileno: 4597157529
Enter value for m: 90
old 1: insert into stud values(&rno,&sname,&mobileno,&m)
new 1: insert into stud values(e800,kumar,4597157529,90)
insert into stud values(e800,kumar,4597157529,90)
*
ERROR at line 1:
ORA-00984: column not allowed here

SQL> insert into stud values(&rno,&sname,&mobileno,&m);

Enter value for rno: 'e800'
Enter value for sname: 'kumar'
Enter value for mobileno: 4567894320
Enter value for m: 79
old 1: insert into stud values(&rno,&sname,&mobileno,&m)
new 1: insert into stud values('e800','kumar',4567894320,79)

1 row created.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
E500 avinash 9898987900 80
E600 bala 9876543210
E700 keerthy 984487900
e800 kumar 4567894320 79

SQL> insert into stud values('&rno','&sname',&mobileno,&m);

Enter value for rno: E655
Enter value for sname: rajesh
Enter value for mobileno: 9876895432
Enter value for m: 93
old 1: insert into stud values('&rno','&sname',&mobileno,&m)
new 1: insert into stud values('E655','rajesh',9876895432,93)

1 row created.
SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
E500 avinash 9898987900 80
E600 bala 9876543210
E700 keerthy 984487900
e800 kumar 4567894320 79
E655 rajesh 9876895432 93

SQL> insert into stud(rollno,sname) values('&rno','&sname');

Enter value for rno: E325
Enter value for sname: nanda
old 1: insert into stud(rollno,sname) values('&rno','&sname')
new 1: insert into stud(rollno,sname) values('E325','nanda')

1 row created.
SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
E500 avinash 9898987900 80
E600 bala 9876543210
E700 keerthy 984487900
e800 kumar 4567894320 79
E655 rajesh 9876895432 93
E325 nanda

6 rows selected.

SQL> select rollno,sname from stud;

ROLL SNAME
---- ----------
E500 avinash
E600 bala
E700 keerthy
e800 kumar
E655 rajesh
E325 nanda

6 rows selected.

SQL> select * from stud where marks>=80;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
E500 avinash 9898987900 80
E655 rajesh 9876895432 93

SQL> select rollno from stud where marks>=80;

ROLL
----
E500
E655

SQL> insert into stud values('&r','&name',&mno,&mrk);

Enter value for r: e739
Enter value for name: uma
Enter value for mno: 9378914795
Enter value for mrk: 45
old 1: insert into stud values('&r','&name',&mno,&mrk)
new 1: insert into stud values('e739','uma',9378914795,45)

1 row created.

SQL> insert into stud values('&r','&name',&mno,&mrk);

Enter value for r: e478
Enter value for name: hari
Enter value for mno: 9123456723
Enter value for mrk: 50
old 1: insert into stud values('&r','&name',&mno,&mrk)
new 1: insert into stud values('e478','hari',9123456723,50)

1 row created.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
e123 raja 9344801234 90
e567 kavi 9675397567 76
e739 uma 9378914795 45
e478 hari 9123456723 50

SQL> update stud set marks=marks+5;

4 rows updated.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
e123 raja 9344801234 95
e567 kavi 9675397567 81
e739 uma 9378914795 50
e478 hari 9123456723 55

SQL> update stud set marks=marks+2 where marks>60;

2 rows updated.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
e123 raja 9344801234 97
e567 kavi 9675397567 83
e739 uma 9378914795 50
e478 hari 9123456723 55

SQL> update stud set marks=60 where marks<60;

2 rows updated.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
e123 raja 9344801234 97
e567 kavi 9675397567 83
e739 uma 9378914795 60
e478 hari 9123456723 60
SQL> update stud set marks=90, mobno=9874561279 where rollno='e567';

1 row updated.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
e123 raja 9344801234 97
e567 kavi 9874561279 90
e739 uma 9378914795 60
e478 hari 9123456723 60

SQL> delete from stud where rollno='e739';

1 row deleted.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
e123 raja 9344801234 97
e567 kavi 9874561279 90
e478 hari 9123456723 60

SQL> delete from stud where marks>80;

2 rows deleted.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
e478 hari 9123456723 60

SQL> insert into stud values('&r','&name',&mno,&m);

Enter value for r: 1234
Enter value for name: yamini
Enter value for mno: 9871252785
Enter value for m: 89
old 1: insert into stud values('&r','&name',&mno,&m)
new 1: insert into stud values('1234','yamini',9871252785,89)

1 row created.

SQL> insert into stud values('&r','&name',&mno,&m);

Enter value for r: 5643
Enter value for name: kumar
Enter value for mno: 9754371589
Enter value for m: 45
old 1: insert into stud values('&r','&name',&mno,&m)
new 1: insert into stud values('5643','kumar',9754371589,45)
1 row created.

SQL> insert into stud values('&r','&name',&mno,&m);

Enter value for r: 3459
Enter value for name: hari
Enter value for mno: 9123427836
Enter value for m: 75
old 1: insert into stud values('&r','&name',&mno,&m)
new 1: insert into stud values('3459','hari',9123427836,75)

1 row created.

SQL> insert into stud values('&r','&name',&mno,&m);

Enter value for r: 6758
Enter value for name: bala
Enter value for mno: 9125486715
Enter value for m: 20
old 1: insert into stud values('&r','&name',&mno,&m)
new 1: insert into stud values('6758','bala',9125486715,20)

1 row created.

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
1234 yamini 9871252785 89
5643 kumar 9754371589 45
3459 hari 9123427836 75
6758 bala 9125486715 20

SQL> select * from stud where marks in(75,45);

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
5643 kumar 9754371589 45
3459 hari 9123427836 75

SQL> insert into stud values('&r','&name',&mno,&m);

Enter value for r: 4589
Enter value for name: babu
Enter value for mno: 9471370340
Enter value for m: 75
old 1: insert into stud values('&r','&name',&mno,&m)
new 1: insert into stud values('4589','babu',9471370340,75)

1 row created.

SQL> select * from stud where marks in(75,45);

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
5643 kumar 9754371589 45
3459 hari 9123427836 75
4589 babu 9471370340 75

SQL> select * from stud where marks not in(75,45);

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
1234 yamini 9871252785 89
6758 bala 9125486715 20

SQL> select * from stud where marks between 50 and 80;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
3459 hari 9123427836 75
4589 babu 9471370340 75

SQL> select * from stud;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
1234 yamini 9871252785 89
5643 kumar 9754371589 45
3459 hari 9123427836 75
6758 bala 9125486715 20
4589 babu 9471370340 75

SQL> select * from stud where sname like 'b%';

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
6758 bala 9125486715 20
4589 babu 9471370340 75

SQL> select * from stud where sname like '%i';

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
1234 yamini 9871252785 89
3459 hari 9123427836 75

SQL> select * from stud where sname like '%a%';

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
1234 yamini 9871252785 89
5643 kumar 9754371589 45
3459 hari 9123427836 75
6758 bala 9125486715 20
4589 babu 9471370340 75

SQL> select * from stud where sname like '_a%';

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
1234 yamini 9871252785 89
3459 hari 9123427836 75
6758 bala 9125486715 20
4589 babu 9471370340 75

SQL> select * from stud where marks =any(75,20); =any in Multiple OR

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
3459 hari 9123427836 75
6758 bala 9125486715 20
4589 babu 9471370340 75

SQL> select * from stud where marks >any(75,20); m>75 or m>20  m>20

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
1234 yamini 9871252785 89
5643 kumar 9754371589 45
3459 hari 9123427836 75
4589 babu 9471370340 75

SQL> select * from stud where marks <any(75,20); m<75 or m<20  m<75

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
5643 kumar 9754371589 45
6758 bala 9125486715 20

SQL> select * from stud where marks <all(75,20); m<75 and m<20  m<20

no rows selected

SQL> select * from stud where marks <all(75,50); m<75 and m<50  m<50

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
5643 kumar 9754371589 45
6758 bala 9125486715 20

SQL> select * from stud where marks <50;

ROLL SNAME MOBNO MARKS

---- ---------- ---------- ----------
5643 kumar 9754371589 45
6758 bala 9125486715 20

SQL> select 5+10 from dual;

5+10
----------
15
Numeric functions:
SQL> desc numtab;
Name Null? Type
---------------------- -------- ----------------------------

N NUMBER(10,4)

SQL> insert into numtab values(567.89);

1 row created.

SQL> insert into numtab values(-10000.525);

1 row created.

SQL> insert into numtab values(930.5679);

1 row created.

SQL> insert into numtab values(30.5231);

1 row created.

SQL> insert into numtab values(0);

1 row created.

SQL> select n,sign(n),abs(n) from numtab;

N SIGN(N) ABS(N)
---------- ---------- ----------
567.89 1 567.89
-10000.525 -1 10000.525
930.5679 1 930.5679
30.5231 1 30.5231
0 0 0

SQL> select n,ceil(n),floor(n) from numtab;

N CEIL(N) FLOOR(N)
---------- ---------- ----------
567.89 568 567
-10000.525 -10000 -10001
930.5679 931 930
30.5231 31 30
0 0 0
25 25 25

SQL> select n,mod(n,5),power(n,2) from numtab;

N MOD(N,5) POWER(N,2)
---------- ---------- ----------
567.89 2.89 322499.052
-10000.525 -.525 100010500
930.5679 .5679 865956.617
30.5231 .5231 931.659634
0 0 0

SQL> select n,sqrt(n) from numtab where n>0;

N SQRT(N)
---------- ----------
567.89 23.8304427
930.5679 30.505211
30.5231 5.52477149
25 5

SQL> select n, sin(n), cos(n),tan(n) from numtab;

N SIN(N) COS(N) TAN(N)

---------- ---------- ---------- ----------
567.89 .673009574 -.73963377 -.90992272
-10000.525 .741688101 -.67074493 -1.1057677
930.5679 .61032796 .792148838 .770471318
30.5231 -.7788477 .627213094 -1.2417593
0 0 1 0

SQL> select n, log(10,n), ln(n) from numtab where n>0;

N LOG(10,N) LN(N)
---------- ---------- ----------
567.89 2.75426422 6.34192774
930.5679 2.96874807 6.83579504
30.5231 1.48462864 3.41848377

SQL> select n,round(n,2),trunc(n,2) from numtab;

N ROUND(N,2) TRUNC(N,2)
---------- ---------- ----------
567.89 567.89 567.89
-10000.525 -10000.53 -10000.52
930.5679 930.57 930.56
30.5231 30.52 30.52
0 0 0
25 25 25

SQL> select round(456879,-1) from dual;

ROUND(456879,-1)
----------------
456880

SQL> select round(456879,-2) from dual;

ROUND(456879,-2)
----------------
456900

SQL> select round(456879,-4) from dual;

ROUND(456879,-4)
----------------
460000

SQL> select round(452319,-4) from dual;

ROUND(452319,-4)
----------------
450000

Character Functions:
SQL> select 6*2 from dual;

6*2
----------
12

SQL> select 2+4, 5-1 from dual;

2+4 5-1
---------- ----------
6 4

SQL> select translate('hello welcome','mo','ab') tran from dual;

TRAN
-------------
hellb welcbae

SQL> create table ctab (c varchar2(10));

Table created.

SQL> insert into ctab values(' hari');

1 row created.

SQL> insert into ctab values(' Banu ');

1 row created.

SQL> insert into ctab values('Ramu ');

1 row created.
SQL> select c, ltrim(c), rtrim(c) from ctab;

C LTRIM(C) RTRIM(C)
---------- ---------- ----------
hari hari hari
Banu Banu Banu
Ramu Ramu Ramu

SQL> create table ctab (c varchar2(10));

Table created.

SQL> insert into ctab values(' hari');

1 row created.

SQL> insert into ctab values(' Banu ');

1 row created.

SQL> insert into ctab values('Ramu ');

1 row created.

SQL> select c, ltrim(c), rtrim(c) from ctab;

C LTRIM(C) RTRIM(C)
---------- ---------- ----------
hari hari hari
Banu Banu Banu
Ramu Ramu Ramu

SQL> select c, trim(c) from ctab;

C TRIM(C)
---------- ----------
hari hari
Banu Banu
Ramu Ramu

SQL> select c, lpad(c,8,'#') from ctab;

C LPAD(C,8
---------- --------
hari ## hari
Banu # Banu
Ramu ##Ramu

SQL> select c, rpad(c,8,'#') from ctab;

C RPAD(C,8
---------- --------
hari hari##
Banu Banu #
Ramu Ramu ##
DATE FUNCTIONS

SQL> select sysdate from dual;

SYSDATE
---------
21-AUG-20

SQL> create table dttab(dt date);

Table created.

SQL> insert into dttab values('21-aug-20');

1 row created.

SQL> insert into dttab values('1-jan-20');

1 row created.

SQL> insert into dttab values('11-dec-19');

1 row created.

SQL> insert into dttab values('15-sep-90');

1 row created.

SQL> select * from dttab;

DT
---------
21-AUG-20
01-JAN-20
11-DEC-19
15-SEP-90

SQL> select dt,add_months(dt,4) M4 from dttab;

DT M4
--------- ---------
21-AUG-20 21-DEC-20
01-JAN-20 01-MAY-20
11-DEC-19 11-APR-20
15-SEP-90 15-JAN-91

SQL> select dt,months_between(dt,sysdate) diff from dttab;

DT DIFF
--------- ----------
21-AUG-20 0
01-JAN-20 -7.6629327
11-DEC-19 -8.3403521
15-SEP-90 -359.21132

SQL> select dt,months_between(dt,'01-feb-20') diff from dttab;

DT DIFF
--------- ----------
21-AUG-20 6.64516129
01-JAN-20 -1
11-DEC-19 -1.6774194
15-SEP-90 -352.54839

SQL> select dt,next_day(dt,'sunday') nxt, last_day(dt) eomnth from dttab;

DT NXT EOMNTH
--------- --------- ---------
21-AUG-20 23-AUG-20 31-AUG-20
01-JAN-20 05-JAN-20 31-JAN-20
11-DEC-19 15-DEC-19 31-DEC-19
15-SEP-90 16-SEP-90 30-SEP-90

SQL> select dt,round(dt,'year') Ryear,round(dt,'month') RMonth, round(dt,'day')

rday from dttab;

DT RYEAR RMONTH RDAY

--------- --------- --------- ---------
21-AUG-20 01-JAN-21 01-SEP-20 23-AUG-20
01-JAN-20 01-JAN-20 01-JAN-20 29-DEC-19
11-DEC-19 01-JAN-20 01-DEC-19 08-DEC-19
15-SEP-90 01-JAN-91 01-SEP-90 16-SEP-90

SQL> select dt,trunc(dt,'year') tyear,trunc(dt,'month') tmonth, trunc(dt,'day')

tday from dttab;

DT TYEAR TMONTH TDAY

--------- --------- --------- ---------
21-AUG-20 01-JAN-20 01-AUG-20 16-AUG-20
01-JAN-20 01-JAN-20 01-JAN-20 29-DEC-19
11-DEC-19 01-JAN-19 01-DEC-19 08-DEC-19
15-SEP-90 01-JAN-90 01-SEP-90 09-SEP-90

SQL> select dt,greatest(dt,'31-dec-19') big, least(dt,'15-aug-19') small from dt

tab;

DT BIG SMALL
--------- --------- ---------
21-AUG-20 21-AUG-20 15-AUG-19
01-JAN-20 01-JAN-20 15-AUG-19
11-DEC-19 31-DEC-19 15-AUG-19
15-SEP-90 31-DEC-19 15-SEP-90

GROUP FUNCTIONS
SQL> create table emp(eno number(4),ename varchar(10),doj date,sal number(9,2),d
no char(2));

Table created.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------

ENO NUMBER(4)
ENAME VARCHAR2(10)
DOJ DATE
SAL NUMBER(9,2)
DNO CHAR(2)

SQL> insert into emp values(1001,'Tilak','10-jan-05',50000,'D1');

1 row created.

SQL> insert into emp values(3001,'Hari','25-sep-15',35000,'D1');

1 row created.

SQL> insert into emp values(1005,'Seetha','05-mar-19',45000,'D2');

1 row created.

SQL> insert into emp values(2002,'Kumar','14-may-17',55000,'D2');

1 row created.

SQL> insert into emp values(3006,'Babu','14-may-17',35000,'D3');

1 row created.

SQL> insert into emp values(2006,'Monisha','10-apr-19',40000,'D3');

1 row created.

SQL> select * from emp;

ENO ENAME DOJ SAL DN

---------- ---------- --------- ---------- --
1001 Tilak 10-JAN-05 50000 D1
3001 Hari 25-SEP-15 35000 D1
1005 Seetha 05-MAR-19 45000 D2
2002 Kumar 14-MAY-17 55000 D2
3006 Babu 14-MAY-17 35000 D3
2006 Monisha 10-APR-19 40000 D3

6 rows selected.

SQL> select count(*) from emp;

 COUNT(*)
----------
6

SQL> select count(*) from emp where dno='d2';

COUNT(*)
----------
0

SQL> select count(*) from emp where dno='D2';

COUNT(*)
----------
2

SQL> select min(sal),max(sal),avg(sal),min(doj),max(doj) from emp;

MIN(SAL) MAX(SAL) AVG(SAL) MIN(DOJ) MAX(DOJ)

---------- ---------- ---------- ---------
---------
35000 55000 43333.3333 10-JAN-05 10-APR-19

SQL> SELECT DISTINCT DOJ FROM EMP;

DOJ
---------
25-SEP-15
10-APR-19
10-JAN-05
05-MAR-19
14-MAY-17

SQL> SELECT COUNT(DISTINCT SAL) FROM EMP;

COUNT(DISTINCT SAL)
------------------
5
SET FUNCTIONS

SQL> create table emp2 as

select * from emp where sal>40000;

Table created.

SQL> select * from emp2;

ENO ENAME DOJ SAL DN

---------- ---------- --------- ---------- --
1001 Tilak 10-JAN-05 50000 D1
1005 Seetha 05-MAR-19 45000 D2
2002 Kumar 14-MAY-17 55000 D2

SQL> insert into emp2 values(5001,'kala','20-mar-19',60000,'D2');

1 row created.

SQL> insert into emp2 values(3010,'Pradeep','15-jun-14',65000,'D3');

1 row created.

SQL> select * from emp2;

ENO ENAME DOJ SAL DN

---------- ---------- --------- ---------- --
1001 Tilak 10-JAN-05 50000 D1
1005 Seetha 05-MAR-19 45000 D2
2002 Kumar 14-MAY-17 55000 D2
5001 kala 20-MAR-19 60000 D2
3010 Pradeep 15-JUN-14 65000 D3

SQL> select * from emp;

ENO ENAME DOJ SAL DN

---------- ---------- --------- ---------- --
1001 Tilak 10-JAN-05 50000 D1
3001 Hari 25-SEP-15 35000 D1
1005 Seetha 05-MAR-19 45000 D2
2002 Kumar 14-MAY-17 55000 D2
3006 Babu 14-MAY-17 35000 D3
2006 Monisha 10-APR-19 40000 D3

6 rows selected.

SQL> select * from emp union select * from emp2;

ENO ENAME DOJ SAL DN

---------- ---------- --------- ---------- --
1001 Tilak 10-JAN-05 50000 D1
1005 Seetha 05-MAR-19 45000 D2
2002 Kumar 14-MAY-17 55000 D2
 2006 Monisha 10-APR-19 40000 D3
3001 Hari 25-SEP-15 35000 D1
3006 Babu 14-MAY-17 35000 D3
3010 Pradeep 15-JUN-14 65000 D3
5001 kala 20-MAR-19 60000 D2

8 rows selected.

SQL> select * from emp union all select * from emp2;

ENO ENAME DOJ SAL DN

---------- ---------- --------- ---------- --
1001 Tilak 10-JAN-05 50000 D1
3001 Hari 25-SEP-15 35000 D1
1005 Seetha 05-MAR-19 45000 D2
2002 Kumar 14-MAY-17 55000 D2
3006 Babu 14-MAY-17 35000 D3
2006 Monisha 10-APR-19 40000 D3
1001 Tilak 10-JAN-05 50000 D1
1005 Seetha 05-MAR-19 45000 D2
2002 Kumar 14-MAY-17 55000 D2
5001 kala 20-MAR-19 60000 D2
3010 Pradeep 15-JUN-14 65000 D3

11 rows selected.

SQL> select * from emp intersect select * from emp2;

ENO ENAME DOJ SAL DN

---------- ---------- --------- ---------- --
1001 Tilak 10-JAN-05 50000 D1
1005 Seetha 05-MAR-19 45000 D2
2002 Kumar 14-MAY-17 55000 D2

SQL> select * from emp minus select * from emp2;

ENO ENAME DOJ SAL DN

---------- ---------- --------- ---------- --
2006 Monisha 10-APR-19 40000 D3
3001 Hari 25-SEP-15 35000 D1
3006 Babu 14-MAY-17 35000 D3

SQL> select * from emp2 minus select * from emp;

ENO ENAME DOJ SAL DN

---------- ---------- --------- ---------- --
3010 Pradeep 15-JUN-14 65000 D3
5001 kala 20-MAR-19 60000 D2
CONVERSION FUNCTIONS

SQL> select to_number('500') from dual;

TO_NUMBER('500')
----------------
500

SQL> select to_number('a') from dual;

select to_number('a') from dual
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select doj, to_char(doj,'dd/mm/yyyy') doj from emp;

DOJ DOJ
--------- ----------
10-JAN-05 10/01/2005
25-SEP-15 25/09/2015
05-MAR-19 05/03/2019
14-MAY-17 14/05/2017
14-MAY-17 14/05/2017
10-APR-19 10/04/2019

6 rows selected.

SQL> select to_char(sysdate,'day hh:mi:ss am') day_time from dual;

DAY_TIME
---------------------
monday 01:47:14 pm

SQL> select doj, to_char(doj,'month year') mnth_year from emp;

DOJ MNTH_YEAR
--------- ----------------------------------------------------
10-JAN-05 january two thousand five
25-SEP-15 september twenty fifteen
05-MAR-19 march twenty nineteen
14-MAY-17 may twenty seventeen
14-MAY-17 may twenty seventeen
10-APR-19 april twenty nineteen

6 rows selected.

SQL> select doj, to_char(doj,'MONTH YEAR') mnth_year from emp;

DOJ MNTH_YEAR
--------- ----------------------------------------------------
10-JAN-05 JANUARY TWO THOUSAND FIVE
25-SEP-15 SEPTEMBER TWENTY FIFTEEN
05-MAR-19 MARCH TWENTY NINETEEN
14-MAY-17 MAY TWENTY SEVENTEEN
14-MAY-17 MAY TWENTY SEVENTEEN
10-APR-19 APRIL TWENTY NINETEEN

6 rows selected.
SQL> select doj, to_char(doj,'Month Year') mnth_year from emp;

DOJ MNTH_YEAR
--------- ----------------------------------------------------
10-JAN-05 January Two Thousand Five
25-SEP-15 September Twenty Fifteen
05-MAR-19 March Twenty Nineteen
14-MAY-17 May Twenty Seventeen
14-MAY-17 May Twenty Seventeen
10-APR-19 April Twenty Nineteen

6 rows selected.

SQL> select to_char(sysdate,'DDth Mon YyyySP') today from dual;

TODAY
---------------------------------------------------
24TH Aug Two Thousand Twenty

SQL> select to_char(399.46) from dual;

TO_CHA
------
399.46

SQL> select to_date('20-04-2010','dd-mm-yyyy') from dual;

TO_DATE('
---------
20-APR-10

SQL> select to_date('20/Apr/10','dd/mon/yy') from dual;

TO_DATE('
---------
20-APR-10

SQL> select to_char(to_date('15-aug-1947','dd-mon-yyyy'),'Day') IDay from dual;

IDAY
---------
Friday
Miscellaneous functions:

SQL> select user from dual;

USER
------------------------------
SYSTEM

SQLPLUS COMMAND
SQL> show user
USER is "SYSTEM"

SQL> select uid from dual;

UID
----------
5

SQL> select rownum, rowid, eno,ename from emp;

ROWNUM ROWID ENO ENAME

---------- ------------------ ---------- ----------
1 AAADdhAABAAAKaCAAA 1001 Tilak
2 AAADdhAABAAAKaCAAB 3001 Hari
3 AAADdhAABAAAKaCAAC 1005 Seetha
4 AAADdhAABAAAKaCAAD 2002 Kumar
5 AAADdhAABAAAKaCAAE 3006 Babu
6 AAADdhAABAAAKaCAAF 2006 Monisha

6 rows selected.

SQL> alter table emp add( bonus number(5));

Table altered.

SQL> select * from emp;

ENO ENAME DOJ SAL DN BONUS

---------- ---------- --------- ---------- -- ----------
1001 Tilak 10-JAN-05 50000 D1
3001 Hari 25-SEP-15 35000 D1
1005 Seetha 05-MAR-19 45000 D2
2002 Kumar 14-MAY-17 55000 D2
3006 Babu 14-MAY-17 35000 D3
2006 Monisha 10-APR-19 40000 D3

6 rows selected.

SQL> select eno,ename,sal,nvl(bonus,1000) from emp;

ENO ENAME SAL NVL(BONUS,1000)

---------- ---------- ---------- ---------------
1001 Tilak 50000 1000
3001 Hari 35000 1000
1005 Seetha 45000 1000
2002 Kumar 55000 1000
3006 Babu 35000 1000
2006 Monisha 40000 1000

6 rows selected.

SQL> select * from emp;

ENO ENAME DOJ SAL DN BONUS

---------- ---------- --------- ---------- -- ----------
1001 Tilak 10-JAN-05 50000 D1
3001 Hari 25-SEP-15 35000 D1
1005 Seetha 05-MAR-19 45000 D2
2002 Kumar 14-MAY-17 55000 D2
3006 Babu 14-MAY-17 35000 D3
2006 Monisha 10-APR-19 40000 D3

6 rows selected.

SQL> select eno,ename,sal,

decode(dno,'D1',sal*0.05,'D3',sal*0.03) Allowance from emp;

ENO ENAME SAL ALLOWANCE

---------- ---------- ---------- ----------
1001 Tilak 50000 2500
3001 Hari 35000 1750
1005 Seetha 45000
2002 Kumar 55000
3006 Babu 35000 1050
2006 Monisha 40000 1200

6 rows selected.

SQL> select eno,ename,sal,

decode(dno,'D1',sal*0.05,'D3',sal*0.03,sal*0.01) Allowance from emp;

ENO ENAME SAL ALLOWANCE

---------- ---------- ---------- ----------
1001 Tilak 50000 2500
3001 Hari 35000 1750
1005 Seetha 45000 450
2002 Kumar 55000 550
3006 Babu 35000 1050
2006 Monisha 40000 1200

6 rows selected.

SQL> update emp set bonus=sal*0.05;

6 rows updated.

SQL> select * from emp;

ENO ENAME DOJ SAL DN BONUS

---------- ---------- --------- ---------- -- ----------
1001 Tilak 10-JAN-05 50000 D1 2500
3001 Hari 25-SEP-15 35000 D1 1750
1005 Seetha 05-MAR-19 45000 D2 2250
2002 Kumar 14-MAY-17 55000 D2 2750
3006 Babu 14-MAY-17 35000 D3 1750
2006 Monisha 10-APR-19 40000 D3 2000

6 rows selected.
Order by

SQL> select * from emp order by sal desc;

ENO ENAME DOJ SAL DN BONUS

---------- ---------- --------- ---------- -- ----------
2002 Kumar 14-MAY-17 55000 D2 2750
1001 Tilak 10-JAN-05 50000 D1 2500
1005 Seetha 05-MAR-19 45000 D2 2250
2006 Monisha 10-APR-19 40000 D3 2000
3001 Hari 25-SEP-15 35000 D1 1750
3006 Babu 14-MAY-17 35000 D3 1750

6 rows selected.

SQL> select * from emp order by ename asc;

ENO ENAME DOJ SAL DN BONUS

---------- ---------- --------- ---------- -- ----------
3006 Babu 14-MAY-17 35000 D3 1750
3001 Hari 25-SEP-15 35000 D1 1750
2002 Kumar 14-MAY-17 55000 D2 2750
2006 Monisha 10-APR-19 40000 D3 2000
1005 Seetha 05-MAR-19 45000 D2 2250
1001 Tilak 10-JAN-05 50000 D1 2500

6 rows selected.

SQL> select * from emp order by dno asc, ename asc;

ENO ENAME DOJ SAL DN BONUS

---------- ---------- --------- ---------- -- ----------
3001 Hari 25-SEP-15 35000 D1 1750
1001 Tilak 10-JAN-05 50000 D1 2500
2002 Kumar 14-MAY-17 55000 D2 2750
1005 Seetha 05-MAR-19 45000 D2 2250
3006 Babu 14-MAY-17 35000 D3 1750
2006 Monisha 10-APR-19 40000 D3 2000

6 rows selected.

Group by, Having

SQL> select dno from emp group by dno;

DN
--
D1
D2
D3

SQL> select dno, sum(sal) from emp group by dno;

DN SUM(SAL)
-- ----------
D1 85000
D3 75000
D2 100000

SQL> select dno, sum(sal) from emp group by dno order by dno;

DN SUM(SAL)
-- ----------
D1 85000
D2 100000
D3 75000

SQL> select dno, max(sal) from emp group by dno;

DN MAX(SAL)
-- ----------
D1 50000
D3 40000
D2 55000

SQL> select dno, count(*) from emp group by dno;

DN COUNT(*)
-- ----------
D1 2
D3 2
D2 2

SQL> select doj,count(*) from emp group by doj;

DOJ COUNT(*)
--------- ----------
25-SEP-15 1
10-APR-19 1
10-JAN-05 1
05-MAR-19 1
14-MAY-17 2

SQL> select dno, max(sal) from emp group by dno

having max(sal)>=45000;

DN MAX(SAL)
-- ----------
D1 50000
D2 55000

SQL> select dno from emp where sal>=45000 group by dno;

DN
--
D1
D2

Joins

SQL> create table t1 (eno number(4),ename varchar2(10),dno char(2));

Table created.

SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER(4)
ENAME VARCHAR2(10)
DNO CHAR(2)

SQL> insert into t1 values(101,'kala','d1');

1 row created.

SQL> insert into t1 values(102,'balan','d2');

1 row created.

SQL> insert into t1 values(103,'dhinesh','d2');

1 row created.

SQL> insert into t1 values(104,'hari','d1');

1 row created.

SQL> select * from t1;

ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1

SQL> create table t2(dno char(2), loc varchar2(10));

Table created.

SQL> desc t2;

Name Null? Type
----------------------------------------- -------- ----------------------------
DNO CHAR(2)
LOC VARCHAR2(10)

SQL> insert into t2 values('d1','chennai');

1 row created.

SQL> insert into t2 values('d2','mysore');

1 row created.

SQL> select * from t2;

DN LOC
-- ----------
d1 chennai
d2 mysore

SQL> select eno,ename,loc from t1,t2;

ENO ENAME LOC

---------- ---------- ----------
101 kala chennai
102 balan chennai
103 dhinesh chennai
104 hari chennai
101 kala mysore
102 balan mysore
103 dhinesh mysore
104 hari mysore

8 rows selected.

Equi Join

SQL> select eno,ename,t2.dno,loc from t1,t2 where t1.dno=t2.dno;

ENO ENAME DN LOC

---------- ---------- -- ----------
101 kala d1 chennai
102 balan d2 mysore
103 dhinesh d2 mysore
104 hari d1 chennai

SQL> create table stud1(rno number(5), sname varchar2(10), course varchar2(10),

marks number(3));

Table created.

SQL> insert into stud1 values(15001,'Rajesh','BSc CSc',80);

1 row created.

SQL> insert into stud1 values(10006,'Hari','BSc Phy',76);

1 row created.

SQL> insert into stud1 values(12006,'Rishi','BSc CSc',84);

1 row created.

SQL> insert into stud1 values(14003,'Latha','BSc Che',78);

1 row created.

SQL> insert into stud1 values(19008,'Anand','BCom',88);

1 row created.

SQL> create table stud2(rno number(5), sname varchar2(10),sports varchar2(10));

Table created.
SQL> insert into stud2 values(10006, 'Hari','Cricket');

1 row created.

SQL> insert into stud2 values(14003, 'Latha','Chess');

1 row created.

SQL> insert into stud2 values(18012, 'Kala','Volleyball');

1 row created.

SQL> insert into stud2 values(19008, 'Anand','Cricket');

1 row created.

SQL> insert into stud2 values(17015, 'Priya','Cricket');

1 row created.

SQL> insert into stud2 values(14022, 'Kumar','Chess');

1 row created.

SQL> select * from stud1;

RNO SNAME COURSE MARKS

---------- ---------- ---------- ----------
15001 Rajesh BSc CSc 80
10006 Hari BSc Phy 76
12006 Rishi BSc CSc 84
14003 Latha BSc Che 78
19008 Anand BCom 88

SQL> select * from stud2;

RNO SNAME SPORTS

---------- ---------- ----------
10006 Hari Cricket
14003 Latha Chess
18012 Kala Volleyball
19008 Anand Cricket
17015 Priya Cricket
14022 Kumar Chess

6 rows selected.

SQL> select stud1.rno,stud1.sname,marks,sports from stud1,stud2;

RNO SNAME MARKS SPORTS

---------- ---------- ---------- ----------
15001 Rajesh 80 Cricket
15001 Rajesh 80 Chess
 15001 Rajesh 80 Volleyball
15001 Rajesh 80 Cricket
15001 Rajesh 80 Cricket
15001 Rajesh 80 Chess
10006 Hari 76 Cricket
10006 Hari 76 Chess
10006 Hari 76 Volleyball
10006 Hari 76 Cricket
10006 Hari 76 Cricket
10006 Hari 76 Chess
12006 Rishi 84 Cricket
12006 Rishi 84 Chess
12006 Rishi 84 Volleyball
12006 Rishi 84 Cricket
12006 Rishi 84 Cricket
12006 Rishi 84 Chess
14003 Latha 78 Cricket
14003 Latha 78 Chess
14003 Latha 78 Volleyball
14003 Latha 78 Cricket
14003 Latha 78 Cricket
14003 Latha 78 Chess
19008 Anand 88 Cricket
19008 Anand 88 Chess
19008 Anand 88 Volleyball
19008 Anand 88 Cricket
19008 Anand 88 Cricket
19008 Anand 88 Chess

30 rows selected.

SQL> select s1.rno,s1.sname,marks,sports from stud1 s1,stud2 s2 where

stud1.rno=stud2.rno;

RNO SNAME MARKS SPORTS

---------- ---------- ---------- ----------
10006 Hari 76 Cricket
14003 Latha 78 Chess
19008 Anand 88 Cricket

Outer Join

SQL> select stud1.rno,stud1.sname,marks,sports from stud1 full outer join stud2

on stud1.rno=stud2.rno;

RNO SNAME MARKS SPORTS

---------- ---------- ---------- ----------
10006 Hari 76 Cricket
14003 Latha 78 Chess
19008 Anand 88 Cricket
15001 Rajesh 80
12006 Rishi 84
Cricket
Chess
 Volleyball

8 rows selected.

SQL> select stud1.*,stud2.* from stud1 full outer join stud2 on

stud1.rno=stud2.rno;

RNO SNAME COURSE MARKS RNO SNAME SPORTS

---------- ---------- ---------- ---------- ---------- ---------- ----------
10006 Hari BSc Phy 76 10006 Hari Cricket
14003 Latha BSc Che 78 14003 Latha Chess
19008 Anand BCom 88 19008 Anand Cricket
15001 Rajesh BSc CSc 80
12006 Rishi BSc CSc 84
17015 Priya Cricket
14022 Kumar Chess
18012 Kala Volleyball

8 rows selected.

SQL> select stud1.*,stud2.* from stud1 left outer join stud2 on

stud1.rno=stud2.rno;

RNO SNAME COURSE MARKS RNO SNAME SPORTS

---------- ---------- ---------- ---------- ---------- ---------- ----------
10006 Hari BSc Phy 76 10006 Hari Cricket
14003 Latha BSc Che 78 14003 Latha Chess
19008 Anand BCom 88 19008 Anand Cricket
15001 Rajesh BSc CSc 80
12006 Rishi BSc CSc 84

SQL> select stud1.*,stud2.* from stud1 right outer join stud2 on

stud1.rno=stud2.rno;

RNO SNAME COURSE MARKS RNO SNAME SPORTS

---------- ---------- ---------- ---------- ---------- ---------- ----------
10006 Hari BSc Phy 76 10006 Hari Cricket
14003 Latha BSc Che 78 14003 Latha Chess
19008 Anand BCom 88 19008 Anand Cricket
17015 Priya Cricket
14022 Kumar Chess
18012 Kala Volleyball

6 rows selected.

SQL> select * from stud1 cross join stud2;

RNO SNAME COURSE MARKS RNO SNAME SPORTS

---------- ---------- ---------- ---------- ---------- ---------- ----------
15001 Rajesh BSc CSc 80 10006 Hari Cricket
15001 Rajesh BSc CSc 80 14003 Latha Chess
15001 Rajesh BSc CSc 80 18012 Kala Volleyball
15001 Rajesh BSc CSc 80 19008 Anand Cricket
15001 Rajesh BSc CSc 80 17015 Priya Cricket
 15001 Rajesh BSc CSc 80 14022 Kumar Chess
10006 Hari BSc Phy 76 10006 Hari Cricket
10006 Hari BSc Phy 76 14003 Latha Chess
10006 Hari BSc Phy 76 18012 Kala Volleyball
10006 Hari BSc Phy 76 19008 Anand Cricket
10006 Hari BSc Phy 76 17015 Priya Cricket

RNO SNAME COURSE MARKS RNO SNAME SPORTS

---------- ---------- ---------- ---------- ---------- ---------- ----------
10006 Hari BSc Phy 76 14022 Kumar Chess
12006 Rishi BSc CSc 84 10006 Hari Cricket
12006 Rishi BSc CSc 84 14003 Latha Chess
12006 Rishi BSc CSc 84 18012 Kala Volleyball
12006 Rishi BSc CSc 84 19008 Anand Cricket
12006 Rishi BSc CSc 84 17015 Priya Cricket
12006 Rishi BSc CSc 84 14022 Kumar Chess
14003 Latha BSc Che 78 10006 Hari Cricket
14003 Latha BSc Che 78 14003 Latha Chess
14003 Latha BSc Che 78 18012 Kala Volleyball
14003 Latha BSc Che 78 19008 Anand Cricket

RNO SNAME COURSE MARKS RNO SNAME SPORTS

---------- ---------- ---------- ---------- ---------- ---------- ----------
14003 Latha BSc Che 78 17015 Priya Cricket
14003 Latha BSc Che 78 14022 Kumar Chess
19008 Anand BCom 88 10006 Hari Cricket
19008 Anand BCom 88 14003 Latha Chess
19008 Anand BCom 88 18012 Kala Volleyball
19008 Anand BCom 88 19008 Anand Cricket
19008 Anand BCom 88 17015 Priya Cricket
19008 Anand BCom 88 14022 Kumar Chess

30 rows selected.

Self Join

SQL> select s1.sname from stud1 s1, stud1 s2 where s1.course=s2.course and
s2.sname='Rajesh';

SNAME
----------
Rajesh
Rishi

SQL> select s1.sname from stud1 s1, stud1 s2 where s1.marks>s2.marks and
s2.sname='Rajesh';

SNAME
----------
Rishi
Anand

SQL> select s1.sname from stud1 s1, stud1 s2 where s1.marks>=s2.marks and
s2.sname='Rajesh';
SNAME
----------
Rajesh
Rishi
Anand

SQL> select s1.sname,s1.marks from stud1 s1, stud1 s2 where s1.marks>=s2.marks and
s2.sname='Rajesh';

SNAME MARKS
---------- ----------
Rajesh 80
Rishi 84
Anand 88

Simple Subquery

SQL> desc emp

Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER(4)
ENAME VARCHAR2(10)
DESIG VARCHAR2(10)
SAL NUMBER(6)
DNO CHAR(2)

SQL> insert into emp values(1201,'Praveen','Clerk',8000,'d1');

1 row created.

SQL> insert into emp values(1523,'James','Programmer',15000,'d1');

1 row created.

SQL> insert into emp values(2572,'Kumar','Programmer',20000,'d1');

1 row created.

SQL> insert into emp values(1452,'Yamini','HR',28000,'d2');

1 row created.

SQL> insert into emp values(3356,'Kanna','Clerk',9000,'d2');

1 row created.

SQL> insert into emp values(4500,'Sakthi','Manager',40000,'d2');

1 row created.

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
 3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2

6 rows selected.

SQL> insert into emp values(3800,'Anand','Programmer',20000,'d2');

1 row created.

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2

7 rows selected.

SQL> select * from emp where desig=

(select desig from emp where ename='Kumar');

select * from emp where desig=’Programmer’;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
3800 Anand Programmer 20000 d2

SQL> select * from emp where sal>=

(select sal from emp where ename='Anand');

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2

SQL> select * from dep;

DN LOC
-- ----------
d1 chennai
d2 mysore
d3 banglore

SQL> select loc from dep where dno=

(select dno from emp where ename='Yamini');

LOC
----------
mysore

SQL> select * from emp where dno=

(select dno from dep where loc='chennai');
 ENO ENAME DESIG SAL DN
---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1

SQL> select * from emp where sal=

(select max(sal) from emp);

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
4500 Sakthi Manager 40000 d2

Subquery returns more than one record

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
2567 Anand HR 28000 d1

SQL> select * from emp where sal>any

(select sal from emp where ename='Anand');

select * from emp where sal>any (20000,28000)

select * from emp where sal>20000 OR sal>28000
select * from emp where sal>20000

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
4500 Sakthi Manager 40000 d2
1452 Yamini HR 28000 d2
2567 Anand HR 28000 d1

SQL> select * from emp where sal>any

(select sal from emp where desig='Programmer');

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
4500 Sakthi Manager 40000 d2
1452 Yamini HR 28000 d2
2567 Anand HR 28000 d1
3800 Anand Programmer 20000 d2
2572 Kumar Programmer 20000 d1

SQL> select * from emp where sal in

(select sal from emp where desig='Programmer');
 Sal in (15000,20000,20000)  sal=15000 or sal=20000

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1523 James Programmer 15000 d1
3800 Anand Programmer 20000 d2
2572 Kumar Programmer 20000 d1

SQL> select * from emp where sal >all

(select sal from emp where desig='Programmer');

Sal >all(15000,20000,20000)  sal>15000 and sal>20000

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1452 Yamini HR 28000 d2
4500 Sakthi Manager 40000 d2
2567 Anand HR 28000 d1

SQL> select * from emp where (desig,sal) in

(select desig,sal from emp where ename='Anand');

(Programmer,20000) (HR,28000)

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
3800 Anand Programmer 20000 d2
2572 Kumar Programmer 20000 d1
2567 Anand HR 28000 d1
1452 Yamini HR 28000 d2

Correlated subquery

SQL> select * from emp x where x.sal=

(select max(y.sal) from emp y where y.dno=x.dno);

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
4500 Sakthi Manager 40000 d2
2567 Anand HR 28000 d1
SQL> select * from emp where sal>any
(select sal from emp where ename='Anand');

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
4500 Sakthi Manager 40000 d2
1452 Yamini HR 28000 d2
2572 Kumar Programmer 20000 d1
3800 Anand Programmer 20000 d2

SQL> select * from emp where sal>any

(select sal from emp where desig='Programmer');

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
4500 Sakthi Manager 40000 d2
1452 Yamini HR 28000 d2
2572 Kumar Programmer 20000 d1
3800 Anand Programmer 20000 d2

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2

7 rows selected.

SQL> select sal from emp where desig='Programmer';

SAL
----------
15000
20000
20000

SQL> select * from emp where desig in

(select desig from emp where sal<15000);

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
3356 Kanna Clerk 9000 d2
1201 Praveen Clerk 8000 d1

SQL> select * from emp where desig in

(select desig from emp where sal<20000);

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
3356 Kanna Clerk 9000 d2
1201 Praveen Clerk 8000 d1
3800 Anand Programmer 20000 d2
2572 Kumar Programmer 20000 d1
1523 James Programmer 15000 d1

SQL> select * from emp where(sal,dno) in

(select sal,dno from emp where ename='Kumar');

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
2572 Kumar Programmer 20000 d1
SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
2567 Anand HR 28000 d1

8 rows selected.

SQL> select * from emp where sal>any

(select sal from emp where ename='Anand');

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
4500 Sakthi Manager 40000 d2
1452 Yamini HR 28000 d2
2567 Anand HR 28000 d1
SQL> select * from emp where (desig,sal) in
(select desig,sal from emp where ename='Anand');

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
3800 Anand Programmer 20000 d2
2572 Kumar Programmer 20000 d1
2567 Anand HR 28000 d1
1452 Yamini HR 28000 d2

SQL> select * from emp x where x.sal=

(select max(y.sal) from emp y where y.dno=x.dno);

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
4500 Sakthi Manager 40000 d2
2567 Anand HR 28000 d1

SQL> select * from emp x where x.sal>=

(select avg(y.sal) from emp y where y.dno=x.dno);

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
4500 Sakthi Manager 40000 d2
2567 Anand HR 28000 d1

SQL> select * from emp x where x.sal>

(select avg(y.sal) from emp y where y.dno=x.dno);

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
4500 Sakthi Manager 40000 d2
2567 Anand HR 28000 d1

SQL> select * from t1;

 ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1
Transaction Control Language
Rollback
SQL> select * from t1;

ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1

SQL> insert into t1 values(204,'Lokesh','d3');

1 row created.

SQL> insert into t1 values(206,'Maran','d3');

1 row created.

SQL> select * from t1;

ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1
204 Lokesh d3
206 Maran d3

6 rows selected.

SQL> rollback;

Rollback complete.

SQL> select * from t1;

ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1

Commit
SQL> insert into t1 values(204,'Lokesh','d3');

1 row created.

SQL> insert into t1 values(206,'Maran','d3');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1
204 Lokesh d3
206 Maran d3

6 rows selected.

SQL> rollback;

Rollback complete.

SQL> select * from t1;

ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1
204 Lokesh d3
206 Maran d3

6 rows selected.

Savepoint
SQL> insert into t1 values(207,'Prabu','d3');

1 row created.

SQL> savepoint A;

Savepoint created.

SQL> insert into t1 values(301,'Banu','d4');

1 row created.

SQL> insert into t1 values(302,'James','d4');

1 row created.

SQL> select * from t1;

ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1
204 Lokesh d3
206 Maran d3
207 Prabu d3
301 Banu d4
302 James d4

9 rows selected.

SQL> rollback to A;

Rollback complete.
SQL> select * from t1;

ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1
204 Lokesh d3
206 Maran d3
207 Prabu d3

7 rows selected.

SQL> commit;

Commit complete.

SQL> select * from t1;

ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1
204 Lokesh d3
206 Maran d3
207 Prabu d3

7 rows selected.

Automatic Commit
SQL> insert into t1 values(310,'Sundar','d4');

1 row created.

SQL> insert into t1 values(311,'Ramu','d4');

1 row created.

SQL> create table t3(num number(4));

Table created.

SQL> select * from t1;

ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1
204 Lokesh d3
206 Maran d3
207 Prabu d3
310 Sundar d4
311 Ramu d4

9 rows selected.

SQL> rollback;
Rollback complete.

SQL> select * from t1;

ENO ENAME DN
---------- ---------- --
101 kala d1
102 balan d2
103 dhinesh d2
104 hari d1
204 Lokesh d3
206 Maran d3
207 Prabu d3
310 Sundar d4
311 Ramu d4

9 rows selected.
View
SQL> create view empvw as select * from emp where dno='d1';

View created.

SQL> select * from empvw;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1

SQL> insert into empvw values(4350,'Kala','Clerk',10000,'d1');

1 row created.

SQL> select * from empvw;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
4350 Kala Clerk 10000 d1

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
4350 Kala Clerk 10000 d1

8 rows selected.

SQL> insert into empvw(eno,ename,dno) values (5000,'Sara','d1');

1 row created.

SQL> select * from empvw;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
4350 Kala Clerk 10000 d1
5000 Sara d1

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
 3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
4350 Kala Clerk 10000 d1
5000 Sara d1

9 rows selected.

SQL> insert into empvw values(5850,'Bala','Programmer',25000,'d2');

1 row created.

SQL> select * from empvw;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
4350 Kala Clerk 10000 d1
5000 Sara d1

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 8000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
4350 Kala Clerk 10000 d1
5000 Sara d1
5850 Bala Programmer 25000 d2

10 rows selected.

SQL> update empvw set sal=11000 where ename='Praveen';

1 row updated.

SQL> select * from empvw;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 11000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
4350 Kala Clerk 10000 d1
5000 Sara d1

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 11000 d1
1523 James Programmer 15000 d1
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
4350 Kala Clerk 10000 d1
 5000 Sara d1
5850 Bala Programmer 25000 d2

10 rows selected.

SQL> delete from empvw where eno=1523;

1 row deleted.

SQL> select * from empvw;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 11000 d1
2572 Kumar Programmer 20000 d1
4350 Kala Clerk 10000 d1
5000 Sara d1

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 11000 d1
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
4350 Kala Clerk 10000 d1
5000 Sara d1
5850 Bala Programmer 25000 d2

9 rows selected.

SQL> drop view empvw;

View dropped.

SQL> select * from empvw;

select * from empvw
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create view empsalvw as select eno,ename,sal from emp;

View created.

SQL> select * from empsalvw;

ENO ENAME SAL

---------- ---------- ----------
1201 Praveen 11000
2572 Kumar 20000
1452 Yamini 28000
3356 Kanna 9000
4500 Sakthi 40000
3800 Anand 20000
4350 Kala 10000
5000 Sara
5850 Bala 25000

9 rows selected.
SQL> update empsalvw set sal=15000,desig='Programmer' where eno=5000;
update empsalvw set sal=15000,desig='Programmer' where eno=5000
*
ERROR at line 1:
ORA-00904: "DESIG": invalid identifier

SQL> update empsalvw set sal=15000 where eno=5000;

1 row updated.

SQL> select * from empsalvw;

ENO ENAME SAL

---------- ---------- ----------
1201 Praveen 11000
2572 Kumar 20000
1452 Yamini 28000
3356 Kanna 9000
4500 Sakthi 40000
3800 Anand 20000
4350 Kala 10000
5000 Sara 15000
5850 Bala 25000

9 rows selected.

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 11000 d1
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
4350 Kala Clerk 10000 d1
5000 Sara 15000 d1
5850 Bala Programmer 25000 d2

9 rows selected.

SQL> insert into empsalvw values(2500,'Manju',23000);

1 row created.

SQL> select * from empsalvw;

ENO ENAME SAL

---------- ---------- ----------
1201 Praveen 11000
2500 Manju 23000
2572 Kumar 20000
1452 Yamini 28000
3356 Kanna 9000
4500 Sakthi 40000
3800 Anand 20000
4350 Kala 10000
5000 Sara 15000
5850 Bala 25000

10 rows selected.

SQL> select * from emp;

 ENO ENAME DESIG SAL DN
---------- ---------- ---------- ---------- --
1201 Praveen Clerk 11000 d1
2500 Manju 23000
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 9000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
4350 Kala Clerk 10000 d1
5000 Sara 15000 d1
5850 Bala Programmer 25000 d2

10 rows selected.

SQL> create view payvw as select eno,sal Basic,sal*0.1 HRA, sal*0.05 DA,
sal*0.08 PF,(sal + sal*0.1 + sal*0.05) Gross,
(sal + sal*0.1 + sal*0.05- sal*0.08) NetSal from emp;

View created.

SQL> select * from payvw;

ENO BASIC HRA DA PF GROSS NETSAL

---------- ---------- ---------- ---------- ---------- ---------- ----------
1201 11000 1100 550 880 12650 11770
2500 23000 2300 1150 1840 26450 24610
2572 20000 2000 1000 1600 23000 21400
1452 28000 2800 1400 2240 32200 29960
3356 9000 900 450 720 10350 9630
4500 40000 4000 2000 3200 46000 42800
3800 20000 2000 1000 1600 23000 21400
4350 10000 1000 500 800 11500 10700
5000 15000 1500 750 1200 17250 16050
5850 25000 2500 1250 2000 28750 26750

10 rows selected.

SQL> update payvw set basic=10000 where eno=3356;

1 row updated.

SQL> select * from payvw;

ENO BASIC HRA DA PF GROSS NETSAL

---------- ---------- ---------- ---------- ---------- ---------- ----------
1201 11000 1100 550 880 12650 11770
2500 23000 2300 1150 1840 26450 24610
2572 20000 2000 1000 1600 23000 21400
1452 28000 2800 1400 2240 32200 29960
3356 10000 1000 500 800 11500 10700
4500 40000 4000 2000 3200 46000 42800
3800 20000 2000 1000 1600 23000 21400
4350 10000 1000 500 800 11500 10700
5000 15000 1500 750 1200 17250 16050
5850 25000 2500 1250 2000 28750 26750

10 rows selected.

SQL> update payvw set pf=1000 where eno=3356;

update payvw set pf=1000 where eno=3356
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL> create view vw1 as select dno,count(*) NumEmp,
sum(sal) TotSal from emp group by dno;

View created.

SQL> select * from vw1;

DN NUMEMP TOTSAL
-- ---------- ----------
1 23000
d1 4 56000
d2 5 123000

SQL> update emp set dno='d3' where ename='Manju';

1 row updated.

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 11000 d1
2500 Manju 23000 d3
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 10000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
4350 Kala Clerk 10000 d1
5000 Sara 15000 d1
5850 Bala Programmer 25000 d2

10 rows selected.

SQL> select * from vw1;

DN NUMEMP TOTSAL
-- ---------- ----------
d3 1 23000
d1 4 56000
d2 5 123000

SQL> update vw1 set dno='d4' where dno='d1';

update vw1 set dno='d4' where dno='d1'
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

SQL> update vw1 set totsal=60000 where dno='d1';

update vw1 set totsal=60000 where dno='d1'
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 11000 d1
2500 Manju 23000 d3
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 10000 d2
4500 Sakthi Manager 40000 d2
 3800 Anand Programmer 20000 d2
4350 Kala Clerk 10000 d1
5000 Sara 15000 d1
5850 Bala Programmer 25000 d2

10 rows selected.

SQL> select * from dep;

DN LOC
-- ----------
d1 chennai
d2 mysore
d3 banglore

SQL> create view v1 as select eno,ename,sal,emp.dno,loc from emp, dep

2 where emp.dno=dep.dno;

View created.

SQL> select * from v1;

ENO ENAME SAL DN LOC

---------- ---------- ---------- -- ----------
1201 Praveen 11000 d1 chennai
2500 Manju 23000 d3 banglore
2572 Kumar 20000 d1 chennai
1452 Yamini 28000 d2 mysore
3356 Kanna 10000 d2 mysore
4500 Sakthi 40000 d2 mysore
3800 Anand 20000 d2 mysore
4350 Kala 10000 d1 chennai
5000 Sara 15000 d1 chennai
5850 Bala 25000 d2 mysore

10 rows selected.

SQL> insert into v1 values(2000,'avinash',25000,'d3',bangalore);

insert into v1 values(2000,'avinash',25000,'d3',bangalore)
*
ERROR at line 1:
ORA-00984: column not allowed here

SQL> insert into v1 values(2000,'avinash',25000,'d3','bangalore');

insert into v1 values(2000,'avinash',25000,'d3','bangalore')
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL> update v1 set sal=17000 where eno=5000;

update v1 set sal=17000 where eno=5000
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
Sequence
SQL> create table stud( rollno number(4), sname varchar2(12), marks number(3));

Table created.

SQL> create sequence stud_seq start with 1701;

Sequence created.

SQL> insert into stud values(stud_seq.nextval,'sowmiyan',76);

1 row created.

SQL> insert into stud values(stud_seq.nextval,'kumaran',72);

1 row created.

SQL> select * from stud;

ROLLNO SNAME MARKS

---------- ------------ ----------
1701 sowmiyan 76
1702 kumaran 72

SQL> insert into stud values(stud_seq.nextval,'hariharan',77);

1 row created.

SQL> select * from stud;

ROLLNO SNAME MARKS

---------- ------------ ----------
1701 sowmiyan 76
1702 kumaran 72
1703 hariharan 77

SQL> select stud_seq.currval from dual;

CURRVAL
----------
1703

SQL> select stud_seq.nextval from dual;

NEXTVAL
----------
1704
SQL> select stud_seq.nextval from dual;

NEXTVAL
----------
1705

SQL> select stud_seq.nextval from dual;

NEXTVAL
----------
1706

SQL> insert into stud values(stud_seq.nextval,'kishore',73);

1 row created.
SQL> select * from stud;

ROLLNO SNAME MARKS

---------- ------------ ----------
1701 sowmiyan 76
1702 kumaran 72
1703 hariharan 77
1707 kishore 73

SQL> insert into stud values(1704,'dilli babu',70);

1 row created.

SQL> select * from stud;

ROLLNO SNAME MARKS

---------- ------------ ----------
1701 sowmiyan 76
1702 kumaran 72
1703 hariharan 77
1707 kishore 73
1704 dilli babu 70

SQL> alter sequence stud_seq maxvalue 1710;

Sequence altered.

SQL> insert into stud values(stud_seq.nextval,'ARUNACHALAM7',72);

1 row created.

SQL> insert into stud values(stud_seq.nextval,'praveen',76);

1 row created.

SQL> insert into stud values(stud_seq.nextval,'ganesh',79);

1 row created.

SQL> select * from stud;

ROLLNO SNAME MARKS

---------- ------------ ----------
1701 sowmiyan 76
1702 kumaran 72
1703 hariharan 77
1707 kishore 73
1704 dilli babu 70
1708 ARUNACHALAM7 72
1709 praveen 76
1710 ganesh 79

8 rows selected.

SQL> insert into stud values(stud_seq.nextval,'deepak',74);

insert into stud values(stud_seq.nextval,'deepak',74)
*
ERROR at line 1:
ORA-08004: sequence STUD_SEQ.NEXTVAL exceeds MAXVALUE and cannot be
instantiated

SQL> drop sequence stud_seq;

Sequence dropped.
SQL> select stud_seq.currval from dual;
select stud_seq.currval from dual
*
ERROR at line 1:
ORA-02289: sequence does not exist

SQL> create sequence seq1 start with 1001 increment by 2;

Sequence created.

SQL> select seq1.currval from dual;

select seq1.currval from dual
*
ERROR at line 1:
ORA-08002: sequence SEQ1.CURRVAL is not yet defined in this session

SQL> select seq1.nextval from dual;

NEXTVAL
----------
1001

SQL> /

NEXTVAL
----------
1003

SQL> /

NEXTVAL
----------
1005

SQL> /

NEXTVAL
----------
1007
SQL> /

NEXTVAL
----------
1009

SQL> /

NEXTVAL
----------
1011

SQL> drop sequence seq1;

Sequence dropped.

SQL> create sequence s1 start with 15 increment by 5

maxvalue 30 minvalue 0 cache 3 cycle;

Sequence created.

SQL> select s1.nextval from dual;

 NEXTVAL
----------
15

SQL> select s1.nextval from dual;

NEXTVAL
----------
20

SQL> select s1.nextval from dual;

NEXTVAL
----------
25

SQL> select s1.nextval from dual;

NEXTVAL
----------
30

SQL> select s1.nextval from dual;

NEXTVAL
----------
0

SQL> select s1.nextval from dual;

NEXTVAL
----------
5

SQL> select s1.nextval from dual;

NEXTVAL
----------
10

SQL> select s1.nextval from dual;

NEXTVAL
----------
15

SQL> select s1.nextval from dual;

NEXTVAL
----------
20

SQL> select s1.nextval from dual;

NEXTVAL
----------
25
Synonym

SQL> create table prod(pid number(3), pname varchar2(10), qty number(2), price
number(5,2));

Table created.

SQL> insert into prod values(101,'Shampoo',12,50.00);

1 row created.

SQL> insert into prod values(102,'Soap',20,12);

1 row created.

SQL> select * from prod;

PID PNAME QTY PRICE

---------- ---------- ---------- ----------
101 Shampoo 12 50
102 Soap 20 12

SQL> create synonym psyn for prod;

Synonym created.

SQL> insert into psyn values(103,'Toothpaste',16,35.50);

1 row created.

SQL> update psyn set qty=25 where pid=101;

1 row updated.

SQL> select * from psyn;

PID PNAME QTY PRICE

---------- ---------- ---------- ----------
101 Shampoo 25 50
102 Soap 20 12
103 Toothpaste 16 35.5

SQL> delete from psyn where pid=102;

1 row deleted.

SQL> select * from psyn;

PID PNAME QTY PRICE

---------- ---------- ---------- ----------
101 Shampoo 25 50
103 Toothpaste 16 35.5

SQL> select * from prod;

PID PNAME QTY PRICE

---------- ---------- ---------- ----------
101 Shampoo 25 50
103 Toothpaste 16 35.5

SQL> drop table prod;

Table dropped.
SQL> select * from psyn;
select * from psyn
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

SQL> select * from prod;

select * from prod
*
ERROR at line 1:
ORA-00942: table or view does not exist

Index
SQL> create index eidx on emp(eno);

Index created.

SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 11000 d1
2500 Manju Programmer 23000 d3
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 10000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
4350 Kala Clerk 10000 d1
5850 Bala Programmer 25000 d2
3000 Mohan Manager 35000 d3

10 rows selected.

SQL> drop index eidx;

Index dropped.
Constraints

NOT NULL
SQL> create table cust(cid number(3), cname varchar2(10) not null,
loc varchar2(10));

Table created.

SQL> desc cust

Name Null? Type
----------------------------------------- -------- ---------------------
CID NUMBER(3)
CNAME NOT NULL VARCHAR2(10)
LOC VARCHAR2(10)

SQL> insert into cust values(100,'vinoth','chennai');

1 row created.

SQL> insert into cust values(200,null,'delhi');

insert into cust values(200,null,'delhi')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."CUST"."CNAME")

SQL> insert into cust(cid,loc) values(200,'delhi');

insert into cust(cid,loc) values(200,'delhi')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."CUST"."CNAME")

SQL> select * from cust;

CID CNAME LOC

---------- ---------- ----------
100 vinoth chennai

SQL> insert into cust(cid,cname) values(200,'Jai');

1 row created.

SQL> select * from cust;

CID CNAME LOC

---------- ---------- ----------
100 vinoth chennai
200 Jai

SQL> drop table cust;

Table dropped.

UNIQUE
SQL> create table cust(cid number(3) unique, cname varchar2(10) not null, loc
varchar2(10));

Table created.
SQL> desc cust
Name Null? Type
----------------------------------------- -------- ---------------------
CID NUMBER(3)
CNAME NOT NULL VARCHAR2(10)
LOC VARCHAR2(10)

SQL> insert into cust values(300,'Murali','Chennai');

1 row created.

SQL> insert into cust values(300,'Krish','Trichy');

insert into cust values(300,'Krish','Trichy')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C004050) violated

SQL> insert into cust values(null,'Kavitha','Mumbai');

1 row created.

SQL> select * from cust;

CID CNAME LOC

---------- ---------- ----------
300 Murali Chennai
Kavitha Mumbai

SQL> insert into cust(cname,loc) values('Babu','Trichy');

1 row created.

SQL> select * from cust;

CID CNAME LOC

---------- ---------- ----------
300 Murali Chennai
Kavitha Mumbai
Babu Trichy

SQL> drop table cust;

Table dropped.

PRIMARY KEY
SQL> create table cust(cid number(3) primary key, cname varchar2(10) not null,
loc varchar2(10));

Table created.

SQL> desc cust

Name Null? Type
----------------------------------------- -------- ---------------------
CID NOT NULL NUMBER(3)
CNAME NOT NULL VARCHAR2(10)
LOC VARCHAR2(10)

SQL> insert into cust values(100,'Aarthi','Chennai');

1 row created.

SQL> insert into cust values(null,'Krish','Trichy');

insert into cust values(null,'Krish','Trichy')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."CUST"."CID")

SQL> insert into cust values(100,'Krish','Trichy');

insert into cust values(100,'Krish','Trichy')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C004052) violated

SQL> select * from cust;

CID CNAME LOC

---------- ---------- ----------
100 Aarthi Chennai

SQL> CREATE TABLE T5( SNO NUMBER(3) PRIMARY KEY, TNAME VARCHAR2(10) PRIMARY KEY);
CREATE TABLE T5( SNO NUMBER(3) PRIMARY KEY, TNAME VARCHAR2(10) PRIMARY KEY)
*
ERROR at line 1:
ORA-02260: table can have only one primary key

CHECK
SQL> create table cust(cid number(3) primary key, cname varchar2(10) not null,
loc varchar2(10),age number(2) check (age>20));

Table created.

SQL> insert into cust values(100, 'Vani','Chennai',40);

1 row created.

SQL> insert into cust values(200, 'Rani','Chennai',19);

insert into cust values(200, 'Rani','Chennai',19)
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.SYS_C004054) violated

SQL> drop table cust;

Table dropped.

SQL> create table cust (cid number(3) constraint cpk primary key,
cname varchar2(10));

Table created.

SQL> desc cust

Name Null? Type
----------------------------------------- -------- ----------------------------
CID NOT NULL NUMBER(3)
CNAME VARCHAR2(10)

SQL> alter table cust modify (cname varchar2(10) not null);

Table altered.

SQL> desc cust

Name Null? Type
 ----------------------------------------- -------- ----------------------------
CID NOT NULL NUMBER(3)
CNAME NOT NULL VARCHAR2(10)

SQL> alter table cust modify (cname varchar2(10) constraint cuq unique);

FOREIGN KEY
SQL> select * from emp;

ENO ENAME DESIG SAL DN

---------- ---------- ---------- ---------- --
1201 Praveen Clerk 11000 d1
2500 Manju Programmer 23000 d3
2572 Kumar Programmer 20000 d1
1452 Yamini HR 28000 d2
3356 Kanna Clerk 10000 d2
4500 Sakthi Manager 40000 d2
3800 Anand Programmer 20000 d2
4350 Kala Clerk 10000 d1
5850 Bala Programmer 25000 d2
3000 Mohan Manager 35000 d3

10 rows selected.

SQL> select * from dep;

DN LOC
-- ----------
d1 chennai
d2 mysore
d3 banglore

SQL> alter table emp add(constraint epk primary key(eno));

Table altered.

SQL> desc emp;

Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
DESIG VARCHAR2(10)
SAL NUMBER(6)
DNO CHAR(2)

SQL> alter table dep add(constraint dpk primary key(dno));

Table altered.

SQL> alter table emp add(constraint efk foreign key(dno) references dep(dno));

Table altered.

SQL> insert into emp values(2800,'Senthil','Manager',50000,'d4');

insert into emp values(2800,'Senthil','Manager',50000,'d4')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.EFK) violated - parent key not found
SQL> delete from dep where dno='d1';
delete from dep where dno='d1'
*
ERROR at line 1:
ORA-02292: integrity constraint (SYSTEM.EFK) violated - child record found

SQL> update emp set dno='d6' where eno=3000;

update emp set dno='d6' where eno=3000
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.EFK) violated - parent key not found

SQL> update dep set dno='d5' where loc='chennai';

update dep set dno='d5' where loc='chennai'
*
ERROR at line 1:
ORA-02292: integrity constraint (SYSTEM.EFK) violated - child record found

SQL> create table t5(cno number(3) primary key, cname varchar2(10) not null, mobno
number(10) unique, age number(2) check (age>18));

Table created.

SQL> desc t5
Name Null? Type
----------------------------------------- -------- ----------------------------
CNO NOT NULL NUMBER(3)
CNAME NOT NULL VARCHAR2(10)
MOBNO NUMBER(10)
AGE NUMBER(2)

SQL> drop table t5;

Table dropped.

SQL> create table t5(cno number(3) constraint cpk1 primary key, cname varchar2(10)
constraint cnn1 not null, mobno number(10) constraint cuq1 unique, age number(2)
constraint cck1 check (age>18));

Table created.

SQL> alter table t5 drop primary key;

Table altered.

SQL> desc t5
Name Null? Type
----------------------------------------- -------- ----------------------------
CNO NUMBER(3)
CNAME NOT NULL VARCHAR2(10)
MOBNO NUMBER(10)
AGE NUMBER(2)

SQL> alter table t5 drop unique(mobno);

Table altered.

SQL> alter table t5 drop constraint cnn1;

Table altered.

SQL> desc t5
Name Null? Type
----------------------------------------- -------- ----------------------------
CNO NUMBER(3)
CNAME VARCHAR2(10)
MOBNO NUMBER(10)
AGE NUMBER(2)

SQL> drop table t5;

Table dropped.

SQL> create table t5( cno number(3), cname varchar2(10) not null, mobno number(10), age
number(2), constraint cpk2 primary key(cno), constraint cuq2 unique(mobno));

Table created.

SQL> desc t5
Name Null? Type
----------------------------------------- -------- ----------------------------
CNO NOT NULL NUMBER(3)
CNAME NOT NULL VARCHAR2(10)
MOBNO NUMBER(10)
AGE NUMBER(2)

SQL> drop table t5;

Table dropped.

SQL> create table t6(cno number(3), cname varchar2(10), mobno number(10));

Table created.

SQL> alter table t6 modify(cno char(4) primary key);

Table altered.

SQL> alter table t6 add (constraint muq unique(mobno));

Table altered.

SQL>