Basic filtering and a mini SQL exercise

Combining conditions

SELECT name, city, marks
FROM students
WHERE marks >= 80 AND city <> 'Mumbai';

• AND requires both conditions.
• OR requires at least one.
• NOT reverses a condition.

AND is evaluated before OR, so use parentheses to make your intention explicit:

-- Students in year 1 who are from Kochi or Bengaluru
SELECT name
FROM students
WHERE year = 1 AND (city = 'Kochi' OR city = 'Bengaluru');
-- Meera Nair, Kiran Rao

Without the parentheses the query would mean "year 1 students from Kochi, or any student from Bengaluru", which also returns Arjun Das.

IN: matching a list

SELECT name, city
FROM students
WHERE city IN ('Chennai', 'Bengaluru');
-- Ravi Kumar, Arjun Das, Kiran Rao

IN is shorter and clearer than several OR conditions. NOT IN excludes the listed values.

BETWEEN: ranges

SELECT name, marks
FROM students
WHERE marks BETWEEN 70 AND 85;
-- Ravi Kumar (72), Priya Shah (81), Kiran Rao (77)

BETWEEN is inclusive: it equals marks >= 70 AND marks <= 85. It works with numbers and dates.

LIKE: text patterns

SELECT name
FROM students
WHERE name LIKE 'A%';
-- Asha Menon, Arjun Das

% matches any number of characters (including none) and _ matches exactly one. In PostgreSQL, LIKE is case-sensitive; ILIKE ignores case.

IS NULL: missing values

Because NULL means unknown, email = NULL is never true. Use IS NULL or IS NOT NULL:

SELECT name
FROM students
WHERE email IS NULL;
-- Kiran Rao

Mini SQL exercise

Try each challenge on your own before reading the solution. All use the students table.

Challenge 1. List the names of second- and third-year students with marks of at least 70, highest marks first.

SELECT name, marks
FROM students
WHERE year IN (2, 3) AND marks >= 70
ORDER BY marks DESC;
-- Asha Menon (88), Priya Shah (81), Ravi Kumar (72)

Challenge 2. Find students whose surname starts with N or S. Surnames come after the space in name.

SELECT name
FROM students
WHERE name LIKE '% N%' OR name LIKE '% S%';
-- Meera Nair, Priya Shah

Challenge 3. Show the student with the lowest marks who has an email address on record.

SELECT name, marks
FROM students
WHERE email IS NOT NULL
ORDER BY marks ASC
LIMIT 1;
-- Arjun Das (64)