w3resource

SQL Exercises: Filter records using in operator


2. Salespeople in Paris or Rome

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 Expression: Filter records using in operator.

Relational Algebra Tree:

Relational Algebra Tree: Filter records using in operator.

Explanation:

Syntax of filter records using in operator

Visual presentation:

Result of filter records using in operator

Go to:


PREV : Salespeople from Paris or Rome. 
NEXT : Salespeople Not in Paris or Rome.


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.

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.