07. DML (DATA MANIPULATION OR MODIFICATION LANGUAGE)

    DML(DATA MANUPULATION OR MODIFICATION LANGUAGE):

•     We use  it  to manipulate the data
•            They are session dependent
•     They are the temporary transactions
•    Explicitly we have to make this transactions permanently
•           They are non-auto commit commands
• 
  
•          INSERT
•         UPDATE
•         DELETE
•         MERGE(INSERT+UPDATE+DELETE)
•         SELECT(DRL OR DQL)

INSERT     

·         VALUE METHOD

·         REFRENCE METHOD

·         SELECT METHOD

·         VALUE METHOD

  

SYN: INSERT INTO TABLENAME [(COL1, COL2,..)] VALUES (VAL1, VAL2,)

EG: INSERT INTO NEWTAB VALUES (10,’A’);

                                

      INSERT INTO NEWTAB (SNO) VALUES (11);

·         REFRENCE METHOD: Ampersand (&) 

Which allows us to provide values from client to database?

SYN: INSERT INTO TABLENAME [(COL1, COL2)] VALUES (&N, &M);

EG: INSERT INTO NEWTAB (SNO, SNAME) VALUES (&N, &M);

·         SELECT METHOD:

SYN: INSERT INTO TABLENAME SELECT …..

EG: CREATE TABLE TB1 AS SELECT * FROM TB;

      

      CREATE TABLE TAB2 AS SELECT * FROM TB WHERE 1=2;

   SELECT * FROM TAB2;

   DESC TB2;

   INSERT INTO TB2 SELECT * FROM TB;

   INSERT INTO T1 VALUES SELECT * FROM T: ERROR;

   INSERT INTO T SELECT * FROM T;

   INSERT INTO T1 (C3) SELECT C2 FROM T;

   INSERT INTO T1 VALUES (10, SELECT C2 FROM T): ERROR;

   INSERT INTO T1 VALUES (10, (SELECT C2 FROM T WHERE C1=1));

UPDATE:

          SYN: UPDATE TABLENAME SET COL=VAL [, COL1=VALUES,…]

                                                                  [WHERE CONDITION];

          EG: UPDATE NEWTAB SET SNAME=’B’ WHERE SNO=10;

                UPDATE NEWTAB SET SNAME=NULL;

                UPDATE T SET C2= (SELECT C3 FROM T1);

                UPDATE T SET C2= (SELECT C2 FROM T WHERE C1=2);

 DELETE: To delete the records.

         SYN:  DELETE FROM TABLENAME [WHERE CONDITION];

           EG: DELETE FROM NEWTAB;

                 DELETE FROM NEWTAB WHERE SNO=10;

 TRUNCATE:

           SYN: TRUNCATE TABLE TABLENAME;

ü  It is used to delete the records permanently unlike deletion.

ü  It is auto commit command.

ü  It is handling only data not the structure.

ü  Not possible to delete the records specifically.

DELETION:

• Delete the records temporarily.
• Possible to delete specific records.