SQL Exercises: Using where, like and underscore operators

SQL Wildcard & Special Operator: Exercise-11 with Solution

From the following table, write a SQL query to find the details of those salespeople whose names begin with ‘N’ and the fourth character is 'l'. Rests may be any character. Return salesman_id, name, city, commission.

Sample table: salesman

 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12

Sample Solution:

-- This query selects all columns from the 'salesman' table.
SELECT *
-- Specifies the table from which to retrieve the data (in this case, 'salesman').
FROM salesman
-- Filters the rows to only include those where the 'name' column:
-- - Starts with the letter 'N'.
-- - Is followed by any two characters represented by '__'.
-- - Is followed by any sequence of characters represented by '%'.
WHERE name LIKE 'N__l%';

Output of the Query:

salesman_id	name		city	commission
5002		Nail Knite	Paris	0.13

Code Explanation:

The said SQL query selects all columns (*) from the 'salesman' table where the value of the "name" column starts with letter 'N', have 3 characters after that, followed by 'l' and any number of characters after that.
The '_' is a wildcard character in SQL, which can match any single character. The 'LIKE' operator is used to find a specific pattern of data in a column that matches a specific pattern of data.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

Visual presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Filtering records with where clause and like operator.
Next SQL Exercise: Using where clause like, underscore, escape operators.