DML Commands in SQL

DML Commands in SQL

by

DML Commands in SQL-

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 and updating and deleting the existing rows tables.

DML_Commands
DML Commands in SQL

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 in SQL:

The INSERT INTO statement is used to insert new records into 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 in SQL:

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 in SQL

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 in SQL

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 in SQL

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 in SQL

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 in SQL

  • 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 in SQL

  • WHERE clause is used to select data from a database with specified conditions.
  • WHERE clause is 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 Comment