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!

2 comments

  1. you have written an excellent blog. I learned something new from your Blog. Keep sharing valuable information.
    Oracle Training in Chennai
    Oracle Training in Bangalore

    ReplyDelete
  2. For me, this is one of the most important bits of knowledge. It was also a joy reading your essay. But there are a few general remarks I'd like to make: the website design is fantastic, and the content is fantastic. Excellent work. If you're a gamer, you should check out this profile Kohi clicking. You can use it to see how fast you click. The Kohi click test is a simple way to enhance your clicking speed.

    ReplyDelete

Powered by Blogger.