Data Coding

Ace data science coding by writing it.

Complete learning tracks for SQL, Python, pandas and NumPy, with hands-on practice and interview-style questions. Jump in now, and happy coding!

Start with SQLStart with PythonPractice problems
Your first lesson: try it right here, no sign-up needed

Lesson 0.1

How SQL reads your query

SQL doesn't run your query top to bottom. Knowing the real order explains most beginner errors.

Imagine you're a data analyst at Amazon. The orders table has one row per order.

1SQL runs the clauses in a fixed order

You write SELECT first, but SQL runs it near the end. The order is: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

SELECT order_id, total_amount      -- 3. pick the columns
FROM orders                        -- 1. read the table
WHERE status = 'delivered'         -- 2. keep some rows
ORDER BY total_amount DESC         -- 4. sort
LIMIT 5;                           -- 5. keep the top 5

Quick check

Which part of a query does SQL run first?

2WHERE can't see a name you made in SELECT

WHERE runs before SELECT, so a new column name from AS doesn't exist yet. Repeat the calculation in WHERE instead.

Common mistake

SELECT order_id, total_amount + shipping_fee AS amount_paid
FROM orders
WHERE amount_paid > 200;   -- error: column "amount_paid" does not exist

SELECT order_id, total_amount + shipping_fee AS amount_paid
FROM orders
WHERE total_amount + shipping_fee > 200;

Practice

This query fails. Fix it so it lists orders where the shipping fee is more than 5% of the order total.

Return: order id and fee share

SELECT order_id, shipping_fee / total_amount AS fee_share
FROM orders
WHERE fee_share > 0.05;

3ORDER BY can use the new name

ORDER BY runs after SELECT, so it can use a name you made with AS.

SELECT order_id, total_amount + shipping_fee AS amount_paid
FROM orders
ORDER BY amount_paid DESC
LIMIT 5;

Practice

Show the 5 orders with the highest amount paid (total plus shipping fee), largest first. Break ties with order_id.

Return: order id and amount paid

Write your query here

4Put it together

Quick check

Which of these queries gives an error?