Enough theory. The fastest way to learn SQL is to run it, get it wrong, and fix it. Every code box below is a real Postgres database running inside your browser (no server, nothing to install). Edit the query, press Run, see the rows. Break things on purpose.
We'll use a tiny online shop: customers, products, orders, and the items in each order.
SELECT: choose what you want
The most basic query names the columns you want and the table they're in. SELECT * means "every column."
Press Run to execute your query.
Try changing * to specific columns, like SELECT name, city FROM customers;. Asking only for the columns you need is a good habit; SELECT * is fine while exploring but wasteful in real code.
WHERE: keep only the rows you care about
WHERE filters rows by a condition. Only rows where the condition is true come back.
Press Run to execute your query.
Text values go in single quotes ('Mumbai'), not double. You can combine conditions with AND and OR, and compare numbers with <, >, <=, >=. Try finding products under ₹1500:
Press Run to execute your query.
A few filters worth knowing: IN (...) matches any of a list, BETWEEN a AND b matches a range, and LIKE 'A%' matches text patterns (here, names starting with A). Note that LIKE is case-sensitive in Postgres, so 'a%' won't match "Aarav"; use ILIKE when you want to ignore case. Edit the query above to WHERE category IN ('Electronics', 'Home') and run it.
NULL is not a value, it's 'unknown'
When a column has no value, it holds NULL, which means "unknown," not zero and not empty string. The catch: NULL is never equal to anything, not even another NULL. So WHERE city = NULL returns no rows, ever, even for customers with no city. To filter on missing values you must use WHERE city IS NULL (or IS NOT NULL). This three-valued logic (true, false, unknown) trips up nearly every beginner, so when a "correct" filter mysteriously returns nothing, suspect a NULL.
ORDER BY: sort the result
Rows come back in no guaranteed order unless you ask. ORDER BY sorts them; add DESC for descending.
Press Run to execute your query.
There is no natural order
A common surprise: without ORDER BY, the database may return rows in any order, and that order can change. Never rely on rows "coming back in the order I inserted them." If order matters, say so with ORDER BY.
LIMIT: just the top few
LIMIT n returns at most n rows. Combined with ORDER BY, it answers "top N" questions, like the three most expensive products.
Press Run to execute your query.
Putting it together
Real queries stack these clauses, and they always go in this order: SELECT … FROM … WHERE … ORDER BY … LIMIT. Here's one that uses all of them: the most recently joined customers from Mumbai.
Press Run to execute your query.
The shape to remember
SELECT columns FROM table WHERE condition ORDER BY column LIMIT n. Four of those five are optional, but when you use them they always come in that order. Get this shape into your fingers and you can read most everyday SQL.
Next up: the data is split across tables (customers, orders, products) on purpose. To answer "what did Aarav buy?" you need to connect them, which is what JOIN does.
Test yourself
Questions· say the answer out loud before you open it. If you can't, the chapter isn't done.
QWhat's the difference between SELECT * and SELECT name, city?+
SELECT * returns every column; SELECT name, city returns only those two. Star is convenient while exploring, but in real code you name the columns you need so you don't fetch and transfer data you'll throw away, and so the result doesn't silently change when someone adds a column.
QWhy single quotes around 'Mumbai' and not double?+
In standard SQL, single quotes mark a string literal. Double quotes mean an identifier (a table or column name), so "Mumbai" would be read as a column called Mumbai and error. Text values you're comparing against always go in single quotes.
QIf you don't write ORDER BY, what order do rows come back in?+
No guaranteed order. The database returns them however is convenient, and that can change between runs or as data changes. If you need a specific order you must state it with ORDER BY; never rely on insertion order.
QHow do you get the 3 most expensive products?+
Sort descending by price and take the top three: SELECT name, price FROM products ORDER BY price DESC LIMIT 3;. ORDER BY does the sorting and LIMIT cuts it to three. Without the ORDER BY, LIMIT would return an arbitrary three.
QHow do you find rows where a column has no value?+
Use IS NULL, not = NULL. NULL means "unknown," and a comparison to unknown is itself unknown, never true, so WHERE city = NULL returns zero rows even when cities are missing. The correct filter is WHERE city IS NULL (or IS NOT NULL for the opposite).
QIn what order do the clauses go?+
SELECT, then FROM, then WHERE, then ORDER BY, then LIMIT. You can omit WHERE, ORDER BY, and LIMIT, but whenever they appear they must be in that sequence. Writing them out of order is a syntax error.
Comments
Loading comments…