PostgreSQL Lesson 17 – SELECT Queries | Dataplexa

SELECT — Querying Your Data

SELECT is the most used statement in SQL. Every time you need to read, analyse, or retrieve data from a database, SELECT is how you do it. It is also one of the richest statements in PostgreSQL — with clauses for filtering, sorting, limiting, aliasing, and more. This lesson builds your complete foundation for reading data from the Dataplexa Store, covering every core SELECT concept you will use every single day.

The Basic SELECT

At its simplest, SELECT tells PostgreSQL which columns to return and FROM tells it which table to look in. The asterisk * is a shorthand for all columns — useful for quick exploration but avoid it in production code where you should always name the columns you actually need.

-- Select all columns from customers
SELECT * FROM customers;

-- Select specific columns only
SELECT first_name, last_name, email FROM customers;

-- Select from products — only the columns you care about
SELECT name, category, price FROM products;

Column Aliases — Renaming Output Columns

A column alias gives a column a different name in the results. It does not change the actual column name in the table — only how it appears in the output. Aliases make results easier to read, are essential when column names would otherwise clash in a join, and are required when naming calculated columns.

-- Simple alias using AS
SELECT first_name AS name, email AS contact FROM customers;

-- Alias a calculated column
SELECT name, price, price * 1.08 AS price_with_tax FROM products;

-- Alias with spaces — use double quotes
SELECT first_name AS "First Name", last_name AS "Last Name" FROM customers;

-- AS keyword is optional — but always use it for clarity
SELECT name product_name, price unit_price FROM products;

Filtering Rows with WHERE

WHERE filters the rows returned by SELECT. Only rows where the condition is true are included in the results. You can use comparison operators, logical operators, and a wide range of special SQL operators to express any condition.

-- Basic comparison operators
SELECT name, price FROM products WHERE price > 100;
SELECT name, price FROM products WHERE price = 29.99;
SELECT name, price FROM products WHERE price != 29.99;

-- Combine conditions with AND / OR
SELECT name, price, category
FROM products
WHERE category = 'Electronics' AND price < 50;

SELECT name, price, category
FROM products
WHERE category = 'Electronics' OR category = 'Office';

-- Negate a condition with NOT
SELECT name, category FROM products WHERE NOT category = 'Furniture';

Comparison Operators Quick Reference

Operator	Meaning	Example
=	Equal to	WHERE state = 'CA'
!= or <>	Not equal to	WHERE status != 'cancelled'
<	Less than	WHERE price < 50
>	Greater than	WHERE salary > 60000
<=	Less than or equal	WHERE stock_qty <= 10
>=	Greater than or equal	WHERE price >= 100
BETWEEN	Within a range (inclusive)	WHERE price BETWEEN 20 AND 100
IN	Matches any value in a list	WHERE state IN ('CA','NY','TX')
LIKE	Pattern match (case-sensitive)	WHERE name LIKE 'Wire%'
ILIKE	Pattern match (case-insensitive)	WHERE name ILIKE '%mouse%'
IS NULL	Column has no value	WHERE phone IS NULL
IS NOT NULL	Column has a value	WHERE phone IS NOT NULL

Sorting Results with ORDER BY

ORDER BY sorts the results by one or more columns. ASC sorts smallest to largest (default), DESC sorts largest to smallest. You can sort by multiple columns — PostgreSQL sorts by the first column, then uses subsequent columns as tiebreakers.

-- Sort products from most expensive to least
SELECT name, price FROM products ORDER BY price DESC;

-- Sort customers alphabetically by last name then first name
SELECT first_name, last_name, city
FROM customers
ORDER BY last_name ASC, first_name ASC;

-- Sort orders by date descending — most recent first
SELECT id, customer_id, total_amount, order_date
FROM orders
ORDER BY order_date DESC;

-- Sort by column position number (2 = second column listed)
SELECT name, price, category FROM products ORDER BY 2 DESC;

Limiting Results with LIMIT and OFFSET

LIMIT restricts how many rows come back. OFFSET skips a number of rows before starting to return results. Together they are the foundation of pagination — showing page 1, page 2, page 3 of results.

-- Get the top 5 most expensive products
SELECT name, price FROM products ORDER BY price DESC LIMIT 5;

-- Get the 5 most recent orders
SELECT id, customer_id, total_amount, order_date
FROM orders
ORDER BY order_date DESC
LIMIT 5;

-- Pagination: page 1 = rows 1-10
SELECT name, price FROM products ORDER BY name LIMIT 10 OFFSET 0;

-- Page 2 = rows 11-20
SELECT name, price FROM products ORDER BY name LIMIT 10 OFFSET 10;

-- Page 3 = rows 21-30
SELECT name, price FROM products ORDER BY name LIMIT 10 OFFSET 20;

Removing Duplicate Results with DISTINCT

SELECT DISTINCT removes duplicate rows from the results. It is useful when you want a unique list of values — all the states your customers are from, all the categories in your product catalogue, all the statuses in your orders table.

-- Get a unique list of states your customers are from
SELECT DISTINCT state FROM customers ORDER BY state;

-- Get all unique product categories
SELECT DISTINCT category FROM products ORDER BY category;

-- Get unique order statuses
SELECT DISTINCT status FROM orders;

-- DISTINCT on multiple columns — unique combinations
SELECT DISTINCT category, is_active FROM products ORDER BY category;

-- Unique states:
state
-------
AZ
CA
IL
NY
PA
TX
(12 rows)

-- Unique categories:
category
-------------
Accessories
Electronics
Furniture
Office

Computed Columns and Expressions

SELECT can compute new values on the fly — arithmetic, string concatenation, function calls — without storing anything in the database. The result only exists in the query output.

-- Calculate total value of stock per product
SELECT name, price, stock_qty,
       price * stock_qty AS total_stock_value
FROM products
ORDER BY total_stock_value DESC;

-- Combine first and last name into one column
SELECT first_name || ' ' || last_name AS full_name, email
FROM customers;

-- Calculate profit margin
SELECT name, price,
       price * 0.60 AS estimated_cost,
       price - (price * 0.60) AS estimated_profit
FROM products
WHERE price > 50;

The ORDER of SELECT Clauses

PostgreSQL evaluates SELECT clauses in a specific logical order. You write them in a fixed order too — and getting this order wrong causes a syntax error. This sequence applies to every SELECT query you will ever write.

Order	Clause	Purpose
1	SELECT	Which columns to return
2	FROM	Which table to read from
3	JOIN	Combine with other tables
4	WHERE	Filter rows before grouping
5	GROUP BY	Group rows for aggregation
6	HAVING	Filter groups after aggregation
7	ORDER BY	Sort the results
8	LIMIT / OFFSET	Restrict number of rows returned

🧪 Practice Questions

Answer in the Dataplexa Store database.

1. Write a query to get a unique sorted list of all states your customers are from.

2. Write a query to get the 3 most expensive products — show name and price.

3. Which clause sorts the results of a SELECT query?

4. Which PostgreSQL operator concatenates two strings together — used to combine first_name and last_name into one column?

5. Which clause skips a number of rows before returning results — used together with LIMIT for pagination?

Next up: Filtering like a pro — WHERE with BETWEEN, IN, LIKE, IS NULL, and more.

← Previous Course Index Next →

PostgreSQL