Data Manipulation Language (DML)

DML Cover following part of SQL:

Data manipulation language commands enable you to query and modify data using ObjectServer SQL. ObjectServer SQL provides the following commands to enable you to manipulate data in existing tables, views, and files.

·                     SELECT - extracts data from a database.

·                     UPDATE - updates data in a database.

·                     DELETE - deletes data from a database.

·                     INSERT INTO - inserts new data into a database.

---

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

Syntex:

SELECT column_list FROM table-name  [WHERE Clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause];

Example:

Select * from tblPerson

The Table Look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Note: * denotes all columns from table. We can specify the column names also.

Select P_Id, LastName, City from tblPerson.

P_Id	LastName	City
1	Hansen	Sandnes
2	Svendson	Sandnes
3	Pettersen	Stavanger

---

SQL UPDATE STATEMENT

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

Syntax:

UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

Example:

Suppose table tblPerson have following data;

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger
5	Tjessem	Jakob

Now update City with Stavanger whose LastName is Hansen.

UPDATE tblPersons SET City='Stavanger' WHERE LastName=’ Hansen’

Now Table will look like this

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Stavanger
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger
5	Tjessem	Jakob

---

SQL DELETE STATEMENT

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

Syntax:

DELETE FROM table_name WHERE some_column=some_value

Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

Example:

Suppose this table contains records like this;

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger
5	Hansen	Jakob	Nissestien 67	Sandnes

Now Delete Those records whose LastName is Hansen.

Delete from tblPerson where LastName=’ Hansen’

Here Two records will deleted because There are two records with LastName=’ Hansen’. So records will look like this;

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger

---

SQL INSERT INTO STATEMENT

The INSERT INTO statement is used to insert new records in a table. The INSERT INTO statement is used to insert a new row in a table.

Syntax: two ways to insert values in Table;

Method 1: INSERT INTO table_name VALUES (value1, value2, value3,...)

Method 2: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

Suppose a table with table name tblPerson have no data with following structure;

ID

Name

Address

City

State

Method 1:

Insert into tblPerson values (1,’Mathew’,’New Street’,’Agra’,’UP’)

Now See the result of tblPerson

ID	Name	Address	City	State
1	Mathew	’New Street	Agra	UP

Method 2: Specify columns

Insert into tblPerson (ID,Name,City) values (2,’Devid’,’Mathura’)

Result Will look like this;

ID	Name	Address	City	State
1	Mathew	’New Street	Agra	UP
2	Devid		Mathura