SQL › How to Retrieve Data from a Table

SELECT

SELECT reads data from a table. You choose the columns, and you get one result row for each row in the table.

Syntax

Order of execution: FROM → SELECT → DISTINCT → ORDER BY

You write SELECT first, but the database reads the table (FROM) first, then builds the columns, then removes duplicates, and only then sorts.

Code
SELECT column1, column2, ...
FROM table_name;

Examples

Every column

See everything a table holds, for example when you open a table for the first time.

Code
SELECT * FROM categories;

Choosing columns

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

Code
-- Chosen columns
SELECT product_name, price FROM products;
 
-- Same columns, different order: the result columns follow your list
SELECT price, product_name FROM products;

Calculated columns with AS

Work out a new value for each row, such as a total or a discounted price, and give it a name.

Code
-- Calculated column, named with AS
SELECT product_name, price * stock AS stock_value FROM products;
 
-- A name with spaces needs double quotes
SELECT product_name, price * 0.9 AS "Sale Price" FROM products;
 
-- The word AS can be left out; the column is still called sale_price
SELECT product_name, price * 0.9 sale_price FROM products;

Calculations without a table

PostgreSQL, MySQL and SQL Server can work out values with no FROM at all, like a calculator. Oracle before 23ai needs FROM DUAL, a built-in one-row table, for the same query.

Code
SELECT 5 + 2 AS total, 7 * 3 AS product;

Removing duplicates with DISTINCT

List the different values in a column, such as every city you ship to, without repeats.

Code
-- Every shipping city, one row per order: cities repeat
SELECT shipping_city FROM orders;
 
-- Each shipping city once
SELECT DISTINCT shipping_city FROM orders;
 
-- DISTINCT on two columns keeps each unique pair, so a category can appear twice
SELECT DISTINCT category_id, is_available FROM products;

Fixed order with ORDER BY

Get rows in a predictable order, so the result is the same every time.

Code
SELECT category_name FROM categories ORDER BY category_name;

Remember

• Columns come back in the order you list them, and problems check that order.
• Without AS, PostgreSQL names a calculated column ?column?; other databases make up their own name.
• In standard SQL, double quotes are for names ("Sale Price") and single quotes are for text ('Delhi'), so PostgreSQL refuses AS 'name'. MySQL quotes names with backticks and SQL Server with square brackets, and both accept AS 'name'.
• In PostgreSQL and SQL Server, whole numbers divide without the remainder: 850 / 100 is 8, while 850 / 100.0 is 8.5. MySQL and Oracle give 8.5 either way.
• Without ORDER BY, rows can come back in any order. Every problem tells you which ORDER BY to add.

Practice

1. Prices With 18% GST (Easy)
2. Loyalty Vouchers (Easy)
3. Sale Price Tags (Easy)
4. Shipping City and Status Pairs (Easy)