DML Command:




The data manipulation language statements are used to retrieve, add, delete, and modify the data that is stored in the objects of database. The keywords or statements that are associated with the data manipulation language are: SELECT INSERT, UPDATE and DELETE. These are the primary statements of data manipulation language (DML) and are used widely.




  • The INSERT statement is used to insert a new row in the database that is adding data to a table.
  • The SELECT statement is used to retrieve record from one or more tables.
  • The UPDATE statement is used to update the data or row in the table.
  • The MERGE statement is used to merge two rows or two tables in the database.
  • The DELETE statement is used to delete a row from the table in the database.

 

INSERT Command

To add data to a table the INSERT command is used. The syntax of the INSERT command is given below:

INSERT into table-name values (data, data …)

EXAMPLE

Suppose you have the following fields in the table named as EMPLOYEE as follows:

Dept_No Dept_Name Emp_No Emp_Name

Now use the following query to insert values to these fields in table.

INSERT into EMPLOYEE values (10, ‘Management’, ‘E01’, ‘John Abraham’);

This query will add the data into the fields and will produce the following result:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham

 

EXAMPLE to insert NULL value to a column

In the above query if you do not specify or define any of the fields then a null value will be inserted into that field or column. For example if the in the above example the field is not specified that is the name of the employee is not mentioned in the query then the name field will be assigned a NULL value. However if a default value is set then a default value will be inserted instead of a null value.

Suppose you have the following fields in the table named as EMPLOYEE as follows:

Dept_No Dept_Name Emp_No Emp_Name

INSERT into EMPLOYEE values (10, ‘Management’, ‘E01’);

This query will add the data into the fields and will produce the following result:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01

You can also write NULL in the reference of the field and NULL value is specified to the field as the following query:

INSERT into EMPLOYEE values (10, ‘Management’, ‘E01’, null);

This will result the same as the above query.

 

EXAMPLE to insert default value to a column:

If some value is set then writing the default keyword for that very specific field, then this value will be inserted into that column. Suppose the default value of the field EMP_NAME is set as “John Abraham” then using the default keyword will insert this name in the field. Consider the following query to understand this:

 INSERT into EMPLOYEE values (10, ‘Management’, ‘E01’, default);

This query will add the data into the fields and will produce the following result:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham




Update Command:

To update a table or row or column in the table we use the update command. The syntax of update command is given below:

Update table-name set column-name = value where condition;

EXAMPLE

Suppose we have a table that is named as employee.

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham
20 Management E02 Tim
30 Finance E10 Ali
40 Finance E11 Faddy
50 IT E25 Kate

To update the table we are going to use the following query:

Update employee set EMP_NO = E04 where DEPT_NO = 30;

This query will produce the following result:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham
20 Management E02 Tim
30 Finance E04 Ali
40 Finance E11 Faddy
50 IT E25 Kate

This will update entry in the column EMP_NO with DEPT_NO 30.

 

EXAMPLE to update multiple columns:

If the user wants to update a number of columns of the table then he can use the following query:

Update employee set EMP_NO = E06, set Emp_Name = ‘Stuart’ where DEPT_NO = 30;

This query will update the entries in the column EMP_NO and Emp_Name with DEPT_NO 30. The following result will be produced:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham
20 Management E02 Tim
30 Finance E06 Stuart
40 Finance E11 Faddy
50 IT E25 Kate

It can be observed that the name Ali is changed to Stuart and E04 is changed to E06.

 

Delete Command

To delete a table row or some data from a table in the database the delete command is used. The syntax of delete command is as follows:

DELETE from table-name;

 

EXAMPLE to delete all records from a table

To delete all records use the delete command. This will delete the attributes, indexes etc. of the table but the table as a whole will not be deleted. To delete all records from the table use the following SQL statement:

DELETE from Employee;

This query will delete all the records from the table named as Employee. This change will be permanent and cannot be recovered.

 

EXAMPLE to delete a particular record from a table:

Suppose you have the following table named as EMPLOYEE:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham
20 Management E02 Tim
30 Finance E04 Ali
40 Finance E11 Faddy
50 IT E25 Kate

If you want to delete a specific record from the table then use the following query:

DELETE from employee where DEPT_NO = 40;

This query will delete the records where the department number is 40 from the table named as employee. This will delete the whole row from the table. The following result will be generated:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham
20 Management E02 Tim
30 Finance E04 Ali
50 IT E25

Kate