SQL Kitchen: Learn SQL by Cooking Up Queries

Recipe

WHERE

SELECT and FROM hand you every row in the table. WHERE is how you keep only some of them. You give it a condition, and it drops every row that does not match.

name             station  price
Bifteki          grill    18
Baklava          pastry   7
Melitzanosalata  salad    16
Loukoumades      pastry   9
Souvlaki         grill    11
Dakos            salad    14

A condition is usually a column, a comparison, and a value. This one keeps the rows where the station column is exactly pastry. Text values always go inside single quotes.

Query

SELECT name
FROM dishes
WHERE station = 'pastry';

↓ result

name
Baklava
Loukoumades

Numbers do not need quotes. You have got the comparisons you would expect: =, <, >, <=, >=, and <> for not equal.

Query

SELECT name, price
FROM dishes
WHERE price < 12;

↓ result

name         price
Baklava      7
Loukoumades  9
Souvlaki     11

You can chain conditions together. AND needs both of them to be true.

Query

SELECT name
FROM dishes
WHERE station = 'grill'
  AND price > 12;

↓ result

name
Bifteki

OR only needs one of them to be true. This one keeps every pastry dish, plus anything over 15 whatever station it is from.

Query

SELECT name
FROM dishes
WHERE station = 'pastry'
  OR price > 15;

↓ result

name
Bifteki
Baklava
Melitzanosalata
Loukoumades

When a WHERE mixes both, SQL does the ANDs first and the ORs second, the same way maths does times before plus. So A OR B AND C quietly means A OR (B AND C).

Wrap the part you want done first in brackets. Without them the query below would keep “anything cheap, or a pricey grill dish”. With them it keeps “a grill dish that is either cheap or pricey”, which is a completely different set of rows.

Query

SELECT name
FROM dishes
WHERE (price < 12 OR price > 15)
  AND station = 'grill';

↓ result

name
Bifteki
Souvlaki

Not everything on the menu is food, either. Konstantinos keeps his own list behind the bar, and WHERE reads it exactly the same way.

name          type     region     price
Assyrtiko     white    Santorini  14
Agiorgitiko   red      Nemea      12
Xinomavro     red      Naoussa    16
Moschofilero  rosé     Mantinia   10
Malagousia    white    Macedonia  11
Mavrodafni    dessert  Patras     9

Query

SELECT name
FROM wine
WHERE type = 'red';

↓ result

name
Agiorgitiko
Xinomavro

WHERE decides which rows you get, SELECT decides which columns. Put them together and the result is still just a smaller version of whichever table you started with.