By Matthew Rathbone · October 25, 2026

Reading about SQL only gets you so far. Seeing a query next to the rows it returns is what makes the syntax click. Every example below runs against the same four-table online shop, and every result was produced by actually running the query, so what you see is what you will get.

Jump to: SELECT · WHERE · ORDER BY & LIMIT · aggregates · GROUP BY · JOINs · subqueries · CASE & functions · window functions · INSERT/UPDATE/DELETE · CREATE TABLE

The sample database

The examples use customers, products, orders and order_items. To follow along, create a free SQLite database in a client such as Beekeeper Studio, paste this script into a query tab and run it. (Prefer no setup at all? The interactive lessons run in your browser.)

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT, signup_date TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, status TEXT);
CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER);

INSERT INTO customers VALUES
  (1, 'Ana Silva', 'Brazil', '2025-01-15'),
  (2, 'Ben Carter', 'USA', '2025-02-03'),
  (3, 'Chloe Dubois', 'France', '2025-02-20'),
  (4, 'Dev Patel', 'India', '2025-03-11'),
  (5, 'Emma Jones', 'USA', '2025-04-02'),
  (6, 'Felix Wagner', 'Germany', '2025-05-18'),
  (7, 'Grace Kim', 'USA', '2025-06-07'),
  (8, 'Hiro Tanaka', 'Japan', '2025-06-30');

INSERT INTO products VALUES
  (1, 'Laptop', 'Electronics', 899.00),
  (2, 'Headphones', 'Electronics', 129.50),
  (3, 'Desk Lamp', 'Home', 34.99),
  (4, 'Notebook', 'Stationery', 4.25),
  (5, 'Pen Set', 'Stationery', 12.00),
  (6, 'Office Chair', 'Home', 189.00),
  (7, 'Webcam', 'Electronics', 59.90),
  (8, 'Backpack', 'Accessories', 45.00);

INSERT INTO orders VALUES
  (1, 1, '2025-07-01', 'shipped'),
  (2, 2, '2025-07-03', 'shipped'),
  (3, 2, '2025-07-15', 'cancelled'),
  (4, 3, '2025-07-20', 'shipped'),
  (5, 5, '2025-08-02', 'shipped'),
  (6, 1, '2025-08-09', 'pending'),
  (7, 4, '2025-08-12', 'shipped'),
  (8, 5, '2025-08-21', 'shipped'),
  (9, 7, '2025-09-04', 'shipped'),
  (10, 2, '2025-09-10', 'shipped'),
  (11, 3, '2025-09-18', 'pending'),
  (12, 7, '2025-09-25', 'cancelled');

INSERT INTO order_items VALUES
  (1, 1, 1), (1, 4, 3),
  (2, 2, 1), (2, 7, 1),
  (3, 6, 1),
  (4, 3, 2), (4, 5, 2),
  (5, 8, 1), (5, 4, 5),
  (6, 2, 2),
  (7, 1, 1), (7, 2, 1),
  (8, 5, 3),
  (9, 6, 1), (9, 3, 1),
  (10, 4, 10), (10, 8, 2),
  (11, 7, 1),
  (12, 1, 1);

How the tables connect: orders.customer_id points at customers.id, and each row of order_items links one order to one product. The queries are standard SQL and work in SQLite, PostgreSQL, MySQL and SQL Server; the few places where databases differ are called out.

1. SELECT: reading data

Select everything from a table

* means "all columns". Handy for a first look at a table, but name your columns in real queries.

SELECT * FROM customers;

Result (8 rows):

idnamecountrysignup_date
1Ana SilvaBrazil2025-01-15
2Ben CarterUSA2025-02-03
3Chloe DuboisFrance2025-02-20
4Dev PatelIndia2025-03-11
5Emma JonesUSA2025-04-02
6Felix WagnerGermany2025-05-18
7Grace KimUSA2025-06-07
8Hiro TanakaJapan2025-06-30

Select specific columns

List only the columns you need, in the order you want them.

SELECT name, country FROM customers;

Result (8 rows):

namecountry
Ana SilvaBrazil
Ben CarterUSA
Chloe DuboisFrance
Dev PatelIndia
Emma JonesUSA
Felix WagnerGermany
Grace KimUSA
Hiro TanakaJapan

Rename a column with an alias

AS gives a column (or table) a friendlier name in the output.

SELECT name AS product, price AS price_usd FROM products;

Result (8 rows):

productprice_usd
Laptop899.0
Headphones129.5
Desk Lamp34.99
Notebook4.25
Pen Set12.0
Office Chair189.0
Webcam59.9
Backpack45.0

Calculate a new column

You can do arithmetic inside SELECT. Here is a 10% discount on each price.

SELECT name, price, ROUND(price * 0.9, 2) AS sale_price FROM products;

Result (8 rows):

namepricesale_price
Laptop899.0809.1
Headphones129.5116.55
Desk Lamp34.9931.49
Notebook4.253.83
Pen Set12.010.8
Office Chair189.0170.1
Webcam59.953.91
Backpack45.040.5

Remove duplicates with DISTINCT

Which countries do our customers live in? DISTINCT returns each value once.

SELECT DISTINCT country FROM customers;

Result (6 rows):

country
Brazil
USA
France
India
Germany
Japan

2. WHERE: filtering rows

Filter with a comparison

Only rows where the condition is true are returned. Text values go in single quotes.

SELECT name, price FROM products WHERE price > 100;

Result (3 rows):

nameprice
Laptop899.0
Headphones129.5
Office Chair189.0

Combine conditions with AND / OR

AND needs both conditions to be true; OR needs one. Use brackets when you mix them.

SELECT name, category, price
FROM products
WHERE category = 'Electronics' AND price < 100;

Result (1 row):

namecategoryprice
WebcamElectronics59.9

Match a list with IN

Shorter and clearer than a chain of ORs.

SELECT name, category FROM products
WHERE category IN ('Stationery', 'Home');

Result (4 rows):

namecategory
Desk LampHome
NotebookStationery
Pen SetStationery
Office ChairHome

Match a range with BETWEEN

BETWEEN is inclusive at both ends.

SELECT name, price FROM products
WHERE price BETWEEN 10 AND 60;

Result (4 rows):

nameprice
Desk Lamp34.99
Pen Set12.0
Webcam59.9
Backpack45.0

Search text with LIKE

% matches any run of characters. This finds names that start with "H".

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

Result (1 row):

name
Hiro Tanaka

Find missing values with IS NULL

You cannot use = NULL; NULL needs IS NULL or IS NOT NULL. Our sample tables have no NULLs, so this returns nothing, which is itself a useful check.

SELECT * FROM customers WHERE country IS NULL;

Result (0 rows):

idnamecountrysignup_date

3. ORDER BY and LIMIT: sorting and top-N

Sort results

ORDER BY sorts ascending by default. Add DESC for highest first.

SELECT name, price FROM products ORDER BY price DESC;

Result (8 rows):

nameprice
Laptop899.0
Office Chair189.0
Headphones129.5
Webcam59.9
Backpack45.0
Desk Lamp34.99
Pen Set12.0
Notebook4.25

Get the top 3

LIMIT keeps only the first N rows, so combine it with ORDER BY to get a "top 3". (SQL Server uses SELECT TOP 3 instead.)

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

Result (3 rows):

nameprice
Laptop899.0
Office Chair189.0
Headphones129.5

Sort by two columns

Rows are sorted by the first column; ties are broken by the second.

SELECT category, name, price FROM products ORDER BY category ASC, price DESC;

Result (8 rows):

categorynameprice
AccessoriesBackpack45.0
ElectronicsLaptop899.0
ElectronicsHeadphones129.5
ElectronicsWebcam59.9
HomeOffice Chair189.0
HomeDesk Lamp34.99
StationeryPen Set12.0
StationeryNotebook4.25

4. Aggregate functions: COUNT, SUM, AVG, MIN, MAX

Count rows

COUNT(*) counts every row in the result.

SELECT COUNT(*) AS customer_count FROM customers;

Result (1 row):

customer_count
8

Several aggregates at once

One row summarising the whole table.

SELECT
  COUNT(*) AS products,
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive,
  ROUND(AVG(price), 2) AS average_price
FROM products;

Result (1 row):

productscheapestmost_expensiveaverage_price
84.25899.0171.71

Total quantity sold

SUM adds up a numeric column.

SELECT SUM(quantity) AS units_ordered FROM order_items;

Result (1 row):

units_ordered
40

5. GROUP BY and HAVING: summaries per group

Count rows per group

GROUP BY collapses rows that share a value, so aggregates are calculated once per group.

SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY customers DESC;

Result (6 rows):

countrycustomers
USA3
Japan1
India1
Germany1
France1
Brazil1

Average per group

Average price for each product category.

SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category;

Result (4 rows):

categoryavg_price
Accessories45.0
Electronics362.8
Home112.0
Stationery8.13

Filter groups with HAVING

WHERE filters rows before grouping; HAVING filters groups after. Here: categories with more than one product.

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
HAVING COUNT(*) > 1;

Result (3 rows):

categoryproducts
Electronics3
Home2
Stationery2

6. JOIN: combining tables

Real databases split data across tables. A join stitches them back together using a column they share. Our guide to SQL joins covers every type in depth; these are the two you will use most.

INNER JOIN: only matching rows

Each order next to the customer who placed it. Orders without a matching customer would be dropped.

SELECT orders.id AS order_id, customers.name, orders.order_date, orders.status
FROM orders
INNER JOIN customers ON customers.id = orders.customer_id
ORDER BY orders.id;

Result (12 rows, first 8 shown):

order_idnameorder_datestatus
1Ana Silva2025-07-01shipped
2Ben Carter2025-07-03shipped
3Ben Carter2025-07-15cancelled
4Chloe Dubois2025-07-20shipped
5Emma Jones2025-08-02shipped
6Ana Silva2025-08-09pending
7Dev Patel2025-08-12shipped
8Emma Jones2025-08-21shipped

LEFT JOIN: keep everything from the left table

Every customer, with their order count. Customers with no orders still appear, with 0.

SELECT customers.name, COUNT(orders.id) AS orders
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.id, customers.name
ORDER BY orders DESC;

Result (8 rows):

nameorders
Ben Carter3
Ana Silva2
Chloe Dubois2
Emma Jones2
Grace Kim2
Dev Patel1
Felix Wagner0
Hiro Tanaka0

Join three tables

Chain joins to follow the relationships: order, then its items, then the product.

SELECT o.id AS order_id, p.name AS product, oi.quantity, p.price * oi.quantity AS line_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
ORDER BY o.id;

Result (19 rows, first 8 shown):

order_idproductquantityline_total
1Laptop1899.0
1Notebook312.75
2Headphones1129.5
2Webcam159.9
3Office Chair1189.0
4Desk Lamp269.98
4Pen Set224.0
5Backpack145.0

Join plus GROUP BY: revenue per customer

Join first, then aggregate. Cancelled and pending orders are excluded.

SELECT c.name, ROUND(SUM(p.price * oi.quantity), 2) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped'
GROUP BY c.id, c.name
ORDER BY revenue DESC;

Result (6 rows):

namerevenue
Dev Patel1028.5
Ana Silva911.75
Ben Carter321.9
Grace Kim223.99
Emma Jones102.25
Chloe Dubois93.98

Find rows with no match (anti-join)

Customers who have never placed an order: LEFT JOIN, then keep the rows where the right side is NULL.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Result (2 rows):

name
Felix Wagner
Hiro Tanaka

7. Subqueries and CTEs

Subquery in WHERE

A query inside a query. The inner one runs first and gives the outer one a value to compare against.

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

Result (2 rows):

nameprice
Laptop899.0
Office Chair189.0

Subquery with IN

Products that appear in at least one order.

SELECT name
FROM products
WHERE id IN (SELECT product_id FROM order_items);

Result (8 rows):

name
Laptop
Headphones
Desk Lamp
Notebook
Pen Set
Office Chair
Webcam
Backpack

Common table expression (CTE)

WITH names an intermediate result so the main query reads top to bottom. Same idea as a subquery, usually easier to read.

WITH shipped_revenue AS (
  SELECT o.customer_id, SUM(p.price * oi.quantity) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE o.status = 'shipped'
  GROUP BY o.customer_id
)
SELECT c.name, ROUND(sr.revenue, 2) AS revenue
FROM shipped_revenue sr
JOIN customers c ON c.id = sr.customer_id
WHERE sr.revenue > 500;

Result (2 rows):

namerevenue
Ana Silva911.75
Dev Patel1028.5

8. CASE, text and date examples

If/then logic with CASE

CASE is SQL's if/else. Label each product by price band.

SELECT name, price,
  CASE
    WHEN price >= 100 THEN 'premium'
    WHEN price >= 20 THEN 'standard'
    ELSE 'budget'
  END AS band
FROM products
ORDER BY price DESC;

Result (8 rows):

namepriceband
Laptop899.0premium
Office Chair189.0premium
Headphones129.5premium
Webcam59.9standard
Backpack45.0standard
Desk Lamp34.99standard
Pen Set12.0budget
Notebook4.25budget

Join text together

SQLite and PostgreSQL use ||; MySQL uses CONCAT(); SQL Server uses + or CONCAT().

SELECT name || ' (' || country || ')' AS customer FROM customers;

Result (8 rows):

customer
Ana Silva (Brazil)
Ben Carter (USA)
Chloe Dubois (France)
Dev Patel (India)
Emma Jones (USA)
Felix Wagner (Germany)
Grace Kim (USA)
Hiro Tanaka (Japan)

Change case and measure length

UPPER, LOWER and LENGTH (LEN in SQL Server) are the everyday text functions.

SELECT UPPER(name) AS shout, LENGTH(name) AS characters FROM customers LIMIT 3;

Result (3 rows):

shoutcharacters
ANA SILVA9
BEN CARTER10
CHLOE DUBOIS12

Filter and group by month

Date functions differ by database. In SQLite, strftime formats a date; PostgreSQL uses DATE_TRUNC or TO_CHAR, MySQL uses DATE_FORMAT.

SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

Result (3 rows):

monthorders
2025-074
2025-084
2025-094

Replace NULL with a default

COALESCE returns the first non-NULL value. Handy after a LEFT JOIN.

SELECT c.name, COALESCE(MAX(o.order_date), 'never') AS last_order
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

Result (8 rows):

namelast_order
Ana Silva2025-08-09
Ben Carter2025-09-10
Chloe Dubois2025-09-18
Dev Patel2025-08-12
Emma Jones2025-08-21
Felix Wagnernever
Grace Kim2025-09-25
Hiro Tanakanever

9. Window functions

Rank rows within a group

RANK() OVER (PARTITION BY ... ORDER BY ...) numbers rows inside each group without collapsing them like GROUP BY does. Here, products ranked by price within each category.

SELECT category, name, price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products
ORDER BY category, price_rank;

Result (8 rows):

categorynamepriceprice_rank
AccessoriesBackpack45.01
ElectronicsLaptop899.01
ElectronicsHeadphones129.52
ElectronicsWebcam59.93
HomeOffice Chair189.01
HomeDesk Lamp34.992
StationeryPen Set12.01
StationeryNotebook4.252

10. Changing data: INSERT, UPDATE, DELETE

These statements modify your data, so try them on a practice database, never on production. Always write the WHERE first on UPDATE and DELETE: without one, they hit every row.

Add a row with INSERT

List the columns you are filling, then the values in the same order.

INSERT INTO products (id, name, category, price)
VALUES (9, 'Mouse', 'Electronics', 24.99);

Check it with SELECT * FROM products WHERE id = 9:

idnamecategoryprice
9MouseElectronics24.99

Change rows with UPDATE

SET says what to change; WHERE says which rows. This raises Electronics prices by 5%.

UPDATE products
SET price = ROUND(price * 1.05, 2)
WHERE category = 'Electronics';

Check it with SELECT name, price FROM products WHERE category = 'Electronics' ORDER BY id:

nameprice
Laptop943.95
Headphones135.97
Webcam62.9
Mouse26.24

Remove rows with DELETE

Delete the product we just added. Check the WHERE with a SELECT first.

DELETE FROM products WHERE id = 9;

Check it with SELECT COUNT(*) AS products_left FROM products:

products_left
8

11. Building tables: CREATE TABLE

Create a table with constraints

Choose a type for each column and add rules: a primary key, NOT NULL, a DEFAULT, and a foreign key linking to another table.

CREATE TABLE reviews (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(id),
  rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

Habits that make every query better

  • Build queries up in small steps. Start with SELECT * FROM table LIMIT 10, add a WHERE, then a join, then the grouping. Run it at each step.
  • Remember the logical order. SQL runs FROM/JOIN, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. That is why you cannot use a SELECT alias inside WHERE.
  • Name your columns. SELECT * is fine for exploring, but explicit columns make queries faster and safer.
  • Test destructive statements first. Turn a DELETE or UPDATE into a SELECT with the same WHERE and check the rows.

Where to go next

FAQ

What are the most common SQL queries?

The ones in sections 1 to 6: SELECT with WHERE, ORDER BY and LIMIT, aggregates with GROUP BY, and JOINs. Analysts use these several times a day; subqueries, CTEs and window functions come next.

How do I write a basic SQL query?

Start with SELECT the columns you want FROM a table, for example SELECT name, price FROM products. Then add WHERE to filter rows and ORDER BY to sort them.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping. HAVING filters the groups produced by GROUP BY, so it can use aggregates such as COUNT(*) > 1.

Which SQL database should beginners use?

SQLite. It needs no server, the file is portable, and nearly everything you learn transfers to PostgreSQL, MySQL and SQL Server.

Where can I practice these SQL queries for free?

Run them in any free SQLite client using the setup script above, or use SQL Easy's free interactive lessons, which run in your browser with instant feedback.