Meet the table
SELECT, FROM, ORDER BY id and LIMIT as a preview
Rick & Morty is one table, scripts: 1,905 lines of dialogue from eleven episodes, one row per spoken line, and Rick is on 420 of them.
Can you
- Finish the sentence one row in scripts represents, and keep the id that proves it
- Choose the columns a question needs and predict the rows and columns that come back
- Say what a five-row preview shows and what it cannot show
The reading
Read the whole chapterOne row is one spoken line
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.
Rule of thumb. Say what one row is before you count, rank or claim anything.
Going deeper (optional, not on the exam)
The column names in scripts run words together, characterName and wordCount, because the file arrived that way. Northshore Coffee writes first_name and hourly_wage with underscores. Neither style is wrong; the schema panel tells you the exact spelling, and the exact spelling is the one the engine accepts.
SELECT * is for a first look. When you hand a result to someone who asked for three columns, name the three. The result is easier to read, and it does not change shape when a column is added to the table next year.
The ladder
Start at the first rung even if the idea looks familiar: it is answerable from the reading alone, and the climb is where the difficulty lives. A wrong answer sends you back to the paragraph that teaches it, then to a hint, then to the worked answer.
Learn
- Practise this rung
Level 1: Watch it answered
- Practise this rung
Level 2: Fill the blank
Practise
- Practise this rung
Level 3: Predict the result
- Practise this rung
Level 4: Write it yourself
Master
- Practise this rung
Level 5: Repair it
- Practise this rung
Level 6: Transfer it
Keep practising
The whole bank for this concept, in any order, with the same review, hint and worked answer.
Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.