Foundation examples: SELECT & WHERE

One scripts row is one dialogue record, not one character or episode. Repeated speaker names are expected. First identify the table and what one row means; then choose the columns that answer the question.

SELECT chooses output columns and their order. FROM identifies the source table. SELECT * is useful for a first inspection; explicit columns make the intended answer clearer. Selecting fewer columns does not itself remove rows.

AS changes an output heading for this query only. It neither renames the stored column nor updates its values. FROM scripts AS s gives the table a temporary query nickname; s.characterName still refers to the original column.

Write text values in straight single quotes, identifiers normally unquoted, and aliases with spaces in double quotes. Commas separate SELECT expressions; do not put a comma immediately before FROM. Finish each statement with a semicolon.

For these single-table queries, reason through FROM, then WHERE, then SELECT, DISTINCT, ORDER BY and LIMIT. This is a useful logical model, not a promise about the engine’s physical execution plan.

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

SELECT s.id, s.characterName AS speaker,
       s.episodeName AS "Episode title"
FROM scripts AS s
ORDER BY s.id
LIMIT 3;

A SELECT expression is calculated for each surviving row. You can return the original value beside a hypothetical value to check the arithmetic. +, -, *, / and % mean addition, subtraction, multiplication, division and integer remainder. Parentheses control arithmetic order.

SQLite divides two integers using integer division: 5 / 2 returns 2. Use 5 / 2.0 for 2.5. For movie hours, runtime / 60.0 avoids truncation. These SELECT calculations do not change stored data.

Use || to concatenate text in SQLite, including an explicit separator such as ' / '. + is arithmetic, not text concatenation. An expression involving NULL usually remains NULL; an absent value is not automatically an empty string.

DISTINCT removes duplicate output rows. DISTINCT characterName returns different speaker labels; DISTINCT characterName, episodeNumber returns distinct pairs. Adding id usually prevents duplicate removal because the id is unique.

Use output aliases in ORDER BY. For WHERE, repeat the original expression or use the source column. SQLite permits some aliases in WHERE as an extension, but relying on that is confusing and not portable.

SELECT id, wordCount,
       wordCount + 2 AS words_after_intro,
       characterName || ' / ' || episodeName AS label
FROM scripts ORDER BY id LIMIT 3;

SELECT DISTINCT characterName
FROM scripts
ORDER BY characterName;

SELECT 5 / 2 AS integer_result,
       5 / 2.0 AS decimal_result,
       17 % 5 AS remainder;

WHERE retains a row only when its condition is true. You may filter on a column that you do not display. Filtering rows and choosing columns are separate decisions.

= means equal; <> and != mean not equal. > and < exclude the boundary; >= and <= include it. Translate “more than” differently from “at least”, and “under” differently from “at most”. Write numeric thresholds without quotes.

IN is an exact-value list for one expression: characterName IN ('Rick', 'Morty') means Rick OR Morty. It is not pattern matching. Every OR condition must be a complete comparison: characterName = 'Rick' OR 'Morty' is not a reliable way to ask this question.

BETWEEN 8 AND 12 includes both 8 and 12. Its equivalent is wordCount >= 8 AND wordCount <= 12. NOT BETWEEN excludes known values inside that range, but it does not automatically include missing values.

This database contains selected episodes, not every episode. An empty result does not prove the SQL is broken: check the values that actually exist. Do not invent a table called characters or a column called season.

SELECT id, characterName, wordCount
FROM scripts
WHERE characterName = 'Summer' AND wordCount >= 10
ORDER BY id;

SELECT id, characterName, wordCount
FROM scripts
WHERE characterName IN ('Beth', 'Jerry')
  AND wordCount BETWEEN 8 AND 12
ORDER BY id;

Use = for an exact stored value and LIKE for a text pattern. characterName = 'Rick%' looks for the literal percent sign; it does not mean “starts with Rick”.

LIKE 'Rick%' matches a prefix. LIKE '%Rick' matches a suffix. LIKE '%Rick%' matches Rick anywhere. % matches zero or more characters, so Rick itself also matches Rick%.

_ matches exactly one character. LIKE 'R___' matches four-character values beginning with R. The underscore is a wildcard only inside a LIKE pattern; underscores in names such as imdb_score are ordinary identifier characters.

The classroom SQLite defaults make LIKE case-insensitive for ASCII letters (Rick and rick), while ordinary text equality here uses the default case-sensitive comparison. Do not assume those rules across every language, collation or database.

LIKE does not understand synonyms, whole words or genre categories. A movie genre field can contain a combined label such as Crime, Drama: = 'Drama' and LIKE '%Drama%' ask different questions.

To put an apostrophe in SQL text, double it: 'Schindler''s List'. To search for a literal wildcard, choose an escape character: LIKE '%!_%' ESCAPE '!' finds an underscore. These are optional reference details.

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

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

AND requires both tests to be true. OR requires at least one. NOT reverses true/false, but NOT unknown remains unknown when NULL is involved. Predict a specific row before running the full query.

AND binds more tightly than OR. year >= 2000 AND runtime <= 100 OR imdb_score >= 8.5 means (recent AND short) OR highly rated: it can admit older highly rated movies.

If every movie must be recent, write year >= 2000 AND (runtime <= 100 OR imdb_score >= 8.5). Parentheses express the business rule; they are not decorative.

Use the real IMDb Movies snapshot here: one movies row is one film. runtime is in minutes; imdb_score is the stored score. This is a historical teaching snapshot, not today’s ratings or a complete census of films.

Validate meaning, not just syntax. A query can run successfully and still include the wrong rows. Explain one qualifying row and invent a boundary case that should fail.

SELECT id, title, year, runtime, imdb_score
FROM movies
WHERE year >= 2000
  AND (runtime <= 100 OR imdb_score >= 8.5)
ORDER BY id;

SELECT id, title, year
FROM movies
WHERE year NOT IN (1999, 2000)
ORDER BY id LIMIT 5;

NULL represents missing or unknown data. It differs from 0, an empty string '', and the literal text 'NULL'. A blank-looking result cell is not enough evidence: use a SQL test.

Use IS NULL and IS NOT NULL. gross = NULL and gross <> NULL evaluate to unknown, so WHERE keeps no rows from those tests. They can run with no syntax error while answering the wrong question.

A missing gross value does not mean a film earned no money. A missing meta_score does not mean a score of zero. Report the missingness instead of inventing a value.

Be explicit when a rule must include missing values: meta_score < 60 OR meta_score IS NULL. NOT IN is especially tricky if its list contains NULL; avoid that construction in today’s work.

COALESCE can supply a display fallback, but replacing missing numbers with zero changes their meaning. It is a later tool, not a substitute for deciding what the missing value means.

SELECT id, title, year, meta_score
FROM movies
WHERE meta_score IS NULL AND year >= 2010
ORDER BY id;

SELECT id, title, gross
FROM movies
WHERE gross IS NOT NULL
ORDER BY id LIMIT 5;

ORDER BY controls display order: ASC (the default) is smallest first; DESC is largest first. Add a unique id as a final tie-breaker when you need repeatable selection among equal scores. This is a short preview of next week, not an extra Lab 2 requirement.

LIMIT 5 returns at most five rows after filtering and ordering. Without ORDER BY, the chosen rows are unspecified; that does not mean SQLite randomly samples them. OFFSET skips rows in that ordered sequence. DISTINCT does not guarantee sorting.

The playground displays up to 200 result rows and labels a truncated result. A displayed count of 200 is not necessarily the total number of matching records. Narrow the question or use an instructor-provided COUNT(*) check; aggregation is not today’s required topic.

Use Quick schema or the database reference to confirm names. Autocomplete helps with table and column names; it does not write the reasoning for you. Run one SQL statement at a time. The sqlite3 shell command .schema is not SQL and will not run here.

If a query fails, check the selected database, FROM table, spelling, commas and quotes. If it runs but seems wrong, check boundaries, parentheses, NULL and patterns. Start simple and add one condition at a time.

Save SQL as text before leaving. A screenshot is useful for discussion but does not satisfy the official Lab 2 requirement to submit runnable query text in the Word report. SELECT does not edit the source data.

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

SELECT id, title
FROM movies
ORDER BY id
LIMIT 5 OFFSET 5;