Delete from relation StarsIn the fact that Sydney GreenStreet was a star in The Maltese Falcon:
DELETE FROM StarsIn WHERE movieTitle = ‘The Maltese Falcon’ AND
movieYear = 1942 AND starName = ‘Sydney Greenstreet’;
10
Example: Delete all Tuples
Make the relation Likes empty: DELETE FROM Likes;
Note o WHERE clause needed.
3
Specifying Attributes in INSERT
We may add to the relation name a list of attributes. Two reasons to do so:
1. We forget the standard order of attributes for the relation.
2. We don’t have values for all attributes, and we want the system to fill in missing components with NULL or a default value.
4
Example: Specifying Attributes
1. Insert a tuple or tuples. 2. Delete a tuple or tuples. 3. Update the value(s) of an existing tuple or tuples.
2
Insertion
To insert a single tuple: INSERT INTO <relation> VALUES ( <list of values> );
12
Updates
To change certain attributes in certain tuples of a relation:
UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;
Using Studio and Movie, add to the relation Studio all movie studios that are mentioned in the relation Movie, but don’t appear in Studio.
7
Solution
INSERT INTO Studio(name) (SELECT DISTINCT studioName FROM Movie WHERE studioName NOT IN
(SELECT name FROM Studio);
8
Deletion
To delete tuples satisfying a condition from some relation:
DELETE FROM <relation> WHERE <condition>;
9
Example: Deletion
Another way to add Sydney Greenstreet to the list of stars of The Maltese Falcon.
INSERT INTO StarsIn(movieTitle, movieYear, starName) VALUES(’The Maltese Falcon’, 1942, ’Sydney GreenStreet’);
5
Inserting Many Tuples
We may insert the entire result of a query into a relation, using the form:
INSERT INTO <relation> ( <subquery> );
6
Example: Insert a Subquery
13
Example: Update
Modify the relation MovieExec by prepending the title Pres. In front of every movie executives who is the president of a studio:
UPDATE MovieExec SET name = ‘Pres. ’ || name WHERE cert# IN (SELECT presC# FROM Studio);
Example: add Sydney Greenstreet to the list of stars of The Maltese Falcon.
INSERT INTO StarsIn VALUES(‘The Maltese Falcon’, 1942, ’Sydney GreenStreet’);
More SQL
Database Modification Defining a Database Schema Views
1
Database Modifications
A modification command does not return a result (as a query does), but changes the database in some way. Three kinds of modifications:
11
Example: Delete Many Tuples
Delete from MovieExec all movie executives whose net worth is low-less than ten million dollars.
DELETE FROM MovieExec WHERE netWorth < 10000000;