Latest Posts

Grouping / HAVING Clauses

To group tuples, we use a GROUP BY clause, following the WHERE clause. The keywords GROUP BY are followed by a list of grouping attributes. In the simplest situation, there is only one relation reference in the FROM clause, and this relation has its tuples grouped according

Full-Relation Operations

In this section we shall look at some operations that act on relations as a whole, rather than on tuples individually or in small numbers (as do joins of various relations, for example). First, we deal with the fact that SQL uses relations that are bags rather than sets, and a tuple can

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.

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.

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

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.

Subqueries

In SQL, one query can be used in many ways to help in the evaluation of another. A query that is part of another is called a subquery. Subqueries can have subqueries, and so on, down as many levels as we desire. We already saw one example of the use of subqueries; in

Union, Intersection, and Difference of Queries

Sometimes we wish to combine relations using the set operations of relational algebra: union, intersection and difference. SQL provides corresponding operators that apply to the results of queries, provided those queries produce relations with the same list of attributes and

Interpreting Multirelation Queries

There are many ways to define the meaning of the select-from-where expressions that we have just studied. All are equivalent, in the sense that they each give the same answer for each query applied to the same relation instances. We shall examine each in turn.

Tuple Variables

Disambiguating attributes by prefixing the relation name works as long as the query involves combining many different relations. On the other hand, sometimes we need to ask a query that involves two or more tuples from the same relation,