SQL as Data Manipulation Language
SQL as Data Manipulation Language: Besides retrieving data from a database using a SELECT statement, SQL also provides statements to manipulate the data of the 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 the 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 a 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 the 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 extracted from one table into another table. In this case, both values should have the same data structures. For example, to copy all those rows of the ‘STUDENT_1’ table that have a 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 the UPDATE statement is:
UPDATE < table name> SET attribute1 = value1 [, attribute2 = value2…] [WHERE <criteria>]
For example, to change the values of the ‘City’ column of all those rows that have the value ‘MULTAN’ with a new value ‘LAHORE’ the statement will be
UPDATE student SET CITY = ‘Lahore’ WHERE City = ‘Multan’.
In the above statement, if the WHERE clause is omitted then all rows of the ‘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 a database table. The rows are permanently deleted and cannot be recovered again. The general syntax of this statement is:
DELETE FROM <table name> [WHERE <criteria>]
To delete a row from the STUDENT table with the roll number 123, the SQL statement is written as:
DELETE FROM student WHERE Roll_No = 123
To delete records which have the value “Lahore” in the ‘City’ attribute in the STUDENT table the SQL statement is written as:
DELETE FROM student WHERE City = ‘Lahore’.
