Non-relational constructs

ORDER BY clause

SQL contains some operators that have nothing to do with relational algebra. For example an entity per definition does not have an order. Nevertheless in SQL you can order your tables using the ORDER BY clause. Using the keywords ASC and DESC the sorting can either be ascending or descending. If no keyword is used, the sorting is in ascending order by default.

In this example all customers are sorted in ascending order according to their names and as a second sort parameter in descending order according to their surname. The ASC keyword is default and could therefore be omitted.

GROUP BY clause

Grouping methods are used to form a subset of tuples of a relation according to certain criteria. These subsets can then be used to calculate statistical parameters such as average, sum etc. The value of a certain attribute serves as grouping criteria. All tuples with the same value for this attribute are grouped. These groups can be used in further processes (a special case would be another grouping to be able to use groups of groups). For this, so-called group functions are used. They can only be applied to numerical attributes.
The group functions that SQL usually offers are the following:

• min returns the smallest value ignoring null values
• max returns the largest value ignoring null values
• sum returns the sum of all values ignoring null values
• count returns the number of rows
• avg returns average value ignoring null values
• stdev returns the standard deviation ignoring null values
• varriance returns the variance ignoring null values

In this query we want to find the customers that spent more than 250 SFR for all their small advertisings (a small add is an add that costs less than 300 SFR). In a first step restriction is applied: A004 is sorted out because it is not a small add (price is over 300 SFR). The remaining tuples are grouped by customer and those with a sum of over 250 SFR are selected (customer groups 002 adn 005 are sorted out because they have not reached the limit yet).

Requests including a GROUP BY clause are processed as follows: First, the condition in the WHERE part is processed (if applicable). Then, the specified columns are grouped. With the condition in the HAVING part of the request, another condition can, if necessary, be specified for the grouped attribute values. Due to this order, you can see that there cannot be a group function in the condition of the WHERE part of the request since there has been no grouping yet. However, all attributes appearing in the request must be either in the GROUP BY clause or in a group function.