SQL Foundations

Chapter 1 8 code blocks 4781 words

Almost every dataset a working analyst touches lives inside a database, not inside a CSV file. Sales, users, orders, exam results, sensor readings — a company stores them in tables and hands you a connection string, not a file. That is why SQL comes before pandas in a data analytics course: pandas can only analyse data you already have, and SQL is how you get it.

This chapter builds the vocabulary — table, row, column, key, NULL, dialect — and sets up the small database that every single query in Chapters 2–13 will run against. Take it slowly; almost every confusing thing later in SQL traces back to one of the ideas on this page.

What is SQL?

SQL stands for Structured Query Language. It is the language you use to talk to a relational database — to ask it questions, and to change what it stores.

The single most important property of SQL is that it is declarative. In Python you write how to do something, step by step. In SQL you write what you want, and the database figures out how to get it.

This has a real consequence for your students: two very differently-written SQL queries can produce the exact same result at the exact same speed, because the database rewrites them internally. It also means when a query is slow, you usually fix it by giving the planner a better tool (an index — Chapter 11), not by rewriting the loop.

SQL vs a database vs a DBMS

Students constantly mix these three words. Separate them once, clearly:

The "R" in RDBMS is relational: data is split across several tables that are related to each other by shared key columns. Chapter 5 (JOINs) is entirely about putting those related tables back together.

Why an analyst must know SQL

Four honest reasons, worth telling students on day one:

1. The data is there. Real company data sits in Postgres/MySQL/BigQuery. No SQL means waiting for someone else to export a CSV for you.
2. Filtering at the source is dramatically faster. Pulling 50 million rows into pandas will exhaust your RAM. WHERE year = 2026 lets the database return 200,000 rows instead — the machine that owns the data does the heavy work.
3. It is the most universal skill in the field. Analysts, data engineers, backend developers, BI specialists and ML engineers all use it. Libraries change every few years; SQL from 1990 still runs.
4. Interviews test it. A data analytics interview almost always contains a live SQL task — usually a JOIN plus a GROUP BY, very often a window function.

Tables, rows, columns, keys

A table (also called a relation) looks exactly like a spreadsheet, with two strict extra rules.

The two strict rules that a spreadsheet does not have:

• Every column has one fixed data type. A column declared INTEGER cannot suddenly hold "unknown" in row 500. In Excel people do this constantly, and it is exactly why Excel data is so painful to analyse.
• Rows have no order. This surprises everyone. A table is a set of rows; the database may return them in any order it likes. If you want a specific order you must write ORDER BY. Never trust the order you saw last time.

Primary key (PK)

A primary key is the column (or combination of columns) that uniquely identifies each row. It must be unique and never NULL. One table has exactly one primary key.

Ask students: could first_name be the primary key of students? No — two students can both be named Aziza. Could phone_number? Risky — people change numbers and a row's identity would change with it. That is why we use a meaningless auto-incrementing student_id: it carries no business meaning, so nothing in the real world can ever force it to change.

Foreign key (FK)

A foreign key is a column that points at the primary key of another table. It is the actual "relation" in relational database.

The foreign key does two jobs at once:

• It tells you (and the query planner) how to JOIN the tables back together.
• It tells the database to refuse invalid data. If you try to insert an enrollment for student_id = 999 and no such student exists, the insert fails. This is called referential integrity, and it is why databases end up much cleaner than spreadsheets.

Data types

Every column is declared with a type. Types differ slightly per database, but these are the ones that cover 95% of analytics work:

NULL — the third value

NULL is the most misunderstood thing in SQL, so introduce it early and deliberately.

NULL is not zero. NULL is not an empty string. NULL means "we do not know." It is the absence of a value.

The consequence is three-valued logic. Any comparison with NULL returns neither TRUE nor FALSE — it returns UNKNOWN, and WHERE only keeps rows that are TRUE.

So this query returns zero rows, even when ungraded students exist:

-- WRONG: never returns anything
SELECT * FROM enrollments WHERE grade = NULL;

-- CORRECT: IS NULL / IS NOT NULL are the only way
SELECT * FROM enrollments WHERE grade IS NULL;
SELECT * FROM enrollments WHERE grade IS NOT NULL;

Result 1

0 rows

Result 2

… first 15 of 674 rows

674 rows

Result 3

… first 15 of 1826 rows

1826 rows

6 ms

:::tip 💡 Teaching line "NULL is not a value — it is a hole where a value should be. You cannot compare a hole to anything, you can only ask whether the hole exists." Students remember this. We return to NULL properly in Chapter 3, and again in Chapter 4 where COUNT(*) and COUNT(column) disagree because of it. :::

DDL / DML / DQL / DCL / TCL

SQL commands are grouped into five families. Students do not need to memorise the acronyms, but they do need to know which commands are dangerous.

SQL dialects (SQLite, PostgreSQL, MySQL)

There is an official ANSI SQL standard, and then every database adds its own extensions. The core — SELECT, WHERE, JOIN, GROUP BY, window functions — is the same everywhere. The differences show up in string functions, date functions, and how you limit rows.

COALESCE() works in all of them, so prefer it — we use it throughout this guide.

This guide uses SQLite for one practical reason: it needs no server, no installation, no password. The database is a single file, and Python ships with sqlite3 built in. Everything you learn transfers directly; where a dialect differs in an important way, there is a note.

Our sample database

Every query in Chapters 2–13 runs against this database. It is deliberately small enough to check answers by hand, and structured richly enough to demonstrate every kind of JOIN, aggregation and window function.

The full setup script

Save this as school_setup.sql, or paste it straight into your SQL tool. It creates all four tables and fills them with data.

-- ============================================
-- school.db  —  sample database for this guide
-- ============================================

DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS teachers;

CREATE TABLE students (
    student_id     INTEGER PRIMARY KEY,
    first_name     TEXT    NOT NULL,
    last_name      TEXT    NOT NULL,
    gender         TEXT    CHECK (gender IN ('M','F')),
    city           TEXT,
    birth_date     DATE,
    enrolled_date  DATE    NOT NULL
);

CREATE TABLE teachers (
    teacher_id  INTEGER PRIMARY KEY,
    full_name   TEXT    NOT NULL,
    subject     TEXT,
    hired_date  DATE,
    salary      DECIMAL(10,2)
);

CREATE TABLE courses (
    course_id  INTEGER PRIMARY KEY,
    title      TEXT    NOT NULL,
    category   TEXT,
    credits    INTEGER DEFAULT 3,
    price      DECIMAL(10,2)
);

CREATE TABLE enrollments (
    enrollment_id  INTEGER PRIMARY KEY,
    student_id     INTEGER NOT NULL,
    course_id      INTEGER NOT NULL,
    teacher_id     INTEGER,
    enrolled_at    DATE    NOT NULL,
    grade          INTEGER,            -- NULL = not graded yet
    status         TEXT    DEFAULT 'active',
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id)  REFERENCES courses(course_id),
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

Where the data comes from

Nobody types this data in by hand. datasets/generate.py builds school.db from a fixed random seed, so every person who runs it gets byte-identical data — and every number printed in this guide comes from querying that file, not from an author's imagination.

import pandas as pd, sqlite3
con = sqlite3.connect(DB)
show(pd.read_sql_query("""
    SELECT 'students'    AS tbl, COUNT(*) AS rows FROM students
    UNION ALL SELECT 'teachers',    COUNT(*) FROM teachers
    UNION ALL SELECT 'courses',     COUNT(*) FROM courses
    UNION ALL SELECT 'enrollments', COUNT(*) FROM enrollments
""", con))

And the rows themselves:

SELECT student_id, first_name, last_name, gender, city, enrolled_date
FROM students
ORDER BY student_id
LIMIT 8;

8 rows

1 ms

:::tip 💡 Why this data looks the way it does The generator plants deliberate teaching traps, so real behaviour shows up instead of a clean fairy tale. Each one is checked by a query later in the guide:

• 8 students have city = NULL — so COUNT(city) and COUNT(*) disagree, and WHERE city <> 'Tashkent' silently drops them.
• "Deep Learning" has no enrollments at all — the only way it appears in a report is a LEFT JOIN.
• 674 grades are NULL (still studying, or dropped) — AVG(grade) ignores them, which is usually right but must be said out loud.
• 67 enrollments have teacher_id = NULL — teacher not assigned yet; another LEFT JOIN case.
• Four statuses (completed, active, dropped, failed) — built for GROUP BY and CASE WHEN.
• One teacher drops far more students than the rest — you will find who in Chapter 5. :::

How to run these queries

Three options, from easiest to most professional. Any one of them is enough for the whole guide.

Option 1 — DB Browser for SQLite (best for the classroom)

A free graphical program (sqlitebrowser.org). Students see the tables, click through the data, and type queries in an "Execute SQL" tab. Nothing to install beyond the app itself, and no command line to scare beginners.

1. New Database → save it as school.db → close the "create table" popup.
2. Go to the Execute SQL tab, paste the setup script, press Ctrl+Return.
3. Press Write Changes (this is the step everybody forgets).

Option 2 — the sqlite3 command line

`sqlite3 school.db < school_setup.sql # build the database sqlite3 school.db # open a session

sqlite> .headers on sqlite> .mode column sqlite> SELECT * FROM students LIMIT 3; sqlite> .tables sqlite> .schema students sqlite> .quit:::tip 💡 Always run these two first.headers onand.mode column` turn unreadable pipe-separated output into a proper table with column names. Without them students cannot tell which column is which. :::

Option 3 — Python (what we build towards in Chapter 12)

import sqlite3
import pandas as pd

# the connection: a file, no server, no password
conn = sqlite3.connect("school.db")

# build the database once, from the setup script
with open("school_setup.sql", "r", encoding="utf-8") as f:
    conn.executescript(f.read())

# now any query comes back as a DataFrame
df = pd.read_sql("SELECT * FROM students;", conn)
print(df.head())

conn.close()

This is the bridge between the two halves of this guide: SQL brings the data in, and from here Seaborn plots it.

Your first query

The smallest useful SQL statement:

SELECT * FROM students; Read it out loud in three pieces, right to left:

• FROM students — which table to look at.
• SELECT * — which columns to show; * means all of them.
• ; — end of statement. Some tools tolerate a missing semicolon; get students into the habit anyway, because as soon as they run two statements together it becomes mandatory.

Now something with an actual question behind it — "which students from Tashkent, newest first?":

SELECT first_name, last_name, city, enrolled_date
FROM students
WHERE city = 'Tashkent'
ORDER BY enrolled_date DESC;

… first 15 of 82 rows

82 rows

1 ms

Result:

Rules that apply to every query you will ever write

• Keywords are case-insensitive. select, SELECT and SeLeCt all work. Convention: write keywords in UPPERCASE and your own names in lowercase, so the structure is visible at a glance.
• Data is case-sensitive. 'Tashkent' and 'tashkent' are different strings in PostgreSQL. (SQLite compares ASCII text case-insensitively with LIKE but not with =.) This trips students up constantly.
• Strings use single quotes. 'Tashkent'. Double quotes mean an identifier (a column or table name), not text — a real difference, not a style choice.
• Whitespace is free. Put each clause on its own line. A five-line query is read in seconds; the same query on one line is not.
• Comments: -- to end of line, or /* several lines */.

Order of execution

This single idea explains more student errors than anything else in SQL. SQL is not executed in the order you write it.

Two rules that students hit in week one both fall out of this diagram:

1. WHERE cannot see an alias, but ORDER BY can

-- ERROR: 'age_years' does not exist yet at step 2,
-- because SELECT (where it is created) is step 5
SELECT first_name, (2026 - CAST(strftime('%Y', birth_date) AS INTEGER)) AS age_years
FROM students
WHERE age_years > 20;

-- WORKS: ORDER BY is step 7, so the alias already exists
SELECT first_name, (2026 - CAST(strftime('%Y', birth_date) AS INTEGER)) AS age_years
FROM students
ORDER BY age_years DESC;

Result 1

… first 15 of 300 rows

300 rows

Result 2

… first 15 of 300 rows

300 rows

1 ms

The fix for the first one is either to repeat the expression in WHERE, or to wrap the query in a subquery or CTE (Chapter 6) so the alias is already computed by the time you filter.

2. WHERE filters rows; HAVING filters groups

WHERE runs at step 2, before grouping — so it cannot use AVG() or COUNT(), because no groups exist yet. HAVING runs at step 4, after grouping, so it can.

-- "Among completed enrollments only, which courses average above 80?"
SELECT course_id, AVG(grade) AS avg_grade
FROM enrollments
WHERE status = 'completed'      -- step 2: filters individual ROWS
GROUP BY course_id              -- step 3: makes the groups
HAVING AVG(grade) > 80          -- step 4: filters whole GROUPS
ORDER BY avg_grade DESC;        -- step 7: alias is available here

1 row

1 ms

:::tip 💡 One sentence to make students repeat "WHERE filters rows before grouping. HAVING filters groups after grouping." This is also one of the most common interview questions in the whole language — Chapter 4 goes deeper. :::

Common mistakes

Interview questions

Q: What is the difference between SQL and MySQL?

SQL is the language. MySQL is a program (an RDBMS) that understands that language and stores the data. PostgreSQL, SQLite and SQL Server are alternatives to MySQL, and they all speak SQL with small dialect differences.

Q: What is a primary key, and how does it differ from a unique key?

Both enforce uniqueness. A primary key additionally cannot be NULL, and a table can only have one of them. A unique key can allow a NULL (once, in most databases), and a table can have several. The primary key is the row's official identity.

Q: What is a foreign key for?

It stores the link to another table's primary key, and it enforces referential integrity — the database refuses to insert a row pointing at a parent that does not exist, and refuses to delete a parent that still has children (unless ON DELETE CASCADE is set).

Q: Is NULL the same as 0 or an empty string?

No. 0 and '' are known values. NULL means unknown. Any comparison with NULL yields UNKNOWN, so = NULL never matches; use IS NULL. NULL also propagates through arithmetic: NULL + 10 is NULL.

Q: What is the difference between WHERE and HAVING?

WHERE filters individual rows before GROUP BY, so it cannot use aggregate functions. HAVING filters groups after GROUP BY, so it can. If there is no GROUP BY, prefer WHERE — it removes rows earlier and therefore does less work.

Q: In what order does a SELECT statement actually execute?

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. That is why an alias created in SELECT is usable in ORDER BY but not in WHERE.

Q: DELETE vs TRUNCATE vs DROP?

DELETE removes rows (can have a WHERE, can be rolled back, logs each row). TRUNCATE empties the whole table quickly, with no WHERE. DROP removes the table itself — structure and all.

Q: Why avoid SELECT * in production code?

It moves unnecessary data over the network, prevents some index-only optimisations, and breaks silently when the table's columns change. Naming columns also documents the query's intent.

Summary

• SQL is declarative — you describe the result you want; the database chooses how to produce it.
• A table = rows (records) × columns (typed attributes). Rows have no inherent order — if you need one, write ORDER BY.
• Primary key = unique, never NULL, one per table. Foreign key = a pointer to another table's PK; it is the "relation" in relational and it protects data quality.
• NULL means unknown, not zero. Compare with IS NULL / IS NOT NULL, never =.
• Use DECIMAL for money, a real DATE type (or 'YYYY-MM-DD' text) for dates.
• Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. This explains aliases, and it explains WHERE vs HAVING.
• Our school.db — students, teachers, courses, enrollments — carries deliberate NULLs and one empty course, so that later chapters can show real behaviour instead of an idealised example.

Next: Chapter 2 takes SELECT apart properly — column expressions, aliases, WHERE, ORDER BY, LIMIT, DISTINCT.