SQL Lab: filters¶

Exercise sql-lab-filters-annex.pdf · PDF

Somebody hands you a shoebox with 949 Pokémon trading cards and asks: which is the fastest electric one? You do not read 949 cards aloud. You take out the electric ones, sort them by speed, look at the top card and read its name.

Four moves with your hands. In a database those four moves have names, and together they make a sentence the database understands. Last lesson you connected Superset to the class database and uploaded the table pokemon. Now you learn to ask it for less: fewer columns, fewer rows, in a useful order. In the crafting game of this module you are still in the farm and loot stage — but a good player does not carry the whole map home, only what the recipe needs.

A shoebox with 949 cards¶

A query is a question written in a language the database understands. That language is SQL (Structured Query Language).

One analogy for this whole page: the table pokemon is a shoebox of trading cards.

The box holds 949 cards, and every card has 22 lines printed on it. In this lesson we use eight of them:

Open SQL → SQL Lab in Superset, choose the database Dades classe and the schema public, and you are ready to type. You run a query with the Run button or with Ctrl+Enter.

The shape of a query¶

A query has up to five lines, always in this order. Each one is a move with your hands.

flowchart LR
    A["<b>FROM</b><br>open the box:<br>949 cards"] --> B["<b>WHERE</b><br>take out the cards<br>you want"]
    B --> C["<b>ORDER BY</b><br>sort the pile"]
    C --> D["<b>LIMIT</b><br>keep the top ones"]
    D --> E["<b>SELECT</b><br>read only the lines<br>you asked for"]

Here is the question from the top of the page, written as a query:

SELECT pokemon, speed
FROM pokemon
WHERE type_1 = 'electric'
ORDER BY speed DESC
LIMIT 1;

One row, two columns. The answer, and nothing else. The next sections build that sentence one line at a time.

SELECT: read only the lines you need¶

SELECT chooses the columns of the result. FROM names the table they come from.

SELECT * FROM pokemon hands you all 22 columns of all 949 rows. It is useful for a first look, and useless as an answer. Name the columns instead:

SELECT pokemon, type_1, attack FROM pokemon;

It still returns 949 rows, but only three columns. The first four:

WHERE: take cards out of the box¶

WHERE keeps only the rows for which a condition is true. A condition is a yes-or-no question asked to every row, one by one.

SELECT pokemon, type_1, attack FROM pokemon WHERE type_1 = 'fire';

This returns 59 rows: charmander, charmeleon, charizard, vulpix... The condition type_1 = 'fire' was asked 949 times, and 59 cards said yes.

The = is a comparison operator. There are six:

Two rules cause most of the errors in a first week of SQL:

Two conditions: AND, OR¶

A logical operator joins two conditions. AND: both must be true. OR: one is enough.

SELECT pokemon, type_1, type_2 FROM pokemon
WHERE type_1 = 'dragon' AND type_2 = 'flying';

Six rows: dragonite, altaria, salamence, rayquaza and the Mega forms of the last two. Change AND to OR and ask something wider:

SELECT pokemon, type_1, type_2 FROM pokemon
WHERE type_1 = 'dragon' OR type_2 = 'dragon';

61 rows: every Pokémon that is a dragon in either slot. AND makes a result smaller, OR makes it bigger.

ORDER BY and LIMIT: sort the pile, keep the top¶

ORDER BY sorts the result by a column. LIMIT keeps only the first rows.

SELECT pokemon, type_1, attack FROM pokemon
WHERE attack > 150
ORDER BY attack DESC;

19 rows, strongest first. Together, the two lines answer every "top N" question:

SELECT pokemon, attack FROM pokemon ORDER BY attack DESC LIMIT 5;

Look at the last two rows. Three Pokémon have an attack of 180: deoxys-attack, groudon-primal and rayquaza-mega. Only two fit in a top 5, and the database picked them however it liked. Your screen may show a different pair, and you would both be right.

DISTINCT: getting to know a column¶

DISTINCT removes repeated rows from the result, so each different value appears once.

Before you filter by a column, find out which values it can take:

SELECT DISTINCT type_1 FROM pokemon ORDER BY type_1;

18 rows, from bug to water: the 18 types, each one once. Now you know that the type is written fire, not Fire and not FIRE. DISTINCT is the cure for the zero-rows trap above.

Four special filters¶

Comparison operators cover numbers and exact text. Four more filters cover the rest.

LIKE uses two wildcards, symbols that stand for text you do not know: % is any text (also none), _ is exactly one character. 'pika%' means "starts with pika": pikachu and its twelve costumes (pikachu-rock-star, pikachu-phd...).

IN is short for several OR: type_1 = 'fire' OR type_1 = 'water' OR type_1 = 'grass'. Put NOT in front for the opposite: type_1 NOT IN ('fire', 'water', 'grass') returns 680 rows, and 269 + 680 = 949. Nobody got lost.

BETWEEN includes both ends: speed BETWEEN 100 AND 120 is the same as speed >= 100 AND speed <= 120. Both return 126. With > and < you would get 83, because the Pokémon at exactly 100 and exactly 120 fall out.

IS NULL finds blanks: the 439 Pokémon with only one type. IS NOT NULL finds the other 510. This one deserves its own section.

NULL: the blank line on the card¶

NULL means unknown: nothing is written here. It is not zero, and it is not an empty text.

Run this, and count:

SELECT pokemon, type_1, type_2 FROM pokemon WHERE type_2 = NULL;

0 rows. No error. Did the 439 single-type Pokémon disappear?

No. Think of two cards with a blank line. Are the two blanks equal? You cannot tell: something might belong there, and you do not know what. So when you compare anything with NULL, the database answers "unknown" — and WHERE only keeps the rows where the answer is a clear yes.

That is why SQL has a separate question for blanks. Remember it: IS NULL, never = NULL.

Wrong answers that look right¶

Every trap on this page has one thing in common: none of them gives an error. The database does exactly what you wrote, and returns a believable number.

The third one, in detail. You want fire or water Pokémon with an attack above 100:

SELECT COUNT(*) FROM pokemon
WHERE type_1 = 'fire' OR type_1 = 'water' AND attack > 100;

79. The database read it as "all fire Pokémon (59), plus the water ones with attack above 100 (20)". Add brackets around the OR part:

SELECT COUNT(*) FROM pokemon
WHERE (type_1 = 'fire' OR type_1 = 'water') AND attack > 100;

34. Same words, different question. (COUNT(*) counts rows; it opens the next lesson.)

Try it: the Pokédex hunt¶

Open SQL Lab and answer each question with one query. Write the query first, then open the solution and compare your number.

If SQL Lab answers with a red box, read the message: it usually names the word it tripped on. Look just before that word for a missing quote or a misspelt keyword. And run one query at a time: with several in the editor, you only see the result of the last one.

Key takeaways¶

• A query has a fixed shape: SELECT (columns) · FROM (table) · WHERE (rows) · ORDER BY (sort) · LIMIT (cut).
• WHERE asks a condition to every row. Text goes in single quotes and is case-sensitive; numbers have no quotes.
• AND narrows, OR widens. AND binds first: when you mix them, use brackets.
• ORDER BY ... DESC LIMIT n answers "top N", but LIMIT cuts ties arbitrarily.
• DISTINCT shows the different values of a column: use it before you filter.
• LIKE matches a pattern, IN a list, BETWEEN a range with both ends, IS NULL a blank. = NULL is never true.
• The dangerous mistakes do not give errors. They return a number that looks right.

Quick check¶

So far every answer was a list of cards. What if the question is "how many fire Pokémon?" — and then the same for all 18 types at once? Next: SQL Lab: groups.