Data manipulation languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are:
1. SELECT ... FROM .. WHERE
SELECT is used to retrieve data from one or more table and can combine with union statement or subquery. Below basic syntax from select statement :
SELECT
[ALL | DISTINCT ]
select_expr
[, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name
| expr | position}]
[HAVING where_condition]
[ORDER BY {col_name
| expr | position}
[ASC | DESC], ...]
Notes :
Theselect_exprindicates a column that you want to retrieve. There must be at least oneselect_expThetable_referencesindicates the table or tables from which to retrieve rows.- The
whereclause, if given, indicates the condition or conditions that rows must satisfy to be selected. - The having clause is used in combination with the GROUP BY Clause to restrict the groups of returned rows to only those whose the condition is TRUE.
SELECT location, sum(sales_quantity),
FROM order
WHERE location in ('Jakarta','Bogor','Depok','Tangerang')
GROUP BY location
HAVING sum(sales_quantity)
ORDER BY location DESC
2. INSERT .. INTO .. VALUES
The Oracle INSERT statement is used to insert data into a table by inserting one or more rows of data. Below the basic syntax of INSERT statement :
INSERT INTO <table name> (col1, col2, col3,…)
VALUES (val1, val2, val3,…)
Example :
INSERT INTO sales (sales_id, sales_number, item, quantity, unit_price, item_type) VALUES (3, 'SO3', 'Aqua2', 30, 2100, 'wtr')
3. UPDATE .. SET .. WHERE
The Oracle UPDATE statement is used to change one or more column data of the table for which the where clause evaluates to TRUE. Below the basic syntax of UPDATE statement :
UPDATE table-Name [[AS] correlation-Name]
SET column-Name = Value [ , column-Name = Value} ]*
[WHERE clause]
Example :
UPDATE sales
SET item = 'Akua1'
WHERE sales_id = 1
4. DELETE .. FROM .. WHEREThe oracle DELETE statement is used to remove entire rows of data from specified table or view. Below the basic syntax of DELETE statement :
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM
tbl_name
[WHERE where_condition]
[WHERE where_condition]
Example :
DELETE FROM sales
WHERE sales_id = 3
The oracle MERGE statement is used to select rows from one or more sources for update or insertion into a table or view. This statement is a convenient way to combine multiple operations. It lets you avoid multiple
INSERT, UPDATE, and DELETE DML statements. Below the basic syntax of MERGE statement :
MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause> / DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
WHEN NOT MATCHED THEN <insert_clause>
Example :
MERGE INTO ynppo_temp_employee yte
USING (SELECT person_id,
employee_number, first_name
FROM
per_all_people_f
WHERE TRUNC
(SYSDATE) BETWEEN effective_start_date
AND effective_end_date
AND
employee_number = '01962') papf
ON (yte.employee_code = papf.employee_number)
WHEN MATCHED THEN
UPDATE
SET first_name =
'Borokotok'
WHEN NOT MATCHED THEN
INSERT (employee_code,
first_name)
VALUES (papf.employee_number,
papf.first_name)
