SQL Fundamentals: SELECT, Filtering, Grouping, and Aggregates

BeginnerDatabases~3 hours
SELECTWHEREGROUP BYindexes

I worked through core SQL retrieval – SELECT projection, WHERE filtering, GROUP BY aggregation, and ORDER BY – then added indexes to keep queries fast. Indexing the filtered columns turned full-table scans into millisecond index lookups.

Objective & Context

SQL is the lingua franca of data access. This lab builds query fluency and the habit of checking execution plans, the base for the joins, design, and administration labs.

Environment & Prerequisites

  • MySQL 8 or PostgreSQL 16 with a sample schema.
  • A table with enough rows to show index impact.
  • Access to EXPLAIN for plan inspection.

Step-by-Step Execution

1. Filter and aggregate

SELECT status, COUNT(*) FROM orders WHERE created > '2026-01-01' GROUP BY status;

2. Add a supporting index

CREATE INDEX idx_orders_created ON orders(created);

3. Confirm the plan uses the index

EXPLAIN SELECT * FROM orders WHERE created > '2026-01-01';
Index Range Scan using idx_orders_created  rows=812  (was Seq Scan rows=2.1M)

Validation & Testing

Run the query before and after indexing and compare the execution plan and latency. Pass criteria: correct aggregate results and a plan that switches from a sequential scan to an index range scan.

Advanced: Troubleshooting
  • Index unused: a function on the column (WHERE DATE(created)) defeats it; filter on the raw column.
  • Wrong counts: mind NULL handling in aggregates and HAVING vs WHERE.
  • Slow GROUP BY: index the grouping columns or pre-aggregate.

Key Results

  • Cut a filtered query from a multi-million-row scan to an index range scan.
  • Returned aggregate reports in milliseconds after indexing.
  • Verified plan changes with EXPLAIN rather than guessing.
  • Established index-aware querying as a default habit.

Related Labs