Which of the following SQL queries correctly selects records where the…
2024
Which of the following SQL queries correctly selects records where the First_Name contains a specific pattern, the Last_Name also matches a pattern using LIKE, and the City does not match a given pattern using NOT LIKE?
- A.
SELECT * FROM Employee
WHERE First_Name LIKE '%A%'
AND Last_Name LIKE '%K%'
AND City NOT LIKE '%Delhi%'; - B.
SELECT * FROM Employee
WHERE First_Name = '%A%'
AND Last_Name = '%K%'
AND City != '%Delhi%'; - C.
SELECT * FROM Employee
WHERE First_Name IN '%A%'
AND Last_Name IN '%K%'
AND City NOT IN '%Delhi%'; - D.
SELECT * FROM Employee
WHERE First_Name LIKE '%A%'
OR Last_Name LIKE '%K%'
OR City NOT LIKE '%Delhi%'; - E.
SELECT * FROM Employee
WHERE First_Name NOT LIKE '%A%'
AND Last_Name LIKE '%K%'
AND City LIKE '%Delhi%';
Attempted by 44 students.
Show answer & explanation
Correct answer: A
The requirement is a single query that filters the Employee table on three conditions joined by AND: First_Name matches a pattern with LIKE, Last_Name matches a pattern with LIKE, and City does NOT match a pattern with NOT LIKE.
In SQL, the LIKE operator performs pattern matching, where % is a wildcard standing for zero or more characters and _ stands for exactly one character. NOT LIKE returns the rows that do not match the pattern. The = / != operators do an exact literal comparison, so a % inside them is treated as a literal percent character, not a wildcard. IN tests membership in a list and must be followed by a parenthesised set of values, e.g. IN ('A','B') — it cannot be used for pattern matching.
Only Option (a) uses LIKE / LIKE / NOT LIKE with the wildcard % and joins all three predicates with AND, so every selected record must satisfy all three conditions. Hence Option (a) is correct.
Option (b) uses = and != with '%', which compares literally instead of pattern-matching. Option (c) misuses IN, which needs a parenthesised list and does not do pattern matching. Option (d) joins the predicates with OR, so a record matching any single condition is selected, which is not what is required. Option (e) inverts the logic (NOT LIKE on First_Name and LIKE on City).