SQL Exercises: Filter records using in operator

Last update on February 10 2025 07:54:54 (UTC/GMT +8 hours)

From the following table, write a SQL query to find the details of the salespeople who come from either 'Paris' or 'Rome'. 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 'city' column has the value 'Paris' or 'Rome'.
WHERE city = 'Paris' OR city = 'Rome';

Output of the Query:

salesman_id	name		city	commission
5002		Nail Knite	Paris	0.13
5006		Mc Lyon		Paris	0.14
5007		Paul Adam	Rome	0.13

Code Explanation:

The above SQL query that selects all columns (*) from the table called 'salesman' where the city is in the list of values ('Paris','Rome').
It will return all rows from the 'salesman' table where the city column has a value of 'Paris' or 'Rome'.
The IN operator is used here instead of multiple OR conditions to check for multiple values in the same column.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

Visual presentation:

Practice Online

For more Practice: Solve these Related Problems:

• Write a SQL query to find the details of salespeople who live in 'San Jose' or 'London'. Return salesman_id, name, city, commission.
• Write a SQL query to retrieve the details of salespeople whose city ends with the letter 'e'. Return salesman_id, name, city, commission.
• Write a SQL query to find the details of salespeople who do not belong to 'Paris' or 'Rome'. Return salesman_id, name, city, commission.
• Write a SQL query to list all salespeople whose city contains the letter 'o'. Return salesman_id, name, city, commission.

Contribute your code and comments through Disqus.

Previous SQL Exercise: Filter records using where clause and or operator. 
Next SQL Exercise: Filter records using not in operator.