Data Manipulation Language

Data Manipulation in RDBMS means maintaining the data in the database. There are three DML statements:Insert,Update and Delete. INSERT statment is used to insert a new record into a table. The UPDATE statement is used to change the existing information of a table. The DELETE statement is used to remove certain information from the table.

We will take an example here:If you are running an apartment complex where you rent apartments,the day to day record maintenance would look like this.

INSERT Statement

If a person named JAMES rented an apartment,we need to add his information into the table. We have to do an INSERT because the information does not exist in the table as of now.The following information has to be entered into the database:-name = JAMES aptno = 891, home_phone as 676-789-9011, work_phone as 777-567-1234, apt_rent = 880 and no_of_pets as 1.

So now how we can write the INSERT statement.

INSERT into TENANT
(tenant_id, aptno, tenant_name, home_phone, work_phone, apt_rent, no_of_pets )
VALUES
(1003, 891, 'JAMES','676-789-9011','777-567-1234', 880, 1 )

After executing the insert statement the table now should have four rows as shown below

Following shown are the different syntaxes available INSERT SQL syntaxes.

Syntax1
INSERT into table_name values (col1, col2, col3....) values (value1, value2, value3.....)

In the syntax 1 we need to specify the column names of a table and values respectively. In the application development its more recommened to use this syntax while doing inserts into the table, reason being if you added a column in the table then it wont give an error except the value for that column wont be supplied and program will run fine.

Syntax2
INSERT into table_name values ( value1, value2.....)

In the Syntax 2 we wont specify the column names and pass all the values to the columns respectively.

Syntax3
INSERT itno table_name (col1, col2, col3...)
SELECT col1, col2, col3........ FROM table

In the Syntax 3 we can insert multiple rows using one INSERT into statement where as in Syntax 1 and Syntax 2 you can insert only one row at a time.

UPDATE Statement

Now we will go the next DML statement UPDATE. Update is used to change the existing value in a column of a table. As JAMES work_phone number changed to 765-123-9087 from 777-567-1234 then we need to change that information in JAMES record in the table.

UPDATE TENANT SET work_phone = '765-123-9087'
WHERE tenant_name = 'JAMES'.

After executing theUPDATE statement the table now should have four rows as shown below.

Syntax

UPDATE (table_name) SET (colname1 = Value1, colname2 = Value2.......)
[WHERE clause]

If you wont include WHERE clause in your UPDATE statement then it will update all the rows in the table, so you should be very careful when you are writing UPDATE statements in work.

DELETE Statement

SMITH moves out of the apartment complex, so now we do not need to have his information in the table. You can use DELETE Sql statement.

DELETE TENANT
WHERE tenant_name = 'SMITH'

After executing the DELETE statement the table now should have three rows as shown below.

Syntax

DELETE FROM (table_name)
[WHERE clause]

If you wont include WHERE clause in your DELETE statement then you delete all the rows in the table, so you should be very careful when you are writing DELETE statements in work.

Some of the examples of INSERT, UPDATE and DELETE statements.

Insert SQL examples

Example 1
INSERT into BOOKS ( book_id, book_nm, author, price ) values ( 234, 'Oracle', 'Smith', 45 );

Example 2
INSERT into BOOKS values ( 235, 'C++','Austin', 50);

Example 3
INSERT into BOOKS (book_id, book_nm, author, price )
SELECT book_no, book_name, author_name, book_price FROM legacy_books
WHERE author_name = 'BILL';

Update SQL Examples

Example 1
UPDATE BOOKS SET book_nm = 'C++ for Experts'

Example 2
UPDATE BOOKS SET book_nm = 'Oracle'
WHERE book_no = 103

Example 3
UPDATE BOOKS SET price = price - 5
WHERE author in ( SELECT author FROM authors WHERE state = 'CA')

Example 4
UPDATE BOOKS SET price = price - 2
WHERE exists ( select author FROM auhtors WHERE books.author = author.author )

DELETE SQL Examples

Example 1
DELETE BOOKS

Example2
DELETE BOOKS WHERE book_no = 235

Example 3
DELETE BOOKS WHERE author in ( SELECT author FROM authors WHERE state = 'TX')

Create a table called PATIENT so that we can do Data manipulation like INSERT, UPDATE and DELETE statements.

Patient_id Number(4) Primary Key
Patient_name Varchar(35) Not Null,
Primary_doctor Number(4) Foreign Key,
Patient_dob Date Not Null,
Patient_phone Char(10) NULL

Using INSERT statements insert the following rows into PATIENT table.

Change the patient name SMITH to RODMAN whose dob is 10/10/1964 and primary doctor is ABDUL.

Change the phone number of WATER from 312-666-1478 to 312-567-8988.

Delete patient SMITH from the PATIENT table.