w3resource
SQL exercises

SQL: Filter records using not in operator

SQL Wildcard & Special Operator : Exercise-3 with Solution

Write a query to produce a list of salesman_id, name, city and commision of each salesman who live in cities other than Paris and Rome.

Sample table: salesman


Sample Solution:

SELECT * 
FROM salesman 
WHERE city NOT IN('Paris','Rome'); 

or Equivalent:

SELECT * 
FROM salesman 
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

Explanation:

Syntax of filter records using not in operator

Pictorial presentation :

Result of filter records using not in operator

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

 

Practice Online


Contribute your code and comments through Disqus.

Previous: Write a query to filter those salesmen with all information who comes from any of the cities Paris and Rome.
Next: Write a query to sort out those customers with all information whose ID value is within any of 3007, 3008 and 3009.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming