SQL Exercises: Filtering records with where clause and like operator

Last update on December 20 2024 11:36:47 (UTC/GMT +8 hours)

From the following table, write a SQL query to find the details of the customers whose names end with the letter 'n'. Return customer_id, cust_name, city, grade, salesman_id.

Sample table: customer

 customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
        3007 | Brad Davis     | New York   |   200 |        5001
        3005 | Graham Zusi    | California |   200 |        5002
        3008 | Julian Green   | London     |   300 |        5002
        3004 | Fabian Johnson | Paris      |   300 |        5006
        3009 | Geoff Cameron  | Berlin     |   100 |        5003
        3003 | Jozy Altidor   | Moscow     |   200 |        5007
        3001 | Brad Guzan     | London     |       |        5005

Sample Solution:

-- This query selects all columns from the 'customer' table.
SELECT *
-- Specifies the table from which to retrieve the data (in this case, 'customer').
FROM customer
-- Filters the rows to only include those where the 'cust_name' column contains the letter 'n' at the end.
WHERE cust_name LIKE '%n';

Output of the Query:

customer_id	cust_name		city	grade	salesman_id
3008		Julian Green		London	300	5002
3004		Fabian Johnson		Paris	300	5006
3009		Geoff Cameron		Berlin	100	5003
3001		Brad Guzan		London		5005

Code Explanation:

The said SQL query selects all columns (*) from the 'customer' table where the value of the "cust_name" column ends with the letter 'n' .
The '%' is a wildcard character in SQL, which can match any string of any length (including an empty string).
The 'LIKE' operator is used to search for a specific pattern in a column.

Relational Algebra Expression: