SQL exercises with solutions: COUNT, SUM, AVG, MIN, MAX

Topics to use: COUNT, SUM, AVG, MIN, MAX.

Query structure:

SELECT COUNT(*)
FROM …

SELECT COUNT(*)
FROM products;

Expected result (1 row):

Topics to use: COUNT, SUM, AVG, MIN, MAX.

Query structure:

SELECT MAX(…)
FROM …

SELECT MAX(salary)
FROM employees;

Expected result (1 row):

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX.

Query structure:

SELECT COUNT(*), SUM(… + …)
FROM …
WHERE … = …

SELECT COUNT(*), SUM(home_goals + away_goals)
FROM matches
WHERE home_goals = away_goals;

Expected result (1 row):

Hint: SELECT COUNT(*) FROM books;

SELECT COUNT(*) AS nb_books
FROM books;

Expected result (1 row):

COUNT(*) counts every row of the table.

Hint 1 · lead: You need to add up a column over all the rows.

Hint 2 · method: Add up the seats_sold column with SUM.

SELECT SUM(seats_sold) AS total_seats
FROM flights;

Expected result (1 row):

SUM adds up the column over all the rows.

Hint 1 · lead: MIN and MAX are not only for numbers.

Hint 2 · method: MIN(loan_date) AS first_loan and MAX(loan_date) AS last_loan.

SELECT MIN(loan_date) AS first_loan, MAX(loan_date) AS last_loan
FROM loans;

Expected result (1 row):

In the YYYY-MM-DD format, alphabetical order is also chronological order: MIN gives the oldest date, MAX the most recent.

Hint 1 · lead: One of the two forms of COUNT ignores empty cells.

Hint 2 · method: COUNT(email) only counts the non-empty emails.

SELECT COUNT(email) AS with_email
FROM contacts;

Expected result (1 row):

COUNT(*) would have given 5; COUNT(email) ignores the 2 contacts without an email.

Hint 1 · lead: First filter the maths grades. A calculation can combine two aggregates, just as it combines two columns.

Hint 2 · method: WHERE subject = 'math', then COUNT(*), ROUND(AVG(grade), 1) and MAX(grade) - MIN(grade).

SELECT COUNT(*) AS nb_grades,
       ROUND(AVG(grade), 1) AS avg_math,
       MAX(grade) - MIN(grade) AS spread
FROM grades
WHERE subject = 'math';

Expected result (1 row):

WHERE applies before the three aggregates, which therefore only see the maths grades. MAX(grade) - MIN(grade) is a calculation between two aggregates.