|
![]() |
![]() |
![]() |
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.
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.
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. A certain
value of an attribute serves a grouping criteria.
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 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.
![]() |
![]() |
![]() |
![]() |