Core SQL: Querying and Manipulating DataLesson 2.1

How to SELECT data from a PostgreSQL table

SELECT syntax, WHERE clause, comparison operators, AND OR NOT, ORDER BY, LIMIT OFFSET, column aliases

Selecting Data

SELECT is the most-used SQL statement. Every filter, sort, and slice you need comes from combining a small set of clauses.

Basic query

SELECT id, username, email
FROM   users
WHERE  created_at > '2024-01-01'
ORDER  BY username ASC
LIMIT  20
OFFSET 40;

Column aliases

SELECT
  first_name || ' ' || last_name AS full_name,
  salary * 12                    AS annual_salary
FROM employees;

Filtering with operators

WHERE salary BETWEEN 50000 AND 90000
WHERE status IN ('active', 'pending')
WHERE email LIKE '%@gmail.com'
WHERE deleted_at IS NULL

Use IS NULL / IS NOT NULL — never = NULL. NULL compared with = always returns NULL (unknown), not true or false.

Ordering

ORDER BY last_name ASC, first_name ASC
ORDER BY created_at DESC NULLS LAST

NULLS LAST pushes NULL values to the bottom of descending sorts, which is usually what you want. Combine LIMIT and OFFSET for pagination, but be aware that large offsets are slow on big tables — keyset pagination is better at scale.