🎓 Velnov

🗄️ SQL · Topic 3 of 20

Filtering with WHERE — SQL Questions with Answers

30 practice questions on Filtering with WHERE, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

3 easy · 16 medium · 11 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

WHEREFilteringComparison operatorsLogical operatorsPrecedenceParenthesesBETWEENRangesDatesGotchasINSubqueriesLIKEWildcardsIndexesPerformanceNULLThree-valued logicNOT INNOT EXISTSNOTNegationCollationCase sensitivity

Jump to: Easy · Medium · Hard

Easy questions (3)

Q1. easy

What does the WHERE clause do?

SELECT * FROM students WHERE marks > 50;
  1. A Filters rows, keeping only those where the condition is TRUE
  2. B Sorts the rows
  3. C Groups the rows
  4. D Selects which columns to show

Answer: A — Filters rows, keeping only those where the condition is TRUE

WHERE decides which ROWS appear; SELECT decides which COLUMNS. A row is kept only when the condition evaluates to TRUE (not FALSE and not NULL).

WHEREFiltering

Q2. easy

Which operator tests equality in SQL?

WHERE city = 'Delhi'
  1. A = (a single equals sign)
  2. B ==
  3. C ===
  4. D eq

Answer: A — = (a single equals sign)

Unlike most programming languages, SQL uses one equals sign for comparison. There is no assignment operator inside a WHERE clause to confuse it with.

Comparison operators

Q3. easy

Which operator means NOT EQUAL in standard SQL?

WHERE status <> 'cancelled'
  1. A <> (and != is accepted by most databases)
  2. B !== only
  3. C NOT=
  4. D =/=

Answer: A — <> (and != is accepted by most databases)

<> is the SQL standard; != works in MySQL, PostgreSQL and SQL Server too. Both behave identically.

Comparison operators

Medium questions (16)

Q4. medium

How do you require TWO conditions to both be true?

  1. A WHERE age > 18 AND city = 'Delhi'
  2. B WHERE age > 18 && city = 'Delhi'
  3. C WHERE age > 18, city = 'Delhi'
  4. D WHERE both(age > 18, city = 'Delhi')

Answer: A — WHERE age > 18 AND city = 'Delhi'

SQL uses the words AND, OR and NOT rather than symbols. (MySQL accepts && but it is non-standard.)

Logical operators

Q6. medium

What does BETWEEN do? WHERE marks BETWEEN 50 AND 80

  1. A Matches values from 50 to 80 INCLUSIVE of both ends
  2. B Matches 51 to 79
  3. C Matches only 50 and 80
  4. D Matches values outside the range

Answer: A — Matches values from 50 to 80 INCLUSIVE of both ends

BETWEEN is inclusive: equivalent to marks >= 50 AND marks <= 80. Remember this when working with dates, where the upper bound often surprises people.

BETWEENRanges

Q8. medium

What does IN do? WHERE city IN ('Delhi', 'Mumbai', 'Pune')

  1. A Matches rows whose city equals ANY value in the list
  2. B Matches rows containing those letters
  3. C Requires all three cities
  4. D Is the same as BETWEEN

Answer: A — Matches rows whose city equals ANY value in the list

IN is shorthand for a chain of ORs and is far more readable. It can also take a subquery: WHERE id IN (SELECT customer_id FROM orders).

INSubqueries

Q9. medium

What does LIKE 'A%' match?

WHERE name LIKE 'A%'      -- starts with A
WHERE name LIKE '%son'    -- ends with son
WHERE name LIKE '%an%'    -- contains an
WHERE code LIKE 'A_1'     -- A, any char, 1
  1. A Any value STARTING with A - % matches zero or more characters
  2. B Only the exact text 'A%'
  3. C Values ending with A
  4. D Values containing exactly one A

Answer: A — Any value STARTING with A - % matches zero or more characters

% is the multi-character wildcard and _ matches exactly one character. So 'A%' is starts-with, '%a%' is contains, and '_a%' means second letter is a.

LIKEWildcards

Q10. medium

What does the _ wildcard match in LIKE?

  1. A Exactly ONE character
  2. B Zero or more characters
  3. C A literal underscore
  4. D A space

Answer: A — Exactly ONE character

'J_n' matches Jan and Jon but not Jain. To match a literal underscore or percent sign, use an ESCAPE clause.

LIKEWildcards

Q14. medium

What does NOT IN do when the list contains a NULL? WHERE id NOT IN (1, 2, NULL)

  1. A It returns NO rows at all - a classic NULL trap
  2. B It ignores the NULL
  3. C It returns all rows
  4. D It throws an error

Answer: A — It returns NO rows at all - a classic NULL trap

NOT IN expands to id <> 1 AND id <> 2 AND id <> NULL, and the last comparison is always UNKNOWN, so the whole condition can never be TRUE. Use NOT EXISTS, or filter NULLs from the subquery.

  • A famous SQL interview question.

NOT INNULLNOT EXISTS

Q15. medium

How do you negate a condition?

  1. A WHERE NOT (age > 18) - or use the opposite operator: age <= 18
  2. B WHERE !age > 18
  3. C WHERE age NOT > 18
  4. D WHERE opposite(age > 18)

Answer: A — WHERE NOT (age > 18) - or use the opposite operator: age <= 18

NOT inverts a condition. It also combines with other keywords: NOT IN, NOT LIKE, NOT BETWEEN, IS NOT NULL.

NOTNegation

Q16. medium

Is string comparison in WHERE case-sensitive?

  1. A It depends on the database's COLLATION - MySQL is usually case-insensitive, PostgreSQL is case-sensitive
  2. B Always case-sensitive
  3. C Always case-insensitive
  4. D Only for numbers

Answer: A — It depends on the database's COLLATION - MySQL is usually case-insensitive, PostgreSQL is case-sensitive

Because behaviour varies, portable code normalises both sides: WHERE LOWER(email) = LOWER('Ana@X.com'). Note that wrapping the column in a function can prevent index use.

CollationCase sensitivity

Q17. medium

Which query finds students with marks over 90 OR under 20?

  1. A WHERE marks > 90 OR marks < 20
  2. B WHERE marks > 90 AND marks < 20
  3. C WHERE marks BETWEEN 90 AND 20
  4. D WHERE marks NOT BETWEEN 20 AND 90

Answer: A — WHERE marks > 90 OR marks < 20

Option B can never be true (no number is both). Option D is close but INCLUSIVE at the boundaries, so it excludes exactly 20 and 90 differently from option A.

ORRanges

Q19. medium

What does this return? SELECT * FROM orders WHERE total > 100 AND total < 100;

  1. A Zero rows - the conditions contradict each other
  2. B All rows
  3. C Rows equal to 100
  4. D An error

Answer: A — Zero rows - the conditions contradict each other

No value satisfies both. Contradictory filters usually mean AND was written where OR was intended.

LogicAND

Q20. medium

How do you filter on a boolean column?

  1. A WHERE is_active = TRUE (or = 1 where booleans are stored as integers)
  2. B WHERE is_active is on
  3. C WHERE is_active
  4. D WHERE active(is_active)

Answer: A — WHERE is_active = TRUE (or = 1 where booleans are stored as integers)

PostgreSQL has a real BOOLEAN type and even allows bare 'WHERE is_active'. MySQL stores booleans as TINYINT, so = 1 is normal.

BooleansDialects

Q22. medium

How do you find rows where a text column is empty OR missing?

  1. A WHERE name IS NULL OR name = ''
  2. B WHERE name = NULL
  3. C WHERE name IS EMPTY
  4. D WHERE LENGTH(name) = NULL

Answer: A — WHERE name IS NULL OR name = ''

An empty string and NULL are different values in SQL: '' is a known-but-blank value, NULL is unknown. Real data often contains both, so check for both.

NULLEmpty string

Q23. medium

What does WHERE id IN (SELECT customer_id FROM orders) do?

  1. A Keeps customers who have at least one order - a subquery supplies the list
  2. B Is invalid syntax
  3. C Returns all customers
  4. D Joins the tables

Answer: A — Keeps customers who have at least one order - a subquery supplies the list

IN accepts a subquery returning one column. For large data, EXISTS often performs better and handles NULLs more safely.

SubqueriesINEXISTS

Q25. medium

Which query is written correctly?

  1. A SELECT name FROM users WHERE age > 18 ORDER BY name;
  2. B SELECT name FROM users ORDER BY name WHERE age > 18;
  3. C SELECT name WHERE age > 18 FROM users;
  4. D WHERE age > 18 SELECT name FROM users;

Answer: A — SELECT name FROM users WHERE age > 18 ORDER BY name;

Clause order is fixed: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Writing them out of order is a syntax error.

Clause order

Q27. medium

How would you exclude several statuses?

  1. A WHERE status NOT IN ('cancelled', 'refunded')
  2. B WHERE status != ('cancelled', 'refunded')
  3. C WHERE NOT status = 'cancelled', 'refunded'
  4. D WHERE status EXCEPT ('cancelled')

Answer: A — WHERE status NOT IN ('cancelled', 'refunded')

NOT IN is the readable form. Remember it silently returns nothing if the list contains NULL, and that rows where status itself is NULL are excluded too.

NOT INNULL

Q30. medium

Before running a DELETE, what is the safest habit?

  1. A Run the same WHERE clause in a SELECT first to see exactly which rows will be affected
  2. B Run it and check afterwards
  3. C Use DELETE without WHERE first
  4. D Disable the index

Answer: A — Run the same WHERE clause in a SELECT first to see exactly which rows will be affected

Previewing with SELECT (and wrapping changes in a transaction you can roll back) prevents the classic disaster of a DELETE whose WHERE was wrong - or missing entirely.

SafetyDELETETransactions

Hard questions (11)

Q5. hard

Which has higher precedence: AND or OR?

-- probably WRONG:
WHERE city = 'Delhi' OR city = 'Mumbai' AND active = 1
-- means: Delhi OR (Mumbai AND active)

-- intended:
WHERE (city = 'Delhi' OR city = 'Mumbai') AND active = 1
  1. A AND - so 'a OR b AND c' means 'a OR (b AND c)'
  2. B OR
  3. C They are equal, evaluated left to right
  4. D It depends on the database

Answer: A — AND - so 'a OR b AND c' means 'a OR (b AND c)'

AND binds tighter, exactly like multiplication before addition. This causes real bugs - use parentheses whenever you mix them.

  • A very common interview question and a frequent real-world bug.

PrecedenceParentheses

Q7. hard

Why is 'WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'' risky?

WHERE order_date >= '2026-01-01'
  AND order_date <  '2026-02-01'
  1. A If the column stores times, rows later on 31 Jan (e.g. 14:30) are EXCLUDED because the bound means midnight
  2. B BETWEEN does not work with dates
  3. C The dates need to be numbers
  4. D It is perfectly safe

Answer: A — If the column stores times, rows later on 31 Jan (e.g. 14:30) are EXCLUDED because the bound means midnight

A DATETIME of 2026-01-31 14:30 is greater than 2026-01-31 00:00. The safe pattern uses a half-open range: >= start AND < next_month_start.

DatesBETWEENGotchas

Q11. hard

Why can 'WHERE name LIKE '%son'' be slow on a large table?

  1. A A leading wildcard prevents the database using an index, forcing a full scan
  2. B LIKE is always slow
  3. C It uses too much memory
  4. D It is not slow

Answer: A — A leading wildcard prevents the database using an index, forcing a full scan

Indexes work like a dictionary - sorted by the start of the value. 'A%' can seek directly; '%son' must check every row. For contains-searches at scale, use full-text search.

IndexesPerformance

Q12. hard

How do you correctly test for a NULL value?

WHERE email IS NULL
WHERE email IS NOT NULL
  1. A WHERE email IS NULL
  2. B WHERE email = NULL
  3. C WHERE email == NULL
  4. D WHERE email = 'NULL'

Answer: A — WHERE email IS NULL

NULL means 'unknown', and comparing anything to unknown yields UNKNOWN - never TRUE. So = NULL silently matches nothing. Always use IS NULL / IS NOT NULL.

  • One of the most-tested SQL concepts.

NULLThree-valued logic

Q13. hard

What does 'WHERE status <> 'shipped'' return for rows where status is NULL?

  1. A It EXCLUDES them - NULL <> 'shipped' is UNKNOWN, not TRUE
  2. B It includes them
  3. C It throws an error
  4. D It includes them only in MySQL

Answer: A — It EXCLUDES them - NULL <> 'shipped' is UNKNOWN, not TRUE

A frequent bug: 'everything not shipped' silently drops NULL rows. Write: WHERE status <> 'shipped' OR status IS NULL.

NULLGotchas

Q18. hard

Why avoid wrapping a column in a function in WHERE? WHERE YEAR(order_date) = 2026

  1. A It makes the condition non-sargable - the index on order_date cannot be used
  2. B YEAR() does not exist
  3. C It returns wrong results
  4. D There is no problem

Answer: A — It makes the condition non-sargable - the index on order_date cannot be used

The database must compute YEAR() for every row before comparing. Rewrite as a range so the index works: order_date >= '2026-01-01' AND order_date < '2027-01-01'.

SargableIndexesPerformance

Q21. hard

What is the result of comparing NULL = NULL?

  1. A UNKNOWN - not TRUE, because two unknown values cannot be proven equal
  2. B TRUE
  3. C FALSE
  4. D An error

Answer: A — UNKNOWN - not TRUE, because two unknown values cannot be proven equal

SQL uses three-valued logic: TRUE, FALSE and UNKNOWN. WHERE keeps only TRUE rows, so NULL comparisons filter everything out. (The IS NOT DISTINCT FROM operator treats NULLs as equal where supported.)

NULLThree-valued logic

Q24. hard

What is the difference between WHERE and HAVING?

SELECT dept, COUNT(*) AS n
FROM employees
WHERE active = 1        -- rows first
GROUP BY dept
HAVING COUNT(*) > 5;    -- then groups
  1. A WHERE filters individual ROWS before grouping; HAVING filters GROUPS after aggregation
  2. B They are interchangeable
  3. C HAVING is faster
  4. D WHERE only works with numbers

Answer: A — WHERE filters individual ROWS before grouping; HAVING filters GROUPS after aggregation

Aggregates like COUNT(*) cannot appear in WHERE because grouping has not happened yet. Filter raw rows in WHERE (more efficient) and group results in HAVING.

HAVINGGROUP BY

Q26. hard

What does this match? WHERE phone LIKE '9%' AND LENGTH(phone) = 10

  1. A Ten-character phone numbers starting with 9
  2. B Any number containing 9
  3. C Numbers ending in 9
  4. D An error

Answer: A — Ten-character phone numbers starting with 9

Combining pattern matching with a length check is a common validation query for finding malformed data. (Some databases call the function LEN instead of LENGTH.)

LIKEData validation

Q28. hard

What does 'WHERE 1=1' accomplish in generated SQL?

WHERE 1=1
  AND city = 'Delhi'
  AND active = 1
  1. A It is an always-true placeholder, so every real condition can be appended with AND
  2. B It filters one row
  3. C It speeds up the query
  4. D It is a syntax error

Answer: A — It is an always-true placeholder, so every real condition can be appended with AND

When code builds queries dynamically, starting with 1=1 avoids awkward logic about whether to write WHERE or AND. Optimisers ignore the constant.

Dynamic SQLTricks

Q29. hard

Why should application code use parameterised queries instead of building WHERE strings?

-- safe (placeholder):
SELECT * FROM users WHERE email = ?;
  1. A To prevent SQL injection - user input is sent separately from the query text
  2. B To make queries shorter
  3. C Because concatenation is a syntax error
  4. D It makes no difference

Answer: A — To prevent SQL injection - user input is sent separately from the query text

Concatenating input allows an attacker to inject SQL (e.g. ' OR 1=1 --). Parameters keep data and code separate, so input can never change the query's structure.

SQL injectionSecurity

Practice “Filtering with WHERE” as a quiz