DML Commands : Data Manipulation Language Commands

DML Commands :  : Data Manipulation Language Commands There are four DML Commands :

• The INSERT INTO Command
• The UPDATE Command
• The DELETE Command
• The SELECT Command

The INSERT INTO Command

The INSERT INTO command is to insert a new row in a table.

Syntax :

There are two ways  to write the INSERT INTO Commands –

Form 1 : (Mainly used if we want to insert data in all columns)

INSERT INTO table_name
VALUES (value1, value2, value3,....);

Example :

INSERT INTO Employee
VALUES (1004,'Surender','Mittal','BHIWANI',50000.00);

Form 2: (Mainly used if we want to insert data in specified columns)

INSERT INTO table_name (column1, column2, column3,....)
VALUES (value1, value2, value3,.....);

Example of Form 2 : 

INSERT INTO Employee VALUES (Emp_ID,Emp_First_Name,Emp_Last_Name)
VALUES (1005,'Renu','Mittal');

The UPDATE Command

The Update Command is used to modify data values within one or more columns for one or more rows of a table.

Syntax :

UPDATE table_name
SET <column_name1> = <value expression1>
   [<column_name2> = <value expression2>
    ......
   ]
   [ WHERE <condition>];

The columns whose values have to be updated and the expressions to derive these values are included in the SET clause. The WHERE Clause – The WHERE Clause is used to extract only those records that fulfill a specified criteria.

Example of UPDATE Command :

UPDATE EMPLOYEE SET Salary = 60000.00
  WHERE Emp_ID = 1004;

The command will modify the salary of the employee with 60000.00 whose ID is 1004.

SQL UPDATE Warning :

Be careful after removing WHERE Clause during updating records Because it will update all records. For Example :

UPDATE Employee
SET ADDRESS = 'ROHTAK', Emp_Last_Name = 'Garg'

The DELETE Command

The Delete Command is used to delete rows in a table.

Syntax :

DELETE FROM table_name
[WHERE <condition(s)>]

Example : 

DELETE FROM Employee
where Emp_ID = 1003;

DELETE All Rows :

It is possible to delete all rows in a table without affecting the schema of the database or say the table structure, attributes and indexes. Omit the WHERE Clause from the DELETE Command. For Example :

DELETE FROM Employee;

The SELECT Command

The SELECT Command is used to select data from a database. The result is stored n a result table called Result-Set. The SELECT can be used for simple to extremely complex queries. It comes under the Data Query Language because it may also be used in <query specifications> as part of other commands such as INSERT or CREATE VIEW.

Syntax For selecting some specified Columns :

SELECT column_name(s) 
FROM table_name;

Example :

SELECT Emp_ID, Emp_First_Name, Salary
FROM Employee;

Syntax for selecting all the rows without specifying column names :

SELECT *
FROM table_name;

Example :

SELECT *
FROM Employee;

Syntax for selecting specific rows which meet some condition(s) :

SELECT column_name(s) 
FROM table_name
WHERE <condition(s)>;

Example :

SELECT Emp_ID, Emp_First_Name, Salary
FROM Employee
WHERE Emp_ID = 1002;

 ]]>