Practise · Ask for Morty · Learn with Abdou

Opening your saved work…

The reading

Ask for Morty

Ask for Morty's lines and the engine walks the table one row at a time, tests each row, and keeps the row when the test is true. WHERE characterName = 'Morty' keeps 347 of the 1,905. Read a query in the order the engine thinks about it: FROM finds the table, WHERE decides which rows survive, SELECT decides which columns of the survivors you see. Filtering rows and choosing columns are separate decisions, and you may filter on a column you never show.

SELECT id, characterName, episodeName
FROM scripts
WHERE characterName = 'Morty'
ORDER BY id
LIMIT 5;

Morty's first five lines: ids 2, 4, 6, 9 and 11. WHERE chose the rows; SELECT chose the columns. Open in the playground

Morty is text, so it goes in single quotes. Write WHERE characterName = Morty with no quotes and the engine looks for a column called Morty, finds none, and stops: no such column: Morty. Numbers go bare: wordCount >= 20, not '20'. The error is the friendly case. The unfriendly case is a query that runs and answers a different question than the one you meant.

So write the contract before the query. Rick lines with at least 20 words has two tests, and at least says a line with exactly 20 words must pass. Rick has 136 such lines. Write wordCount > 20 instead and you get 125: the eleven lines that sit exactly on the boundary are gone, and nothing on the screen says so. Before you run a filter with a number in it, name one row that should pass and one that should fail, on each side of the line.

SELECT id, characterName, wordCount
FROM scripts
WHERE characterName = 'Rick' AND wordCount = 20
ORDER BY id;

Eleven Rick lines of exactly 20 words. A > keeps none of them; a >= keeps all eleven. Open in the playground

The quick test for a boundary is to ask for the boundary itself. Lines with exactly 20 words exist. If your query is supposed to keep them, you can see whether it does.

Rule of thumb. Write the contract first, then test one row on each side of the boundary.

Going deeper (optional, not on the exam)

<> and != both mean not equal. The course writes <>, and the engine accepts either.

An empty result is not a broken query. This snapshot holds eleven episodes from three seasons, not the whole show, and one table, scripts. If a filter returns nothing, look at the values that exist, with SELECT DISTINCT, before you decide the SQL is wrong. There is no characters table and no season column; the schema panel is the list of what there is.

Rick gets a free pass

AND keeps a row when both tests pass; OR keeps it when at least one does. Now ask for Rick or Morty lines with at least 20 words, and write it the way you said it: characterName = 'Rick' OR characterName = 'Morty' AND wordCount >= 20. It runs. The first row back is id 1, Rick, 11 words. AND binds before OR, so the engine read A OR (B AND C): any Rick line at all, or a Morty line of 20 words or more. Rick got a free pass.

SELECT id, characterName, wordCount
FROM scripts
WHERE characterName = 'Rick' OR characterName = 'Morty' AND wordCount >= 20
ORDER BY id
LIMIT 8;

Eight rows, and the first has 11 words. The word test only applied to Morty. Open in the playground

Parentheses state the rule you meant: (characterName = 'Rick' OR characterName = 'Morty') AND wordCount >= 20. Then test one row from each case before you believe it: Rick with 19 words is out, Rick with 20 is in, Morty with 20 is in, Summer with 30 is out because she is the wrong speaker. A good explanation says why each row is kept or dropped. It does not repeat the keywords.

SELECT id, characterName, wordCount
FROM scripts
WHERE (characterName = 'Rick' OR characterName = 'Morty') AND wordCount >= 20
ORDER BY id
LIMIT 8;

Eight rows, every one with 20 words or more. Ids 21 and 24 sit exactly on the boundary and are kept. Open in the playground

IN is a list of exact values for one column: characterName IN ('Rick', 'Morty') is one test, not two, so it keeps you out of the parentheses trouble. BETWEEN 8 AND 12 includes both ends; it means wordCount >= 8 AND wordCount <= 12 and nothing more. Both are shorthand for comparisons you already know how to write.

A syntax error stops you, which is a kindness. A logic error hands you a neat table. Someone who wants films from 1999 or 2000 writes WHERE year = 1999 OR 2000, and the engine returns every film in the table, because a bare 2000 counts as true and true OR anything is true. Every OR needs a complete comparison on each side, or an IN list. After any repair, look at one row on the boundary and one row that should have been excluded.

Rule of thumb. When AND and OR share a WHERE, put parentheses around the OR.

Going deeper (optional, not on the exam)

NOT flips a test: NOT city = 'Duluth' is the same as city <> 'Duluth'. NOT BETWEEN 100 AND 200 keeps the values outside the range and nothing else: a row whose value is unknown fails both the range and its opposite. Missing is not zero, two sections on, explains why.

Read a mixed condition as a small decision tree: which test is asked first, and what each branch leads to. Draw it on paper for a teammate. If you cannot, the parentheses are in the wrong place.

Ask for Morty · WHERE, boundaries, AND, OR, parentheses, IN and BETWEEN · level 6 of 6

Canadian cities

Level 6: Transfer it · Exercise 12 of 12

Same idea as Ask for Morty, new data.

The cities table, north_american_cities, holds twelve North American cities with their country, population and position. Return city and population for the Canadian cities, in any order. Finish line: two columns.

Finish line: 2 rows · columns city, population

Data: North American Cities.

I would like help with this exercise

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run your query to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.