# Structured Query Language/Data Manipulation Language

← Data Query Language | Data Definition Language →
Data Manipulation Language is used to modify the records in the database. It never modifies the schema of the database (table features, relationships, ...). It describes three statements: INSERT, UPDATE and DELETE.

## INSERT statement

The exhaustive syntax of the INSERT statement is as follows:
The INSERT statement is used to add new records (rows) in a table. For instance, we want to add a new reunion:
* Its primary key is 7,
* Its name is "Job interview",
* Its description is "Meeting with Mr. SPENCER",
* Its priority is B,
* Its planned,
* Its date is on October 28, 2009,
* Its hour is 18:30:00,
* Its duration is 30,
* Its office technical id is 23,
* There is no pdf report.
* The table before the statement:
reunion :
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
* Query:
* The table after the statement:
reunion :
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
The INTO clause contains the name of the table where the record needs to be inserted. It can be followed by a list of columns in brackets. The VALUES clause contains the values to insert in brackets. If the column names are omitted, the VALUES clause must contains as many values as the number of columns of the table. The values are inserted in the table columns in the same order that the order in which the columns have been declared in the table. If the column names are mentioned, there must be as many column names as values. The values are respectively inserted into the named columns. If a column in the table is omitted, a NULL value is inserted instead.
The VALUES clause can be replaced by an inner SELECT statement. In this case, the INSERT statement can insert several rows in the table. For example, we want to plan twice all the reunion with a B priority level, one year later:
* The table before the statement:
reunion :
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
* Query:
* The table after the statement:
reunion :
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
| 10 | Change | What we need to change in the project. | B | 1 | 2009-06-03 | 9:30:00 | 90 | 41 | |
| 12 | Reporting | Explanation to the new beginner. | B | 1 | 2010-03-15 | 14:00:00 | 60 | 7 | |
| 13 | Learning | A new software version has been installed. | B | 1 | 2010-09-21 | 16:00:00 | 120 | 11 | |
| 14 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2010-10-28 | 18:30:00 | 30 | 23 | |

## UPDATE statement

The exhaustive syntax of the UPDATE statement is as follows:
The UPDATE statement is used to modify already existent records in a table. The UPDATE clause is followed by the table name in which the rows need to be changed. The SET clause is followed by couples of column name and value. The values will be inserted in the given columns. The WHERE clause contains predicates. If the predicates are true for an existent row, this row will be modified.
For instance, we want to change the date, the hour and the description of the reunion with id 14:
* The table before the statement:
reunion :
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
| 10 | Change | What we need to change in the project. | B | 1 | 2009-06-03 | 9:30:00 | 90 | 41 | |
| 12 | Reporting | Explanation to the new beginner. | B | 1 | 2010-03-15 | 14:00:00 | 60 | 7 | |
| 13 | Learning | A new software version has been installed. | B | 1 | 2010-09-21 | 16:00:00 | 120 | 11 | |
| 14 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2010-10-28 | 18:30:00 | 30 | 23 | |
* Query:
* The table after the statement:
reunion :
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
| 10 | Change | What we need to change in the project. | B | 1 | 2009-06-03 | 9:30:00 | 90 | 41 | |
| 12 | Reporting | Explanation to the new beginner. | B | 1 | 2010-03-15 | 14:00:00 | 60 | 7 | |
| 13 | Learning | A new software version has been installed. | B | 1 | 2010-09-21 | 16:00:00 | 120 | 11 | |
| 14 | Job interview | Meeting with Ms. JOHNSON | B | 1 | 2010-02-11 | 08:00:00 | 30 | 23 | |

## DELETE statement

The exhaustive syntax of the DELETE statement is as follows:
The DELETE statement is used to remove specific rows in a table with conditions. The FROM clause is followed by the table name in which the rows need to be removed. The WHERE clause contains predicates. If the predicates are true for an row, this row will be removed. If the predicates are false for all the rows, the statement do nothing. A DELETE statement without WHERE clause empties the table.
For example, we want to remove all the reunions that last two hours:
* The table before the statement:
reunion :
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 4 | Presentation | Presentation of the project. | D | 0 | 2008-09-11 | 15:30:00 | 120 | 27 | |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 6 | Learning | A new software version has been installed. | B | 1 | 2009-09-21 | 16:00:00 | 120 | 11 | 785278...37528 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
| 10 | Change | What we need to change in the project. | B | 1 | 2009-06-03 | 9:30:00 | 90 | 41 | |
| 12 | Reporting | Explanation to the new beginner. | B | 1 | 2010-03-15 | 14:00:00 | 60 | 7 | |
| 13 | Learning | A new software version has been installed. | B | 1 | 2010-09-21 | 16:00:00 | 120 | 11 | |
| 14 | Job interview | Meeting with Ms. JOHNSON | B | 1 | 2010-02-11 | 08:00:00 | 30 | 23 | |
* Query:
* The table after the statement:
reunion :
| id_reunion | name | description | priority | planned | date | hour | duration | # id_office | pdf_report |
| 1 | Planning | We need to plan the project. | A | 1 | 2008-03-24 | 10:30:00 | 60 | 35 | 48644...846348 |
| 2 | Progress | What we have done. | C | 1 | 2008-05-12 | 14:00:00 | 30 | 13 | 9862...15676 |
| 3 | Change | What we need to change in the project. | B | 1 | 2008-06-03 | 9:30:00 | 90 | 41 | 34876...4846548 |
| 5 | Reporting | Explanation to the new beginner. | B | 1 | 2009-03-15 | 14:00:00 | 60 | 7 | 19739...37718 |
| 7 | Job interview | Meeting with Mr. SPENCER | B | 1 | 2009-10-28 | 18:30:00 | 30 | 23 | |
| 10 | Change | What we need to change in the project. | B | 1 | 2009-06-03 | 9:30:00 | 90 | 41 | |
| 12 | Reporting | Explanation to the new beginner. | B | 1 | 2010-03-15 | 14:00:00 | 60 | 7 | |
| 14 | Job interview | Meeting with Ms. JOHNSON | B | 1 | 2010-02-11 | 08:00:00 | 30 | 23 | |
Now you can use an already existing database schema to handle your own data.