SQL: Practice Exercises for LIKE Condition
If you want to test your skills using the SQL LIKE condition, try some of our practice exercises.
These practice exercises allow you to test your skills with the LIKE condition. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer.
Get started!
Practice Exercise #1:
Based on the employees table populated with the following data, find all records whose last_name contains the letter "h".
CREATE TABLE employees ( employee_number int NOT NULL, last_name char(50) NOT NULL, first_name char(50) NOT NULL, salary int, dept_id int, CONSTRAINT employees_pk PRIMARY KEY (employee_number) ); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1001, 'Smith', 'John', 62000, 500); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1002, 'Anderson', 'Jane', 57500, 500); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1003, 'Everest', 'Brad', 71000, 501); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1004, 'Horvath', 'Jack', 42000, 501);
Solution for Practice Exercise #1:
The following SELECT statement uses the LIKE condition to return the records whose last_name contains the letter "h".
Try ItSELECT * FROM employees WHERE last_name LIKE '%h%';
It would return the following result set:
employee_number | last_name | first_name | salary | dept_id |
---|---|---|---|---|
1001 | Smith | John | 62000 | 500 |
1004 | Horvath | Jack | 42000 | 501 |
Practice Exercise #2:
Based on the employees table populated with the following data, find all records whose first_name starts with the letter "B".
CREATE TABLE employees ( employee_number int NOT NULL, last_name char(50) NOT NULL, first_name char(50) NOT NULL, salary int, dept_id int, CONSTRAINT employees_pk PRIMARY KEY (employee_number) ); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1001, 'Smith', 'John', 62000, 500); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1002, 'Anderson', 'Jane', 57500, 500); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1003, 'Everest', 'Brad', 71000, 501); INSERT INTO employees (employee_number, last_name, first_name, salary, dept_id) VALUES (1004, 'Horvath', 'Jack', 42000, 501);
Solution for Practice Exercise #2:
The following SELECT statement would use the LIKE condition to return the records whose first_name start the letter "B".
Try ItSELECT * FROM employees WHERE first_name LIKE 'B%';
It would return the following result set:
employee_number | last_name | first_name | salary | dept_id |
---|---|---|---|---|
1003 | Everest | Brad | 71000 | 501 |
Practice Exercise #3:
Based on the customers table populated with the following data, find all customers whose first_name is 4 characters long and starts with 'J'.
CREATE TABLE customers ( customer_id int NOT NULL, last_name char(50) NOT NULL, first_name char(50) NOT NULL, favorite_website char(50), CONSTRAINT customers_pk PRIMARY KEY (customer_id) ); INSERT INTO customers (customer_id, last_name, first_name, favorite_website) VALUES (4000, 'Jackson', 'Joe', 'techonthenet.com'); INSERT INTO customers (customer_id, last_name, first_name, favorite_website) VALUES (5000, 'Smith', 'Jane', 'digminecraft.com'); INSERT INTO customers (customer_id, last_name, first_name, favorite_website) VALUES (6000, 'Ferguson', 'Samantha', 'bigactivities.com'); INSERT INTO customers (customer_id, last_name, first_name, favorite_website) VALUES (7000, 'Reynolds', 'Allen', 'checkyourmath.com'); INSERT INTO customers (customer_id, last_name, first_name, favorite_website) VALUES (8000, 'Anderson', 'Paige', NULL); INSERT INTO customers (customer_id, last_name, first_name, favorite_website) VALUES (9000, 'Johnson', 'Derek', 'techonthenet.com');
Solution for Practice Exercise #3:
The following SELECT statement would use the LIKE condition to return the records whose supplier_id is 4 digits and starts with "500".
Try ItSELECT * FROM customers WHERE first_name LIKE 'J___';
It would return the following result set:
customer_id | last_name | first_name | favorite_website |
---|---|---|---|
5000 | Smith | Jane | digminecraft.com |
Advertisements