Read data with SELECT — filter rows with WHERE, match patterns with LIKE/ILIKE, sort and paginate with ORDER BY and LIMIT, remove duplicates with DISTINCT, and handle NULLs with COALESCE.
Why: SELECT reads data. You name the columns you want (or * for all of them), the table after FROM, and an optional WHERE to keep only matching rows.
SELECT id, email, full_name
FROM users
WHERE is_active = true;Why: WHERE supports far more than equals. LIKE matches text patterns (% means "any characters"); ILIKE is the same but ignores upper/lower case. IN checks a list, BETWEEN checks a range, and IS NULL checks for missing values (you cannot use = NULL).
SELECT * FROM users WHERE email LIKE '%@example.com'; -- ends with @example.comSELECT * FROM users WHERE full_name ILIKE 'ada%'; -- starts with "ada", any caseSELECT * FROM products WHERE price BETWEEN 10 AND 50; -- 10 to 50 inclusiveSELECT * FROM users WHERE id IN (1, 2, 3); -- id is 1, 2, or 3SELECT * FROM users WHERE last_login IS NULL; -- never logged inWhy: ORDER BY sorts the results (ASC = smallest first, DESC = largest first). LIMIT caps how many rows come back, and OFFSET skips rows — together they give you pages of results.
SELECT name, price
FROM products
ORDER BY price DESC, name ASC
LIMIT 10 OFFSET 20; -- skip the first 20, then take 10 (this is "page 3")Why: DISTINCT collapses repeated values so each one appears only once — handy for "what are all the different countries we have users in?".
SELECT DISTINCT country FROM users;Why: a SELECT column can be a calculation, and AS gives the result a readable name. COALESCE returns the first value that is not NULL, which is the clean way to supply a fallback for missing data.
-- a column can be a calculation
SELECT
name,
price,
price * 1.2 AS price_with_tax
FROM products;-- COALESCE fills in a fallback when a value is NULL (ada has no country)
SELECT
full_name,
COALESCE(country, 'Unknown') AS country
FROM users;