Module master check

Let's put SELECT into practice

QUERY dims the other panels. Only one terminal remains lit, displaying the three tasks in your first-clearance exam.

The Academy doesn't take your word for it — only queries count.

Over the course of this module, you've assembled the essential toolkit for reading the archive. You can now do more than write a basic query like:

SELECT *
FROM products;

You can ask the database a precise question.

You choose exactly which columns to return:

SELECT name, price
FROM products;

You give them clear names:

SELECT
    name AS product_name,
    price AS product_price
FROM products;

You filter out all but the rows you need:

SELECT name, price
FROM products
WHERE price >= 1000;

You work with missing values:

SELECT id, name, city
FROM users
WHERE city IS NULL;

You write concise filters using ranges, lists, and patterns:

SELECT name, category, price
FROM products
WHERE category IN ('Книги', 'Игрушки')
  AND price BETWEEN 1000 AND 3000
  AND name LIKE 'К%';

You control the sort order:

SELECT name, price
FROM products
ORDER BY price DESC;

You return just the top few rows:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

When you need a list of unique values, you eliminate duplicates:

SELECT DISTINCT status
FROM orders;

And you add simple logic directly to the output:

SELECT
    name,
    price,
    CASE
      WHEN price < 1500 THEN 'cheap'
      WHEN price < 4000 THEN 'medium'
      ELSE 'expensive'
    END AS segment
FROM products;

Now it's time to see all of this as one coherent tool.

Not as separate clauses.
Not as a collection of random operators.
But as one complete path from a table to a result someone can understand.

QUERY: You don't earn your first clearance just by knowing the words. You earn it by asking the archive a precise question.

How to read a complete SELECT query

When a query grows beyond a few lines, beginners often lose track of it. SQL can seem to run strictly from top to bottom, like ordinary prose.

But a query is easier to understand when you read it in logical layers.

For example:

SELECT
    name,
    category,
    price,
    CASE
      WHEN price < 1500 THEN 'cheap'
      WHEN price < 4000 THEN 'medium'
      ELSE 'expensive'
    END AS segment
FROM products
WHERE category IN ('Книги', 'Игрушки')
  AND price BETWEEN 1000 AND 3000
ORDER BY price DESC, id
LIMIT 5;

A query like this can be broken down step by step.

Start with the source:

FROM products

The database reads from the products table.

Next comes the row filter:

WHERE category IN ('Книги', 'Игрушки')
  AND price BETWEEN 1000 AND 3000

This leaves only books and toys priced from 1000 to 3000, inclusive.

Next come the columns and computed values:

SELECT
    name,
    category,
    price,
    CASE ... END AS segment

The query returns the name, category, and price, then uses CASE to add a segment.

Then comes the sort order:

ORDER BY price DESC, id

Products are sorted from most to least expensive, with id used as a consistent tie-breaker.

Finally, apply the limit:

LIMIT 5

Once the rows have been sorted, only the first five remain.

The key idea:

LIMIT doesn't choose the "best" rows by itself. It simply returns the first rows in the order defined by ORDER BY.

What makes a query reliable

At this stage, getting a green tick isn't enough. You also need to understand why the query returned exactly those rows.

A reliable query usually has three qualities.

First, it states exactly which rows it wants.

Conditions shouldn't have to be inferred from fragments of logic. A good WHERE clause reads like a precise rule:

WHERE category IN ('Книги', 'Игрушки')
  AND price BETWEEN 1000 AND 3000

Second, it states exactly how the result should be ordered.

If you need the most expensive products, the most recent orders, or users in alphabetical order, specify that in ORDER BY:

ORDER BY price DESC, id

Third, it anticipates uncertainty.

If the data can contain NULL, the query should account for it explicitly:

WHERE city IS NULL

or:

WHERE city <> 'Москва'
   OR city IS NULL

If some rows don't match any CASE branch, decide in advance how to handle them:

ELSE 'Other status'

If the order of tied rows matters, add a unique key as a tie-breaker:

ORDER BY price DESC, id

Small training tables can hide many of these flaws. In a real database, such details can cause silent errors: the query runs successfully, but the result isn't the one you intended.

Silent errors: when the query runs but lies

The trickiest SQL errors don't always show up as a red syntax error.

More often, the query runs, returns a table — and gives you the wrong rows.

Take this comparison with NULL:

WHERE city = NULL

The syntax may be perfectly valid, but this will never find rows with no city. You have to test for NULL explicitly:

WHERE city IS NULL

Now consider a condition that mixes AND and OR:

WHERE category = 'Книги'
   OR category = 'Игрушки'
  AND price < 3000

At a glance, you might read that as:

books or toys priced under 3000

But SQL evaluates AND before OR, so the query actually means:

WHERE category = 'Книги'
   OR (category = 'Игрушки' AND price < 3000)

If you want the price limit to apply to both categories, you need parentheses:

WHERE (category = 'Книги' OR category = 'Игрушки')
  AND price < 3000

A cleaner version is:

WHERE category IN ('Книги', 'Игрушки')
  AND price < 3000

Another silent error can come from a CASE expression without an ELSE:

CASE
  WHEN status = 'paid' THEN 'оплачен'
  WHEN status = 'pending' THEN 'ожидает'
END AS status_label

If a row has the cancelled status, its label will be NULL. That may be intentional, but in a report it's usually better to provide a fallback:

ELSE 'другой статус'

Finally, consider LIMIT without ORDER BY:

SELECT name, price
FROM products
LIMIT 5;

This query does not return the top five rows. It simply returns five rows in whatever order the database happens to choose.

To get a top-five list, you need to define the ranking:

SELECT name, price
FROM products
ORDER BY price DESC, id
LIMIT 5;

Silent errors are dangerous precisely because the database doesn't complain. It does exactly what you wrote — even when that's not what you meant.

QUERY: The Archive can't read your mind. It reads the query. If you don't write the logic explicitly, it doesn't exist.

Before you submit your query

Before you submit your solution in the trainer, take a moment to give your query one final check.

First, make sure you selected exactly the columns the exercise asks for.

If the exercise asks for:

name, price

don't submit:

SELECT *

That may be convenient while you're drafting, but your final answer should return only the requested columns.

Next, check the filter.

If the exercise says "between 1000 and 3000, inclusive", this is a good fit:

BETWEEN 1000 AND 3000

If it asks for items in the Books or Toys categories, this is clearer:

IN ('Книги', 'Игрушки')

For "starts with", use:

LIKE 'К%'

For "contains", use:

LIKE '%К%'

But remember: a leading % can slow down queries on large tables.

Next, check how you handle NULL.

To find a missing value, don't write:

= NULL

Write:

IS NULL

To exclude a value while keeping rows where it is NULL, make that explicit:

WHERE city <> 'Москва'
   OR city IS NULL

Next, check the sort order.

If the exercise uses words like:

most expensive
cheapest
latest
first
top

you almost certainly need ORDER BY.

If the exercise limits the number of rows, you almost certainly need LIMIT.

Finally, if the sort column can contain duplicate values, add a unique column as a tiebreaker:

ORDER BY price DESC, id

That keeps the result order stable.

What you can already do by the end of the module

In this module, you learned how to retrieve rows.

You haven't started calculating revenue, average order value, or order counts by group yet — that comes next. But you can already retrieve from a table exactly the slice of data an answer needs.

You know the core query pattern:

SELECT ...
FROM ...
WHERE ...
ORDER BY ...
LIMIT ...

You know how to use aliases:

price * stock AS stock_value

You can work with missing values:

IS NULL
IS NOT NULL
COALESCE(...)

You can express common filters concisely:

BETWEEN
IN
LIKE

You can list the unique values in a column:

SELECT DISTINCT status
FROM orders;

You can add human-readable labels to your results:

CASE
  WHEN ... THEN ...
  ELSE ...
END

Most importantly, you now know that valid syntax does not guarantee correct logic.

A query can run without errors and still return the wrong result if its conditions don't express exactly what you mean.

QUERY: From here the archive opens only to those who count. Revenue, average order value, groups — the next level of clearance.