w3resource

SQL Exercises: Customers who differ from the grade in the city Paris

SQL SUBQUERY: Exercise-29 with Solution

29. From the following tables write a SQL query to find those customers whose grades are different from those living in Paris. Return customer_id, cust_name, city, grade and salesman_id.

Sample table : Customer
customer_id  cust_name     city        grade       salesman_id
-----------  ------------  ----------  ----------  -----------
3002         Nick Rimando  New York    100         5001
3005         Graham Zusi   California  200         5002
3001         Brad Guzan    London      100         5005
3004         Fabian Johns  Paris       300         5006
3007         Brad Davis    New York    200         5001
3009         Geoff Camero  Berlin      100         5003
3008         Julian Green  London      300         5002
3003         Jozy Altidor  Moncow      200         5007

Sample Solution:

-- Selecting all columns from the 'customer' table
SELECT *
-- Specifying the table to retrieve data from ('customer')
FROM customer 
-- Filtering the results based on the condition that 'grade' is not in the set of 'grade' values returned by a subquery
WHERE grade NOT IN
   -- Subquery: Selecting 'grade' values from the 'customer' table where 'city' is 'Paris'
   (SELECT grade
	FROM customer
	WHERE city='Paris');

Output of the Query:

customer_id	cust_name		city		grade	salesman_id
3002		Nick Rimando		New York	100	5001
3007		Brad Davis		New York	200	5001
3005		Graham Zusi		California	200	5002
3009		Geoff Cameron		Berlin		100	5003
3003		Jozy Altidor		Moscow		200	5007

Explanation:

The said query is selecting all columns and rows from the 'customer' table where the grade is not present in the subquery. The subquery is selecting the grade from the 'customer' table where the city is equal to 'Paris'. So the main query is getting the rows of all customers whose grade is not present in Paris.

Visual Explanation:

SQL Subqueries Inventory Exercises: Find all those customers whose grade are not as the grade, belongs to the city Paris.

Practice Online


Sample Database: inventory

HR database model

Query Visualization:

Duration:

Query visualization of Find all those customers whose grade are not as the grade, belongs to the city Paris - Duration

Rows:

Query visualization of Find all those customers whose grade are not as the grade, belongs to the city Paris - Rows

Cost:

Query visualization of Find all those customers whose grade are not as the grade, belongs to the city Paris - Cost

Contribute your code and comments through Disqus.

Previous SQL Exercise: Customers whose grade differs from a London customer.
Next SQL Exercise: Different grades than other Dallas customers.

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.