Querying with SELECT

SELECT reads data. List the columns you want (avoid SELECT * in application code), filter rows with WHERE, sort with ORDER BY and take the first rows with LIMIT.

Useful filters: =, <>, <, >=, BETWEEN a AND b, IN (...), ILIKE 'a%' (case-insensitive pattern), and combining with AND, OR, NOT.

NULL means "unknown" — it is never equal to anything, not even another NULL. Test for it with IS NULL / IS NOT NULL, and replace it with coalesce(value, fallback). You can also compute new columns and rename them with AS.

queries.sqlSQL

SELECT full_name, form
FROM students
WHERE form = 4
ORDER BY full_name;

SELECT full_name, fee_balance
FROM students
WHERE fee_balance > 0 AND form IN (1, 2, 3)
ORDER BY fee_balance DESC
LIMIT 3;

SELECT full_name FROM students WHERE full_name ILIKE '%ma%';

SELECT full_name, coalesce(email, 'no email') AS email
FROM students
WHERE email IS NULL;

SELECT full_name,
       date_part('year', age(current_date, birth_date)) AS age_years,
       fee_balance / 1000 AS balance_thousands
FROM students
WHERE birth_date BETWEEN '2008-01-01' AND '2009-12-31'
ORDER BY birth_date;

SELECT DISTINCT form FROM students ORDER BY form;

Runs in your browser · PostgreSQL

Key points

SELECT columns → FROM table → WHERE filter → ORDER BY → LIMIT.
Use IS NULL, never = NULL; coalesce supplies defaults.
Name computed columns with AS so results are readable.

Exercise

Write queries for: students in Form 3 or 4 with a fee balance; students whose name starts with 'R'; the two youngest students; and every student's name in upper case with their email or 'missing'.

Show solution

Try the exercise yourself first — then compare your approach with this one.

Each question maps to one clause: IN for a list of forms, LIKE 'R%' for names starting with R, ORDER BY birth_date DESC LIMIT 2 for the two youngest, and coalesce to replace a missing email.

queries.sqlSQL

-- Form 3 or 4 students who still owe fees
SELECT full_name, form, fee_balance
FROM students
WHERE form IN (3, 4) AND fee_balance > 0
ORDER BY form, full_name;

-- Names starting with R
SELECT full_name FROM students WHERE full_name LIKE 'R%';

-- The two youngest students (latest birth dates)
SELECT full_name, birth_date
FROM students
WHERE birth_date IS NOT NULL
ORDER BY birth_date DESC
LIMIT 2;

-- Every name in capitals, with the email or 'missing'
SELECT upper(full_name) AS name, coalesce(email, 'missing') AS email
FROM students
ORDER BY full_name;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding