DML Commands in SQL

DML_Commands

Data manipulation language(DML Commands) is a core part of SQL. When you want to add, update and delete data in the database then we execute a DML statement. SQL is divided into mainly four sub-languages and Data manipulation language is one of them. So there are four DML commands which are given below:-

  • INSERT – Add a new row to a table
  • UPDATE – Modify existing rows in a table
  • DELETE – Remove existing rows in a table
  • SELECT – To retrieve particular rows from the table.

CREATE TABLE Customers

(

Cust_id Number PRIMARY KEY

CustomerName Varchar2(150) NOT NULL,

Contact Number NOT NULL, 

Address Varchar2(250),

City Varchar2(100) NOT NULL,

PostalCode Varchar2(8),

Country Varchar2(100)

)

Note:- Few authors include the SELECT query in DQL Sublaguage which is also known as Data Query Language. A collection of DML statements that form a logical unit of work is called a Transaction.

INSERT INTO Statement:

The INSERT INTO statement is used to insert new records in a table. It is possible to write the INSERT INTO statement in two forms.

1.  The first form does not specify the column names where the data will be inserted, only their values:

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

Example:-

INSERT INTO Customers VALUES(1,’Ramesh’,2345670000,’C 24 Armapur’,’Kanpur’, ‘208009,India’);

2.  The second form specifies both the column names and the values to be inserted:

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

Example:

INSERT INTO Customers (Cust_id, CustomerName, Contact, Address, City, PostalCode, Country)

VALUES (2,’Abhinav’,4567890000,’B 16 Model Town’,’Lucknow’, ‘226004’,’India’);

3.  Insert data in specific columns:

The following SQL statement will insert a new row, but only insert data in the “CustomerName”, “City”, and “Country” columns (and the CustomerID is needed because it’s a PRIMARY KEY):

Example:

INSERT INTO Customers (Cust_id, CustomerName, City, Country) VALUES (8, ‘Cardinal’, ‘Stavanger’, ‘USA’);

4.  Bulk Insert

INSERT ALL

INTO Customers  VALUES (3,’Suresh’,3456780000,’Vijay Nagar’,’Allahabad’,’234501′,’India’)

INTO Customers VALUES (4,’Danish’,7890120000,’Gumti No.5′,’Kanpur’,’208005′,’India’)

INTO Customers VALUES (5,’Sandeep’,6789100000,’Ramadevi’, ‘Delhi’,’226048′,’India’)

INTO Customers VALUES (6,’Rajneesh’,5678900000,’260/50 Aishbagh’,’Kanpur’,’208006′,’India’)

INTO Customers VALUES (7, ‘Cardinal‘,’Erichsen‘,’Skagen 21′,’Stavanger’,’4006′,’Norway’)

SELECT * FROM dual;

5. UPDATE statement

The UPDATE statement is used to update records in a table.

Syntax:

UPDATE table_name

SET column1=value1,column2=value2,… WHERE some_column=some_value;

Example:

UPDATE Customers SET CustomerName=‘Suresh Menon’, City=‘Agra’ WHERE Cust_id = 2;

6.DELETE Statement:

The DELETE statement is used to delete rows in a table.

Syntax

DELETE FROM table_name

WHERE some_column=some_value;

Example

DELETE FROM Customers WHERE CustomerName=’Danish’ AND City=‘Kanpur’;

7. Delete All Data

It is possible to delete all rows in a table without deleting the table.

This means that the table structure, attributes, and indexes will be

 intact:

Syntax:

DELETE FROM table_name;

8. SELECT Statement:

  • The SELECT statement is used to select data from a database.
  • The result is stored in a result table, called the result-set.

Syntax:

SELECT column1_name,column2_name FROM table_name;

OR

SELECT * FROM table_name;

Example:

SELECT CustomerName, City FROM Customers;

OR

SELECT * FROM Customers;

9. SELECT Statement with WHERE clause :

  • WHERE clause is used to select data from a database with specified conditions.
  • WHERE clause also used with Logical, Conditional, Relational, and Comparison Operator.

Syntax:

SELECT column1_name,column2_name FROM table_name WHERE conditions;

Example:

SELECT CustomerName, City FROM Customers WHERE City=‘Kanpur’;

OR

SELECT * FROM Customers WHERE CustomerName=‘Jone’ and City=‘Kanpur’;

Leave a Reply

Your email address will not be published. Required fields are marked *