Unit 17 · Lesson 3 Excel, Python and SQL

The data is already there. Learn to ask it questions.

The story so far The bike chain (Lessons 16.1 to 16.3) saves every sale in a database: which store, which month, how much. Your manager asks: "How much did Austin sell this quarter?" Nobody emails you a spreadsheet. The answer sits in a table with millions of rows, and the way to get it is SQL, a language for asking databases questions. Almost every bank, fund and company keeps its data this way, and SQL is one of the skills finance job posts ask for most.

What

SQL (say "sequel" or "S-Q-L") is a language for pulling data out of a database: the rows you want, added up the way you want.

Why

Company data is too big for a spreadsheet. SQL fetches just what you need, often in seconds, from millions of rows.

How

You write a query: SELECT the columns, FROM a table, WHERE rows match a rule, then sort, count or add them up.

Who

Data analysts and FP&A analysts pull reports with it. Risk and compliance teams search trades. Quants load prices before using Python.

What you're looking at: a database of sales on the left, and the answer on the right. The dark box is the SQL query that asks for it: add up the amount column, from the sales table, but only rows where the store is Austin. The answer is $3,900.

01 See it

A database is a set of tables.

A database keeps data in tables. Each row is one record, like one sale; each column is one kind of fact, like the store or the amount. SQL is how you ask for it, and each question is a query. Here is the chain's sales table for one quarter. A real one has millions of rows; the rules are the same.

store	month	amount
Austin	Jan	1200
Denver	Jan	900
Miami	Jan	1500
Austin	Feb	1400
Denver	Feb	700
Miami	Feb	1600
Austin	Mar	1300
Denver	Mar	1100
Miami	Mar	1700

What you're looking at: the sales table: 9 rows (one per store per month) and 3 columns. Amounts are in dollars.

SELECT, FROM and WHERE

Every query starts with SELECT (which columns) and FROM (which table). * means every column. WHERE keeps only the rows that pass a test. Text goes in single quotes, like 'Austin', and a semicolon ends the query. AND joins two tests: a row must pass both. > means more than and < means less than; a value equal to the number doesn't pass.

SELECT month, amount
FROM sales
WHERE store = 'Austin';

Jan 1200 Feb 1400 Mar 1300

In this example

SELECT month, amount: Only those two columns come back.
WHERE store = 'Austin': Keeps 3 of the 9 rows.
Add AND amount > 1250: Only Feb (1400) and Mar (1300) pass both tests.

ORDER BY and LIMIT

ORDER BY sorts the results; add DESC for biggest first. LIMIT keeps only the first few rows. Together they answer "what were our top 3?"

SELECT store, month, amount
FROM sales
ORDER BY amount DESC
LIMIT 3;

Miami Mar 1700 Miami Feb 1600 Miami Jan 1500

In this example

The top 3 months: All Miami: 1700, 1600 and 1500.

Adding up: COUNT, SUM, AVG and GROUP BY

SQL can summarize as it fetches. COUNT(*) counts rows, SUM() adds a column, and AVG() averages it. GROUP BY splits the rows into groups first, so you get one answer per group, like a totals row for each store.

SELECT store, SUM(amount), AVG(amount)
FROM sales
GROUP BY store;

Austin 3900 1300 Denver 2700 900 Miami 4800 1600

In this example

COUNT(*) FROM sales: 9 rows.
Austin's SUM: 1200 + 1400 + 1300 = 3900, the answer to your manager's question.
Austin's AVG: 39003 = 1300 a month.

Pick a question to ask the sales table

What you're looking at: four queries on the same 9-row sales table. Pick one to see the SQL and the table the database sends back.

JOIN: connect two tables

Databases split data into many tables so nothing is typed twice. JOIN puts them back together by matching a column they share.

SELECT stores.manager, SUM(sales.amount)
FROM sales
JOIN stores ON sales.store = stores.store
GROUP BY stores.manager;

Kim 2700 Lopez 4800 Rivera 3900

In this example

The stores table: Austin: manager Rivera. Denver: Kim. Miami: Lopez.
Matched on store: Each sale finds its store's manager, so Lopez's stores sold 4800.

The order clauses go in

SELECT → FROM → JOIN → WHERE → GROUP BY → ORDER BY → LIMIT

You can skip any clause you don't need, but the ones you use must go in this order.

02 Hear it

Two minutes. Just listen.

Tap any line to jump to it.

03 Play it

Query check.

Eight questions about the same 9-row sales table, shown here. One try each.

Question 1 of 8

Right0 of 0

store	Jan	Feb	Mar
Austin	1200	1400	1300
Denver	900	700	1100
Miami	1500	1600	1700

What you're looking at: the sales amounts laid out by store and month (each number is one row of the sales table), then a query and one question about it.

04 Break it down

The logic, step by step.

To turn any question into a query, take four steps.

Step 1: Which table?

FROM the table that holds the facts

In this example

Austin's quarter: FROM sales.

Step 2: Which rows?

WHERE keeps the rows that pass every test

In this example

The filter: WHERE store = 'Austin': 3 rows.

Step 3: Summarize?

SUM, COUNT or AVG, with GROUP BY for one answer per group

In this example

The total: SELECT SUM(amount): 3900.

Step 4: Sort and trim

ORDER BY … DESC, then LIMIT

In this example

One number: Nothing to sort here: the answer is one row.

Worked example: Denver's slow months

The regional manager asks: "Which months did Denver sell less than $1,000?" Here are the facts:

The table: sales, with store, month and amount.
Two tests: the store is Denver, and the amount is under 1000.
Wanted: the months, smallest first.

SELECT month, amount
FROM sales
WHERE store = 'Denver' AND amount < 1000
ORDER BY amount;

Feb 700 Jan 900

Table. FROM sales.
Rows. Denver has Jan 900, Feb 700 and Mar 1100. With AND, both tests must pass, so Mar drops out.
Sort. ORDER BY amount with no DESC puts the smallest first.
The answer. February ($700) and January ($900). Denver picked up in March ($1,100), which is worth telling the manager too.

05 Nature link

A bird that queries its memory.

Each fall, a Clark's nutcracker, a bird of western mountains, hides tens of thousands of pine seeds in thousands of small caches spread across the slopes. Months later, often under snow, it flies back and digs up the right spots, using rocks and trees as landmarks.

What you're looking at: an illustration, not data. Brown dots are seed caches hidden across a slope; the green circles are the few the nutcracker is digging up today.

Why this sticks. The bird doesn't dig up the whole mountain; it goes straight to the caches it wants. A good query does the same: the database holds everything, and WHERE takes you straight to the rows you need.

06 Notes

The page you'd copy down.

Database = tables. Row = one record; column = one kind of fact. A query asks a question in SQL.
SELECT columns FROM table WHERE test. * = every column. Text in 'single quotes'. AND = both tests.
Austin only: Jan 1200, Feb 1400, Mar 1300.
ORDER BY amount DESC LIMIT 3: Miami 1700, 1600, 1500.
COUNT(*) = 9 rows. SUM Austin = 3900. AVG = 39003 = 1300.
GROUP BY store: Austin 3900, Denver 2700, Miami 4800.
JOIN matches two tables on a shared column (store → manager).
Order: SELECT, FROM, JOIN, WHERE, GROUP BY, ORDER BY, LIMIT.

Fill in the blanks

1. To keep only rows that pass a test, use .

2. To get one total per store, use GROUP store.

3. SELECT COUNT(*) FROM sales returns for our table.

4. To sort biggest first, add after ORDER BY amount.

07 Together

Grab a friend or a study group.

Human database

Write each sale on a card. One person reads out a query; the others hold up only the cards that match, then add them up.

Question to query

Take turns asking a plain question about the sales table, like "Which store sold the least in February?" The others write the SQL.

Try it for real

Search for a free online SQL playground, paste in the sales table, and run the lesson's queries. Then invent your own.

08 On your own

Just you and your money.

These stay on this device. Nobody else sees them.

If your bank account were a table, what query would you write to see where your money went last month?

Spreadsheets, Python or SQL: which one do you want to get good at first, and why?

Companies know a lot from data like this. What data about you would you not want a company to query?

09 Check

Exam-style questions.

Written the way a college exam would ask them. Each right answer on the first try is worth 10 xp.

On the job: you're a data analyst at the bike chain

The CFO is meeting store managers tomorrow and sends you a message: "Which store had the highest total sales this quarter, and what was its average month?" Here's what you have:

The table: sales, with store, month and amount (the 9 rows in this lesson).
Wanted: one row, with the store, its total and its average.

Your task: write one query that answers both, and the reply you'd send.

See a model answer

SELECT store, SUM(amount), AVG(amount)
FROM sales
GROUP BY store
ORDER BY SUM(amount) DESC
LIMIT 1;

Miami 4800 1600

To the CFO: Miami led the quarter with $4,800 in sales, an average of $1,600 a month, and it grew every month (1500, 1600, 1700). Austin was second at $3,900.

10 Where it leads

Where this leads.

What you're looking at: a map of where this lesson fits in the course. Read it left to right: the lessons it builds on, this lesson in the middle, and where it leads.

Back · Unit 17Spreadsheets for finance
Back · Unit 17Python for finance
Back · Unit 12The income statement
You are hereSQL for finance
Coming soon · Unit 18A finance resume and LinkedIn

That's the analyst's toolkit: spreadsheets to model, Python to repeat and scale, SQL to pull the data. Unit 18 turns it into a job: your resume, your network and the interview.

Revisit: Python for finance →Course map