Topic 5 — Databases and SQL basics

Goal: Learn to ask a database questions with SQL — the analyst's key skill.

Lesson 5.1 — The day Nadia met the database

For three years Nadia ran inventory and weekly sales reports for a chain of stores, all of it in spreadsheets she built by hand. On her second day as an entry-level analyst at Perch, Marcus from marketing dropped by with a question: "How are sign-ups doing this month?" Nadia reached for her old reflex, the export button, and found nothing to export. The numbers lived somewhere else now: a database, a giant, organized store of Perch's data that no spreadsheet could open.

Priya, her mentor, pulled up a chair. "You don't download the database," she said. "You ask it questions. The language you ask in is SQL."

SQL stands for Structured Query Language. People say it two ways, "sequel" or just the letters "S-Q-L," and both are correct. It's the standard language for asking a database questions, and for a Data Analyst it is the single most important technical skill you can have. The reassuring part, the part that surprised Nadia within an hour, is that SQL reads almost like English. She had never written a line of code in her life. By lunch she had written her first working query.

You don't open a database. You ask it a question, and SQL is the question.

Lesson 5.2 — SELECT, FROM, WHERE: the three words that do the work

Priya wrote one line on the whiteboard and told Nadia to read it out loud like a sentence:

SELECT name, signup_date
FROM customers
WHERE country = 'Israel';

Nadia read it: "Select the name and signup_date columns, from the customers table, where the country is Israel." Priya grinned. "That's it. You just described exactly what the database handed back."

A SQL request is called a query, and nearly every query is built from three keywords:

• SELECT — which columns you want back.
• FROM — which table they live in.
• WHERE — a condition that filters the rows down to the ones you care about.

One detail trips up every beginner, so catch it now: text values go in single quotes. It's WHERE country = 'Israel', not double quotes (double quotes name a column/identifier), not bare. Numbers don't need quotes (WHERE order_total > 500), but anything that's words does. Nadia forgot the quotes on her first try, got a red error, added them, and it ran. That loop, write it, run it, fix it, is most of what learning SQL feels like.

Change the three pieces and you can answer an endless run of questions without ever leaving this shape. Different columns, a different table, a different condition. The grammar stays put.

Lesson 5.3 — Peeking at the data: ORDER BY and LIMIT

Marcus came back wanting "the newest sign-ups, just the top of the list." Nadia's SELECT / FROM / WHERE gave her every matching row in whatever order the database felt like. Two more keywords fixed that, and they round out the everyday toolkit.

ORDER BY sorts the results. You point it at a column and pick a direction: ASC for ascending (smallest or oldest first, also the default) or DESC for descending (largest or newest first).

LIMIT keeps only the first N rows. It's how you take a quick peek at a huge table without waiting for a million rows to scroll by.

SELECT name, signup_date
FROM customers
ORDER BY signup_date DESC
LIMIT 10;

That reads: newest sign-ups first, show me just the top ten. Nadia now uses LIMIT 10 constantly, before she trusts any query, to glance at a handful of rows and confirm the data looks the way she expects. It's the closest SQL gets to "scroll to the top of the spreadsheet."

Lesson 5.4 — Totals by category: GROUP BY and aggregation

Then Marcus asked the question Nadia had been dreading: not a list of customers, but a number. "How many orders are we getting per country?" In her old job she'd have built a PivotTable. In SQL, the same job belongs to GROUP BY.

GROUP BY collapses rows that share a value into one row per group, and an aggregate function crunches each group down to a single number. The five aggregates you'll use over and over:

• COUNT(*) — how many rows
• SUM(...) — adds a column up
• AVG(...) — the average
• MIN(...) / MAX(...) — the smallest and largest

SELECT country, COUNT(*) AS order_count
FROM orders
GROUP BY country
ORDER BY order_count DESC;

This says: for each country, count its orders, name that count order_count (the AS just relabels it), and sort so the busiest country sits on top. One row per country, one number each. That's a PivotTable, written as a sentence.

Priya gave Nadia one rule before she let her run it: name the metric before you pull the data. "How are sign-ups doing" is not yet a query. "Count of new customers per week" is. Half of an analyst's job is turning Marcus's fog into a sentence precise enough that GROUP BY can answer it.

Lesson 5.5 — WHERE vs HAVING, and combining tables with JOIN

Two final ideas, and one of them is the most-confused point in beginner SQL.

Marcus wanted only the countries with real volume, "more than 10 orders, ignore the tiny ones." Nadia's instinct was to add a WHERE. It failed. Here's why: WHERE filters individual rows before grouping. HAVING filters whole groups after aggregation. You can't ask WHERE to check COUNT(*) > 10, because the count doesn't exist until the groups are formed. That's HAVING's job.

SELECT country, COUNT(*) AS order_count
FROM orders
GROUP BY country
HAVING COUNT(*) > 10
ORDER BY order_count DESC;

WHERE filters rows before the grouping. HAVING filters groups after it.

The second idea is JOIN, and it answers most real questions, because most real questions span two tables. Perch keeps orders in an orders table and customer details in a customers table, linked by a shared ID. To show each order with the customer's name, you stitch the tables together on that shared column:

SELECT orders.order_id, customers.name, orders.order_total
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

The ON line is the hinge: match each order to the customer whose ID equals it. Two flavors matter early on. An INNER JOIN (the plain JOIN above) keeps only rows that match in both tables. A LEFT JOIN keeps every row from the left table even when there's no match on the right, leaving blanks where data is missing, which is exactly how you'd find customers who've never placed an order.

When Nadia's JOIN returned suspiciously few rows, she didn't guess. She walked over to Tom, the data engineer who owns the warehouse, and asked whether some orders were missing a customer_id at the source. They were. Knowing which join to reach for is half the skill; knowing who to ask when the data looks wrong is the other half.

Worked example — Nadia answers Marcus in one query

Marcus needs it for a Monday budget meeting: "Which countries spend the most with us? Give me the big markets, not the noise." Dana, the VP, will want a single sentence and a recommendation, so Nadia has to get the number right.

She names the metric first, the way Priya taught her: total revenue per country, only countries with more than 10 orders, biggest first. Then she assembles the building blocks she's learned:

SELECT customers.country, SUM(orders.order_total) AS total_revenue
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY customers.country
HAVING COUNT(*) > 10
ORDER BY total_revenue DESC
LIMIT 5;

Read it as one breath. JOIN orders to customers so every order knows its country. GROUP BY country to collapse into one row each. SUM the order totals into total_revenue. HAVING drops countries with 10 orders or fewer. ORDER BY ... DESC puts the biggest market on top, and LIMIT 5 hands Marcus the five that matter.

Here's the catch that explains why SQL works the way it does. Nadia wrote SELECT near the top, but the database doesn't run it first. SQL is written SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY, but it's logically executed in a different order: FROM/JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY and LIMIT last. That single fact untangles a mystery beginners hit constantly: you can't use a SELECT alias like total_revenue inside WHERE, because when WHERE runs, SELECT hasn't happened yet and the alias doesn't exist. (You can use it in ORDER BY, which runs after SELECT.)

Nadia sent Marcus the five rows. He turned it into one line for Dana: "Israel and Germany are our top repeat markets, so that's where the next ad budget should go." Five building blocks, one business decision.