WHERE with LIKE pattern matching in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find names starting with 'Jo' using LIKE

Given a table Employees with a column name, which query returns all employees whose names start with 'Jo'?

SQL

SELECT name FROM Employees WHERE name LIKE 'Jo%';

A SELECT name FROM Employees WHERE name LIKE '%Jo%';

B SELECT name FROM Employees WHERE name LIKE '%Jo';

C SELECT name FROM Employees WHERE name LIKE 'J_o%';

D SELECT name FROM Employees WHERE name LIKE 'Jo%';

💡 Hint

The percent sign (%) matches any sequence of characters, and placing it after 'Jo' means names starting with 'Jo'.

Option D correctly uses 'Jo%' to match any name starting with 'Jo'. Option D matches names ending with 'Jo'. Option D matches names containing 'Jo' anywhere. Option D matches names starting with 'J' followed by any single character, then 'o'.

❓ query_result

intermediate

Select emails containing 'admin' anywhere

Which SQL query returns all rows from Users where the email contains the word 'admin' anywhere in the string?

SQL

SELECT email FROM Users WHERE email LIKE '%admin%';

A SELECT email FROM Users WHERE email LIKE '%admin';

B SELECT email FROM Users WHERE email LIKE '%admin%';

C SELECT email FROM Users WHERE email LIKE 'admin%';

D SELECT email FROM Users WHERE email LIKE '_admin%';

💡 Hint

Use % before and after 'admin' to find it anywhere in the string.

Option B uses '%admin%' which matches any string containing 'admin' anywhere. Option B matches strings starting with 'admin'. Option B matches strings ending with 'admin'. Option B matches strings with any single character before 'admin' at the start.

📝 Syntax

advanced

Identify the query with syntax error in LIKE pattern

Which SQL query will cause a syntax error due to incorrect LIKE pattern?

A SELECT * FROM Orders WHERE order_id LIKE '12%3_'

B SELECT * FROM Orders WHERE order_id LIKE '123%';

C SELECT * FROM Orders WHERE order_id LIKE '12_3%';

D SELECT * FROM Orders WHERE order_id LIKE '%123';

💡 Hint

Check for missing quotes or incomplete strings in the LIKE pattern.

Option A is missing the closing quote for the LIKE pattern, causing a syntax error. All other options have correct syntax.

❓ optimization

advanced

Optimize query using LIKE for prefix search

You want to find all customers whose city starts with 'San'. Which query is most efficient for this search?

A SELECT * FROM Customers WHERE city LIKE '%San';

B SELECT * FROM Customers WHERE city LIKE '%San%';

C SELECT * FROM Customers WHERE city LIKE 'San%';

D SELECT * FROM Customers WHERE city LIKE '_San%';

💡 Hint

Using a pattern that starts with the search string allows index use.

Option C uses 'San%' which matches cities starting with 'San' and can use indexes efficiently. Option C and C use leading %, which prevents index use. Option C matches cities with any single character before 'San', which is not the intended search.

🧠 Conceptual

expert

Understanding LIKE pattern matching with escape characters

Given a table Files with a column filename, which query correctly finds all files with names ending with '_backup' (underscore is a literal character, not a wildcard)?

A SELECT filename FROM Files WHERE filename LIKE '%\_backup' ESCAPE '\';

B SELECT filename FROM Files WHERE filename LIKE '%[_]backup';

C SELECT filename FROM Files WHERE filename LIKE '%_backup';

D SELECT filename FROM Files WHERE filename LIKE '%\_backup';

💡 Hint

Use ESCAPE clause to treat underscore as a normal character.

Option A correctly escapes the underscore using '\' and specifies ESCAPE '\' so underscore is treated literally. Option A escapes underscore but lacks ESCAPE clause, so backslash is treated literally. Option A treats underscore as wildcard matching any single character. Option A uses character class which may work in some SQL dialects but is not standard.