Data Manipulation Language

A data manipulation language (DML) is a family of computer languages including commands permitting users to manipulate data in a database. This manipulation involves inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data. DML is mostly incorporated in SQL databases.
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 :

• The select_expr indicates a column that you want to retrieve. There must be at least one select_exp 
• The table_references indicates the table or tables from which to retrieve rows.
• The where clause, 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.

For example :
        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 .. WHERE
The 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]

Example :

DELETE FROM sales

      WHERE sales_id = 3

5. MERGE .. INTO .. USING
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>

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)