w3resource

SQL Exercises: Filter records using not in operator

SQL Wildcard & Special Operator: Exercise-3 with Solution

From the following table, write a SQL query to find the details of those salespeople who live in cities other than Paris and 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:

-- Selecting all columns from the 'salesman' table
SELECT *
-- Specifying the table to retrieve data from ('salesman')
FROM salesman
-- Filtering the results based on the condition that 'city' is not in the specified list ('Paris' and 'Rome')
WHERE city NOT IN('Paris','Rome');

or Equivalent:

-- Selecting all columns from the 'salesman' table
SELECT *
-- Specifying the table to retrieve data from ('salesman')
FROM salesman
-- Filtering the results based on the condition that 'city' is not in the list ('Paris', 'Rome')
WHERE NOT city IN ('Paris', 'Rome');

Output of the Query:

salesman_id	name		city		commission
5001		James Hoog	New York	0.15
5005		Pit Alex	London		0.11
5003		Lauson Hen	San Jose	0.12

Code Explanation:

The said SQL query that selects all columns (*) from the table called 'salesman' where the city is not in the list of values ('Paris','Rome').
It will return all rows from the "salesman" table where the city column has a value that is not 'Paris' or 'Rome'.
The NOT IN operator is used here to exclude the specified values in the list and return all other values in the column.

Relational Algebra Expression:

Relational Algebra Expression: Filter records using not in operator.

Relational Algebra Tree:

Relational Algebra Tree: Filter records using not in operator.

Explanation:

Syntax of filter records using not in operator

Visual presentation :

Result of filter records using not in operator

Practice Online


Query Visualization for Sample Solution:

Duration:

Query visualization of Filter records using not in operator - Duration

Rows:

Query visualization of Filter records using not in operator - Rows

Cost:

Query visualization of Filter records using not in operator - Cost

Query Visualization for alternate Sample Solution:

Duration:

Query visualization of Filter records using not in operator - Duration

Rows:

Query visualization of Filter records using not in operator - Rows

Cost:

Query visualization of Filter records using not in operator - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Filter records using in operator.
Next SQL Exercise: Sort records using 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.