Your first meaningful query
What you'll learn
- write queries with an explicit list of columns after
SELECTand a row filter viaWHERE - translate a business question into SQL along the route: entity →
FROM, →WHERE(withAND), output →SELECT, order →ORDER BY - explain why
SELECT *is bad practice in queries - check a query by saying it aloud: if your retelling matches the original question, the query is correct in meaning
The map is learned — and QUERY changes the rules. Until now you’ve been taking in the whole archive like a tourist; an archivist works differently. Everything on the holopanel goes dark except one table, and a task appears: ask your question so that the answer contains not a single extra row and not a single extra column.
Choosing the columns and rows you need
Grabbing everything with * is convenient for a first look, but in real work it’s like hauling the entire warehouse up to the checkout. Usually you need specific fields: list them, comma-separated, after SELECT.
It’s the same with rows: instead of the whole storefront, pick only the segment you need via WHERE. For example, let’s ask Kotomarket for only the products in the Books category:
| name | price |
|---|---|
| SQL для профи | 1290.00 |
| Чистый код | 1490.00 |
| Алгоритмы на практике | 1690.00 |
From a business question to a query
Real questions don’t reach the archivist in SQL: "which books are cheaper than fifteen hundred?", "what electronics cost more than 5000?". The translation always follows one route:
- Entity → table. What’s the question about: products, buyers, orders? "Books under 1500" is about products, so
FROM products. - Constraints → WHERE. Every condition in the question is a separate filter condition: category "Books," price under 1500.
- What to show → SELECT. What does the person want to see in the answer? The name and the price — those are what we list; the rest is noise.
- Shape of the answer → ORDER BY. "From cheap to expensive" is a sort by price.
-- "Which books cost less than 1500 and how much, from cheap to expensive?"
SELECT name, price
FROM products
WHERE category = 'Книги' AND price < 1500
ORDER BY price;
Several conditions are joined with the word AND — we’ll cover the filter operators in detail in the next chapter.
And the good archivist’s reverse habit: read the finished query aloud as a sentence. "From products, take the books under 1500, show the name and price, sort by price" — if your retelling matches the original question, the query is correct in meaning. If "all columns" or "the whole storefront" creeps into your retelling, go back and narrow it down.
Interview question
Interview question: why is SELECT * considered bad practice in queries?
Strong answer: first, wasted data: network and memory are spent on columns you don’t need, and the database loses the ability to answer from the index alone (an index-only scan; indexes are Chapter 10). Second, fragility: when columns are added or renamed, you break reports and code that rely on the set and order of columns. An explicit column list is the query’s contract; SELECT * is appropriate only in interactive data exploration.
SELECT name FROM products WHERE category = 'Книги' return?The index has been read: you know what’s in the five tables and which threads stitch them together. QUERY dims the map, and the next clearance level lights up on the holopanel — the reading terminal.
QUERY: Remember the cardinal property of this place: the archive answers only well-formed questions. From the next clearance on, we learn to ask precisely.