Lab 2 of 10 — Foundations
WHERE: Filtering Your Results
Find exactly what you’re looking for
Loading SQL engine...
DATABASE SCHEMA — Available Tables
TABLE: students (id, name, major, gpa, year, scholarship, city, age)Concept Recap
WHERE filters which rows are returned. It goes after FROM.
- Comparison operators:
=,!=,>,<,>=,<= - Combine conditions:
AND,OR,NOT - List check:
IN ('value1', 'value2') - Range check:
BETWEEN 3.0 AND 3.9(inclusive) - Pattern matching:
LIKE 'A%'(% = any characters, _ = one character) - Null check:
IS NULL/IS NOT NULL
Text values always need single quotes. Numbers and booleans (0/1) do not.
Worked Example
-- Students with GPA above 3.5
SELECT name, gpa FROM students WHERE gpa > 3.5;
-- CS students with scholarships
SELECT name, major, scholarship
FROM students
WHERE major = 'Computer Science' AND scholarship = 1;
-- Students from LA or SF
SELECT name, city FROM students
WHERE city IN ('Los Angeles', 'San Francisco');
-- GPA between 3.0 and 3.6 (inclusive)
SELECT name, gpa FROM students WHERE gpa BETWEEN 3.0 AND 3.6;
-- Names containing the letter 'a' (case-insensitive)
SELECT name FROM students WHERE name LIKE '%a%' OR name LIKE '%A%';
Guided
Exercise 1 — Juniors and Seniors
Fill in the blanks to find all students who are juniors (year = 3) OR seniors (year = 4).
Hint: Fill in:
= 3 and = 4. You can also write this as WHERE year IN (3, 4). Independent
Exercise 2 — Scholarship Eligibility
Write a query finding scholarship students (scholarship = 1) with a GPA between 3.5 and 4.0. Show their name, GPA, and major.
Hint: Combine
scholarship = 1 AND gpa BETWEEN 3.5 AND 4.0 with AND. Challenge
Exercise 3 — Multi-Condition Filter
Write a query finding students who:
- Are NOT from Los Angeles
- ARE in their first or second year
- Have a GPA above 3.0
- Have the letter “i” anywhere in their name (case-insensitive — use
LIKE '%i%' OR LIKE '%I%')
Hint: Use
NOT city = 'Los Angeles' or city != 'Los Angeles'. Group conditions carefully with parentheses: (name LIKE '%i%' OR name LIKE '%I%'). Mini Project
Mini Project — Financial Aid Officer
Play the role of a financial aid officer. Write 4 WHERE queries and label each with a comment:
- Scholarship-eligible students — gpa ≥ 3.5
- Students who might need support — gpa < 3.0
- Senior students nearing graduation — year = 4
- CS or Math students in years 3–4 — use IN() for majors and IN() or BETWEEN for years
Lab 2 Complete!
You can now filter rows with precision. WHERE is used in virtually every real-world SQL query. Master it and you’ve unlocked the core of data retrieval.
Continue to Lab 3: ORDER BY, LIMIT & DISTINCT →