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 :

Let the Initial table will be :

Emp_ID Emp_First_Name Emp_Last_Name Address Salary
1000 Renu Garg Bhiwani 31000.00
1001 Pooja Garg Bhiwani 32000.00
1002 Komal Mittal Rohtak 35000.00
(Table 1)
INSERT INTO Employee 
VALUES (1003,'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 (1004,'Renu','Mittal');

The Updated table after Insertion Opertion :

Emp_ID Emp_First_Name Emp_Last_Name Address Salary
1000 Renu Garg Bhiwani 31000.00
1001 Pooja Garg Bhiwani 32000.00
1002 Komal Mittal Rohtak 35000.00
1003 Surender Mittal Bhiwani 50000.00
1004 Renu Mittal NULL NULL
(Table 2)

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 = 1003;

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'

So, the table data after applying update operation will be as :

Emp_ID Emp_First_Name Emp_Last_Name Address Salary
1000 Renu Garg Rohtak 31000.00
1001 Pooja Garg Rohtak 32000.00
1002 Komal Garg Rohtak 35000.00
1003 Surender Garg Rohtak 60000.00
1004 Renu Garg Rohtak NULL
(Table 3)

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;

The result will be :

Emp_ID Emp_First_Name Salary
1000 Renu 31000.00
1001 Pooja 32000.00
1002 Komal 35000.00
1003 Surender 50000.00
1004 Renu NULL
(Table 4)
Syntax for selecting all the rows without specifying column names :
SELECT *
FROM table_name;
Example :
SELECT *
FROM Employee;

The result will be (Table 3).

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;

The Result will be as :

Emp_ID Emp_First_Name Emp_Last_Name Address Salary
1002 Komal Garg Rohtak 35000.00
(Table 5)

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;

The Result will be after delete operation as :

Emp_ID Emp_First_Name Emp_Last_Name Address Salary
1000 Renu Garg Rohtak 31000.00
1001 Pooja Garg Rohtak 32000.00
1002 Komal Garg Rohtak 35000.00
1004 Renu Garg Rohtak NULL
(Table 6)
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;

All the table data will be deleted.


Previous Home Next
SQL Commands and Some Rules for Writing SQL Commands DCL Commands : Data Control Language Commands

     

Incoming search terms:

  • dql with syntax edugrab
  • dml commands edugrabs
  • dml commands with syntax edugrab
  • dml with syntax eduggrab

Leave a Reply