Database Reference
In-Depth Information
EId Name Title
Salary DId MId
25
Mary
Engineer
90K
100
23
40
John
Writer
65K
100
5
21
Peter
Accountant
70K
101
3
...
...
...
...
...
...
Table Emp
DId Description City
Budget
100
R&D
Seattle
10M
101
Finance
New York
1M
102
Sales
Seattle
1M
...
...
...
...
Table Dept
FIGURE 1.2
Tables modeling employees and departments.
data source of a
SQL
query is the Cartesian product of each multiset refer-
enced in the
FROM
clause. The predicate in the
WHERE
clause serves as a filter
to restrict the set of tuples in the combined data source. The
SELECT
clause
reshapes the tuples that satisfy the
WHERE
clause, by selecting a subset of
their columns and optionally specifying expressions obtained by manipulat-
ing such columns (e.g.,
1.1 * Salary
). The
GROUP BY
clause, when present,
specifies that the tuples in the combined data source must be separated in
groups, where each group agrees on the values of the
GROUP BY
columns. If a
GROUP BY
clause is present, the expressions in the
SELECT
clause can be de-
fined only over either the
GROUP BY
columns or aggregate values (e.g.,
SUM
or
AVG
) of other columns. In this way, each group of tuples with the same
GROUP
BY
column values is transformed into a representative tuple in the result. A
HAVING
clause can be present only if there is a
GROUP BY
clause and specifies
a predicate to restrict the groups that are considered in the output (since
it refers to groups of tuples, the predicate can use either
GROUP BY
columns
or other column aggregates). Finally, the
ORDER BY
clause specifies an order
of the results and can be used only in the outermost query block (i.e., there
cannot be
ORDER BY
clauses in subqueries).
The canonical way of evaluating a
SQL
query can be summarized in the
following seven steps (note, however, that this “recipe” is almost never used
to actually evaluate
SQL
queries, as there are significantly more ecient alter-
natives):
1. Evaluate each nested relational input in the
FROM
clause that is not
already a table.
2. Generate the Cartesian product of all tables in the
FROM
clause.
Search WWH ::
Custom Search