Displaying constraints information of a table:-

***********************************************
USER_CONSTRAINTS
USER_CONS_CONSTRAINTS

NOte:
Specify the table name in any case at the time of creation
but internally i maintains the table names in upper case only.

select constraint_name,constraint_type
from USER_CONSTRAINTS
where table_name='STUDENT';

NOTE:
In the above query, the table name should be in capital case.

CONSTRAINT_NAME CONSTRAINT_TYPE
------------------------------ ---------------
SYS_C007050 C -either Not null or Check
SYS_C007051 C
SYS_C007052 C
SYS_C007053 P --Primary key
SYS_C007054 U --Unique key
SYS_C007055 R --Foreign key

CONSTRAINTS WITH user-friendly names:

*************************************

SYN:
<col> datatype(size)
CONSTRAINT <short name> <actual name>

Ex:
rno number(3)
Constraint pk_rno_student Primary key,

create table stud_dtls

(
rno number(2)
constraint pk_rno_stud_dtls primary key,
sname varchar2(10)
constraint nn_sname_stud_dtls not null,
course varchar2(7)
constraint nn_course_stud_dtls not null,
fee number(5)
constraint nn_fee_stud_dtls not null,
mobile number(10)
constraint uk_mobile_stud_dtls unique,
CONSTRAINT ck_rno_stud_dtls
check(rno between 1 and 60),
constraint ck_course_stud_dtls
check (course in('oracle','java','unix')),
 constraint ck_fee_stud_dtls
check(fee between 12000 and 20000),
constraint ck_mobile_length_stud_dtls
check(length(mobile)=10),
constraint ck_mobile_stud_dtls_validity
check(mobile like'7%' or mobile like'8%' or mobile like'9%')
);

Ex:
select constraint_name,constraint_type
from user_constraints
where table_name='STUD';

create table student_dtls

(
rno number(2) constraint pk_rno_student primary key,
constraint ck_rno_student check (rno between 1 and 60),
sname varchar2(20) constraint nn_sname_student not null,
course varchar2(7) constraint nn_course_student not null,
constraint ck_course_student check (course In('cse','ece','eee','it')),
fee number(5) constraint nn_fee_student not null,
constraint ck_fee_student check (fee between 30000 and 40000),
mobile number(10) constraint uk_mobile_student unique
);

select constraint_name,constraint_type from user_constraints where

table_name='STUDENT_DTLS';

CONSTRAINT_NAME CONSTRAINT_TYPE
------------------------------ ---------------
NN_SNAME_STUDENT C
NN_COURSE_STUDENT C
NN_FEE_STUDENT C
CK_RNO_STUDENT C
CK_COURSE_STUDENT C
CK_FEE_STUDENT C
PK_RNO_STUDENT P
UK_MOBILE_STUDENT U

8 rows selected
-----------------------------------------------------------------------------------
-----

2) DOMAIN constraints:
**********************
It is used to define a valid range / valid list of values and any valid condition
on a column by using the keyword CHECK.
CHECK can use any relational operator to write a condition.

syn:
create table <table_name>
(
col1 datatype(size) <Key_constraint_name>,
col2 datatype(size) <Key_constraint_name>,
: : : :,
 CHECK (cond-1),
CHECK (cond-2),
....
....

);

Note:
Defining CHECK with Friendly Name:-

CONSTRAINT <FriendlyName> CHECK(condition)

Ex:

SQL> --create a table "studinfo" like above table,

SQL> --with below rules
SQL> --rno between 1 and 999
SQL> --course names are oracle,unix,java
SQL> --Min fee 12000 and max fee 20000
SQL> --mobile number length should be 10 digit
SQL> --mobile number begin with either 9/8/7/6?
SQL>

create table studinfo

(
rno number(3) primary key,
check(rno between 1 and 100),
sname varchar2(10) not null,
course varchar2(10) not null,
check(course in('oracle','unix','java')),
fee number(5) not null,
check(fee between 12000 and 20000),
mobile number(10) unique,
check(length(mobile)=10),
check(mobile like'9%' or mobile like'8%' or mobile like'7%')
);

Table created.

Ex: create the above table along with below domain

constraints:

--rno between 1 and 60

--course names are oracle, java and unix
--Min fee is 10000 and max fee 20000

create table stud_dtls

(
rno number(3) primary key,
sname varchar2(10) not null,
course varchar2(15) not null,
fee number(5) not null,
mobile char(10) unique,
check (rno between 1 and 100),
check (course in('oracle','java','unix')),
check (fee between 1000 and 2000)
);
CREATING TABLE WITH user friendly
constraint names:-
******************************************

create table stud_dtls

(
rno number(2) constraint pk_rno_stud primary key,
sname varchar2(10) constraint nn_name_stud not null,
course varchar2(15) constraint nn_course_stud not null,
fee number(5) constraint nn_fee_stud not null,
mobile char(10) constraint uk_mobile_stud unique,
constraint ck_rno_stud check (rno between 1 and 60),
constraint ck_course_stud check (course in('oracle','java','unix')),
constraint ck_fee_stud check (fee between 10000 and 20000)
);

Records:

insert into stud_dtls values(1,'a','oracle',7000,1212);

insert into stud_dtls values(0,'b','java',7000,null);
insert into stud_dtls values(61,'a','oracle',7000,null);
insert into stud_dtls values(12,'b','java',7000,null);
insert into stud_dtls values(11,'ajay','unics',10000,2212);
insert into stud_dtls values(21,'hari','unix',17000,1211);
insert into stud_dtls values(21,'hari','unix',11000,1211);

client--Senior employee( developer/admin/ceo)

|
V------->Requirements------>in normal english statements
|
V BDS/BRS/BD
SRS/Design documents
customer account detils--------> Technical terms-----
>cust_act_dtls
products information -----
>Prod_info
customer transactions ------
>cust_trans

CREATING A TABLE WITH USER-FRIENDLY NAMES TO THE CONSTRAINTS:-

create table s_dtls

(
rno number(2) CONSTRAINT PK_RNO_S_DTLS primary key,
sname varchar2(10) CONSTRAINT NN_SNAME_S_DTLS not null,
course varchar2(15) CONSTRAINT NN_COURSE_S_DTLS not null,
fee number(5) CONSTRAINT NN_FEE_S_DTLS not null,
mobile char(10) CONSTRAINT UK_MOB_S_DTLS unique,
CONSTRAINT CK_RNO_S_DTLS check (rno between 1 and 60),
 CONSTRAINT CK_COURSE_S_DTLS check (course in('oracle','sql server','unix')),
CONSTRAINT CK_FEE_S_DTLS check (fee between 10000 and 20000)
);

How do i display constraints information of a table?

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='s_dtls'; ----> this won't work

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='S_DTLS';

CONSTRAINT_NAME CONSTRAINT_TYPE
------------------------------ ---------------
NN_SNAME_S_DTLS C
NN_COURSE_S_DTLS C
NN_FEE_S_DTLS C
CK_RNO_S_DTLS C
CK_COURSE_S_DTLS C
CK_FEE_S_DTLS C
PK_RNO_S_DTLS P
UK_MOB_S_DTLS U

constraint_type Meaning
----------------- -------------

C check or not null

P Primary Key
U Unique key
R Foreign key

Assignment:

i) create customers table with columns custid,custname,city,gender,mailid,phone,

Address with the constraints Pk,NN,NN,NN, and Unique and Unique Respectively.

ii)
create table cust_info
(
custid number(3) primary key,
check(custid between 111 and 999),
custname varchar2(10) not null,
city varchar2(10) not null,
check(city in('hyd')),
gender char not null,
check(gender in('m','f')),
mobile number(10) unique,
check(length(mobile)=10),
check(mobile like'7%' or mobile like'8%' or mobile like'9%')
);

-----------------------------------------------------------------------------------
-

NORMALIZATION AND DENORMALIZATION CONCEPTS:

*******************************************

emp dept
------- --------
eid ename sal dno dname loc
----- ------ ------- ------ ------------ ------
1 a 2000 10 production hyderabad
2 x 1200 20 sales hyderabad
3 a 3400 30 finance chennai
4 z 5000
5 c 1000
6 s 1300
7 d 2300
8 x 1200
9 b 2200

--> How many number of emps working under production dept?

--> Get names of emps from Sales dept?
--> Get highest salary of dept finance?
--> what is dept name of emid 7?
--> What is the working location of emp id 1?

Note:
From above tables, we have "Communication Gap" between data base and end user.
The solution for this problem is as follows.

To answer such kind of requirements we have to maintain the data in 2 methods.

1) Maintain all necessary information in one table

[ DENORMALIZATION ]

2) Maintaining data in different tables and define relation

between the tables.
[ NORMALIZATION ]

1) DENORMALIZED DATA:
Maintaining necessary information in one big table is known as
Denormalized method.

Emp_Dept_Details
----------------------
eid ename sal dno dname loc
----- ----- ----- ------ ----------- -----
1 a 2000 10 production hyderabad
2 x 1200 10 production hyderabad
3 a 3400 10 production hyderabad
4 z 5000 10 production hyderabad
5 c 1000 20 sales hyderabad
6 s 1300 20 sales hyderabad
7 d 2300 20 sales hyderabad
8 x 1200 30 fin chennai
9 b 2200 30 fin chennai

--> How many number of emps working under production dept?

4
--> Get names of emps from Sales dept?
c
s
d
--> Get highest salary of dept finance?
2200
--> what is dept name of emid 7?
sales
--> What is the working location of emp id 1?
hyderabad

Drawbacks:
----------
From the above table we will get the required information,
but it has data duplicacy and
it occupies more disk space and
data search time is very long.

disk space: 6X9=54 Kb

2) NORMALIZATION:
***************
The Process of Dividing big table in to sub tables
until the data duplicacy is maximum reduced is called
normalization process.

i) Ist NF(normal form):

dividing the table into sub tables based on
repeated groups of data.

emp dept
----- ------
eid ename sal dno dname loc
--- ----- ---- ------ ------ ----
1 a 2000 10 production hyderabad
2 x 1200 10 production hyderabad
3 a 3400 10 production hyderabad
4 z 5000 10 production hyderabad
5 c 1000 20 sales hyderabad
6 s 1300 20 sales hyderabad
7 d 2300 20 sales hyderabad
8 x 1200 30 fin chennai
9 b 2200 30 fin chennai

Ex:

Emp Table:

create table emp

as
select eid,ename,sal from emp_dept_details;

DEPT table:

create table dept

as
select dno,dname,loc from emp_dept_details;

ii) IInd NF:

Eleminating duplicate records and defining
primary keys in the above tables.

emp dept
----- -----
eid ename sal dno dname loc
----- ------- ---- ------ ----------- ----
1 a 2000 10 production hyderabad
2 x 1200 20 sales hyderabad
3 a 3400 30 fin chennai
4 z 5000 PK
5 c 1000
6 s 1300
7 d 2300
8 x 1200
9 b 2200
|
V
PK

Ex: Eleminating Duplicate records

create table dept1

as
select distinct dno,dname,loc from dept;

iii) IIIrd NF: [ Boyce Codd Normal Form ] (BCNF)

we can define Physical relation between tables.

By using Primary key of a table , we can define foreign key
in other table.

"From parent PK , we can define FK in the child table."

emp dept
----- ------
eid ename sal dno dno dname loc
----- ------ ------ ---- ------- ----------- ------
1 a 2000 10 10 production hyderabad
2 x 1200 10 20 sales hyderabad
3 a 3400 10 30 fin chennai
4 z 5000 10 PK
5 c 1000 20
6 s 1300 20
7 d 2300 20
8 x 1200 30
9 b 2200 30
PK FK

--> How many number of emps working under production dept?

4
--> Get names of emps from Sales dept?
c
s
d

--> Get highest salary of dept finance?

2200
--> what is dept name of emid 7?
sales
--> What is the working location of emp id 1?
hyderabad

It occupies less disk space and max. data duplicacy is reduced.

disk space: 4X9=36

3X3=9

45KB

Advantages:
--Searching for required data is as much as fast
--And data retrieval is fast
--Max data duplicacy is eleminated.
--Occupy less Disk space.

-----------------------------------------------------------------------------------

3) REFERENTIAL INTEGRITY CONSTRAINT

/ REF CONSTRAINT:

**Used to implement PHYSICAL relation between tables by

using primary key of parent table, we can define foriegn key in
child table.

**Foriegn key column contains only values from primary key.

Foriegn key contains duplicates and null values also.

--A table which has primary key is considered as

Parent/Master/Base table.
--A table which has foriegn key is known as
Child/Detailed/Derived table.

REFERENCES:
***********
we can use this keyword in the
"creation of child table and to
define foriegn key column" as follows.
syn:
Foreign key column defenition:-

<col_name> data_type(size)
REFERENCES
<parent_table>(Primary_Key_column)

Note:
Generally , foreign key column name and
primary key column name are similar.
And their datatypes are maintained as same.

ON DELETE CASCADE
-----------------
It is a keyword and it can be defined with FOREIGN KEY constraint.
It allows the user to delete parent table records.
As an effect the dependent child records are deleted automatically.

SYNTAX:
For Foriegn key constraint implementation

constraint <friendly_name>
FOREIGN KEY(ChildTablecolumn name)
REFERENCES <Parent_Table>(Primary_key)
[ON DELETE CASCADE]

Examples:

Ex: create comp_dtls as parent table

Ex: create prod_dtls as child table

--------------------------------------------------------------
create table comp_dtls
(
cmpid char(5)
constraint pk_cmpid_cmp primary key,
cmpname varchar2(20) not null,
Country varchar2(20) not null
);
--------------------------------------------------------------
create table prod_dtls
(
pid char(4) primary key,
pname varchar2(20) not null,
cost number(7,2),
mfg date,
warrenty varchar2(10),
cmpid char(5)
references comp_dtls(cmpid)
);
--------------------------------------------------------------

create table comp_dtls

(
comp_code char(5) constraint pk_cmpid_comp_dtls primary key,
comp_Name varchar2(20) not null,
Country varchar2(20) not null,
constraint
ck_country_cmp check (country IN('india','usa','japan','uk'))
);

insert into comp_dtls values('cmp01','sony','japan');

insert into comp_dtls values('cmp02','wipro','india') ;
insert into comp_dtls values('cmp03','Philips','india');
insert into comp_dtls values('cmp04','semantic','usa');

create table prod_dtls

(
pid char(4) primary key,
pname varchar2(20) not null,
cost number(7,2),
mfg date,
warrenty varchar2(10),
comp_code char(5),
constraint fk_prod_cmpid
FOREIGN KEY(comp_code) REFERENCES comp_dtls(comp_code)
/* foriegn key column */
on delete cascade
);

insert into prod_dtls values

('p001','smart phone',34000,'12-may-14','1 year','cmp01');
insert into prod_dtls values
('p002','laptop',54000,'03-feb-14','3 years','cmp01');
insert into prod_dtls values
('p003','Television',24000,'08-aug-14','5 years','cmp03');
insert into prod_dtls values
('p004','Home Theatre',55000,'11-aug-13','2 years','cmp03');
insert into prod_dtls values
('p005','Mobile',24000,'08-aug-14','1 year',null);
insert into prod_dtls values
('p006','vmware',64000,'22-oct-10','1 year','cmp04');
=============================================

create table dept

(
dno char(2) constraint pk_dno_dept primary key,
dname varchar2(20) constraint nn_dname_dept not null
);

create table emp

(
eid number(2) constraint pk_eid_emp primary key,
name varchar2(10) constraint nn_name_emp not null,
sal number(5) ,
dno char(2),
constraint fk_dno_emp FOREIGN KEY(dno)
references dept(dno)
);
---------------------------------------------------------------------------------

create table act_types

(
act_type varchar2(10) primary key,
act_name varchar2(20) not null
);

create table customers

(
cid number(5) primary key,
cname varchar2(20) not null,
city varchar2(10) not null,
gender char,
mobile number(10) unique,
mailid varchar2(30),
idproof varchar2(10),
pancard char(10)
);

create table cust_act_dtls

(
actno number(14) primary key,
Open_dt date,
Bal number(7,2),
act_type varchar2(10),
constraint fk_act_type_cad FOREIGN KEY(act_type)
references act_types(act_type),
cid number(5),
constraint fk_cid_cad FOREIGN KEY(cid)
references customers(cid)
);

-----------------------------------------------------------------------------------
--------------
ADDING / DELETING / CHANGE CONSTRAINTS
on existing table:-

--we can add constraints

--we can delete constraints
--we can change constraints

By using "ALTER" command we can add/remove/change

constraints on the existed table.

1) How to add constraints?

For adding constraints (pk,fk,ck and uk)

Keyword:
ADD CONSTRAINT

SYN-1
To add pk

alter table <table_name>

ADD
CONSTRAINT <friendly name> PRIMARY KEY(colname);

SYN-2
To add UK
 alter table <table_name>
ADD
CONSTRAINT <friendly name> UNIQUE(colname);

SYN-3
To add CK

alter table <table_name>

ADD
constraint <friendly name> CHECK(col <condition>);

syn-4:-
To add FK

alter table <table_name>

ADD
CONSTRAINT <friendly name> FOREIGN KEY(child_colname)
REFERENCES <parent>(pk_col)
[ on delete cascade ];

How to add NOT NULL constraint?

Keyword:
MODIFY

syn:
alter table <name>
MODIFY col datatype(size) NOT NULL;
OR

alter table <name>

MODIFY col datatype(size)
constraint <friendly name> NOT NULL;

How to delete NOT NULL constraint?

syn:
alter table <name>
MODIFY col datatype(size) NULL;

How to delete any constraint?

syn:-

alter table <name>

DROP CONSTRAINT <Friendly_Name/sys_cxxxxxx>;

How to change constraint?

a) delete old constraint from the column.

b) Add new constraint on the column.
Ex: alter table cust
add constraint pk_cid_cust primary key(cid);

Ex: alter table cust

add constraint uk_mobile_cust unique(mobile);

Ex: alter table cust

add
constraint ck_city_cust check(city in('hyd','blore','chennai'));

Ex:
alter table prod_dtls
add
constraint fk_cmpid_prod FOREIGN KEY(cmpid)
references comp_dtls(cmpid);

Ex: alter table cust

modify cname varchar2(10) constraint nn_cname_cust not null;
-----------------------------------------------------------------------------------
------
ENABLE / DISABLE constraints:
*****************************
If we add any constraint, then by default constraint is in
ENABLE state.

How to ENABLE constraint?

syn:
alter table <table_name>
[ENABLE / DISABLE ] CONSTRAINT <const_name>;

Ex: alter table prod_dtls

disable constraint pk_pid_prod;

Ex: alter table prod_dtls

enable constraint fk_cmpid_prod;

ON DELETE CASCADE:

Ex: adding "on delete cascade" on cmpid in prod table?

a) alter table prod_dtls

drop constraint fk_cmpid_prod;

b) alter table prod_dtls

add constraint fk_cmpid_prod FOREIGN key(cmpid)
references comp_dtls(cmpid)
on delete cascade;

--> DEFERRABLE CONSTRAINT:

 It can be enabled even column is having invalid data.
syn:
colname datatype(size)
constraint <friendly name> <name> DEFERRABLE

--> How to enable deferrable constraint?

syn:
alter table <name>
ENABLE NOVALIDATE CONSTRAINT <constname>;

Example:
create table customers
(
custid number(4) constraint pk_custid_cust primary key DEFERRABLE,
---
---
);

---> DISABLE DEFERRABLE constraints

alter table customers

disable constraint pk_custid_cust;

---> load your old data into above table

SQL * LOADER

---> Enable pk
alter table customers
enable NOVALIDATE constraint pk_custid_cust;

--> from this moment onwards it will not accept any invalid data.

Ex:
create the following tables and Implement relationships
accordingly?

1) Cust_dtls ( parent table)

cno cname city gender mobile

|
Primary key

2) Act_types ( parent table)

Act_type act_name desc

|
SB Savings Bank
DEMAT Trading account
CA Current account

Primary key
3) cust_act_dtls (Child table)

Actno Act_type Act_open_date Act_bal cno

PK Foreign key Foreign key

-----------------------------------------------------------------------------------
------
SEQUENCE:
*********
It is a data base object which is used to generate
sequential integers with the specified interval value.
Generally the sequences are used to generate primary
key values.

syn: create sequence <seq_name>

[start with <val>]
[increment by <val>]
[maxvalue <val> | nomaxvalue ]
[minvalue <val> | nominvalue ]
[cache |no cache]
[cycle |no cycle];

Note:
By default the sequence starts with 1 and increment
value is also 1.

** Sequence is an independent object.

( It is not depending on any table )

Uses:
-->we can insert sequence values in to table column.
-->We can update table column values with sequence values.
-->In Oracle 12c, we can define sequence values as a DEFAULT value
in to a column.

Ex: create sequence srno;

Pseudo columns:
***************

CURRVAL To get current value of the sequence

NEXTVAL To get nextvalue of sequence

Ex: select srno1.nextval from dual;

NEXTVAL
----------
1

Ex: select srno1.currval from dual;

CURRVAL
----------
1

Ex: create a sequence starts with 1001?

create sequence custno start with 1001;

Ex:
update the cust_dtls table under that update
the cust_srno column with sequence values?

update cust_dtls set cust_srno=srno1.nextval;

Ex: adding a primary key constraint on the column cust_srno?

alter table cust_dtls add constraint pk_custsrno primary key(cust_srno);

Ex: Insert new record in the table cust_dtls along with sequence values?

insert into cust_dtls

values(4,'abc','hyd',6767676765,'F',null,srno1.nextval);
**************

Ex:

create table stud

2 (
3 studid number(3) DEFAULT srno.nextval,
4 sname varchar2(10),
5 course varchar2(10) DEFAULT 'oracle',
6 fee number(4) default '2000'
7 );

Hands-on:

CREATE TABLE CUSTOMERS

(
ID INT,
NAME VARCHAR2(20),
SRNO NUMBER)

CREATE SEQUENCE ID START WITH 6600 INCREMENT BY 5

CREATE SEQUENCE SRNO

INSERT INTO CUSTOMERS VALUES(ID.NEXTVAL,'AJAY',NULL);

INSERT INTO CUSTOMERS VALUES(ID.NEXTVAL,'KIRAN',NULL);
INSERT INTO CUSTOMERS VALUES(ID.NEXTVAL,'MADHU',NULL);

SELECT * FROM CUSTOMERS

UPDATE CUSTOMERS SET SRNO=SRNO.NEXTVAL

results:

CREATE TABLE succeeded.

CREATE SEQUENCE succeeded.
CREATE SEQUENCE succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
ID NAME SRNO
---------------------- -------------------- ----------------------
6605 AJAY
6610 KIRAN
6615 MADHU

3 rows selected

3 rows updated
ID NAME SRNO
---------------------- -------------------- ----------------------
6605 AJAY 1
6610 KIRAN 2
6615 MADHU 3

3 rows selected

Ex:

CREATE SEQUENCE t1_seq;

CREATE TABLE t1 (
id NUMBER DEFAULT t1_seq.NEXTVAL,
description VARCHAR2(30)
);

INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

INSERT INTO t1 (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
INSERT INTO t1 (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

SELECT * FROM t1;

ID DESCRIPTION
---------- ------------------------------
1 DESCRIPTION only
999 ID=999 and DESCRIPTION
ID=NULL and DESCRIPTION

3 rows selected.

HOW TO DELETE THE SEQUENCE?

Ex: drop sequence srno;

system table:

user_sequences
select sequence_name from user_sequences;

-----------------------------------------------------------------------------------
------
DEFAULT:
*********
It is used to define default value into a column.
IF we will not insert any value into default column, then
default value inserted into the column.
Default column also accept different values and null values.

syn:

colname datatype(size) DEFAULT 'value'

New Feature:(Introduced in 12 c )
-----------------------------------------------------------------------------------
-
We can also define a default value into a column
from sequence.

1) create any sequence

create sequence custid

start with 1111;

2) define defualt value from sequence into column

colname datatype(size) DEFAULT seqname.nextval

Ex:

create a table cust with columns cid,cname,gender,city?

cid values are taken from sequence and
default value inthe city column is DELHI?

create sequence custid;

create table cust

(
cid number(4) DEFAULT custid.nextval,
cname varchar2(20),
gender char,
city varchar2(10) DEFAULT 'DELHI'
);

-----------------------------------------------------------------------------------
------
MERGE statement:
----------------
Merge statement is used to do "upsert"
i.e. update existing rows in a table or
insert new rows depending on a match condition.
This is known as synchronize a table periodically
with data from another source(table/view/query).

Syntax:
MERGE into <target table>
USING
<source table/view/result of subquery>
ON
<match condition>
WHEN MATCHED THEN
<update clause>
<delete clause>
WHEN NOT MATCHED THEN
<insert clause>;

Example:
--Create student table
create table student
(
stid number(3),
sname varchar2(14),
score number(3)
);

insert into student(stid,sname,score)

values
(111,'jack',890);
insert into student(stid,sname,score)
values
(222,'aarya',620);
insert into student(stid,sname,score)
values
(333,'vivek',750);

--Create a source table results

create table results

(
stid number(3),
sname varchar2(14),
score number(3)
);

insert into results(stid,sname,score)

values
(444,'harini',780);
insert into results(stid,sname,score)
values
(222,'aarya',910);
insert into results(stid,sname,score)
values
(333,'vivek oberai',840);

Req:
Merge RESULTS data into target table STUDENT

merge into student a

using
(select stid, sname, score
from results) b
on (a.stid = b.stid)
 when matched then
update
set a.sname = b.sname,
a.score = b.score
when not matched then
insert (a.stid, a.sname, a.score)
values (b.stid, b.sname, b.score)
/