SQL Essentials

SQL is a language for asking a database questions. A database keeps data in tables. A question written in SQL is called a query.

The database answers with a new, small table. PostgreSQL is a popular free database. We use it in this chapter.

For example: “Which orders are paid, and how much was each one?” Text goes in single quotes, like 'paid'. In SQL, you write it like this:

SELECT order_id, amount
FROM orders
WHERE status = 'paid';

The shop we use in every example

All examples use one small online shop with three tables.

Here is all the data. It is small on purpose, so you can check every answer with your eyes.

country is a two-letter code: IN is India, GB is Great Britain, US is the United States.

Look at customer_id and order_id. These columns connect the tables:

orders.customer_id says who placed the order. order_items.order_id says which order the product belongs to.

NULL means a value is missing, so SQL does not know it. Dev has no country. Order 105 has no amount yet.

Omar is a customer, but Omar has not ordered anything yet. You need Omar in chapter 2.

What you will learn

1. Read a table: rows, columns and what one row means.
2. Write a query: choose columns, keep rows, sort, and cut the list.
3. Handle missing values (NULL) without losing rows.
4. Work with numbers, text and dates.
5. Count rows correctly.

Why this matters in interviews

Most data engineering interviews start with a SQL test. The first questions check these basics. People often fail them on small things, like NULL or a wrong count.

The examples use PostgreSQL. Most databases work the same way. When one is different, we tell you.

A table is a grid, like a spreadsheet.

A column is one kind of value, like status. A row is one line, like one order. Look at the picture:

Grain: what one row means

The grain of a table is what one row stands for. In orders, one row is one order. In order_items, one row is one product inside an order.

Look at orders 101 and 102 in both tables. The tables show only some columns:

Order 101 is one row in orders. It is two rows in order_items, because it has two products.

This is not an error. The two tables have different grains.

Keys: the column that names a row

A key is a column, or a few columns, that point to exactly one row. In orders, the key is order_id. No two orders have the same order_id.

In order_items, order_id is not enough: 101 appears twice. The key is order_id and product together.

A query is made of parts called clauses. Each clause does one job.

Text after -- is a comment. The database ignores it. Here, the numbers in the comments show the order the database runs each clause:

SELECT order_id, amount   -- 3. which columns
FROM orders               -- 1. which table
WHERE status = 'paid'     -- 2. which rows
ORDER BY amount DESC      -- 4. sort them
LIMIT 2;                  -- 5. keep the first 2

Step by step

Follow the 6 orders through the query above:

1. FROM orders: start with all 6 orders.
2. WHERE status = 'paid': 4 rows are left (101, 102, 104, 106).
3. SELECT order_id, amount: keep only these 2 columns.
4. ORDER BY amount DESC: biggest first (106, 101, 102, 104).
5. LIMIT 2: keep the first 2 rows (106 and 101).

Write order and run order

You write the clauses in one order. The database runs them in another order. Look at SELECT: you write it first, but the database runs it later.

The picture also shows GROUP BY. You learn it in chapter 3:

In the next section, you see one problem this order causes.

After SELECT, list the columns you want. Put a comma between them. The result shows only those columns, in that order.

SELECT name, country
FROM customers;

Rename a column with AS

AS gives a column a new name in the result. The table itself does not change.

SELECT order_id, amount AS order_total
FROM orders
ORDER BY order_id
LIMIT 3;

Calculate a new column

You can also do math in SELECT. Here, every paid order gets a 5.00 discount:

SELECT order_id, amount, amount - 5 AS after_discount
FROM orders
WHERE status = 'paid'
ORDER BY order_id;

WHERE cannot see a name made in SELECT

WHERE runs before SELECT. So when WHERE runs, the name after_discount does not exist yet. This query gives an error:

SELECT order_id, amount - 5 AS after_discount
FROM orders
WHERE after_discount > 30;
-- ERROR: column "after_discount" does not exist

Fix it: write the calculation again inside WHERE.

SELECT order_id, amount - 5 AS after_discount
FROM orders
WHERE amount - 5 > 30
ORDER BY order_id;

SELECT * means all columns

For a first look at a table, you can write * instead of the column names:

SELECT *
FROM customers
LIMIT 2;

WHERE has a condition. A condition is a test that is TRUE or FALSE for each row, like amount > 30.

Rows where it is TRUE stay. All other rows are removed:

SELECT order_id, amount
FROM orders
WHERE amount > 30;

These are the comparison signs you use most:

Two conditions: AND, OR

AND means both conditions must be true. OR means at least one must be true.

SELECT order_id, status, amount
FROM orders
WHERE status = 'paid' AND amount > 30;

AND runs before OR

SQL does AND before OR. It is like math: 2 + 3 × 4 is 14, because you do 3 × 4 first.

You want paid or refunded orders over 50. This query gives the wrong rows:

SELECT order_id, status, amount
FROM orders
WHERE status = 'paid' OR status = 'refunded' AND amount > 50;

Look at order 102: its amount is 25, not over 50, but it is in the result.

Add parentheses ( ). SQL runs the part inside them first:

SELECT order_id, status, amount
FROM orders
WHERE (status = 'paid' OR status = 'refunded') AND amount > 50;

Rule: when you mix AND and OR, always use parentheses.

Dates: a start and an end

To get orders from 10 to 15 January, use >= for the first day. Use < for the day after the last day:

SELECT order_id, order_date
FROM orders
WHERE order_date >= '2026-01-10'
  AND order_date <  '2026-01-16';

Remember the NULL values in our shop: Dev has no country, and order 105 has no amount.

NULL is not zero, and it is not empty text. These are three different things:

= NULL never finds anything

You want customers with no country. You try = NULL. It returns no rows, not even Dev:

SELECT name
FROM customers
WHERE country = NULL;

Use IS NULL. It is the only way to test for a missing value:

SELECT name
FROM customers
WHERE country IS NULL;

The opposite is IS NOT NULL.

Why: TRUE, FALSE and UNKNOWN

A test with NULL has three possible answers: TRUE, FALSE or UNKNOWN. SQL cannot know if a missing value is equal to something, so the answer is UNKNOWN.

WHERE keeps a row only when the answer is TRUE. Look at the test country <> 'GB' for each customer:

A problem: <> also removes NULL rows

Which customers are not from GB? You expect Asha, Carla, Dev and Omar. But Dev is missing:

SELECT name, country
FROM customers
WHERE country <> 'GB';

To keep Dev, ask for the NULL rows too:

SELECT name, country
FROM customers
WHERE country <> 'GB' OR country IS NULL;

NOT IN with a NULL returns nothing

NOT IN means “not equal to any value in the list”. If the list contains a NULL, no row comes back:

SELECT name
FROM customers
WHERE country NOT IN ('GB', NULL);

NOT IN ('GB', NULL) means: not 'GB' AND not NULL. Look at Carla. 'US' <> 'GB' is TRUE, but 'US' <> NULL is UNKNOWN.

TRUE AND UNKNOWN is UNKNOWN. WHERE keeps only TRUE, so every row is removed.

In real work, the list often comes from another table. If that table has a NULL, you get no rows.

Remove the NULLs from the list first. Chapter 2 shows a safer way.

Replace NULL with COALESCE

COALESCE(a, b) gives a when a is not NULL. When a is NULL, it gives b.

SELECT name, COALESCE(country, 'unknown') AS country
FROM customers
ORDER BY customer_id;

Every column has a data type. The type says what kind of value the column holds.

Text that looks like a number

'10' in quotes is text. 10 without quotes is a number. They sort in different ways.

Imagine you load prices from a file. They are saved as text:

SELECT price
FROM raw_prices
ORDER BY price;

CAST(value AS type) changes the type. After CAST, the prices sort as numbers:

SELECT price
FROM raw_prices
ORDER BY CAST(price AS integer);

Whole numbers drop the decimals

In PostgreSQL and SQL Server, a whole number divided by a whole number gives a whole number. This is called integer division.

4 of our 6 orders are paid. We type 4 and 6 ourselves. What percent is paid?

SELECT 4 / 6                  AS wrong_rate,
       ROUND(4 * 100.0 / 6, 1) AS paid_percent;

MySQL, Snowflake and BigQuery keep decimals here. Still, write 100.0. Then your query is correct on every database.

Text values go in single quotes: 'paid'. In PostgreSQL, double quotes are for column names, not for text.

Find a pattern with LIKE

LIKE checks if text matches a pattern. In a pattern, % means “any text, any length”. _ means “exactly one character”.

SELECT name
FROM customers
WHERE name LIKE '%a';

More patterns, tested on our five names (Asha, Ben, Carla, Dev, Omar):

Capital letters matter

In PostgreSQL, LIKE cares about capital letters. 'a%' does not match Asha.

LOWER() changes text to small letters (a, b, c). Compare after LOWER():

SELECT name
FROM customers
WHERE LOWER(name) LIKE 'a%';

Clean messy text

Text from files is often not clean. The same status can come in three ways:

• ' Paid' has a space at the start.
• 'PAID ' has a space at the end.
• 'paid' is clean.

WHERE status = 'paid' finds only the third one. Clean the text with TRIM (removes spaces at the start and end) and LOWER:

SELECT status AS raw_status,
       LOWER(TRIM(status)) AS clean_status
FROM raw_orders;

To put two pieces of text together, use ||:

SELECT 'Order ' || order_id AS label
FROM orders
WHERE order_id = 101;

LEFT(text, n) gives the first n characters. LEFT('Asha', 1) gives A.

CASE gives a value based on conditions. Here, every order gets a size label:

SELECT order_id, amount,
       CASE
         WHEN amount >= 50 THEN 'large'
         WHEN amount >= 20 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders
ORDER BY order_id;

SQL reads the WHEN lines from top to bottom. It uses the first one that is TRUE and stops.

The order of WHEN matters

Put amount >= 20 first, and order 103 (60.00) becomes medium. It matches >= 20 first, so SQL never checks >= 50. Put the rule for the biggest amounts first.

NULL goes to ELSE

In the first result, order 105 has no amount, but it is called small.

NULL >= 50 is UNKNOWN, and NULL >= 20 is UNKNOWN. So it goes to ELSE. Add a rule for NULL first:

SELECT order_id, amount,
       CASE
         WHEN amount IS NULL THEN 'unknown'
         WHEN amount >= 50 THEN 'large'
         WHEN amount >= 20 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders
WHERE order_id IN (104, 105);

If you leave out ELSE and no WHEN is TRUE, CASE gives NULL.

Without ORDER BY, the database can return rows in any order. The order can change from one run to the next.

ORDER BY sorts the result. ASC means small to big, and it is the default. DESC means big to small.

LIMIT then keeps the first rows:

SELECT order_id, amount
FROM orders
WHERE amount IS NOT NULL
ORDER BY amount DESC
LIMIT 3;

The WHERE line removes order 105, because its amount is NULL. Without it, PostgreSQL would list order 105 first. The last part of this section explains why.

LIMIT without ORDER BY gives you any 3 rows, not the top 3.

Ties: add a second column

A tie is when two rows have the same value. Then their order is not fixed. Here, orders 101 and 102 both belong to customer 1:

SELECT order_id, customer_id
FROM orders
ORDER BY customer_id, order_id;

Where does NULL go?

Databases do not agree. PostgreSQL puts NULL last when you sort ASC, and first when you sort DESC. MySQL and SQL Server do the opposite.

Say what you want with NULLS LAST:

SELECT order_id, amount
FROM orders
ORDER BY amount DESC NULLS LAST;

Apps show long lists in pages. LIMIT says how many rows go on a page. OFFSET says how many rows to skip first.

SELECT order_id
FROM orders
ORDER BY order_id
LIMIT 2 OFFSET 2;

All three pages:

Two problems with OFFSET

• It gets slow. For page 1,000, the database still reads every row before it, then ignores them.
• Rows can move. In a newest-first list, page 1 shows 106 and 105. Then order 107 arrives. Page 2 now starts with 105, a row you already saw.

Start after the last row

Remember the last order_id on the page. For the next page, ask for rows after it:

SELECT order_id
FROM orders
WHERE order_id > 102
ORDER BY order_id
LIMIT 2;

The database can jump straight to order 103 with an index (a sorted list it keeps for the column). It does not read the rows before it, so page 1,000 is as fast as page 2.

This method is called keyset pagination. It also fixes the second problem: a new order does not change “after order 102”.

The ORDER BY column must be unique, like order_id. If it has ties, rows can still move between pages.

COUNT tells you how many rows there are. It has three forms, and they give different numbers:

Count the rows and the orders in order_items:

SELECT COUNT(*)                 AS item_rows,
       COUNT(DISTINCT order_id) AS order_count
FROM order_items;

Now count all orders, and orders that have an amount:

SELECT COUNT(*)      AS all_orders,
       COUNT(amount) AS with_amount
FROM orders;

DISTINCT removes repeated rows

SELECT DISTINCT keeps one copy of each different row. Which customers placed orders?

SELECT DISTINCT customer_id
FROM orders
ORDER BY customer_id;

DISTINCT looks at the whole row, not one column. With two columns, it keeps one row for each different pair:

SELECT DISTINCT customer_id, status
FROM orders
ORDER BY customer_id, status;

Each exercise opens in the SQL workspace with its own table. Write the query, run it, and check the result.

They follow the order of this chapter. Start at the top. If a result surprises you, find out why before you move on.