SQL queries: SELECT, WHERE, ORDER BY, SUM and COUNT

The shape of a query

SELECT lists the fields to show, FROM names the table, WHERE gives the condition rows must meet, and ORDER BY sorts the result. ASC (the default) sorts smallest or earliest first, and DESC the other way.

SELECT Title, Price
FROM BOOK
WHERE Price < 10 AND InStock = TRUE
ORDER BY Price DESC;

Conditions

WHERE uses the comparisons =, <>, <, >, <= and >=, joined with AND (both parts must be true) or OR (either part). Text values go in quotes, such as Genre = 'Horror'. Numbers and Boolean values don’t.

SUM and COUNT

SUM adds up the values in a numeric field, and COUNT counts the rows. Both can be combined with WHERE to work on only some rows.

SELECT SUM(Distance)
FROM RIDE
WHERE BikeType = 'Electric';

SELECT COUNT(*)
FROM RIDE
WHERE Distance > 5;

Reading a query

When asked what a query outputs, go through the table row by row and keep only the rows that meet the WHERE condition. Show only the fields in SELECT, in the order they are listed, then sort by the ORDER BY field.