12.14. Summary

The data manipulation aspect of the relational model includes relational algebra and relational assignment (:=). Apart from the use of logical and comparison operators in expressing conditions, the algebra includes eight basic table operations. Comparison operators have precedence over logical operators, which have precedence over table operators. Table 12.3 listed the six priority levels of the operators: =, <>, <, >, < =, >=; not; and; or; selection (where ...) and projection ([ ]); and finally union ∪, intersection ∩, difference - (termed except in SQL), Cartesian product ×, natural join ⋈ and division ÷. Parentheses may be used to override this order.

Operators on the same precedence level are left associative (evaluated in left to right order as they appear in an expression). Our priority convention is designed to minimize use of parentheses and is stronger than some other conventions in use. If in doubt, or purely for the sake of clarity, feel free to add extra parentheses.

The table operators are summarized visually in Figure 12.64. Many different notations exist for selection, projection, and joins. A notation common in academic journals is shown on the left, with our notation on the right.

The laws for the comparison and logical operators are well known. Table 12.17 sets out most of the main laws for the table operators. Some of these have not been discussed: their proof is left as an easy exercise. Here A, B, and C are tables, c is a condition, and p is a projection list of attributes. Various other distributive laws could be stated; recall, however, that [ ] does not distribute over Pi ∩ or -.

A relational DBMS has the relational table as its only essential data structure and supports relational selection, projection, and join without needing to specify access paths. SQL is the dominant language used in relational DBMSs, and various versions have been standardized over the years, with the most recent being SQL:2003. Most commercial SQL dialects support all of SQL-89, some of the extra features in SQL-92 and SQL: 1999, and some of their own special features.

Database objects such as tables and columns may be named using standard identifiers or delimited identifiers (allowing any characters inside double quotes). Database fields may be defined in terms of predefined data types (e.g., varchar(20)). SQL: 1999 added user-defined types, row types, reference types, and arrays. The SQL select statement is used for queries and has the following basic syntax.

select * | [all | distinct] expression[[as] column-alias][,...]
  from table [[as]alias]
      [,| cross join table[[as] alias]
           | natural [inner | [outer] {left| right [ fufl}] join table[[as] alias]
        |[inner | [outer] {left | right 1 full}] join table[[as] alias]
          { on condition| using (col-list)}
        | union join table [[as] alias]
         [,-]]
[where col-condition]
[group by colname[,...]
   [having group-condition]]
[union | intersect | except...]
[order by col[ase | desc] [,...]]

The select list chooses the result columns, the from clause declares the source tables and joins, the where clause is used to filter out rows, the group by clause forms groups of rows, the having clause is used to filter out groups, and the order by clause determines the order of rows.

A subquery is a parenthesized select query used inside an outer query. If the subquery includes a condition that refers to the outer query, it is a correlated subquery. A subquery after an exists quantifier is an existential subquery. An exists condition is true if the sub-query returns a row, and false otherwise. The three most common forms of conditions with an embedded subquery are as shown, where Θ is a comparison operator.

Table populations may be changed using insert, update, and delete statements, with the following basic syntax.

insert into tablename [ (col-list) ]
   {values (constant-list)| select-query]

updatetablename
   set colname = expression [,.,.]
   [wherecondition]
delete from tablename
   [wherecondition]