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.
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,....);
Let the Initial table will be :
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 :
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.
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 :
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;
SELECT Emp_ID, Emp_First_Name, Salary FROM Employee;
The result will be :
Syntax for selecting all the rows without specifying column names :
SELECT * FROM table_name;
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)>;
SELECT Emp_ID, Emp_First_Name, Salary FROM Employee WHERE Emp_ID = 1002;
The Result will be as :
The DELETE Command
The Delete Command is used to delete rows in a table.
DELETE FROM table_name [WHERE <condition(s)>]
DELETE FROM Employee where Emp_ID = 1003;
The Result will be after delete operation as :
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.
|SQL Commands and Some Rules for Writing SQL Commands||DCL Commands : Data Control Language Commands|
Incoming search terms:
- dml command
- dml commands with example
- five commond of DML
- five DML command in dbms
- dml commands
- DML commands general syntax
- dml commands with syntax and examples
- What is data manipulation language commands