recent

Titulo

Data Manipulation Language

Data Manipulation language (DML) statements access and manipulate data in existing table, view or in a materialized view. The effect of a DML statement is not permanent until you commit the transaction. A transaction is a sequence of SQL statements that Oracle database treats as an unit. Until a transaction is committed, it can’t be rolled back.  SELECT, INSERT, UPDATE, DELETE,  and MERGE are DML statements.

SELECT: The SELECT statement allows you to retrieve data from tables. The user can view all the fields or just certain fields from the table.

Examples:
SELECT * 
FROM   employee; 

SELECT fname 
FROM   employee;

SELECT DISTINCT 
from   employee;

SELECT UNIQUE 
FROM   employee; 

SELECT fname AS first_name 
FROM   employee; 

SELECT fname 
       || lname 
FROM   employee;

INSERT: The insert statement insert rows into an existing table. The simplest recommended form of the insert statement has this syntax

INSERT INTO EMPLOYEES (
  EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  EMAIL,
  PHONE_NUMBER,
  HIRE_DATE,
  JOB_ID,
  SALARY,
  COMMISSION_PCT,
  MANAGER_ID,
  DEPARTMENT_ID
)
VALUES (
  10,              -- EMPLOYEE_ID
  'George',        -- FIRST_NAME
  'Gordon',        -- LAST_NAME
  'GGORDON',       -- EMAIL
  '650.506.2222',  -- PHONE_NUMBER
  '01-JAN-07',     -- HIRE_DATE
  'SA_REP',        -- JOB_ID
  9000,            -- SALARY
  .1,              -- COMMISSION_PCT
  148,             -- MANAGER_ID
  80               -- DEPARTMENT_ID
);

UPDATE: The UPDATE statement updates the value of a set of existing table or mview rows. A simple form of the UPDATE statement has this syntax:

UPDATE table_name
SET column_name = value [, column_name = value]...
[ WHERE condition ];

UPDATE EMPLOYEES
SET SALARY = 2500
WHERE LAST_NAME = 'smith';

DELETE: The DELETE statement deletes row(s) from a table. A simple form of the DELETE statement has this syntax:

DELETE FROM table_name
[ WHERE condition ];

Example
DELETE FROM EMPLOYEES
WHERE HIRE_DATE = '01-JAN-07';

Note: If you include the WHERE clause, the statement deletes only row that satisfy condition. If you omit the WHERE clause, the statement deletes all rows from the table, but the empty table still exists. To delete a table, use the DROP table statement.

MERGE: Did you know MERGE is a DML statement also? I didn't know  about merge statement for a while. Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. You can search for MERGE on my site if you want to learn more on MERGE.


Interested in working with me? I can be reached at pbaniya04[at]gmail.com for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

No comments

Powered by Blogger.