SQLSQLiteIntermediate~3h

SQL: Queries

Exactly the rows you asked for

One table, and every clause that acts on it. Four modules of saying what a query returns — how many rows, which columns, in what order — on questions generated fresh that never run out. Then a SQL prompt of your own, twenty-four real rows in it, and questions to put to them.

Start this projectSee the modules
Modules
6
Drills
9
Build steps
7
Time
~3h

Walk out able to

Read a single-table `SELECT` and name the rows it returns before running it — then write the one query that answers a question somebody actually asked.

What you drill

01

Predicates that mean it

AND, OR, NOT, brackets, BETWEEN, IN and both wildcards.

02

Order, ties and pages

Second keys that decide, and the arithmetic under OFFSET.

03

Columns, values, totals

Projection, DISTINCT, computed columns and six aggregates.

04

A prompt of your own

Load a 24-row table and put real questions to it.

The pitch

What you practise, and what you leave with

A SQL prompt of your own, a 24-row shop table loaded into it, and five questions answered with one query each.

You will practise

  • Rows returned, with and without DISTINCT
  • WHERE: AND, OR, NOT, BETWEEN, IN, LIKE
  • ORDER BY with ties, and LIMIT arithmetic
  • Aliases, aggregates, clauses spelled right

Afterwards you can

  • Say what a SELECT returns before running it
  • Read an unbracketed AND beside an OR correctly
  • Turn a page number into the right OFFSET
  • Answer a stated question with one query

Modules

6 modules, 20 items

Lessons explain one idea. Drills repeat it until it sticks. Build steps make something that exists afterwards.

  1. 1

    Say what comes back

    ~27 min

    Read a SELECT and name the rows and columns it returns, repeats and all.

    • LessonWhat a query hands back11 min
    • LessonDuplicates, and DISTINCT7 min
    • DrillCount the rows, then the values9 min
  2. 2

    Filter rows with WHERE

    ~34 min

    Run a predicate down a table and get the count right, brackets or no brackets.

    • DrillKeep only the matching rows11 min
    • DrillSettle it without brackets12 min
    • DrillMatch a range, a set, a pattern11 min
  3. 3

    Order it, then cut it

    ~20 min

    Break a tie on purpose, and say which rows a given page of a result holds.

    • DrillBreak a tie on purpose12 min
    • DrillTake the page you asked for8 min
  4. 4

    Compute, name and total

    ~49 min

    Name a computed column, total a table, and spell the clause from memory.

    • LessonName a column you computed12 min
    • LessonOne number out of every row8 min
    • DrillPut it where the clause sees it8 min
    • DrillCollapse a table to one number10 min
    • DrillSpell the clause from memory11 min
  5. 5

    Get a prompt and load it

    ~13 min

    Put a real SQL engine in front of you, and put the shop table into it.

    • Build stepGet a SQL prompt of your own6 min
    • Build stepLoad the shop table7 min
  6. 6

    Four questions, then your own

    ~44 min

    Write and run the query each one needs, then ask the table something of your own.

    • Build stepFilter it down and order it10 min
    • Build stepPage through the result8 min
    • Build stepValue the whole shelf10 min
    • Build stepTotal one aisle8 min
    • Build stepAsk it something of your own8 min

More in Data & databases

Keep going

All Data & databases projects

ORMs

Count the queries before the mapper sends them.

Intermediate~4h6 modules

SQL: Joins & NULL

The rows that found no partner, and the values that are not there.

Advanced~4h5 modules

SQL: Advanced

The clause you did not write decided the number.

Advanced~6h7 modules

PostgreSQL

Predict what the engine will do before it does it.

Advanced~4h6 modules

Say what a query returns before you run it.

The first item is free. ~3h of focused work, at your own pace.

Start SQL: QueriesAll projects

Intermediate~3h6 modules