Database Modifications

Database Modifications

To this point, we have focused on the normal SQL query form: the select-from-where statement. There are a number of other statement forms that do not return a result, but rather change the state of the database. In this section, we shall focus on three types of statements that allow us to

1. Insert tuples into a relation.
2. Delete certain tuples from a relation.
3. Update values of certain components of certain existing tuples.

We refer to these three types of operations collectively as modifications.


The basic form of insertion statement consists of:

1. The keywords INSERT INTO,
2. The name of a relation R,
3. A parenthesized list of attributes of the relation R,
4. The keyword VALUES, and
5. A tuple expression, that is, a parenthesized list of concrete values, one for each attribute in the list (3).

That is, the basic insertion form is

INSERT INTO R(A1,. . . , An) VALUES (v1,. . . ,vn) ;

A tuple is created using the value vi for attribute Ai, for i = 1, 2,. . . , n. If the list of attributes does not include all attributes of the relation R, then the tuple created has default values for all missing attributes. The most common default value is NULL, the null value, but there are other options to be discussed in next blogs.

Example 1 : Assume we wish to add Sydney Greenstreet to the list of stars of The Maltese Falcon. We say:

1) INSERT INTO StarsIn(movieTitle, movieYear, starName)
2) VALUES('The Maltese Falcon', 1942, 'Sydney Greenstreet') ;

The effect of executing this statement is that a tuple with the three components on line (2) is inserted into the relation StarsIn. Since all attributes of StarsIn are mentioned on line (1), there is no need to add default components. The values on line (2) are matched with the attributes on line (1) in the order given, so 'The Maltese Falcon' becomes the value of the component for attribute movieTitle, and so on.

If, as in Example 1, we provide values for all attributes of the relation, then we may omit the list of attributes that follows the relation name. That is, we could just say:

VALUES('The Maltese Falcon', 1942, 'Sydney Greenstreet');

On the other hand, if we take this option, we must be sure that the order of the values is the same as the standard order of attributes for the relation. We shall see in "Defining a Relation Schema in SQL" how relation schemas are declared, and we shall see that as we do so we provide an order for the attributes. This order is assumed when matching values to attributes, if the list of attributes is missing from an INSERT statement.

●  If you are not sure of the standard order for the attributes, it is best to list them in the INSERT clause in the order you choose for their values in the VALUES clause.

The simple INSERT explained above only puts one tuple into a relation. Instead of using explicit values for one tuple, we can compute a set of tuples to be inserted, using a subquery. This subquery replaces the keyword VALUES and the tuple expression in the INSERT statement form explained above.

Example 2 : Assume we want to add to the relation

Studio(name, address, presC#)

all movie studios that are mentioned in the relation

Movie(title, year, length, inColor, studioName, producerC#)

but do not appear in Studio. Since there is no way to determine an address or a president for such a studio, we shall have to be content with value NULL for attributes address and presC# in the inserted Studio tuples. A way to make this insertion is illustrated in Figure 1.

Adding new studios

Like most SQL statements with nesting, Figure 1 is easiest to examine from the inside out. Lines (5) and (6) generate all the studio names in the relation Studio.

The Timing of Insertions

In this way, line (4) tests that a studio name from the Movie relation is none of these studios.

Now, we see that lines (2) through (6) produce the set of studio names found in Movie but not in Studio. The use of DISTINCT on line (2) assures that each studio will appear only once in this set, no matter how many movies it owns. Lastly, line (1) inserts each of these studios, with NULL for the attributes address and presC#, into relation Studio.