Conditions Involving Tuples

A tuple in SQL is represented by a parenthesized list of scalar values. Examples are (123, foo) and (name, address, networth). The first of these has constants as components; the second has attributes as components. Mixing of constants and attributes is permitted.

Correlated Subqueries

The simplest subqueries can be evaluated once and for all, and the result used in a higher-level query. A more complicated use of nested subqueries requires the subquery to be evaluated many times: once for each assignment of a value to some term in the subquery that comes

Subqueries in FROM Clauses

Another use for subqueries is as relations in a FROM clause. In a FROM list, instead of a stored relation, we may use a parenthesized subquery. Since we don't have a name for the result of this subquery, we must give it a tuple-variable alias.

Natural Joins / Outerjoins

As we recall from Natural Joins / Theta-Joins, a natural join differs from a theta-join in that: 1. The join condition is that all pairs of attributes from the two relations having a common name are equated, and there are no other conditions. 2. One of each pair of equated attributes is projected out.

Declaring Foreign-Key Constraints

A second important kind of constraint on a database schema is that values for certain attributes must make sense. That is, an attribute like presC# of relation Studio is expected to refer to a particular movie executive. The implied "referential integrity" constraint is that if a studio's

Maintaining Referential Integrity

We have studied how to declare a foreign key, and we learned that this declaration implies that any set of values for the attributes of the foreign key, none of which are NULL, must also appear in the corresponding attribute(s) of the referenced relation. But how is this constraint

Deferring the Checking of Constraints

Suppose the situation of "Declaring Foreign-Key Constraints" Example 1, where presC# in Studio is a foreign key referencing cert# of MovieExec. Bill Clinton decides, after his national presidency, to found a movie studio, called Redlight Studios, of which he will naturally be the

Constraints on Attributes and Tuples

We have seen key constraints, which compel certain attributes to have distinct values among all the tuples of a relation, and we have seen foreign-key constraints, which enforce referential integrity between attributes of two relations. Now, we shall see a third important kind of

Instead-Of Triggers

There is a useful feature of triggers that did not make the SQL-99 standard, but figured into the discussion of the standard and is supported by some commercial systems. This extension allows BEFORE or AFTER to be replaced by INSTEAD OF; the meaning is that when an event

System Aspects of SQL

We now consider the question of how SQL fits into a complete programming environment. In "SQL in a Programming Environment" we see how to embed SQL in programs that are written in an ordinary programming language, such as C. A critical issue is how we move data

Page 2 of 3 Previous 1 2 3 Next