SQL & Databases
Topic 3 · Querying data · ⏱ about 2 h

SELECT: Choosing Columns, Sorting & Limiting

The shape of every query: SELECT columns FROM a table. Pick and rename columns, calculate new ones, remove duplicates with DISTINCT, sort with ORDER BY, and take the top rows with LIMIT.

Show:
In this topic
Beginner

The shape of a query

Every query that reads data starts with two parts: SELECT (which columns) and FROM (which table). A semicolon ends the statement.

SELECT name, price -- the columns you want, separated by commas FROM products; -- the table they come from

Use * for every column: SELECT * FROM products;. It is handy for a first look, but in real queries list the columns you need: it is clearer and faster.

SQL keywords are not case-sensitive (select works too), but writing them in capitals is the usual style. Text values go in single quotes: 'Brisbane'. Comments start with --.

Beginner

Calculated columns and aliases

A column in SELECT can be a calculation. Give it a readable name with AS (an alias):

SELECT name, price, price * 1.1 AS price_with_gst, ROUND(price / 10, 2) AS weekly_cost FROM products;

Arithmetic uses + - * /. In SQLite and PostgreSQL, dividing two whole numbers gives a whole number (7 / 2 is 3), so write 7 / 2.0 or 7 * 1.0 / 2 to get 3.5.

Beginner

DISTINCT: unique values

SELECT DISTINCT removes duplicate rows from the result. SELECT DISTINCT category FROM products; lists each category once. With several columns, it removes rows where all the listed columns are the same.

Beginner

ORDER BY: sorting

ORDER BY sorts the result. ASC (smallest first) is the default; DESC sorts largest first. Sort by several columns: the second only breaks ties in the first.

SELECT name, category, price FROM products ORDER BY category, price DESC; -- by category A→Z, then most expensive first within each

Without ORDER BY, the database may return rows in any order, and the order can change. If order matters, always say so.

Beginner

LIMIT and OFFSET: the top few rows

LIMIT n returns only the first n rows: with ORDER BY, that is a "top n" query. OFFSET skips rows first, which is how websites show page 2, page 3 and so on.

SELECT name, price FROM products ORDER BY price DESC LIMIT 3; -- the 3 most expensive SELECT name FROM products ORDER BY name LIMIT 5 OFFSET 5; -- rows 6 to 10: "page 2"

SQL Server writes this as SELECT TOP 3 …, and the standard form is FETCH FIRST 3 ROWS ONLY.

Try it

Try it: select, sort and limit

Run each preset, then change it: different columns, a different sort, a different limit.

Interactive: this part runs in your browser.

Beginner

The order you write vs the order it runs

You write SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT, but the database works in a different order: first FROM (get the table), then WHERE (filter rows), GROUP BY, HAVING, then SELECT (compute columns), then ORDER BY and LIMIT. This explains a classic error: an alias made in SELECT cannot be used in WHERE, because WHERE runs first. It can be used in ORDER BY, which runs after.

Beginner

Practice: check yourself

Write the query, then press ✔ Check my answer: your query runs on a fresh copy of the shop database and its result is compared with the correct one. ▶ Run just shows your result. Each run starts from the original data, so you cannot break anything.

Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)

Exercise 1. Most expensive first

Show the name and price of every product, most expensive first.

Exercise 2. Top 3 cheapest

Show the name and price of the 3 cheapest products, cheapest first.

Exercise 3. Price with GST

Show each product's name, price, and a column called price_gst: the price plus 10%, rounded to 2 decimals.

Exercise 4. The categories

List each product category once, in alphabetical order.

Beginner

Summary

Key words

query
a request for data, starting with SELECT
clause
a part of a query: SELECT, FROM, WHERE, ORDER BY…
alias
a new name for a column or table, with AS
DISTINCT
removes duplicate rows
ORDER BY … DESC
sorts, largest first
LIMIT / OFFSET
take the first n rows / skip some rows first

Common mistakes

  • Double quotes for text: "Brisbane" means a column called Brisbane in standard SQL. Use single quotes: 'Brisbane'.
  • A trailing comma before FROM: SELECT name, price, FROM products is a syntax error.
  • Relying on the order rows come back in without ORDER BY.
  • Expecting 7 / 2 to be 3.5: use 7 / 2.0.
  • Using a SELECT alias in WHERE.
Check yourself

Quiz

1. Which query lists each category only once?

2. How do you get the 5 cheapest products?

3. Which quotes are for text values in SQL?

4. What does SELECT 7 / 2 return in SQLite?

5. Without ORDER BY, the order of rows is…

Go deeper

📚 Further reading

Trusted, mostly free sources: official documentation, well-known textbooks and courses. When you want to confirm or go deeper on anything here, start with these.

Ready