Course Library · Lesson 2 of 6

Digital · Guided lesson

Select, filter and order rows

About 29 minutes · 7 reading + 22 practice and checks

Time includes reading, practice, and questions. Extra videos or further study are optional.

See this course’s project requirements

Answer whether one assigned workshop session can accommodate six additional bookings using your accumulated query collection.

Keep your working files in your own notebook or software. Save a private description, checklist, and work link in My Proof.

What to include

Include the complete original two-table fixture and dictionary.
Show filtering, ordering and grouped summaries with expected results.
Demonstrate both session-preserving and venue-preserving joins.
Detect an invalid test record and record missing-data limits.
Deliver the decision query with reproducible output and interpretation.

Review and revise

Use these criteria to check your work and choose what to improve.

Question fit: row and group meanings match the decision.
Query correctness: results follow from explicit SQL or accurate traces.
Quality: totals reconcile and missing matches are explained.
Reproducibility: fixture, queries and limits travel with the answer.

Open the full course project

Want more background?

Before you begin

The idea in everyday life

A large result is not automatically a useful answer. Someone planning a workshop wants to see which sessions still have seats, not every stored field. SQL separates the question about rows from the choice of displayed columns. You will calculate availability from the fixture and make the output order reproducible instead of relying on storage order.

The idea

SELECT chooses columns, WHERE chooses rows, and ORDER BY makes order explicit.

Try it

One step at a time.

Use the sample or information you have permission to use. Keep your work as you go. You can pause between steps.

1
Write the question first: which sessions have at least four unbooked seats? Express availability as seats minus booked, and decide which columns will explain the result without unnecessary data.
2
Use SELECT for id and the arithmetic expression. Add AS available to give the calculated column a readable name. FROM identifies the table that supplies the rows.
3
Place seats - booked >= 4 in WHERE. Trace the expression separately for every fixture row before deciding whether that row belongs in the result.
4
Add ORDER BY available DESC, id ASC. DESC sorts availability from largest to smallest; ASC sorts tied rows by ID from smallest to largest. Without ORDER BY, do not assume a fixed result order.
5
Save the query and the expected output. Change the threshold to six, trace again, and explain which result disappears. Both executions or paper traces belong in your final query collection.

Example

See how it works.

Fictional worked query:

sql

SELECT id, seats - booked AS available
FROM sessions
WHERE seats - booked >= 4
ORDER BY available DESC, id ASC;

The four available counts are 4, 0, 9 and 6. Therefore the output pairs are (3,9), (4,6), (1,4), in that order. Session 2 is excluded because it has no open seats. Raising the threshold to six removes session 1 while preserving the other two rows. This query includes an unassigned venue because venue assignment was not part of its filter. Add that requirement explicitly if the planning question needs it.

Check your work

Did you cover these?

Availability is calculated for all four rows.
The threshold includes equality.
The output includes an explicit tie-break order.

Your work

What to finish

Two filtering queries with traced row inclusion and ordered outputs.

Common mistake

Watch for this

Assuming an unstated condition, such as assigned venue, is automatically applied.

How can I tell I understand it?

Explain inclusion and order independently.

Finish this lesson

Pass the check. We’ll save the progress.

Answer all 3 questions above and get at least 2 right. You can review the explanations and try again.

Go to the knowledge check

Tools, templates, and lesson sources

Tools and templates

Tools open in a new tab so you can keep your place here.

SQL starter packFictional database setup, ten reference queries, expected tables, a blank answer brief, and a runner using Python’s built-in SQLite.

Your course project worksheetA downloadable text worksheet with each lesson’s working output, space for your answers, and the shared final-project checklist. Save your own copy.

Sources and further reading

Explore the references behind this lesson.

Harvard CS50 — Introduction to Databases with SQL

Source & access notes · checked 2026-09-10

Opened university OpenCourseWare overview; verified querying, relationships, design and optimization topics. Used as optional further study, with original fixtures here.

Access: Free OpenCourseWare reading and videos. External grading or progress services may require accounts; optional verified certificates have separate enrollment and may be paid. This course awards no Harvard credit or affiliation.

Reuse: Linked for reference. Examples, activities and assessments here are original; free access alone does not grant permission to reuse source text or branding.

SQLite — SELECT reference

Source & access notes · checked 2026-09-10

Opened official SELECT documentation covering WHERE, GROUP BY, joins, ORDER BY and NULL behavior. All supplied queries target SQLite syntax.

Access: Free public reading; no account or certificate is required for this Life Starter course.

Reuse: Linked for reference. Examples, activities and assessments here are original; free access alone does not grant permission to reuse source text or branding.

Report a problem with this lesson

Tell us what went wrong. Please leave out personal information and private project contents.