SQL as Data Manipulation Language
SQL as Data Manipulation Language: Besides of retrieving data from database using SELECT statement, SQL also provide the statements to manipulate the data of database. SQL is a complete data manipulation language. In data manipulation, data is inserted into the database tables, updated tables and deleted the data from database.
The important data manipulation languages are
- Insert statement
- Update statement
- Delete Statement
The INSERT INTO Statement
The INSERT INTO statement is used to insert or add new row or record into the database. The new record is appended or added at the end of the database table.
The general syntax of the INSERT INTO statement to insert one row at a time into database table is written as:
INSERT INTO table-name [(field list)] VALUES (value list)
For example, to insert a row with values (16, ‘Fozia’, ‘Karachi’, 650) into the STUDENT table the INSERT statement will be
INSERT INTO Student (Roll_No, Name, City, Marks) VALUES (16, ‘Fozia’, ‘Karachi’, 650)
You can also insert multiple rows extracting from one table to another table. In this case, both the values should have the same data structures. For example, to copy all those rows of ‘STUDENT_1’ table that have value greater than 700 in the ‘Marks’ column into the ‘STUDENT_2’ table , the statement is written as
INSERT INTO VALUES (select * FROM student_1 WHERE Marks > 700)
The UPDATE Statement
The UPDATE statement is used to modify the values of specified attributes of one or more selected rows. The general syntax of UPDATE statement is:
UPDATE < table name> SET attribute1 = value1 [, attribute2 = value2…] [WHERE <criteria>]
For example, to change the values of ‘City’ column of all those rows that have value ‘MULTAN’ with a new value ‘LAHORE’ the statement will be
UPDATE student SET CITY = ‘Lahore’ WHERE City = ‘Multan’.
In the above statement, if WHERE clause is omitted then all rows of ‘STUDENT’ table will be modified with the value ‘Lahore’ in the city column. Similarly, to modify the values of more than one column, they are separated values by commas after the SET value.
The DELETE Statement
The DELETE statement is used to delete rows from database table. The rows are permanently deleted and cannot be recovered again. The genera syntax of this statement is:
DELETE FROM <table name> [WHERE <criteria>]
To delete a row from STUDENT table with the roll number 123, the SQL statement is written as:
DELETE FROM student WHERE Roll_No = 123
To delete record which have value “Lahore” in ‘City’ attribute in STUDENT table the SQL statement is written as:
DELETE FROM student WHERE City = ‘Lahore’.
