GITTA-Logo
PDF Version of this document Search Help

Lesson Navigation IconAnfragesprache SQL

Unit Navigation IconSQL Overview

Unit Navigation IconBasic database queries

LO Navigation IconSelect-From-Where

LO Navigation IconMultiple conditions

LO Navigation IconComplex conditions

LO Navigation IconArithmetische Operatoren

LO Navigation IconNon-relational constructs

LO Navigation IconSet operators

LO Navigation IconUsage of SQL

LO Navigation IconDatabase queries

Unit Navigation IconSQL Insert, Delete and Update

Unit Navigation IconSummary

Unit Navigation IconRecommended Reading

Unit Navigation IconBibliography

Unit Navigation IconMetadata


GITTA/CartouCHe news:


Go to previous page Go to next page

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.

ORDER BY clause

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. 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
GROUP BY clause

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.

Top Go to previous page Go to next page