Bird
Raised Fist0
SQLquery~30 mins

WHERE with LIKE pattern matching in SQL - Mini Project: Build & Apply

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Filtering Customer Names Using WHERE with LIKE Pattern Matching
📖 Scenario: You work at a small online store. You have a list of customers in a database table. You want to find customers whose names start with certain letters.
🎯 Goal: Build a SQL query that selects customer names from a table called customers where the names match a pattern using the LIKE operator.
📋 What You'll Learn
Create a table called customers with a column name and insert exact customer names.
Create a variable pattern to hold the pattern string for matching names.
Write a SQL SELECT query that uses WHERE name LIKE pattern to filter customers.
Complete the query by adding a semicolon at the end.
💡 Why This Matters
🌍 Real World
Filtering customer or user data by name patterns is common in business reports and search features.
💼 Career
Knowing how to use WHERE with LIKE helps in writing queries for data filtering and reporting in many database jobs.
Progress0 / 4 steps
1
Create the customers table and insert data
Write SQL statements to create a table called customers with a column name of type VARCHAR(50). Then insert these exact customer names: 'Alice', 'Bob', 'Charlie', 'David', and 'Eve'.
SQL
Hint

Use CREATE TABLE to make the table and INSERT INTO to add each customer name.

2
Define the pattern variable for matching names
Create a SQL variable called pattern and set it to the string 'A%' to match names starting with the letter A.
SQL
Hint

Use DECLARE to create a variable and set it to 'A%'.

3
Write the SELECT query using WHERE with LIKE
Write a SQL SELECT statement to get the name column from the customers table where name LIKE pattern.
SQL
Hint

Use SELECT name FROM customers WHERE name LIKE pattern to filter names.

4
Complete the SQL query with a semicolon
Add a semicolon ; at the end of the SELECT query to complete the SQL statement.
SQL
Hint

Remember to end SQL statements with a semicolon ;.

Practice

(1/5)
1. Which SQL clause is used to filter rows based on a pattern in a text column?
SELECT * FROM employees WHERE name LIKE 'A%';
easy
A. GROUP BY
B. ORDER BY
C. WHERE with LIKE
D. JOIN

Solution

  1. Step 1: Understand the purpose of LIKE

    The LIKE keyword is used to match text patterns in SQL queries.

  2. Step 2: Identify the filtering clause

    The WHERE clause filters rows, and combined with LIKE, it filters rows matching a pattern.

  3. Final Answer:

    WHERE with LIKE -> Option C

  4. Quick Check:

    WHERE with LIKE = B [OK]
Hint: LIKE is always used with WHERE to filter text patterns [OK]
Common Mistakes:
  • Confusing LIKE with GROUP BY
  • Using LIKE without WHERE
  • Thinking ORDER BY filters rows
2. Which of the following is the correct syntax to find names starting with 'Jo' in a table called users?
easy
A. SELECT * FROM users WHERE name LIKE '%Jo';
B. SELECT * FROM users WHERE name LIKE 'Jo%';
C. SELECT * FROM users WHERE name LIKE '_Jo%';
D. SELECT * FROM users WHERE name LIKE 'Jo_';

Solution

  1. Step 1: Understand the pattern for names starting with 'Jo'

    Names starting with 'Jo' means 'Jo' followed by any characters, so pattern is 'Jo%'.

  2. Step 2: Match the correct syntax

    SELECT * FROM users WHERE name LIKE 'Jo%'; uses LIKE 'Jo%' which matches names starting with 'Jo'. Others do not match this pattern.

  3. Final Answer:

    SELECT * FROM users WHERE name LIKE 'Jo%'; -> Option B

  4. Quick Check:

    Start with 'Jo' = LIKE 'Jo%' [OK]
Hint: Use % after text to match any ending characters [OK]
Common Mistakes:
  • Using % before text to match start
  • Using _ instead of %
  • Misplacing wildcards
3. Given the table products with a column product_code, what rows will this query return?
SELECT * FROM products WHERE product_code LIKE '_A%';
medium
A. Rows where product_code starts with 'A'
B. Rows where product_code ends with 'A'
C. Rows where product_code contains 'A' anywhere
D. Rows where product_code has 'A' as the second character

Solution

  1. Step 1: Understand the pattern '_A%'

    The underscore (_) matches exactly one character, so the first character can be anything. 'A' is the second character. '%' means any characters after.

  2. Step 2: Interpret the query result

    The query returns rows where the second character of product_code is 'A', regardless of the first character or what follows.

  3. Final Answer:

    Rows where product_code has 'A' as the second character -> Option D

  4. Quick Check:

    _A% means second char is 'A' [OK]
Hint: Use _ for single char, % for any chars [OK]
Common Mistakes:
  • Thinking _ matches zero or multiple characters
  • Confusing position of 'A' in pattern
  • Assuming LIKE matches anywhere without wildcards
4. Identify the error in this SQL query:
SELECT * FROM customers WHERE city LIKE '%York_';

It should find cities ending with 'York' followed by exactly one character.
medium
A. The pattern is correct; no error
B. The underscore should be before 'York' like '_%York'
C. The pattern should be '%York%' to match any after 'York'
D. The underscore should be after 'York' but without % like '%York_'

Solution

  1. Step 1: Analyze the pattern '%York_'

    The % matches any characters before 'York', then 'York' literal, then _ matches exactly one character after.

  2. Step 2: Check if it matches cities ending with 'York' plus one char

    This pattern correctly matches cities ending with 'York' plus exactly one character, so the query is correct.

  3. Final Answer:

    The pattern is correct; no error -> Option A

  4. Quick Check:

    '%York_' matches 'York' + 1 char [OK]
Hint: Check if _ and % are placed as intended [OK]
Common Mistakes:
  • Thinking _ matches multiple characters
  • Confusing placement of % and _
  • Assuming pattern must end with %
5. You want to find all employees whose email ends with '.com' but have exactly one character before '.com'. Which query is correct?
hard
A. SELECT * FROM employees WHERE email LIKE '%_.com';
B. SELECT * FROM employees WHERE email LIKE '%_.com%';
C. SELECT * FROM employees WHERE email LIKE '%.__.com';
D. SELECT * FROM employees WHERE email LIKE '%.com';

Solution

  1. Step 1: Understand the requirement

    The email should end with '.com' and have exactly one character before '.com'.

  2. Step 2: Analyze each pattern

    SELECT * FROM employees WHERE email LIKE '%.com'; matches any email ending with '.com' but no restriction on characters before.
    SELECT * FROM employees WHERE email LIKE '%_.com'; uses '%_.com' meaning any characters, then exactly one character, then '.com' at the end.
    SELECT * FROM employees WHERE email LIKE '%.__.com'; expects two characters before '.com' which is incorrect.
    SELECT * FROM employees WHERE email LIKE '%_.com%'; allows any characters after '.com' which is incorrect for ending.

  3. Final Answer:

    SELECT * FROM employees WHERE email LIKE '%_.com'; -> Option A

  4. Quick Check:

    Use _ before '.com' for exactly one char [OK]
Hint: Use _ before '.com' to match exactly one char [OK]
Common Mistakes:
  • Using % instead of _ for single character
  • Placing % after '.com' allowing extra chars
  • Miscounting characters before '.com'
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard