This SQL Tutorial focuses on the SQL DML statements. DML (Data Manipulation Language) statements are the SQL, sublanguage that is used for data retrieval and manipulation. Using DML commands you can perform operations such as inserting new rows, updating and deleting the existing rows tables.
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,
City Varchar2(100) NOT NULL,
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,…);
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,…);
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):
INSERT INTO Customers (Cust_id, CustomerName, City, Country) VALUES (8, ‘Cardinal’, ‘Stavanger’, ‘USA’);
4. Bulk Insert
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.
SET column1=value1,column2=value2,… WHERE some_column=some_value;
UPDATE Customers SET CustomerName=‘Suresh Menon’, City=‘Agra’ WHERE Cust_id = 2;
The DELETE statement is used to delete rows in a table.
DELETE FROM table_name
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
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.
SELECT column1_name,column2_name FROM table_name;
SELECT * FROM table_name;
SELECT CustomerName, City FROM Customers;
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.
SELECT column1_name,column2_name FROM table_name WHERE conditions;
SELECT CustomerName, City FROM Customers WHERE City=‘Kanpur’;
SELECT * FROM Customers WHERE CustomerName=‘Jone’ and City=‘Kanpur’;