Stop 04 · SQL & Data Engineering BasicsTopics · 1/9

SQL Basics (SELECT, JOIN, GROUP BY)

In short

Most company data, the stuff you'll train models on, sits in SQL databases. If you can write a query yourself, you don't wait days for someone to send you a CSV.

The query language for relational databases — most real-world training data lives in SQL databases, so you need to pull it out yourself.

The purple blob hands a request slip to a librarian who pulls books from tall shelves.

Think of it like this

The request slip at the library

You don't wander the stacks. You fill a slip: 'books by this author, published after 2015, sorted by title'. The librarian knows where everything is and brings exactly that. SQL is the slip. You describe what you want, and the database works out how to fetch it.

Watch it happen

The order a database actually runs your query

FROM / JOIN

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

1/6First, the tables are picked and joined: every order gets its customer's row attached.

The big ideas

What you need to know

SELECT picks columns

SELECT name, city FROM users returns just those columns. Avoid SELECT * on big tables in real work.

WHERE filters rows, HAVING filters groups

WHERE runs before grouping, so it can't use COUNT(*). HAVING runs after, so it can.

JOIN combines tables

JOIN customers ON customers.id = orders.customer_id. INNER keeps matches, LEFT keeps every row from the left table.

GROUP BY summarises

Every column in SELECT must be either in GROUP BY or inside an aggregate like SUM, COUNT or AVG.

See it in code

Walk through the code

sql

1SELECT c.city,2       COUNT(*)      AS orders,3       SUM(o.amount) AS revenue4FROM orders AS o5JOIN customers AS c ON c.id = o.customer_id6WHERE o.created_at >= '2026-01-01'7GROUP BY c.city8HAVING COUNT(*) >= 109ORDER BY revenue DESC10LIMIT 5;

1/4Join each order to its customer using the shared id.

Top 5 cities by revenue this year, counting only cities with at least 10 orders. Short aliases like o and c keep joins readable.

Save yourself the pain

Common mistakes

Oops

Writing WHERE COUNT(*) > 10.

Do this instead

Aggregates aren't known yet at WHERE time. Put group conditions in HAVING.

Oops

Forgetting the ON condition, or getting it wrong, so every row joins every other row.

Do this instead

Always join on the key that links the tables, and sanity-check the row count of the result.

Your mission

Query a real database in your browser

~25 min

Quick check

You want cities whose average order value is above ₹500. Where does AVG(amount) > 500 go?

Go deeper

1Practice SELECT, WHERE, JOIN, GROUP BY and ORDER BY on a sample database (e.g. SQLite).
2Write queries that answer specific analytical questions, not just syntax drills.
3Learn to read a query execution plan at a basic level.