Ask questions of one table

Open the playground, choose Rick & Morty, and run the five-row preview below. The first row is id 1: Rick, in the episode Pilot, saying 'Morty! You gotta come on. Jus'... you gotta come with me.' Nine columns describe that one line: which season and episode it belongs to, who said it, the words themselves, and how long it is. wordCount says 11. Count the words yourself; the table is right.

SELECT *
FROM scripts
ORDER BY id
LIMIT 5;

That is one row. Before you write any other query, finish this sentence for the table in front of you: one row in scripts represents one spoken line. Not one character: Rick is on 420 rows. Not one episode: Pilot is on 241. A familiar name is not an identifier. The id is, and it is the column you keep while you investigate, because it lets you point at one line and say this one.

SELECT names the columns you want, in the order you want them; FROM names the table. Asking for two columns instead of nine changes the shape of the result, not the number of rows: 1,905 lines stay 1,905 lines. ORDER BY id makes the first four mean the same four every time you run it, and LIMIT 4 keeps the preview short. A preview is a sample. It tells you what the table looks like; it cannot tell you anything about the lines you did not look at.

SELECT id, characterName, episodeName
FROM scripts
ORDER BY id
LIMIT 4;

So make the claim as narrow as the evidence. Six rows that show only Rick and Morty are six lines and two names. They are not six characters, and they are not the main characters. Say what you saw, and keep the id so a teammate can look at the same six lines.

The recap editor does not want a heading called characterName. Write characterName AS speaker and the output column is called speaker. That is all an alias does: it names a column of this result, for this query. Open the schema panel and the table still says characterName. Nothing was renamed.

An expression calculates a column that was never stored. wordCount + 5 AS five_more_words gives 16 for id 1, whose wordCount is 11. Run SELECT wordCount FROM scripts WHERE id = 1 afterwards and you get 11 again. SELECT reads; it never writes. Check an expression against one row you can do in your head, 11 plus 5, before you trust it on 1,905.

SELECT id, characterName AS speaker, wordCount + 5 AS five_more_words
FROM scripts
ORDER BY id
LIMIT 3;

Text joins with ||, not with +. characterName || ' in ' || episodeName AS scene_label gives Rick in Pilot. The single quotes around ' in ' make it a piece of text, spaces included. Leave the quotes off a column name; put them on anything that is literally text.

SELECT characterName || ' in ' || episodeName AS scene_label
FROM scripts
ORDER BY id
LIMIT 5;

Now put quotes where they do not belong: SELECT 'characterName' AS speaker. The engine does not complain. It prints the word characterName on every row, because quoted text is a value, not a column. It ran. That is not the same as being right. When a column repeats one word all the way down, look for quotes that should not be there.

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.

= asks for an exact stored value. LIKE asks for a shape. Rick is in the title of four episodes in this snapshot: A Rickle in Time, Pickle Rick, Rick Potion 9 and The Rickshank Rickdemption. Ask for episodeName LIKE 'Rick%' and you get one of them, Rick Potion 9, because 'Rick%' means begins with Rick. '%Rick' means ends with Rick, and '%Rick%' means Rick anywhere, which is what the question wanted.

SELECT DISTINCT episodeName
FROM scripts
WHERE episodeName LIKE '%Rick%'
ORDER BY episodeName;

% stands for any run of characters, including none, so Rick on its own also matches 'Rick%'. _ stands for exactly one character. characterName LIKE 'R___' is four characters that start with R: one speaker label, Rick. Change it to 'R%' and Rick J-22 and Riq IV come along. Pick the wildcard that says what you mean about length.

SELECT DISTINCT characterName
FROM scripts
WHERE characterName LIKE 'R___'
ORDER BY characterName;

A pattern matches characters, not meaning. A Rickle in Time matched because of four letters in a row, and a title with Rick in it says nothing about who speaks the lines. The four Rick-titled episodes hold 714 lines; 113 of them are Rick's. If a teammate claims every line in those episodes is Rick's, you do not need a second matching row. You need one line that is not his, and there are 601 to choose from. Keep claims about titles, speakers and dialogue separate, and say which one your evidence is about.

List characterName for every line and you get 1,905 rows, most of them Rick and Morty over and over. Put DISTINCT in front and you get 48: one row per different name. Nothing was deleted. DISTINCT removes repeats from this result, and it changes what one row means: a row is now a speaker label, not a line.

SELECT DISTINCT characterName
FROM scripts
ORDER BY characterName;

DISTINCT looks at the whole list of columns you selected. DISTINCT characterName, episodeName gives 104 rows, one per speaker-and-episode pair: Rick in Pilot once, Rick in Lawnmower Dog once. Add id to the list and every row is distinct again, all 1,905, because no two lines share an id. After any DISTINCT, say out loud what one row now represents.

DISTINCT is a reporting choice, not a repair. If a result repeats and you did not expect it, ask which columns should define a duplicate before you reach for the keyword. And sort a distinct list by the column it lists, so two people comparing their speaker directories see the same order.

Rick's table has a number in every wordCount. Northshore Coffee's menu does not: the Chocolate Croissant has no calorie count on file, and neither do five other items. That blank is NULL, and NULL means unknown. It is not 0, it is not an empty piece of text, and it is not the word NULL. A croissant with an unknown calorie count is not a croissant with no calories.

Ask for calories = NULL and the engine returns no rows and no error. The comparison does not come out true or false; it comes out unknown, and WHERE keeps only rows whose test is true. The test that works is IS NULL, and its opposite is IS NOT NULL. The same goes for <> NULL, which keeps nothing either.

SELECT name, calories
FROM menu_items
WHERE calories IS NULL
ORDER BY name;

An unknown value fails every ordinary test, in both directions. calories < 100 keeps 10 menu items, calories >= 100 keeps 29, and six items are in neither list: 10 plus 29 plus 6 is the whole menu of 45. BETWEEN and NOT BETWEEN split the same way. If a rule is supposed to include the unknowns, say so in the query: calories < 100 OR calories IS NULL.

Report the gap instead of filling it. A film with no gross on file did not earn nothing; 169 of the thousand IMDb films have no gross at all, and a report that quietly leaves them out, or quietly counts them as zero, is wrong twice. Say how many rows are unknown, next to the number you computed from the rest.

ORDER BY wordCount DESC puts the longest line first; ASC, the default, puts the shortest first. LIMIT 5 then keeps the first five of that order. Without ORDER BY, LIMIT 5 keeps the first five rows the engine happened to reach. In this file that is usually id order, and nothing promises it. Five rows with no ORDER BY is a sample. It is not a top five.

SELECT id, title, imdb_score
FROM movies
ORDER BY imdb_score DESC, id
LIMIT 5;

Ties need a rule. Three IMDb films share a score of 9.0, and ORDER BY imdb_score DESC alone lists those three in whatever order the engine met them. ORDER BY imdb_score DESC, id breaks the tie by id, so the same five films come back in the same order every time. The tie-break is a second column after a comma; make it one that is unique, and the result is repeatable.

Exactly five and everyone tied for fifth are different requests. LIMIT 5 gives you exactly five, and a tie-break decides which of the tied rows make the cut. It never pulls the rest of the tie in. When someone asks for a top five, ask back which of the two they mean.

Describe the population before you rank it. Rick's five longest lines filters to Rick first, then sorts: the longest is id 241, at 196 words. That is one line. It does not tell you who talks most across the show; that is a question about totals per speaker, and it waits for the next chapter.

SELECT id, characterName, wordCount
FROM scripts
WHERE characterName = 'Rick'
ORDER BY wordCount DESC, id
LIMIT 5;

OFFSET skips rows of the ordered result: LIMIT 5 OFFSET 5 is lines six to ten, the second page. The playground shows at most 200 rows and labels a result that was cut off. A display that stops at 200 is not a count of 200; narrow the question, or wait for COUNT in chapter 3.