Sorting, grouping and aggregate functions

Overview

ORDER BY, GROUP BY and HAVING, the aggregate functions that summarise a column, and where each clause sits in the order of a statement.

What you need to know

Answer each one before you reveal it.

Questions from this subtopic

Each one with the model answer underneath.

1. 1

   What does the ORDER BY clause do?

   Model answer

   The ORDER BY clause sorts the rows of the result on one or more named columns. Ascending order is used unless DESC is stated.

2. 2

   Book holds Title and Price. Write SQL to list titles and prices from cheapest to dearest.

   Model answer

   SELECT Title, Price FROM Book ORDER BY Price ASC. The keyword ASC may be left out because ascending is the default.

3. 3

   Book holds Title and YearPub. Write SQL to list titles with the newest book first.

   Model answer

   SELECT Title, YearPub FROM Book ORDER BY YearPub DESC. The keyword DESC reverses the sort into descending order.

4. 4

   Where does ORDER BY appear in relation to WHERE?

   Model answer

   The ORDER BY clause is written after the WHERE clause. The rows are selected first and sorted afterwards.

5. 5

   What does the GROUP BY clause do?

   Model answer

   The GROUP BY clause collects rows that share a value into a single group. One row of output is then produced for each group.

6. 6

   Define the term aggregate function.

   Model answer

   An aggregate function calculates a single value from a set of rows. COUNT, SUM, AVG, MIN and MAX are the common aggregate functions.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.