Experiment No.
AIM: To study and implement DML commands.
THEORY:
DML (Data Manipulation Language):
The SQL commands that deals with the manipulation of data present in the database belong to
DML or Data Manipulation Language and this includes most of the SQL statements. It is the
component of the SQL statement that controls access to data and to the database. Basically,
DCL statements are grouped with DML statements.
List of DML commands:
INSERT : It is used to insert data into a table.
UPDATE: It is used to update existing data within a table.
DELETE : It is used to delete records from a database table.
INSERT:
There are different ways of inserting values into a database table using INSERT INTO statement,
as per the requirement of the Database Administrator (DBA).
The INSERT INTO statement is used to add new data to a database.
The INSERT INTO statement adds a new record to a table.
INSERT INTO can contain values for some or all of its columns.
INSERT INTO can be combined with a SELECT to insert a record.
Here, we will first create a database and table into it and then, perform the INSERT INTO
statement.
CREATE DATABASE databasename; CREATE DATABASE Organization;
The following example creates a table called "Persons" that contains five columns: PersonID,
LastName, FirstName, Address, and City.
CREATE TABLE table_name ( CREATE TABLE Persons
column1 datatype, (
column2 datatype, PersonID int,
column3 datatype, LastName varchar(25),
.... FirstName varchar(25),
); Address varchar(25),
City varchar(25)
);
It is possible to write the INSERT INTO statement in different ways.
Method 1- Specifie both the column names and the values to be inserted.
INSERT INTO table- INSERT INTO Persons (PersonID, LastName, FirstName, Address
name (column- , City)
names) VALUES (values VALUES (101, 'Erichsen', 'Tom', 'Street no-21', 'New York');
);
INSERT INTO Persons (PersonID, LastName, FirstName, Address
, City)
VALUES (102, 'Johnson', 'Marry', 'Old Street Road-
43', 'California');
Method 2- Insert Data Only in Specified Columns.
The following SQL statement will insert a new record, but only in the "PersonID", "LastName",
and "FirstName" columns.
INSERT INTO table_name (col1,col2) INSERT INTO Persons (PersonID, LastName,FirstN
VALUES (val1,val2) ame) VALUES (103, 'Steve','Rossy')
Method 3- Adding the values for all the columns
If you are adding the values for all the columns of the table, you do not need to specify the
column names in the SQL query. However, make sure the order of the values is in the same order
as the columns in the table. The INSERT INTO syntax would be as below.
INSERT INTO table_name INSERT INTO Persons VALUES (104, 'Allen', 'Ketty', 'South
VALUES ( val1,val2,…valn) Side Road', 'U.S.');
DELETE
The DELETE statement is used to delete existing records in a table.
DELETE FROM table_name WHERE conditio DELETE FROM Persons WHERE PersonI
n; D =104;
UPDATE
The SQL UPDATE Query is used to modify the existing records in a table. You can use the
WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows
would be affected.
The following SQL statement updates the first customer (CustomerID = 1) with a new contact
person and a new city.
UPDATE table_name UPDATE Persons
SET column1 = value1, column2 = value2, ... SET PersonID = 110, FirstName = 'SACHIN
WHERE condition; CHAVHAN'
WHERE PersonID = 103;
Conclusion: Thus we have successfully studied implemented DML commands