Lesson 1 of 6 in this module

5/33 in track

Relational basics

sql8 min

Overview

SELECT names columns, WHERE filters rows, DISTINCT removes duplicates, LIMIT caps output.

On this page8 sections

The table we keep using

Every order ShopKart takes becomes 1 row in a table. To answer a question about revenue, customers or delivery, you write a query: a short request in SQL that the database answers with another table.

The real orders table in the editor has 180 rows, which is too many to follow by hand. So this lesson builds a 5-row copy called orders_demo first. Every query below runs on it, and you can check each result with your finger.

Can you build the 5-row table yourself?

Run this once. CREATE OR REPLACE means you can run it again without an error. Later lessons also create a table called orders_demo with their own rows, so run each lesson's CREATE block before its queries.

SQLBuild the 5-row practice table
CREATE OR REPLACE TABLE orders_demo (
  order_id     VARCHAR,
  user_id      VARCHAR,
  order_status VARCHAR,
  order_total  DOUBLE,
  created_at   TIMESTAMP
);

INSERT INTO orders_demo VALUES
  ('O0001', 'U100', 'paid',      149.00, '2026-08-01 09:15:00'),
  ('O0002', 'U101', 'cancelled',  42.50, '2026-08-01 10:30:00'),
  ('O0003', 'U102', 'paid',       88.00, '2026-08-02 14:22:00'),
  ('O0004', 'U100', 'pending',   210.75, '2026-08-03 08:45:00'),
  ('O0005', 'U103', 'paid',       55.20, '2026-08-03 16:10:00');

orders_demo. The editor drops trailing zeros, so 149.00 prints as 149.

order_iduser_idorder_statusorder_total (₹)created_at
O0001U100paid1492026-08-01 09:15:00
O0002U101cancelled42.52026-08-01 10:30:00
O0003U102paid882026-08-02 14:22:00
O0004U100pending210.752026-08-03 08:45:00
O0005U103paid55.22026-08-03 16:10:00

1 row is one order, not one customer: U100 appears twice because that customer placed 2 orders.

These 5 rows stay the same for the whole lesson.

What does a query with 1 column return?

SELECT names the columns you want. FROM names the table they come from.

SQL1 column, no filter
SELECT order_id
FROM orders_demo;

Predict the number of rows before you open the output.

order_id
O0001
O0002
O0003
O0004
O0005

All 5 rows come back, because nothing told the database to skip any.

RememberA SELECT only reads

A query that starts with SELECT looks at the data and returns a result. It does not change the table, so you can try things freely.

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
SELECTChoose which columns to show
FROMSay which table to read
WHEREKeep only the rows that match a test
ORDER BYSort the result
LIMITShow only the first few rows
ClauseOne part of a query, like WHERE

What we need from it

Finance wants the two largest paid orders, so they can review the big payments first.

How do clauses map to a business question?

Each part of a query answers one small question. You add only the parts you need.

Only SELECT and FROM are required. The rest shape the result.

ClauseQuestion it answersRequired?In this request
SELECTWhich columns should appear?Yesorder_id, order_total
FROMWhich table holds the rows?Yesorders_demo
WHEREWhich rows pass the rule?Noorder_status = 'paid'
ORDER BYIn what order should rows appear?Noorder_total DESC
LIMITHow many rows at most?No2
DISTINCTShould repeated rows be removed?NoSELECT DISTINCT user_id

Why does a big table need a WHERE?

On 5 rows nothing is slow. A warehouse table can hold 50 lakh rows. Without WHERE, the query reads every one of them and sends them all back to you.

The database will usually run it without complaint, so the cost shows up as waiting time and, in some cloud warehouses, as a bill based on how much data was read.

Filter early, and add LIMIT while you are only exploring.

Trace it step by step

How does WHERE drop rows?

WHERE tests a condition against each row. A row that fails is dropped whole.

The 5-row orders table on the left, with the paid rows O0001, O0003 and O0005 shaded green and the other two greyed out. An arrow labelled WHERE status equals paid points to a result table of 3 rows with only order_id and order_total.
WHERE drops whole rows horizontally before SELECT projects columns vertically.

In the picture the table is labelled orders and amounts have two decimals. In the editor it is orders_demo. Rows O0002 and O0004 fail the status check and disappear.

In what order does the database run the clauses?

You write SELECT first, but the database needs to know the table and which rows to keep before it can pick columns.

Logical order of SQL clauses
1. FROM orders_demo2. WHERE filter rows3. SELECT columns4. DISTINCTdeduplicate5. ORDER BY sort6. LIMIT cap rows

You write SELECT first, but logically it runs third. Filtering first means there is less left to sort.

This is the logical order, the one that explains the results. The optimizer may run the steps differently inside, as long as the answer is the same.

What if you write the clauses in the wrong order?

The clauses must be written in a fixed order. Try WHERE after ORDER BY.

SQLWHERE written too late
SELECT order_id, order_total
FROM orders_demo
ORDER BY order_total DESC
WHERE order_status = 'paid';
Parser Error: syntax error at or near "WHERE"

LINE 1: ..., order_total FROM orders_demo ORDER BY order_total DESC WHERE order_status = 'paid'
                                                                    ^

The parser stops at the word it did not expect. The error points at WHERE, but the mistake is the order.

Write them in this order: SELECT, FROM, WHERE, ORDER BY, LIMIT.

Same table, next cut

How do you pick specific columns?

List the column names after SELECT, separated by commas. This is called projection.

SQL3 columns, no filter
SELECT order_id, order_status, order_total
FROM orders_demo;

Check the table above: how many rows and how many columns?

order_id  order_status  order_total
O0001     paid          149
O0002     cancelled     42.5
O0003     paid          88
O0004     pending       210.75
O0005     paid          55.2

5 rows, 3 columns. user_id and created_at were left out.

How do you keep only cancelled orders?

Put the rule in WHERE. Text values go in single quotes.

SQLFilter on text
SELECT order_id, order_total
FROM orders_demo
WHERE order_status = 'cancelled';

Which order survives, and what is its total?

order_id  order_total
O0002     42.5

Only O0002 has the status cancelled.

What does AND do?

AND keeps a row only when both conditions are true.

SQLPaid orders above 80 rupees
SELECT order_id, order_total, order_status
FROM orders_demo
WHERE order_status = 'paid'
  AND order_total > 80;

O0005 is paid, but its total is 55.2. Does it survive?

order_id  order_total  order_status
O0001     149          paid
O0003     88           paid

O0005 passes the first condition and fails the second, so it is dropped.

Why does 'Paid' with a capital P return nothing?

Text comparison in DuckDB and PostgreSQL is case-sensitive, so 'Paid' and 'paid' are different values. (MySQL ignores case by default, which is one reason to test on the engine you use.)

SQLWrong capitalisation
SELECT order_id, order_total
FROM orders_demo
WHERE order_status = 'Paid';

How many rows will come back?

order_id  order_total
(0 rows)

No error and no rows. The database did what you asked: no row has the status 'Paid'.

If a query returns zero rows when you expected some, check the case and spelling of the text value.

How do you find the two largest paid orders?

Filter to paid, sort the total from high to low with DESC, then cap with LIMIT 2.

SQLThe two largest paid orders
SELECT order_id, order_total
FROM orders_demo
WHERE order_status = 'paid'
ORDER BY order_total DESC
LIMIT 2;

Which 2 orders appear, and in what order?

order_id  order_total
O0001     149
O0003     88

3 orders were paid. The sort put 149, 88 and 55.2 in that order, and LIMIT 2 kept the first two.

Watch outLIMIT without ORDER BY gives no promise

A table has no built-in order. Without ORDER BY, LIMIT 2 returns some 2 rows, and which two can change between runs or engines. Use ORDER BY whenever the rows you get matter.

How do you list each customer once?

U100 placed 2 orders, so a plain SELECT user_id would show U100 twice. DISTINCT removes repeated rows from the result.

SQLUnique customer ids
SELECT DISTINCT user_id
FROM orders_demo
ORDER BY user_id;

5 orders, but how many different customers?

user_id
U100
U101
U102
U103

4 customers. U100 appears once.

Does DISTINCT look at 1 column or the whole row?

It compares the whole row of selected columns. Add order_status and see what changes.

SQLUnique user and status pairs
SELECT DISTINCT user_id, order_status
FROM orders_demo
ORDER BY user_id, order_status;

U100 has one paid order and one pending order. How many rows come back?

user_id  order_status
U100     paid
U100     pending
U101     cancelled
U102     paid
U103     paid

5 rows. The pairs (U100, paid) and (U100, pending) are different, so both stay.

How can DISTINCT check a freshly loaded table?

Listing the distinct values of a column shows what the data really contains. A misspelt status would stand out straight away.

SQLUnique status values
SELECT DISTINCT order_status
FROM orders_demo
ORDER BY order_status;

Which statuses exist in our 5 rows?

order_status
cancelled
paid
pending

3 values here. The real orders table also has refunded, so always look at the table you are actually querying.

Does the same query work on the real table?

Yes. Only the table name changes, and the answer comes from all 180 rows.

SQLThe same question on the real orders table
SELECT order_id, order_total
FROM orders
WHERE order_status = 'paid'
ORDER BY order_total DESC
LIMIT 2;
order_id  order_total
ORD-0105  1389.16
ORD-0178  1262.06

How does the query shape the 5 rows?

How the query shapes 5 orders
  1. Start: 5 rows in orders_demo.

  2. WHERE order_status = 'paid' drops O0002 and O0004. 3 rows are left.

  3. SELECT order_id, order_total keeps two of the 5 columns.

  4. ORDER BY order_total DESC puts 149, 88 and 55.2 in that order.

  5. LIMIT 2 keeps O0001 and O0003 and drops O0005.

  • A SELECT only reads. It never changes the table.
  • WHERE and FROM logically run before SELECT and ORDER BY.
  • On a big table, filter early and use LIMIT while exploring.
  • Pair LIMIT with ORDER BY when the choice of rows matters.
  • DISTINCT compares the whole selected row, not 1 column.

Common beginner questions

Why is WHERE written after FROM if the database filters early?

SQL was designed in the 1970s to read like English: select these columns from this table where this is true. The order you write is for people. Logically the database finds the table first, then filters the rows, then picks the columns.

What is the difference between single quotes and double quotes in SQL?

Single quotes mark a text value, such as 'paid' or 'Bengaluru'. Double quotes mark a name, such as a table or column whose name has spaces or is a reserved word (for example "order"). Do not put double quotes around a text value.

Can I use a column alias from SELECT inside WHERE?

In standard SQL, no. WHERE runs before SELECT creates the alias, so the alias does not exist yet. Some engines, including DuckDB, allow it as an extension. Do not rely on that if your code has to run on other databases: repeat the original expression instead.

What comes next

Every cell in these 5 rows held a value. Real tables often have gaps where something was never recorded. SQL calls a gap NULL, and it breaks filters in ways that surprise most beginners. The next lesson shows how to handle NULL safely.

Practice

Return order_id and order_total for cancelled orders in the real orders table, with the highest total first, capped at 10 rows.

It combines everything above: choose columns with SELECT, filter with WHERE, sort with ORDER BY, cap with LIMIT. Write them in that order, and sort before you cap.

Going further: CASE, IN, and why an alias fails in WHERE

Two small tools do a lot of daily work: IN replaces a chain of ORs, and CASE turns a value into a label. A habit worth learning early is not using a SELECT alias in WHERE, because it works in some engines and fails in others.

The products table.

skupricediscount
A10020
B250NULL
C400
D99910
SQLCASE and IN
SELECT sku, price,
       CASE WHEN price >= 250 THEN 'premium'
            WHEN price >= 100 THEN 'standard'
            ELSE 'budget' END AS tier
FROM products
WHERE sku IN ('A', 'B', 'C')
ORDER BY sku
sku  price  tier
A    100    standard
B    250    premium
C    40     budget

CASE checks its conditions top to bottom and uses the first one that is true, so the order of the branches matters. Now the alias.

SQLRepeat the expression in WHERE
SELECT sku, price - discount AS net
FROM products
WHERE price - discount > 50
ORDER BY sku
sku  net
A    80
D    989

In standard SQL, WHERE is evaluated before SELECT, so the name net does not exist yet. PostgreSQL and SQL Server reject WHERE net > 50. Some engines, including the DuckDB this course runs on, accept it as a convenience, so a query that works here can break when you move it.

Repeat the expression, or move the calculation into a subquery or CTE. Look at B: NULL minus anything is NULL, and a NULL is never greater than 50, so B is silently dropped. That is the next lesson's topic.

Try a new scenario

Label each product 'cheap' if its price is under 50, 'mid' if under 500, otherwise 'dear', and list only the cheap and dear ones.

Open the answer

The result is: sku label ; C cheap ; D dear. The label is derived in SELECT, but the filter cannot use it, so the WHERE repeats the price conditions. Writing WHERE label IN ('cheap','dear') may run here but fails on engines that follow the standard evaluation order.

What is the difference between DISTINCT and GROUP BY?

DISTINCT removes duplicate rows from the result. GROUP BY forms groups so you can compute an aggregate for each. For a plain list of unique values they give the same answer, and only GROUP BY can also count or sum.

Close the lesson and explain it

Without scrolling up, explain why a SELECT alias is unreliable inside WHERE, using the order in which a standard query is evaluated.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Practice this

Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.

Rate:
Was this useful?