Setting Up Your Practice Environment

You cannot learn SQL by reading it. You need a real server you can break, so the first job is a throwaway PostgreSQL you do not mind destroying.

Pick one of the three routes below. They all end at the same place: a prompt where you can type SQL.

Route 1: Docker (recommended)#

Docker gives you a disposable server and keeps your machine clean. If you ruin everything, delete the container and start again in ten seconds.

docker run --name sqlcourse \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  -v sqlcourse-data:/var/lib/postgresql/data \
  -d postgres:16

Each flag does something specific:

• --name sqlcourse gives the container a name you can refer to later.
• -e POSTGRES_PASSWORD=postgres sets the password for the postgres superuser. Fine for a laptop, never for a real server.
• -p 5432:5432 maps the container's port to your machine, so clients on your laptop can reach it.
• -v sqlcourse-data:... stores the data in a named volume, so stopping the container does not wipe your tables.

Open a shell inside it:

docker exec -it sqlcourse psql -U postgres

Route 2: Native install#

On macOS, brew install postgresql@16 followed by brew services start postgresql@16. On Ubuntu or Debian, sudo apt install postgresql. On Windows, use the official installer from postgresql.org, which also installs psql and pgAdmin.

Native installs create a postgres user and a default database. Connect with psql -U postgres (you may need sudo -u postgres psql on Linux).

Route 3: SQLite for the first four modules#

SQLite needs no server at all. It is one file, and it ships inside Python. It supports joins, aggregates, CTEs and window functions, so Modules 2 to 4 will mostly run on it.

It does not support stored procedures, triggers written in PL/pgSQL, row-level locking, or the concurrency material in Module 5. Use it to start, then move to PostgreSQL before Module 5.

Create the course database#

Inside psql:

CREATE DATABASE shop;
\c shop

CREATE DATABASE makes a new, empty database. \c shop is not SQL. It is a psql meta-command that reconnects your session to the new database. The prompt changes from postgres=# to shop=#.

Everything you create from now on goes into shop, and you can drop the whole thing with DROP DATABASE shop; from another database when you want a clean slate.

How a connection is described#

Every client needs the same five facts, and tools usually pack them into one URL:

postgresql://postgres:postgres@localhost:5432/shop
   |            |        |        |        |     |
 protocol     user   password   host     port  database

When a connection fails, one of those five is wrong. Port conflicts (another Postgres already on 5432) and wrong passwords cause almost every first-day failure.

The psql commands worth memorizing#

Anything starting with a backslash is handled by psql itself and never reaches the server.

Try them as soon as you have tables. \d is the one you will type a hundred times a week.

Two habits that save hours#

End statements with a semicolon. If you forget, psql assumes the statement continues and shows a shop-# prompt with a dash. Nothing is wrong. Type the semicolon and press Enter.

Test with something harmless first.

SELECT version();
SELECT 1 + 1 AS two;
SELECT CURRENT_DATE;

Expected output for the second one:

 two
-----
   2
(1 row)

A SELECT without a FROM is legal in PostgreSQL. It evaluates an expression and returns one row, which makes it a calculator.

Common mistakes#

Running everything as the superuser. For learning it is fine. In real systems an application connecting as postgres can drop any database. Module 6 covers creating restricted roles.

Forgetting which database you are in. Creating tables in the default postgres database and then wondering why shop is empty is a classic. The prompt shows the name. Read it.

Losing data by deleting the volume. docker rm -f sqlcourse keeps the volume. docker volume rm sqlcourse-data does not forgive you.

Pasting Windows-style smart quotes. If a script copied from a web page fails with a strange character error, the quotes were curly. SQL needs plain single quotes.

Practice#

1. Start your server, connect, and run SELECT version();. Write down the major version number.
2. Create the shop database, switch to it, and run \dt. What does it print, and why?
3. Run SELECT 10 / 4 AS a, 10 / 4.0 AS b;. The two columns differ. Note the difference now, because Module 2 explains it.
4. Deliberately connect with a wrong password or wrong port and read the error. Which of the five connection facts does the message point at?