Practise · Where is Rick in the title?

Opening your saved work…

The reading

Where is Rick in the title?

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

Four titles. 'Rick%' would have found one of them. Open in the playground

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

One row. Four characters, the first an R. Open in the playground

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.

Rule of thumb. LIKE matches characters, not meaning.

Going deeper (optional, not on the exam)

The browser engine's LIKE ignores the case of plain letters: LIKE 'rick' finds Rick's 420 lines, while = 'rick' finds none. Other databases make their own choices here, so do not carry the habit across without checking.

To put an apostrophe inside text, double it: 'Schindler''s List'. To search for a literal underscore or percent sign, name an escape character: LIKE '%!_%' ESCAPE '!'. Both are reference details, not exam material.

characterName = 'Rick%' is not a pattern. With = the percent sign is an ordinary character, and no name ends in one.

One of each

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;

48 rows, from Agency Director to Young Rick. One row is one speaker label. Open in the playground

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.

Rule of thumb. DISTINCT changes what one row represents; say what it is now.

Where is Rick in the title? · LIKE, %, _ and DISTINCT · level 6 of 6

Find a word fragment

Level 6: Transfer it · Exercise 3 of 3

Same idea as Where is Rick in the title?, new data.

Return id and title for the films whose title contains The anywhere, in any order. The browser engine's LIKE ignores the case of plain letters. Then find a title that matched without containing the word The.

Finish line: 250 rows · columns id, title

Data: IMDb Movies.

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.