SQL course · Module 2: Querying one table · Lesson 4 of 40
Quick answer: The SQL SELECT statement reads rows from a table. SELECT * FROM products; returns every column, SELECT name, price FROM products; returns only the columns you list, AS gives a result column a new name, DISTINCT removes duplicate rows, and LIMIT 3 returns at most three rows. Always add ORDER BY when you use LIMIT, so you know which rows you get.
In this lesson you will learn:
- how to select all columns or only the ones you need
- how to calculate new columns and name them with
AS - when a name needs double quotes and when a value needs single quotes
- how
DISTINCTandLIMITwork, and whyLIMITneedsORDER BY
Before you start: Lesson 3, Install PostgreSQL and Load a Sample Database for SQL Practice.
Open psql on the practice database with psql -U postgres -d shop and type each query. On Linux, Postgres.app or Homebrew, change the command as Lesson 3 explains. We ran every example on PostgreSQL 16.15. Everything used here is described the same way in the current PostgreSQL 18 manual, so it works the same on PostgreSQL 17 and 18.
Table of Contents
Select all columns with SELECT *
SELECT * FROM products;
id | name | category | price
----+-----------------------------+-------------+--------
1 | Wireless Mouse | Electronics | 24.99
2 | Mechanical Keyboard | Electronics | 89.00
3 | USB-C Hub | Electronics | 39.50
4 | Laptop Stand | Accessories | 45.00
5 | Desk Lamp | Home Office | 32.00
6 | Notebook Pack | Stationery | 12.50
7 | Gel Pens 10-Pack | Stationery | 6.75
8 | Noise-Cancelling Headphones | Electronics | 149.00
9 | Ergonomic Chair | Home Office | 259.00
10 | HD Webcam | Electronics | 54.90
(10 rows)
This statement has two parts. SELECT says which columns you want, and FROM says which table to read. The star * means “all columns”, in the order the table defines them. The semicolon ends the statement. The last line, (10 rows), is psql telling you how many rows came back. It is not part of the data.
We did not ask for any order. The rows came back by id because the table was just loaded, but the PostgreSQL docs say that without sorting, rows “will be returned in an unspecified order” (PostgreSQL docs: Sorting Rows). From now on we add ORDER BY id to our queries, so your rows come out in the same order as ours. Lesson 7 explains ORDER BY in full.
SELECT * is handy for a first look at a table. In saved queries and programs, list the columns you need instead. The result is smaller, and the query does not change shape when someone adds a column to the table later.
Select only the columns you need
Write the column names after SELECT, separated by commas. These are the columns of the products table from Lesson 1.
SELECT name, price FROM products ORDER BY id;
name | price
-----------------------------+--------
Wireless Mouse | 24.99
Mechanical Keyboard | 89.00
USB-C Hub | 39.50
Laptop Stand | 45.00
Desk Lamp | 32.00
Notebook Pack | 12.50
Gel Pens 10-Pack | 6.75
Noise-Cancelling Headphones | 149.00
Ergonomic Chair | 259.00
HD Webcam | 54.90
(10 rows)
The result has the columns in the order you write them, not the order of the table:
SELECT price, name, category FROM products ORDER BY id LIMIT 3;
price | name | category
-------+---------------------+-------------
24.99 | Wireless Mouse | Electronics
89.00 | Mechanical Keyboard | Electronics
39.50 | USB-C Hub | Electronics
(3 rows)
LIMIT 3 keeps the output short. It is explained at the end of this lesson.
Expressions: calculate new columns
The select list can hold calculations, not only column names. Here is every price with a 10% discount:
SELECT name, price, price * 0.9 FROM products ORDER BY id;
name | price | ?column?
-----------------------------+--------+----------
Wireless Mouse | 24.99 | 22.491
Mechanical Keyboard | 89.00 | 80.100
USB-C Hub | 39.50 | 35.550
Laptop Stand | 45.00 | 40.500
Desk Lamp | 32.00 | 28.800
Notebook Pack | 12.50 | 11.250
Gel Pens 10-Pack | 6.75 | 6.075
Noise-Cancelling Headphones | 149.00 | 134.100
Ergonomic Chair | 259.00 | 233.100
HD Webcam | 54.90 | 49.410
(10 rows)
Two things to notice. The new column has no real name, so PostgreSQL calls it ?column?. And the result has three decimal places, because a price with 2 decimals times 0.9 (1 decimal) keeps all 3. The table itself does not change: a SELECT only reads data.
Name columns with aliases (AS)
AS gives a result column a name. This name is called an alias.
SELECT name, price, round(price * 0.9, 2) AS sale_price FROM products ORDER BY id;
name | price | sale_price
-----------------------------+--------+------------
Wireless Mouse | 24.99 | 22.49
Mechanical Keyboard | 89.00 | 80.10
USB-C Hub | 39.50 | 35.55
Laptop Stand | 45.00 | 40.50
Desk Lamp | 32.00 | 28.80
Notebook Pack | 12.50 | 11.25
Gel Pens 10-Pack | 6.75 | 6.08
Noise-Cancelling Headphones | 149.00 | 134.10
Ergonomic Chair | 259.00 | 233.10
HD Webcam | 54.90 | 49.41
(10 rows)
round(value, 2) rounds to 2 decimal places. Lesson 8 covers functions like this. Notice that 6.075 became 6.08: for numeric values, PostgreSQL breaks ties “by rounding away from zero” (PostgreSQL docs: Mathematical Functions).
Expressions can use several columns. Each row in order_items has a quantity and the unit price paid, so the line total is their product:
SELECT order_id, product_id, quantity, unit_price,
quantity * unit_price AS line_total
FROM order_items
ORDER BY order_id, product_id
LIMIT 5;
order_id | product_id | quantity | unit_price | line_total
----------+------------+----------+------------+------------
1 | 1 | 1 | 22.99 | 22.99
1 | 6 | 2 | 12.50 | 25.00
2 | 1 | 1 | 22.99 | 22.99
2 | 2 | 1 | 89.00 | 89.00
3 | 8 | 1 | 149.00 | 149.00
(5 rows)
Order 1 bought 2 notebook packs at 12.50, so that line is 25.00. A query can be split over several lines like this; psql runs it when it reaches the semicolon.
PostgreSQL also lets you leave out FROM to calculate a single value:
SELECT 2 + 3 AS answer;
answer
--------
5
(1 row)
The PostgreSQL docs note that “some other SQL databases cannot do this” without a dummy one-row table (PostgreSQL docs: SELECT).
AS is optional, but write it
SELECT name product, price AS "Price (USD)" FROM products ORDER BY id LIMIT 3;
product | Price (USD)
---------------------+-------------
Wireless Mouse | 24.99
Mechanical Keyboard | 89.00
USB-C Hub | 39.50
(3 rows)
name product works without AS. The docs say AS “is usually optional”, but when the alias is one of certain PostgreSQL key words (such as from or year) you must write AS or put the alias in double quotes, and they recommend that you “always either write AS or double-quote the output column name” (PostgreSQL docs: Select Lists). The second alias is in double quotes because it contains spaces, brackets and capital letters.
Upper case, lower case and double quotes
SELECT NAME, Price AS Sale FROM PRODUCTS ORDER BY id LIMIT 3;
name | sale
---------------------+-------
Wireless Mouse | 24.99
Mechanical Keyboard | 89.00
USB-C Hub | 39.50
(3 rows)
This works, and every heading is in lower case, even the alias Sale. In PostgreSQL, “key words and unquoted identifiers are case-insensitive”, and “unquoted names are always folded to lower case”. But “quoting an identifier also makes it case-sensitive” (PostgreSQL docs: Lexical Structure). So a quoted "Name" looks for a column with a capital N, which does not exist:
SELECT "Name" FROM products;
ERROR: column "Name" does not exist
LINE 1: SELECT "Name" FROM products;
^
HINT: Perhaps you meant to reference the column "products.name".
The HINT line even suggests the fix. Remember the two kinds of quotes:
- Single quotes make a text value, such as
'Pakistan'. - Double quotes make a name (a table, column or alias), such as
"Price (USD)".
The same docs note that folding to lower case is PostgreSQL behaviour; the SQL standard folds to upper case. The simplest habit: write table and column names in lower case without quotes, as the shop database does.
Remove duplicates with DISTINCT
Ten customers come from seven countries, so the country column has repeats:
SELECT country FROM customers ORDER BY country;
country
----------
Canada
France
Germany
Italy
Pakistan
Pakistan
Pakistan
Pakistan
UK
USA
(10 rows)
DISTINCT, written right after SELECT, removes duplicate rows: “one row is kept from each group of duplicates” (PostgreSQL docs: SELECT).
SELECT DISTINCT country FROM customers ORDER BY country;
country
----------
Canada
France
Germany
Italy
Pakistan
UK
USA
(7 rows)
DISTINCT and a missing value
SELECT DISTINCT city FROM customers ORDER BY city;
city
---------
Berlin
Chicago
Karachi
Lahore
London
Milan
Paris
Toronto
(9 rows)
Two customers live in Lahore, so it appears once. The empty last row is Hira Siddiqui, who has no city: her city is NULL, and psql prints NULL as an empty cell. For DISTINCT, “null values are considered equal”, so all NULLs become one row (PostgreSQL docs: Select Lists). Lesson 6 is all about NULL.
DISTINCT looks at the whole row
SELECT DISTINCT country, city FROM customers ORDER BY country, city;
country | city
----------+---------
Canada | Toronto
France | Paris
Germany | Berlin
Italy | Milan
Pakistan | Karachi
Pakistan | Lahore
Pakistan |
UK | London
USA | Chicago
(9 rows)
With two columns, DISTINCT removes rows where both values repeat. Pakistan now appears three times, because it has three different cities (Karachi, Lahore and no city). Only the second Pakistan and Lahore row was removed.
Limit the number of rows with LIMIT
LIMIT n returns at most n rows. Combined with ORDER BY price DESC (highest price first), it gives the three most expensive products:
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
name | price
-----------------------------+--------
Ergonomic Chair | 259.00
Noise-Cancelling Headphones | 149.00
Mechanical Keyboard | 89.00
(3 rows)
Without ORDER BY, LIMIT 3 just gives you any three rows. The docs warn that you then “will get an unpredictable subset of the query’s rows” (PostgreSQL docs: LIMIT and OFFSET).
The SQL standard has its own way to write this, and PostgreSQL accepts it too:
SELECT name, price FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY;
name | price
-----------------------------+--------
Ergonomic Chair | 259.00
Noise-Cancelling Headphones | 149.00
Mechanical Keyboard | 89.00
(3 rows)
Dialect note. The docs call LIMIT “PostgreSQL-specific syntax, also used by MySQL”, while FETCH FIRST comes from the SQL:2008 standard. SQLite also supports LIMIT (SQLite: SELECT). SQL Server uses TOP instead: SELECT TOP (3) name, price FROM products ORDER BY price DESC;. Microsoft’s docs say “always use an ORDER BY clause with the TOP clause” (Microsoft Learn: TOP). Lesson 2 compares these databases.
Try it yourself
Exercise 1: Product list with nice headings
Show the name and category of every product, in id order. The column headings must be Product and Category, with capital first letters.
Exercise 2: Which statuses exist?
List each order status from the orders table once, in alphabetical order.
Exercise 3: The newest orders
Show the id, date and status of the three most recent orders.
Solutions
Solution 1
SELECT name AS "Product", category AS "Category" FROM products ORDER BY id;
Product | Category
-----------------------------+-------------
Wireless Mouse | Electronics
Mechanical Keyboard | Electronics
USB-C Hub | Electronics
Laptop Stand | Accessories
Desk Lamp | Home Office
Notebook Pack | Stationery
Gel Pens 10-Pack | Stationery
Noise-Cancelling Headphones | Electronics
Ergonomic Chair | Home Office
HD Webcam | Electronics
(10 rows)
Without the double quotes, the headings would be folded to product and category.
Solution 2
SELECT DISTINCT status FROM orders ORDER BY status;
status
-----------
cancelled
delivered
pending
shipped
(4 rows)
The 22 orders use only four statuses.
Solution 3
SELECT id, order_date, status FROM orders ORDER BY order_date DESC LIMIT 3;
id | order_date | status
----+------------+---------
22 | 2026-10-30 | pending
21 | 2026-10-27 | shipped
20 | 2026-10-21 | shipped
(3 rows)
ORDER BY order_date DESC puts the newest order first, and LIMIT 3 keeps three rows.
Common mistakes
- Forgetting a comma.
SELECT name price FROM productsgives no error. PostgreSQL readspriceas an alias forname, so you get one column calledpricethat is full of product names. - A comma before FROM.
SELECT name, price, FROM products;fails withERROR: syntax error at or near "FROM". Remove the last comma. - Single quotes around a column name.
SELECT 'name' FROM productsreturns the wordnameon every row, in a column called?column?. Use no quotes, or double quotes. - Spaces in an alias without double quotes.
SELECT price AS unit price FROM products;fails withERROR: syntax error at or near "price". WriteAS "unit price"orAS unit_price. - A wrong table name.
SELECT name FROM product;fails withERROR: relation "product" does not exist. The table isproducts; type\dtin psql to see the real names.
Frequently asked questions
Is SELECT * bad practice?
It is fine for exploring a table by hand. In code, reports and views, list the columns you need: the query returns less data, and its result does not change when someone adds or reorders columns in the table.
Is the AS keyword required in SQL?
Usually not, but write it anyway. PostgreSQL requires AS (or double quotes) when the alias is one of certain key words, such as year, and its docs recommend always writing AS or double-quoting the alias. It also makes a missing comma easier to spot.
Does SELECT DISTINCT apply to all columns?
Yes. DISTINCT compares whole result rows, so SELECT DISTINCT country, city removes a row only when both the country and the city repeat. To get one row per value of one column, select only that column.
Summary
SELECT columns FROM table;reads data;*means all columns, and the result follows the order of your list.- The select list can hold expressions like
quantity * unit_price; name them withAS, or psql shows?column?. - Unquoted names are folded to lower case; double quotes keep capitals and spaces; single quotes are for text values.
DISTINCTremoves duplicate rows, compares all selected columns, and treats NULLs as equal.LIMIT n(orFETCH FIRST n ROWS ONLY) returns at most n rows; use it withORDER BY, and useTOPin SQL Server.
These few clauses are most of the daily work in the data analyst roadmap: almost every report starts with a SELECT.
Next lesson
Next: Lesson 5, SQL WHERE Clause: Filter Rows with AND, OR, IN, BETWEEN and LIKE.
Sources
- PostgreSQL documentation: SELECT
- PostgreSQL documentation: Select Lists
- PostgreSQL documentation: Sorting Rows (ORDER BY)
- PostgreSQL documentation: LIMIT and OFFSET
- PostgreSQL documentation: Lexical Structure
- PostgreSQL documentation: Mathematical Functions and Operators
- SQLite: SELECT
- Microsoft Learn: TOP (Transact-SQL)





