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 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;

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.

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;

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;

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.