The Smallest and the Largest

MIN(column) finds the smallest value of a column, MAX(column) the largest one. The cheapest and the most expensive game:

SELECT MIN(price) AS cheapest,
       MAX(price) AS most_expensive
FROM games;

They work on text and dates too. For text, the smallest value is the first one in alphabetical order and the largest the last one, in the same order in which ORDER BY sorts. Dates written as 'YYYY-MM-DD' sort as dates do, so MIN finds the earliest one and MAX the latest:

SELECT MIN(city) AS first_city,
       MAX(city) AS last_city,
       MIN(joined) AS first_joined,
       MAX(joined) AS last_joined
FROM customers;

Notice that the first city isn't NULL, although ORDER BY city puts NULL first. Like the other aggregate functions, MIN and MAX skip NULL.

As you can see, one query can have several aggregate functions, and different ones. They all work on the same rows, those that WHERE has left. A short summary of the action games:

SELECT COUNT(*) AS game_count,
       MIN(price) AS cheapest,
       MAX(price) AS most_expensive,
       ROUND(AVG(price), 1) AS average_price
FROM games
WHERE genre = 'Action';

Typical mistake: you want the title of the cheapest game and write SELECT title, MIN(price) FROM games;. In SQLite, this happens to work: with MIN and MAX, it takes the other columns from the row with the smallest or the largest value, here Cube Logic. But other databases report an error, and when two games share the lowest price, SQLite shows just one of them. Use what you know from lesson 2 instead, ORDER BY price LIMIT 1, and to see whether another game costs the same, show a few more rows.