Hello, SQL

SQL asks a database a question: you describe the result you want and the engine works out how to produce it. By the end of this lesson you can write a query, quote text correctly, and predict exactly which characters the engine prints.

SELECT produces values

SELECT is the verb of the language: produce these values. It does not need a table. Hand it a value and it hands that value straight back as one row of output.

SELECT 42;

Output:

42

Text goes between single quotes:

SELECT 'Ada Lovelace';

Output:

Ada Lovelace

The engine behind this course is SQLite. It prints results bare: no headers, no frame, no quotes around text, one row per line, exactly the characters of the value. What you see in those output blocks is all there is, and the grader compares it character by character.

Single quotes are text; double quotes are not

'...' is a text value. "..." is an identifier -- the name of a table or a column. The two are not interchangeable, and in SQLite the difference is quiet rather than loud:

SELECT "Ada Lovelace";

SQLite looks for a column named Ada Lovelace, finds none, and falls back to treating the text as a value, so this prints Ada Lovelace and never complains. Postgres and MySQL reject the same line outright. That fallback is a trap: a typo inside double quotes is silently printed instead of reported. Type single quotes for text, always.

An apostrophe inside text

A single quote ends the string, so a value that contains an apostrophe writes two single quotes where you want one:

SELECT 'don''t panic';

Output:

don't panic

Count the quote characters on that line: one to open, two in the middle, one to close. SQL has no backslash escape -- \' is not a thing. Written the wrong way, 'don\'t panic' ends the string right after don\, and the leftover t panic' is not valid SQL, so the statement never runs.

One script, several statements

A semicolon ends a statement. A script may hold several; they run top to bottom and each prints its own rows as it goes.

SELECT 'first';
SELECT 'second';

Output:

first
second

Swap those two lines and the two output lines swap with them -- position in the output is decided by position in the script. Delete the semicolon after the first one and the engine reads both lines as a single broken statement, reports a syntax error, and prints nothing at all.

Keywords are case-insensitive: select and SELECT are the same word. The convention every SQL programmer follows is keywords upper, names lower.

Common mistakes

• Escaping an apostrophe with a backslash. The string ends early, the rest of the line is not valid SQL, and the run dies with a syntax error -- you get no output rather than wrong output.
• Forgetting the semicolon between two statements. Same failure: one broken statement, a syntax error, nothing printed. Every statement needs its own terminator.
• Putting the statements in the wrong order. Each one prints when it runs, so the output lines come out in script order; the right three lines in the wrong order is still a wrong answer.
• Using double quotes for text. SQLite prints it anyway, so this mistake survives until a typo inside the quotes reaches the grader as an unexplained wrong answer.

Your exercise

Print three values -- one text value, one text value containing an apostrophe, and one number -- using one statement each, in the order the exercise gives them. The starter holds a single placeholder statement that runs and prints the wrong text; replace it with your three. The apostrophe in the second value is where this exercise is won or lost.