Data Manipulation Language (DML) Statements

      DML statements are used for managing data within schema objects like retrieving, inserting, update and delete etc.

INSERT : Insert statement is used to insert the data into a table.

Syntax :

  1. insert into table_name(column1,..,columnn) values(value1,..,value2);
  2.  insert into table_name values(value1,value2,…,value4);

We can use #1 syntax to insert the data into particular fields or columns and #2 syntax used insert data for all fields or columns. 

For #1, we need to provide the values for mentioned  fields or columns in “table ()”.

For #2, we need to provide values for all fields or columns.  

SQL> CREATE TABLE STUDENT(SI_NO NUMBER(6),FULL_NAME VARCHAR2(30),BRANCH CHAR(4),
SECTION CHAR(1),PERCENTAGE NUMBER(4,2),GRADE CHAR(1));

Table created.

SQL>
SQL>
SQL>
SQL> INSERT INTO STUDENT(SI_NO,FULL_NAME,BRANCH,SECTION) VALUES(101,'AAA','CSE',
'A');

1 row created.

SQL>
SQL> SELECT SI_NO,FULL_NAME,BRANCH,SECTION FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S
---------- ------------------------------ ---- -
 101       AAA                            CSE  A

SQL>
SQL> INSERT INTO STUDENT VALUES(102,'AAB','CSE','A',74.90,'B');

1 row created.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A
 102       AAB                            CSE  A 74.9       B

SQL>
SQL>
SQL>
SQL> INSERT INTO STUDENT VALUES(&SI_NO,&FULL_NAME,&BRANCH,&SECTION,&PERCENTAGE,
&GRADE);
Enter value for si_no: 103
Enter value for full_name: 'AAC'
Enter value for branch: 'CSE'
Enter value for section: 'A'
Enter value for percentage: 75.20
Enter value for grade: 'A'
old 1: INSERT INTO STUDENT VALUES(&SI_NO,&FULL_NAME,&BRANCH,&SECTION,&PERCENTAGE,&GRADE)
new 1: INSERT INTO STUDENT VALUES(103,'AAC','CSE','A',75.20,'A')

1 row created.

SQL> /
Enter value for si_no: 104
Enter value for full_name: 'AAD'
Enter value for branch: 'CSE'
Enter value for section: 'A'
Enter value for percentage: 65.90
Enter value for grade: 'C'
old 1: INSERT INTO STUDENT VALUES(&SI_NO,&FULL_NAME,&BRANCH,&SECTION,&PERCENTAGE,
&GRADE)
new 1: INSERT INTO STUDENT VALUES(104,'AAD','CSE','A',65.90,'C')

1 row created.

SQL> INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',
&PERCENTAGE,'&GRADE');
Enter value for si_no: 105
Enter value for full_name: AAE
Enter value for branch: CSE
Enter value for section: A
Enter value for percentage: 76.00
Enter value for grade: A
old 1: INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE')
new 1: INSERT INTO STUDENT VALUES(105,'AAE','CSE','A',76.00,'A')

1 row created.

SQL>

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 105       AAE                            CSE  A 76         A

SQL>

We can use “&” char in values statement to provide values manually out side of the query. If we want into insert multiple records this will very help full.

We can repeat the same query by ” / ” char and then enter. It will execute previous query.

Note : For string and date values, we must enclose the values by single quote or double quote. 

Note : In above query using “&”, we can enclose fields in values statement(‘&FULL_NAME) or while entering the values (‘AAA’).

UPDATE : It is used to update existing records in a table.

Syntax : update table_name set field=value  where conditions;

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 105       AAE                            CSE  A 76         A

SQL>
SQL>
SQL>
SQL> UPDATE STUDENT SET PERCENTAGE=85.09 WHERE SI_NO=101;

1 row updated.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 105       AAE                            CSE  A 76         A

SQL>
SQL>
SQL> UPDATE STUDENT SET GRADE='A' WHERE PERCENTAGE>75;

3 rows updated.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09      A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 105       AAE                            CSE  A 76         A

SQL>

Update multiple fields.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09      A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 105       AAE                            CSE  A 76         A

SQL>


SQL> SQL> SQL>
SQL>
SQL> UPDATE STUDENT SET SI_NO=165,BRANCH='IT',SECTION='B' WHERE FULL_NAME='AAE';

1 row updated.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09      A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 165       AAE                            IT   B 76         A

SQL>

Note : We can use update command with out where condition. But it is very risky, it updates all records in the table. If auto commit enabled, we can’t rollback it.

DELETE: It is used to delete records in a table. It never delete allocated space.

Syntax : delete from table_name [where conditions],

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09      A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 165       AAE                            IT   B 76         A

SQL>
SQL> DELETE FROM STUDENT WHERE SI_NO=103;

1 row deleted.

SQL>
SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09      A
 102       AAB                            CSE  A 74.9       B
 104       AAD                            CSE  A 65.9       C
 165       AAE                            IT   B 76         A

SQL>

Delete all records from table.

SQL> DELETE FROM STUDENT;

4 rows deleted.

SQL> SELECT *FROM STUDENT;

no rows selected

SQL>

MERGE : Use this statement to select rows from one or more source tables for update or insertion into a another table (target table).

SQL> CREATE TABLE EMP_BONUS (EMP_ID NUMBER,BONUS NUMBER DEFAULT 100);

Table created.

SQL>
SQL> INSERT INTO EMP_BONUS(EMP_ID) (SELECT EMPNO FROM EMPLOYEES);

14 rows created.

SQL>
SQL> SELECT * FROM EMP_BONUS;

EMP_ID     BONUS
---------- ----------
 7369      100
 7499      100
 7521      100
 7566      100
 7654      100
 7698      100
 7782      100
 7788      100
 7839      100
 7844      100
 7876      100

EMP_ID     BONUS
---------- ----------
 7900      100
 7902      100
 7934      100

14 rows selected.

SQL>
SQL> MERGE INTO EMP_BONUS B
 USING (SELECT * FROM EMPLOYEES) S
 ON (B.EMP_ID = S.EMPNO)
 WHEN MATCHED THEN UPDATE SET B.BONUS = B.BONUS + S.SAL*.01
 DELETE WHERE (S.SAL > 2000)
 WHEN NOT MATCHED THEN INSERT (B.EMP_ID, B.BONUS)
 VALUES (S.EMPNO, S.EMPNO*0.1)
 WHERE (S.SAL <= 2000); 2 3 4 5 6 7 8 14 rows merged. SQL> select * from EMP_BONUS;

EMP_ID     BONUS
---------- ----------
 7369      108
 7499      116
 7521      112.5
 7654      112.5
 7844      115
 7876      111
 7900      109.5
 7934      113

8 rows selected.

SQL>
SQL> select d.EMP_ID,e.sal,d.BONUS from EMP_BONUS d,EMPLOYEES e where 
d.EMP_ID=e.EMPNO;

 EMP_ID    SAL        BONUS
---------- ---------- ----------
 7369      800        108
 7499      1600       116
 7521      1250       112.5
 7654      1250       112.5
 7844      1500       115
 7876      1100       111
 7900      950        109.5
 7934      1300       113

8 rows selected.

SQL>

Here, We have increased the bonus  to 1% for employees whose salary less than 2000 and deleted entries of whose salary greater than 2000.

LOCK TABLE : Lock one or more tables in a specified mode. Table access denied to a other users until your table operation completed.

Syntax : LOCK TABLE table_name IN [ SHARE | EXCLUSIVE ] MODE

SQL> LOCK TABLE EMPLOYEES IN SHARE MODE NOWAIT;

Table(s) Locked.

SQL>

Click here for more on LOCK TABLE

CALL :It is used to execute a stored procedure or a function within SQL.

SQL> CALL my_procedure()

We will learn more about Stored procedures in next chapters.

EXPLAIN PLAN : The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. 

SQL> explain plan for select * from EMPLOYEES;

Explained.

SQL> 
SQL> 
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------
|  0  | SELECT STATEMENT  |           |   14 |   532 |       3 (0)|  00:00:01 |
|  1  |  TABLE ACCESS FULL| EMPLOYEES |   14 |   532 |       3 (0)|  00:00:01 |
-------------------------------------------------------------------------------

8 rows selected.

SQL>
Advertisements