w3resource

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 Expression: Using where, like and underscore operators.

Relational Algebra Tree:

Relational Algebra Tree: Using where, like and underscore operators.

Explanation:

Syntax of SQL wildcard and special operator

Visual presentation:

Exercise: SQL wildcard and special operator

Practice Online


Query Visualization:

Duration:

Query visualization of Using where, like and underscore operators - Duration

Rows:

Query visualization of Using where, like and underscore operators - Rows

Cost:

Query visualization of Using where, like and underscore operators - 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.