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.

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

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

More Details: https://docs.oracle.com/error-help/db/ora-02292

Statement 12
```
drop table dep_policy
```
```
Table dropped.
```

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.

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

Statement 18

delete from emp where eid=101

1 row(s) deleted.

Statement 19

select *from dep_policy


EID NAME AGE
102 xyz       20

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.

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

Statement 21

drop table emp

ORA-02449: unique/primary keys in table referenced by foreign keys

More Details: https://docs.oracle.com/error-help/db/ora-02449

Statement 22
```
drop table dep_policy
```
```
Table dropped.
```
Statement 23
```
drop table emp
```
```
Table dropped.
```

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.

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

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

More Details: https://docs.oracle.com/error-help/db/ora-01407

Statement 34
```
drop table dep_policy
```
```
Table dropped.
```
Statement 35
```
drop table emp
```
```
Table dropped.
```

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.

EID	CID
101	201
101	202

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
-	201
-	202

Additional Information

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