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