Learning Data Manipulation Language

In a database management system, various SQL (Structured Query Language) statements or commands are used to interact with the database. Data Manipulation Language, a subset of SQL, is used to modify, retrieve and insert data in a database. In any process involving dynamic web design and development of database-driven applications, DML statements are widely used in the programs that work with the database.

Data Manipulation Language is primarily consists of the following commands:

• SELECT
  
• INSERT
  
• UPDATE
  
• DELETE

SELECT Statement
The SELECT statement is the most commonly used and the basic SQL statement. For instance, the process of development of website with a database cannot be complete with proper usage of INSERT statements for retrieving data. The SELECT statement retrieves data from one or more tables which can be further filtered or arranged using clauses like WHERE, ORDER BY, GROUP BY, HAVING etc. and combined using JOIN statements.

INSERT statement
The INSERT command is used to add data in an existing table. In a SQL statement, the INSERT command is followed by the INTO statement. For example:

INSERT INTO EMP_DETAIL VALUES ("John", "Smith", "EMP1123", "$20000")

The above statement will insert details like "FIRST_NAME", "LAST_NAME", "EMPLOYEE_ID", "SALARY" etc. in the table "EMP_DETAIL".

UPDATE
The UPDATE statement is used to modify existing records in a table. The UPDATE statement can be used for a single record or for bulk modification. For example:

UPDATE EMP_DETAIL SET Salary = Salary * 1.5 WHERE EMPLOYEE_ID = "EMP1123"

This statement will increase the employee John Smith's salary by 1.5 times.

DELETE
The DELETE statement is used to delete all or specific records from the table. For example:

DELETE FROM EMP_DETAIL WHERE EMPLOYEE_ID = "EMP1123"
The above statement will delete all information related to John Smith from the table EMP_DETAIL. The DELETE statement can also be used with the wildcard character "*". For example:

DELETE * FROM EMP_DETAIL

This statement will delete all records in the table EMP_DETAIL. This option is useful in emptying any table without affecting the table indexes, structure and attributes.