Session 01

View All Public Scripts Login to Run Script

Script Name Session 01
Visibility Unlisted - anyone with the share link can access
Description 18-08-2021
Contributor 121910318004@gitam.in
Created Wednesday August 18, 2021

Statement 1

create table emp(eid int primary key,ename varchar2(20) not null,age int,designation char(20) not null,sal int)

Table created.

Statement 2

insert into emp(eid,ename,age,designation,sal)values(101,'a',25,'programmer',20000)

1 row(s) inserted.

Statement 3

insert into emp(eid,ename,age,designation,sal)values(102,'b',26,'developer',20000)

1 row(s) inserted.

Statement 4

insert into emp(eid,ename,age,designation,sal)values(103,'c',27,'analyst',20000)

1 row(s) inserted.

Statement 5
```
select *from emp
```
EID ENAME AGE DESIGNATION SAL
101 a 25 programmer 20000
103 c 27 analyst 20000
102 b 26 developer 20000

3 rows selected.

EID	ENAME	AGE	DESIGNATION	SAL
101	a	25	programmer	20000
103	c	27	analyst	20000
102	b	26	developer	20000

Statement 6

create table dep_policy(eid int,name char(10),age int,primary key(eid,name),foreign key(eid) references emp(eid))

Table created.

Statement 7

insert into dep_policy values(101,'abc',20)

1 row(s) inserted.

Statement 8

insert into dep_policy values(102,'xyz',20)

1 row(s) inserted.

Statement 9

insert into dep_policy values(101,'xyz',20)

1 row(s) inserted.

Statement 10
```
select *from dep_policy
```
EID NAME AGE
101 abc 20
102 xyz 20
101 xyz 20

3 rows selected.
Statement 11
```
delete from emp where eid=101
```
ORA-02292: integrity constraint (SQL_HPHNATQIXIBTMGZEBRAWVVPHV.SYS_C0063638054) violated - child record found ORA-06512: at "SYS.DBMS_SQL", line 1721
Statement 12
```
drop table dep_policy
```
Table dropped.

EID	NAME	AGE
101	abc	20
102	xyz	20
101	xyz	20

Statement 13

create table dep_policy(eid int,name char(10),age int,primary key(eid,name),foreign key(eid) references emp(eid) on delete cascade )

Table created.

Statement 14

insert into dep_policy values(101,'abc',20)

1 row(s) inserted.

Statement 15

insert into dep_policy values(102,'xyz',20)

1 row(s) inserted.

Statement 16

insert into dep_policy values(101,'xyz',20)

1 row(s) inserted.

Statement 17
```
select *from dep_policy
```
EID NAME AGE
101 abc 20
102 xyz 20
101 xyz 20

3 rows selected.
Statement 18
```
delete from emp where eid=101
```
1 row(s) deleted.
Statement 19
```
select *from dep_policy
```
EID NAME AGE
102 xyz 20
Statement 20
```
select *from emp
```
EID ENAME AGE DESIGNATION SAL
103 c 27 analyst 20000
102 b 26 developer 20000

2 rows selected.
Statement 21
```
drop table emp
```
ORA-02449: unique/primary keys in table referenced by foreign keys
Statement 22
```
drop table dep_policy
```
Table dropped.
Statement 23
```
drop table emp
```
Table dropped.

EID	NAME	AGE
101	abc	20
102	xyz	20
101	xyz	20

EID	NAME	AGE
102	xyz	20

EID	ENAME	AGE	DESIGNATION	SAL
103	c	27	analyst	20000
102	b	26	developer	20000

Statement 24

create table emp(eid int primary key,ename varchar2(20) not null,age int,designation char(20) not null,sal int)

Table created.

Statement 25

insert into emp(eid,ename,age,designation,sal)values(101,'a',25,'programmer',20000)

1 row(s) inserted.

Statement 26

insert into emp(eid,ename,age,designation,sal)values(102,'b',26,'developer',20000)

1 row(s) inserted.

Statement 27

insert into emp(eid,ename,age,designation,sal)values(103,'c',27,'analyst',20000)

1 row(s) inserted.

Statement 28

create table dep_policy(eid int,name char(10),age int,primary key(eid,name),foreign key(eid) references emp(eid) on delete set null)

Table created.

Statement 29

insert into dep_policy values(101,'abc',20)

1 row(s) inserted.

Statement 30

insert into dep_policy values(102,'xyz',20)

1 row(s) inserted.

Statement 31

insert into dep_policy values(103,'xyz',20)

1 row(s) inserted.

Statement 32
```
select *from dep_policy
```
EID NAME AGE
101 abc 20
102 xyz 20
103 xyz 20

3 rows selected.
Statement 33
```
delete from emp where eid=101
```
ORA-01407: cannot update ("SQL_HPHNATQIXIBTMGZEBRAWVVPHV"."DEP_POLICY"."EID") to NULL ORA-06512: at "SYS.DBMS_SQL", line 1721
Statement 34
```
drop table dep_policy
```
Table dropped.
Statement 35
```
drop table emp
```
Table dropped.

EID	NAME	AGE
101	abc	20
102	xyz	20
103	xyz	20

Statement 36

create table emp(eid int primary key,ename varchar2(20) not null,age int,designation char(20) not null,sal int)

Table created.

Statement 37

insert into emp(eid,ename,age,designation,sal)values(101,'a',25,'programmer',20000)

1 row(s) inserted.

Statement 38

insert into emp(eid,ename,age,designation,sal)values(102,'b',26,'developer',20000)

1 row(s) inserted.

Statement 39

insert into emp(eid,ename,age,designation,sal)values(103,'c',27,'analyst',20000)

1 row(s) inserted.

Statement 40
```
select *from emp
```
EID ENAME AGE DESIGNATION SAL
101 a 25 programmer 20000
102 b 26 developer 20000
103 c 27 analyst 20000

3 rows selected.

EID	ENAME	AGE	DESIGNATION	SAL
101	a	25	programmer	20000
102	b	26	developer	20000
103	c	27	analyst	20000

Statement 41

create table contract(eid int default 101,cid int primary key,foreign key(eid) references emp on delete set null)

Table created.

Statement 42
```
insert into contract values(101,201)
```
1 row(s) inserted.
Statement 43
```
insert into contract values(101,202)
```
1 row(s) inserted.
Statement 44
```
select *from contract
```
EID CID
101 201
101 202

2 rows selected.
Statement 45
```
delete from emp where eid=101
```
1 row(s) deleted.
Statement 46
```
select *from contract
```
EID CID
- 201
- 202

2 rows selected.

EID	CID
101	201
101	202

EID	CID
-	201
-	202

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials