w3resource

SQL Exercises: Filter records using in operator

SQL Wildcard & Special Operator: Exercise-2 with Solution

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

Practice Online


Query Visualization:

Duration:

Query visualization of Filter records using in operator - Duration

Rows:

Query visualization of Filter records using in operator - Rows

Cost:

Query visualization of Filter records using in operator - Cost

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.

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.