DML - Data Manipulation Language

         TO manipulate data stored in database we use DML commands.

DML defines the following Commands:

• INSERT 
• UPDATE
• DELETE
• LOCK

Let's understand each DML command one by one:

1. INSERT:

        INSERT command is used to insert data or tuple in a table.

We can insert data into a table using the following ways:

    a. Insert by specifying column name and values as:

          Syntax:

                 INSERT INTO table_name(col1, col2, ...) VALUES (val1, val2, ...);

    b. Insert without specifying column name as:

           Syntax:

                   INSERT INTO table_name VALUES (val1, val2, ....);

Example: Let's insert some values in our Employee table

    CREATE TABLE Employee(

                    emp_id INT NOT NULL PRIMARY KEY,

                    emp_name VARCHAR(20),

                    dept VARCHAR(20),

                    salary FLOAT

     );

            --Insert values inside the table by specifying column name

            INSERT INTO Employee (emp_id, emp_name, dept, salary) VALUES (1, "Joey", "CSE", 23000.00);

            --insert values without specifying column name

            INSERT INTO Employee VALUES (2, "Ross", "CE", 30000.00);

            INSERT INTO Employee VALUES (3, "Rachel", "ME", 25000.00);

       Run the following command to see the table:

             SELECT * FROM Employee;

     c. insert data using Insert Select:

              Syntax:

                       INSERT INTO table_name (col1, col2, ...) SELECT col1, col2, ... FROM table_name

    

     Note: You can also use where clause with select statement. It will retrieve data from one table and add into another table.

     Remember the data type of column should be same as values provided.

You can also add multiple values at a time by separating values using comma(,).

    Example:

         INSERT INTO Employee (emp_id, emp_name, dept, salary) VALUES (1, "Joey", "CSE", 23000.00),  (2, "Ross", "CE", 30000.0),(3, "Rachel", "ME", 25000.00);

2. UPDATE:

         Update command in SQL is used to modify an already existing data in a database.

To update tuples based on a condition we use WHERE Clause.

        Syntax: 

              UPDATE table_name SET col1=value1, col2=value2, ... WHERE condition

        Example: Let's say in the above Employee table we want to update the dept of Ross from CE to CSE, we can execute the following query

        UPDATE Employee SET dept = "CSE" WHERE emp_id = 2;

        You can also update multiple fields at the same time as 

        UPDATE Employee SET dept = "CSE", salary = 50000.00 WHERE emp_id = 2;

3. DELETE:

        Delete command is used to delete row(s) from a table.

        Syntax:

              DELETE FROM table_name WHERE condition;

       Example: Let say we want to delete records of an employee whose id is equal to 2

           DELETE FROM Employee WHERE emp_id= 2;

         To delete all the records from the table run the query by removing where clause as:

            DELETE FROM Employee;

Hence where clause is used to avoid the deletion of all the tuples inside the table.

4. LOCK

        We know that data consistency is an important factor in a database. To apply consistency, we use SQL locks.

In other words, a lock is applied on a table or database when the transaction starts and released when transaction completes successfully.

We'll discuss LOCK in detail later with shared and exclusive locks.

Continue Learning: DQL - Data Query Language