Module 1 · Foundations

Set up & run SQL anywhere

Beginner 12 min · hands-on Zero-install option included

You do not need to install a database server to learn SQL. SQLite ships inside Python, so you can be running real queries in under a minute. This lesson covers the four practical options, which dialect to learn, and how to load the course project database.

1. Four ways to get a database

Option	Effort	Best for	Limitation
A · SQLite	Already installed	Learning, this course's project, quick analysis	Single-file; weak types; no real concurrency
B · Postgres in Docker	2 min	Learning the most standards-compliant dialect	Needs Docker
C · MySQL in Docker	2 min	What most web companies actually run	Needs Docker (see the MySQL course)
D · Browser playground	0 min	Trying one snippet, sharing a repro	No persistence, small data

2. Option A — SQLite (recommended for this course)

python -c "import sqlite3; print(sqlite3.sqlite_version)"
# 3.50.4  -- anything 3.25+ supports window functions and CTEs, which is all we need

import sqlite3

con = sqlite3.connect("practice.db")     # ":memory:" for a throwaway database
con.row_factory = sqlite3.Row            # rows behave like dicts

con.executescript("""
    DROP TABLE IF EXISTS employees;
    CREATE TABLE employees (
        emp_id   INTEGER PRIMARY KEY,
        name     TEXT NOT NULL,
        dept     TEXT,
        salary   INTEGER,
        hired_on DATE
    );
    INSERT INTO employees (emp_id, name, dept, salary, hired_on) VALUES
      (1, 'Asha Nair',   'Engineering', 120000, '2021-03-01'),
      (2, 'Ravi Menon',  'Engineering',  98000, '2022-07-15'),
      (3, 'Meera Iyer',  'Sales',        87000, '2020-01-20'),
      (4, 'Arjun Das',   'Sales',        91000, '2023-05-02'),
      (5, 'Divya Rao',   'Marketing',    76000, '2022-11-11');
""")
con.commit()

for row in con.execute("""
        SELECT   dept, COUNT(*) AS n, ROUND(AVG(salary)) AS avg_salary
        FROM     employees
        GROUP BY dept
        ORDER BY avg_salary DESC"""):
    print(dict(row))

con.close()

{'dept': 'Engineering', 'n': 2, 'avg_salary': 109000.0} {'dept': 'Sales', 'n': 2, 'avg_salary': 89000.0} {'dept': 'Marketing', 'n': 1, 'avg_salary': 76000.0}

Prefer a shell?

Download the sqlite3 CLI from sqlite.org (a single executable, no installer), then sqlite3 practice.db. Useful dot-commands:

.tables            # list tables
.schema employees  # show the DDL
.headers on        # column names in output
.mode column       # aligned output  (.mode box is prettier in 3.33+)
.read seed.sql     # run a file
.quit

3. Load this course's project database

cd sql/project
python build_data.py        # writes retail.db, schema.sql, seed.sql, stats.txt
python solutions.py         # runs all 30 challenge solutions and reports PASS/FAIL

import sqlite3, textwrap

con = sqlite3.connect("sql/project/retail.db")

def q(sql, limit=20):
    """Run a query and print it as an aligned table."""
    cur = con.execute(sql)
    cols = [d[0] for d in cur.description]
    rows = cur.fetchmany(limit)
    w = [max(len(str(c)), *(len(str(r[i])) for r in rows)) if rows else len(c)
         for i, c in enumerate(cols)]
    print(" | ".join(str(c).ljust(w[i]) for i, c in enumerate(cols)))
    print("-+-".join("-" * x for x in w))
    for r in rows:
        print(" | ".join(str(v).ljust(w[i]) for i, v in enumerate(r)))

q("SELECT category, COUNT(*) AS n FROM products GROUP BY category ORDER BY n DESC")

4. Options B and C — real servers in Docker

docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:16

# connect with the bundled client
docker exec -it pg psql -U postgres

# load the project schema (from your host machine)
docker exec -i pg psql -U postgres < sql/project/schema.sql
docker exec -i pg psql -U postgres < sql/project/seed.sql

docker run --name my8 -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=retail \
           -p 3306:3306 -d mysql:8

docker exec -it my8 mysql -uroot -psecret retail

docker exec -i my8 mysql -uroot -psecret retail < sql/project/schema.sql
docker exec -i my8 mysql -uroot -psecret retail < sql/project/seed.sql

The project schema is portable, with two caveats

INTEGER PRIMARY KEY auto-increments in SQLite only. For Postgres use GENERATED ALWAYS AS IDENTITY; for MySQL use INT AUTO_INCREMENT. The project's schema.sql marks these with [PG] / [MYSQL] comments. Since the seed supplies explicit ids, the plain version loads fine everywhere.

5. A GUI client

DBeaver

Free, cross-platform, connects to everything (SQLite, Postgres, MySQL, and 80 more). The default recommendation.

DataGrip

Paid, JetBrains. Best-in-class completion and refactoring if you live in SQL all day.

Built-in tools

pgAdmin for Postgres, MySQL Workbench for MySQL, DB Browser for SQLite. Fine, and free.

6. Which dialect should you learn?

Learn standard SQL and treat the differences as accents. Roughly 90% of what you write is identical everywhere. Here is the 10% that is not — keep this table for reference.

Task	PostgreSQL	MySQL	SQLite
Limit rows	`LIMIT 10`	`LIMIT 10`	`LIMIT 10`
Auto-increment PK	`GENERATED ALWAYS AS IDENTITY`	`AUTO_INCREMENT`	`INTEGER PRIMARY KEY`
String concat	`a \|\| b`	`CONCAT(a,b)`	`a \|\| b`
Current time	`NOW()`	`NOW()`	`DATETIME('now')`
Format a date	`TO_CHAR(d,'YYYY-MM')`	`DATE_FORMAT(d,'%Y-%m')`	`STRFTIME('%Y-%m',d)`
Date difference	`d2 - d1`	`DATEDIFF(d2,d1)`	`JULIANDAY(d2)-JULIANDAY(d1)`
Upsert	`ON CONFLICT … DO UPDATE`	`ON DUPLICATE KEY UPDATE`	`ON CONFLICT … DO UPDATE`
Aggregate strings	`STRING_AGG(x, ',')`	`GROUP_CONCAT(x)`	`GROUP_CONCAT(x)`
Case-insensitive LIKE	`ILIKE`	`LIKE` (default collation)	`LIKE` (ASCII only)
Show the plan	`EXPLAIN (ANALYZE)`	`EXPLAIN ANALYZE`	`EXPLAIN QUERY PLAN`
Quote an identifier	`"my col"`	`my col`	both

SQLite's one genuine weirdness

SQLite has dynamic typing: a column declared INTEGER will happily store the string 'banana'. Declared types are "affinities", hints rather than rules. That is fine for learning, but it means SQLite will not catch type bugs that Postgres and MySQL would. From SQLite 3.37 you can add STRICT to a table definition to get real type enforcement.

7. Habits to start with now

Keyword case: uppercase keywords, lowercase identifiers. Not required; universally expected.
One clause per line, aligned — it makes the logical order (lesson 02) visible at a glance.
Alias every table in a multi-table query, and qualify every column: o.amount, not amount. Future-you will thank you when a column is added upstream.
Never ship SELECT * — it breaks when columns are added, transfers data you do not need, and prevents covering-index optimisations (lesson 26).
Test destructive statements as a SELECT first. Write SELECT * FROM t WHERE …, check the rows, then swap SELECT * for DELETE.
Wrap manual writes in a transaction so you can ROLLBACK (lesson 25).

Recap

SQLite needs no install and supports everything this course teaches.
Docker gives you a real Postgres or MySQL in two minutes when you want server behaviour.
Learn standard SQL; keep the dialect table for dates, concat, upsert and auto-increment.
SQLite types are advisory unless you use STRICT tables.
Start the habits now: qualify columns, avoid SELECT *, preview deletes as selects.

Checkpoint

1 · You insert the string 'banana' into a SQLite column declared INTEGER. What happens?

SQLite uses dynamic typing: the declared type expresses a preference for how to store values, and non-conforming values are kept as-is. This is convenient for quick work and dangerous for correctness, which is why CREATE TABLE … STRICT was added in 3.37. Postgres and MySQL would both reject the insert.

2 · Why is SELECT * discouraged in production queries?

Three separate costs. Your application code may depend on column position or count; every unneeded column is bytes over the wire; and if an index contains all the columns you asked for, the engine can answer from the index alone — impossible when you ask for everything. SELECT * is fine while exploring interactively.