Oracle SQL Statement Types and Usage - Part II

Data Retrieval or Query Language (DQL or DRL) :

SELECT clause will come under this category. Main usage of select to identify the result set column list.

Syntax:
select * from table_name;

select column1,column2…
from table_name;

Example:
select *from dept;

======================

Data Manipulation Language (DML) 

These statements are used to manage the data with database objects.

INSERT:

insert data into a table :

Syntax:

INSERT INTO <table_name>
(<column_name>)
VALUES
(<value>);

Example 1:

INSERT INTO DEPT VALUES (10, ‘ACCOUNTING’, ‘NEW YORK’);

Example 2:
INSERT INTO EMP VALUES
(7369, ‘SMITH’, ‘CLERK’, 7902,
TO_DATE(’17-DEC-1980′, ‘DD-MON-YYYY’), 800, NULL, 20);

UPDATE:
updates existing data within a table.

Syntax :
UPDATE <table_name>
SET <column_name> = <value>
WHERE <column_name> = <value>;

Examples:

update emp
set sal=sal+2000;

it will update all records of emp, but it’s suggestible way in real time, surely updation can be done based on some condition.

update emp
set sal=sal+2000
where empno=7788;

DELETE:
deletes all records from a table :

Syntax :
delete
from table_name
where <column_name>=<value>;

Examples:

delete
from emp
where deptno=10;

MERGE:
Primary usage merge when moving large amounts of data from one table to another table. This kind work can be done in data warehouse.

Syntax:

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;

Example:

CREATE TABLE employee (
employee_id NUMBER(5),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
dept_no NUMBER(2),
salary NUMBER(10));

INSERT INTO employee VALUES (1, ‘Dan’, ‘Morgan’, 10, 100000);
INSERT INTO employee VALUES (2, ‘Jack’, ‘Cline’, 20, 100000);
COMMIT;

CREATE TABLE bonuses (
employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses (employee_id) VALUES (1);
INSERT INTO bonuses (employee_id) VALUES (2);
COMMIT;

MERGE INTO bonuses B
USING (
SELECT employee_id, salary
FROM employee
WHERE dept_no =20) E
ON (B.employee_id = E.employee_id)
WHEN MATCHED THEN
UPDATE SET B.bonus = E.salary * 0.1
WHEN NOT MATCHED THEN
INSERT (B.employee_id, B.bonus)
VALUES (E.employee_id, E.salary * 0.05);