Table of Contents Link to heading

• Data Manipulation
• INSERT INTO Statement
  • Insert a single tuple
  • Use a sub-query (insert from another table)
• DELETE FROM statement
• DELETE FROM and DROP TABLE statements
• UPDATE statement

Data Manipulation Link to heading

So we have created a set of tables. However:

• How do we actually put data in our database?
• How do we modify data that is already in our database?
• How do we answer questions (queries) using our database data?

Key Data Manipulation SQL commands:

1. INSERT INTO - Creates new tuple(s) in a table.
2. DELETE FROM - Permanently removes tuple(s) from a table.
3. UPDATE - Modifies existing tuple(s) in a table.
4. SELECT - Retrieves tuple(s) of data from table (a Query).

INSERT INTO Statement Link to heading

This statement is used to populate with data - your newly created database.

• String data (text) must be wrapped in single ‘quotations’.
• Same applies to date strings: ‘01/03/2014’

Insert a single tuple Link to heading

There are two ways of creating new tuple(s) in a table: default positions and keywords.

For example: Student(studentName, studentID)

1. Insert new values in the default order of the attributes when the table was created.

INSERT INTO TableName VALUES (x, y, z);
INSERT INTO Student VALUES ('50011', 'Barry');

2. Insert the values in a different order to the default order of the attributes when the table was created.

INSERT INTO TableName (attr3, attr2, attr1) VALUES (z, y, x);
INSERT INTO Student (studentName, studentID) VALUES ('Stacy', '50022');

Note the different order of studentName and studentID. If the order is not specified, you must insert the values in the order of the table attribute when they are defined (left -> right).

Use a sub-query (insert from another table) Link to heading

Insert values from another table into the given table as a new tuple.

Syntax:

INSERT INTO GivenTable
    SELECT attr1, attr2, attr3
    FROM AnotherTable
    WHERE <condition>;

Example:

INSERT INTO Student (StudentID, StudentName)
    SELECT id, name
    FROM AnotherTable
    WHERE name IS NOT NULL;

DELETE FROM statement Link to heading

This statement is used to remove ALL the tuple(s) that satisfy the condition from a given table.

• The removal may result in deletions from other tables if a FK constraint with CASCADE ON DELETE has been used.

1. If the WHERE clause is omitted, DELETE FROM empties (removes all tuples from) the table:

For example, remove all students from the table:

DELETE FROM TableName;
DELETE FROM Student;

2. If the WHERE clause presents, each tuple is compared to it and if TRUE is returned, that tuple is deleted.

For example, remove all students from the table whose name is ‘Henry’:

DELETE FROM TableName WHERE <condition>;
DELETE FROM Student WHERE studentName = 'Henry';

DELETE FROM and DROP TABLE statements Link to heading

UPDATE statement Link to heading

This statement is used to modify the existing records in a table that satisfy the condition from a given table.

Syntax:

UPDATE TableName
  SET attribute=<Expression|SelectSQL|NULL|DEFAULT>
  {, attribute=<Expression|SelectSQL|NULL|DEFAULT>}
  {, attribute=<Expression|SelectSQL|NULL|DEFAULT>}
  [WHERE <condition>]

Akin to the DELETE FROM statement, the presence of the WHERE clause is vital.

1. If the WHERE clause is omitted, all records in the table will be updated.

For example, all employees will get a double raise:

UPDATE Employee
SET Salary = Salary * 2;

2. If the WHERE clause presents, each tuple is compared to it and if TRUE is returned, that tuple is updated.

For example, only senior employees will get a double raise:

UPDATE Employee
SET Salary = Salary * 2.0
WHERE rank = senior;