Database Concepts - Data Warehouse Systems: Design and Implementation - page 36

or null if the employee has no supervisor” can be implemented in SQL using
the LEFT OUTER JOIN operation:

SELECT E.FirstName, E.LastName, S.FirstName, S.LastName
FROM Employees E LEFT OUTER JOIN Employees S
ON E.ReportsTo = S.EmployeeID

Analogously, we can use the FULL OUTER JOIN operation to also include
in the answer the employees who do not supervise anybody:

SELECT E.FirstName, E.LastName, S.FirstName, S.LastName
FROM Employees E FULL OUTER JOIN Employees S
ON E.ReportsTo = S.EmployeeID

As shown in the examples above, SQL is a declarative language, that
is, we tell the system what we want, whereas in relational algebra, being
a procedural language, we must specify how we will obtain the result. In
fact, SQL query processors usually translate an SQL query into some form
of relational algebra in order to optimize it.

Aggregation and Sorting in SQL

Aggregation is used to summarize information from multiple tuples into a
single one. For this, tuples are grouped and then an aggregate function
is applied to every group. In data warehouses, particularly in OLAP,
aggregation plays a crucial role, as we will study in subsequent chapters
of this topic.
Typically, DBMSs provide five basic aggregate functions, namely, COUNT ,
SUM , MAX , MIN ,and AVG .The COUNT function returns the number
of tuples in each group. Analogously, the functions SUM , MAX , MIN ,
and AVG are applied over numeric attributes and return, respectively, the
sum, maximum value, minimum value, and average of the values in those
attributes, for each group. Note that all of these functions can be applied to
the whole table considered as a group. Further, the functions MAX and MIN
can also be used with attributes that have nonnumeric domains if a total
order is defined over the values in the domain, as is the case for strings.
The general form of an SQL query with aggregate functions is as follows:

SELECT list of grouping attributes list of aggr funct(attribute)

FROM list of tables
WHERE condition
GROUP BY list of grouping attributes
HAVING condition over groups
ORDER BY list of attributes

An important restriction is that if there is a GROUP BY clause, the
SELECT clause must contain only aggregates or grouping attributes. The
HAVING clause is analogous to the WHERE clause, except that the condition