Skip to content

Day 02 Part 1 — SQL Basics: Agenda

SQL is the language every data analyst uses every single day. By the end of this session you will be writing queries that answer real business questions — not just retrieving rows, but filtering, sorting, and shaping data to tell a story.

Session Overview

Duration: 3 hours Prerequisite: None — we start from scratch Database: Standard SQL (examples run in MySQL and PostgreSQL unless noted)

Learning Objectives by Level

Beginner

By the end of this session you will be able to: - Write a SELECT statement to retrieve specific columns from a table - Use WHERE to filter rows based on conditions - Sort results with ORDER BY - Limit result sets with LIMIT / TOP - Eliminate duplicates with DISTINCT - Explain what NULL means and how to handle it correctly in filters

Mid-level

Additionally: - Explain the logical execution order of SQL (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT) and why it matters - Use COALESCE to handle NULL in calculations - Write multi-condition filters using AND, OR, NOT with correct parenthesisation - Diagnose and fix silent bugs caused by = NULL, NOT IN with NULLs, and AND/OR precedence

Senior

Additionally: - Identify performance anti-patterns: SELECT *, leading wildcards in LIKE, OFFSET pagination on large tables - Write production-safe queries: specific columns, meaningful aliases, defensive NULL handling - Articulate the cost of a query before running it

Session Flow

Time Topic File
0:00 – 0:30 The SELECT statement — anatomy of a SQL query 01-select-statement
0:30 – 1:00 Filtering with WHERE — conditions, operators, NULL 02-where-clause
1:00 – 1:20 Sorting results with ORDER BY 03-order-by
1:20 – 1:40 LIMIT, DISTINCT, and result shaping 04-limit-and-distinct
1:40 – 2:10 Guided practice — basic query patterns 05-basic-query-practice
2:10 – 3:00 Exercises + review 06-sql-exercises

Setup

You need access to a SQL environment. Any of these work:

Option A — Browser (no install)

  • DB Fiddle — db-fiddle.com — select MySQL 8 or PostgreSQL 15
  • SQL Fiddle — sqlfiddle.com — supports MySQL, PostgreSQL, SQL Server

Option C — Local database

  • SQLite — zero setup, file-based. DBeaver connects to it instantly. Good for local practice.
  • PostgreSQL 15+ — recommended for the full course. Closest to production environments.
  • MySQL 8+ — widely used in industry, slightly different syntax in a few places (noted throughout).

For beginners

Start with DB Fiddle (MySQL 8 mode). No install required — paste the CREATE TABLE and INSERT statements from 05-basic-query-practice and start querying immediately.

For senior analysts

Use PostgreSQL locally with DBeaver. PostgreSQL is strict about GROUP BY rules, supports FULL OUTER JOIN natively, and has EXPLAIN ANALYZE for query planning — all of which matter for real work.

The Sample Schema

Every query in this module uses one consistent 4-table schema. Internalise this structure — it will appear in all examples and exercises.

-- One row per customer
customers (
    customer_id   VARCHAR(10)   PRIMARY KEY,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    email         VARCHAR(100),
    city          VARCHAR(50),
    country       VARCHAR(50),
    signup_date   DATE,
    segment       VARCHAR(20)   -- 'VIP', 'Regular', 'New'
)

-- One row per order
orders (
    order_id      VARCHAR(10)   PRIMARY KEY,
    customer_id   VARCHAR(10),  -- FK → customers.customer_id
    product_id    VARCHAR(10),  -- FK → products.product_id
    order_date    DATE,
    quantity      INT,
    unit_price    DECIMAL(10,2),
    discount_pct  DECIMAL(5,2), -- NULL if no discount
    status        VARCHAR(20),  -- 'completed', 'pending', 'cancelled', 'refunded'
    delivery_date DATE          -- NULL if not yet delivered
)

-- One row per product
products (
    product_id    VARCHAR(10)   PRIMARY KEY,
    product_name  VARCHAR(100),
    category      VARCHAR(50),
    unit_price    DECIMAL(10,2),
    cost_price    DECIMAL(10,2),
    stock_qty     INT
)

-- One row per line item in an order (for multi-product orders)
order_items (
    item_id       INT           PRIMARY KEY AUTO_INCREMENT,
    order_id      VARCHAR(10),  -- FK → orders.order_id
    product_id    VARCHAR(10),  -- FK → products.product_id
    quantity      INT,
    unit_price    DECIMAL(10,2)
)

Entity Relationships

customers ──< orders >── products
                │
                └──< order_items >── products
  • One customer can have many orders (1:N)
  • One order has many items (1:N via order_items)
  • One product can appear in many order items (1:N)

Why a 4-table schema?

This schema mirrors what you'll encounter in real e-commerce and SaaS databases. orders references customers and products via foreign keys. Understanding these relationships is the foundation for writing correct JOINs.

Key Terms for This Session

Term Meaning
SELECT Specify which columns to return
FROM Specify which table to query
WHERE Filter rows before returning
ORDER BY Sort the result set
LIMIT Cap the number of rows returned (MySQL/PostgreSQL); TOP in SQL Server
DISTINCT Remove duplicate rows from results
NULL Missing or unknown value — not zero, not empty string
Primary key A column (or combination) that uniquely identifies each row
Foreign key A column that references the primary key of another table
Alias A temporary name assigned to a column or table with AS

SQL Execution Order — Memorise This

You write SQL in this order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

SQL executes it in this order:

1. FROM        — which table(s)?
2. WHERE       — which rows survive?
3. GROUP BY    — collapse into groups?
4. HAVING      — which groups survive?
5. SELECT      — which columns and calculated values?
6. ORDER BY    — in what order?
7. LIMIT       — how many rows?

This order explains why you cannot use a SELECT alias in a WHERE clause — the alias is created at step 5, but WHERE runs at step 2. It also explains why aggregate functions belong in HAVING, not WHERE.

← Day 01 Part 2: Excel · Next: SELECT Statement →