Database Modifications

Database Modifications

  • Introduction to statement forms that change the state of the database, known as modifications.
  • Focus on three types of modifications: inserting tuples, deleting tuples, and updating values in existing tuples.
#1

read: Section 6.5: Database Modifications (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Introduction to statement forms that change the state of the database, known as modifications.
- Focus on three types of modifications: inserting tuples, deleting tuples, and updating values in existing tuples.

In contrast to query statements that retrieve data, SQL provides statement forms that alter the state of the database. These operations are collectively referred to as modifications. We shall focus on three fundamental types of database modifications.

  • Insertion

    This operation adds new tuples into a relation. The values for the components of the new tuple are specified explicitly or derived from a query.

  • Deletion

    This operation removes from a relation all tuples that satisfy a specified condition.

  • Update

    This operation alters the values of one or more components in all tuples of a relation that satisfy a specified condition.

Insertion Overview

The INSERT statement is used to add a single tuple or a set of tuples to a relation.

Definition: INSERT Statement

The general form of the insertion statement is:

INSERT INTO R(A_1, ..., A_n) VALUES (v_1, ..., v_n);

or

INSERT INTO R(A_1, ..., A_n) (SELECT ...);

  • Tuple Insertion with VALUES

    A single tuple is created and inserted into relation \(R\). The value \(v_i\) is assigned to the attribute \(A_i\). If the list of attributes \((A_1, ..., A_n)\) does not include all attributes of \(R\), the missing attributes are assigned a default value, typically NULL. If values are provided for all attributes in their standard order, the attribute list \((A_1, ..., A_n)\) may be omitted.

  • Tuple Insertion with a Subquery

    A set of tuples, computed by a SELECT statement, is inserted into relation \(R\). The SQL standard requires that the subquery be evaluated completely before any insertions occur. This ensures that the insertion process does not interfere with the evaluation of the subquery’s conditions.

Deletion Overview

The DELETE statement removes tuples from a relation.

Definition: DELETE Statement

The general form of the deletion statement is:

DELETE FROM R WHERE <condition>;

  • Operation of DELETE

    This statement removes every tuple from relation \(R\) that satisfies the <condition> specified in the WHERE clause. It is not possible to specify a single tuple for deletion directly; rather, one must formulate a condition that uniquely identifies the target tuple or tuples.

Update Overview

The UPDATE statement modifies the values of attributes in existing tuples.

Definition: UPDATE Statement

The general form of the update statement is:

UPDATE R SET A_1 = e_1, ..., A_n = e_n WHERE <condition>;

  • Operation of UPDATE

    For every tuple in relation \(R\) that satisfies the <condition>, the statement modifies its attributes. For each assignment \(A_i = e_i\), the expression \(e_i\) is evaluated and its result becomes the new value for the attribute \(A_i\) in that tuple. The expressions can refer to the old values of attributes within the tuple being updated.

  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-09-20 13:24:36
  • Duration: 18.16485095024109
  • Cummulative cost: $2.14e-02

Insertion

  • The basic form of the insertion statement is INSERT INTO R(A1,..., An) VALUES (v1,..., vn);.
  • A tuple is created using value \(v_i\) for attribute \(A_i\). If not all attributes of relation R are listed, missing attributes get default values.
  • If values for all attributes are provided, the attribute list can be omitted, but the value order must match the relation’s standard attribute order.
  • Instead of explicit values, a set of tuples can be computed and inserted using a subquery, which replaces the VALUES clause.
#2

read: Section 6.5.1: Insertion (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- The basic form of the insertion statement is `INSERT INTO R(A1,..., An) VALUES (v1,..., vn);`.
- A tuple is created using value $v_i$ for attribute $A_i$. If not all attributes of relation R are listed, missing attributes get default values.
- If values for all attributes are provided, the attribute list can be omitted, but the value order must match the relation's standard attribute order.
- Instead of explicit values, a set of tuples can be computed and inserted using a subquery, which replaces the `VALUES` clause.

The INSERT statement is used to add new tuples to a relation. There are two primary forms for this statement: one for inserting a single, explicitly defined tuple, and another for inserting a set of tuples generated by a query.

Definition: SQL INSERT Statement

The basic form of the insertion statement is:

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

  • A tuple is formed by assigning the value \(v_i\) to the attribute \(A_i\) for \(i = 1, 2, \dots, n\).

    If the specified list of attributes does not encompass all attributes of the relation \(R\), any missing attributes in the new tuple are assigned a default value, which is typically NULL.

  • If values are provided for all attributes of the relation in their standard order, the attribute list may be omitted.

    The statement can be simplified to INSERT INTO R VALUES (v1,..., vn);. However, one must be certain that the sequence of values in the VALUES clause exactly matches the predefined sequence of attributes in the relation’s schema.

  • A set of tuples can be inserted by using a subquery in place of the VALUES clause.

    This form allows for the insertion of multiple tuples derived from the existing data in the database. The subquery replaces the VALUES keyword and the tuple expression. For instance, to add all movie studios from the Movies relation that are not already in the Studio relation, one would write:

    INSERT INTO Studio(name)
  SELECT DISTINCT studioName
  FROM Movies
  WHERE studioName NOT IN (SELECT name FROM Studio);
  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-09-20 13:24:50
  • Duration: 13.604377031326294
  • Cummulative cost: $1.31e-02

Deletion

  • The basic form of a deletion statement is DELETE FROM R WHERE <condition>;.
  • The effect is that every tuple satisfying the condition is deleted from the relation R.
#3

read: Section 6.5.2: Deletion (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- The basic form of a deletion statement is `DELETE FROM R WHERE <condition>;`.
- The effect is that every tuple satisfying the condition is deleted from the relation R.

The deletion statement removes tuples from a relation.

Definition: Deletion Statement

The form of a deletion statement is:

DELETE FROM R WHERE <condition>;

  • The effect of this statement is that every tuple in relation \(R\) that satisfies the <condition> is deleted.

  • If the WHERE clause is omitted, all tuples in the relation are deleted.

  • The <condition> in the WHERE clause determines which tuples are to be removed. It can be a simple condition to remove a single tuple or a more general condition to remove many tuples.

    To remove a single tuple, the WHERE clause must uniquely identify it. For example, to delete the record for Sydney Greenstreet in ‘The Maltese Falcon’ from the StarsIn relation:

    DELETE FROM StarsIn
WHERE movieTitle = 'The Maltese Falcon' AND
      movieYear = 1942 AND
      starName = 'Sydney Greenstreet';

    To remove a set of tuples, the WHERE clause specifies a property shared by them. For instance, to delete all movie executives with a net worth below $10,000,000:

    DELETE FROM MovieExec
WHERE netWorth < 10000000;
  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-09-20 13:25:02
  • Duration: 11.714666843414307
  • Cummulative cost: $1.42e-02

Updates

  • An UPDATE in SQL changes components of one or more existing tuples.
  • The general form is UPDATE R SET <new-value assignments> WHERE <condition>;.
  • All tuples in R that satisfy the condition are changed according to the new-value assignments.
#4

read: Section 6.5.3: Updates (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- An `UPDATE` in SQL changes components of one or more existing tuples.
- The general form is `UPDATE R SET <new-value assignments> WHERE <condition>;`.
- All tuples in R that satisfy the condition are changed according to the new-value assignments.

  • In SQL, an UPDATE statement is a specific modification that changes the values of certain components within one or more existing tuples.

  • The general form of the UPDATE statement is defined as follows.

    Definition: SQL UPDATE Statement

    The formal structure for an update operation is:

    UPDATE R
SET <new-value assignments>
WHERE <condition>;

    Here, R is the relation to be updated. The <new-value assignments> clause consists of one or more assignments, separated by commas, each of the form Attribute = Expression. The <condition> is a predicate that selects the tuples in R to be modified.

  • The effect of this statement is to find all tuples in the relation R that satisfy the specified condition.

    For each tuple that meets the criteria of the WHERE clause, the expressions in the SET clause are evaluated. The resulting values are then assigned to the corresponding attributes of that tuple.

  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-09-20 13:25:12
  • Duration: 10.136175870895386
  • Cummulative cost: $1.15e-02