Lesson 2 of 2

SQL & Data Basics for App Devs

Debugging a slow query a PM flagged as "the app is broken" is usually a five-minute fix once you know what to look for.

The core SQL you actually need

Four concepts cover most of what a working app developer touches day to day.

SELECT / WHERE / JOIN

Filtering rows and combining tables — the majority of queries you will ever write or debug.

GROUP BY / aggregates

Collapsing many rows into a summary — counts, sums, averages per group — the shape behind almost every dashboard number.

Indexes

A lookup structure that lets the database find matching rows without scanning the whole table — the difference between instant and timed-out.

Transactions

A group of writes that either all succeed or all roll back together — what keeps a multi-step operation from leaving data half-updated.

Enough data modeling to be dangerous

A handful of terms let you read an existing schema without a tutorial.

A primary key uniquely identifies a row in its own table; a foreign key is a column referencing a primary key in another table, establishing a relationship between them. Most schemas boil down to one-to-many relationships (one customer, many orders) and occasionally many-to-many ones (many students, many courses) via a join table in between.

Exercises

Look at the sanitized query and fix below, then work through both exercises.

-- Flagged by a PM as "the app is broken" — actually just slow
select * from orders
where customer_email = 'someone@example.com';
-- 4.2s on a 12M-row table, no index on customer_email

-- After adding an index:
create index idx_orders_customer_email on orders (customer_email);
-- same query: 8ms

Concept check

Answer in your head first, then click a question to reveal the answer.

Scenario checklist

Work through the sample slow query and check each item off. Progress is saved in your browser.

Identify what the original query is doing and why it is slowIdentify the column that needs an index based on the WHERE clauseConfirm the improvement an index would make, and whyDetermine whether adding this index could have any downsideDescribe how you would verify the fix before and after in production

Go deeper

These primers are a starting point. Read the primary sources to build real fluency.

PostgreSQL Docs: The SQL LanguageSELECT, JOIN, and aggregates, from the official tutorial PostgreSQL Docs: Introduction to indexesWhat an index actually does and when the planner will use one PostgreSQL Docs: TransactionsThe all-or-nothing guarantee behind a multi-step write

Training Notes

What building this project taught me.

This module isn't a flagged gap, but it comes up everywhere

SQL fluency wasn't a named gap in the job-hunting scan, but it was one of the most frequently required skills across all 30+ job descriptions reviewed — hence its place here rather than a dedicated module of its own.

This lesson connects to the Data trail, deliberately

Enough SQL and data modeling to read an unfamiliar schema is the shared prerequisite between this app-dev-facing lesson and the Data Engineering Trail's Modern Data Stack Foundations lesson.