Databases, SQL & Your First Query — SQL Course

What you'll learn

A relational database stores durable facts under explicit rules, while SQL describes the result you want rather than the steps used to retrieve it. You will connect to PostgreSQL, inspect a table, and write your first reproducible query.

By the end of this lesson, you'll be able to:

Distinguish a server, database, schema, and table
Explain SQL's declarative model
Write and format a SELECT query
Read columns, rows, types, and ordering in a result set

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Relation	A table-shaped set of rows sharing named columns	Treat each row as one fact at a clearly defined grain
SELECT	A query that produces a result relation	List only the columns required by the question
Result order	The sequence in which rows are returned	Assume no order unless ORDER BY makes it explicit

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the first result set question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Retrieve a useful result

The statement names the desired columns and source. A semicolon terminates the command in psql.

01_first_query.sql

SELECT
  title,
  price,
  published_at
FROM books
ORDER BY published_at DESC, title ASC
LIMIT 5;

Ask the database about itself

PostgreSQL exposes current connection context as queryable values, which is useful when confirming the target environment.

02_connection_context.sql

SELECT
  current_database() AS database_name,
  current_schema() AS schema_name,
  current_user AS connected_as;

Production practice

Contract

A saved query states its purpose, selected columns, source relation, and any ordering or row limit.

Verification

Compare the result columns and row count with the business question, then vary one condition to confirm your mental model.

Operations

Know which database and role your session uses before executing any statement, especially outside a learning environment.

Common failure mode

SELECT * hides the output contract and changes when the table changes. It is convenient during exploration but a poor default for reusable queries.

Independent workshop

Create a first-query workbook that introduces the bookstore dataset to another learner.

Your finished workshop must include:

Connection-context query
Three explicit SELECT lists
One calculated expression
Deterministic ordering
A top-five query
Comments explaining result grain

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Relation: Treat each row as one fact at a clearly defined grain
SELECT: List only the columns required by the question
Result order: Assume no order unless ORDER BY makes it explicit

Quick check

1. Which rule best applies to Relation?

2. Which rule best applies to SELECT?

3. Which rule best applies to Result order?

Next: PostgreSQL Setup, psql & the Course Dataset