SQL Exercise: Customers from a different city than the salesmen
20. Cartesian Product with Non Matching Cities
Write a SQL statement to make a Cartesian product between salesman and customer i.e. each salesman will appear for all customers and vice versa for those salesmen who must belong to a city which is not the same as his customer and the customers should have their own grade.
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 table: customer
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
3008 | Julian Green | London | 300 | 5002
3004 | Fabian Johnson | Paris | 300 | 5006
3009 | Geoff Cameron | Berlin | 100 | 5003
3003 | Jozy Altidor | Moscow | 200 | 5007
3001 | Brad Guzan | London | | 5005
Sample Solution:
-- Selecting all columns from the Cartesian product of the 'salesman' and 'customer' tables
SELECT *
-- Specifying the tables to perform the cross join ('salesman' as 'a' and 'customer' as 'b')
FROM salesman a
CROSS JOIN customer b
-- Filtering the results based on three conditions: 'city' in 'salesman' is not null, 'grade' in 'customer' is not null, and 'city' in 'salesman' is not equal to 'city' in 'customer'
WHERE a.city IS NOT NULL
AND b.grade IS NOT NULL
AND a.city <> b.city;
Output of the Query:
salesman_id name city commission customer_id cust_name city grade salesman_id 5002 Nail Knite Paris 0.13 3002 Nick Rimando New York 100 5001 5005 Pit Alex London 0.11 3002 Nick Rimando New York 100 5001 5006 Mc Lyon Paris 0.14 3002 Nick Rimando New York 100 5001 5007 Paul Adam Rome 0.13 3002 Nick Rimando New York 100 5001 5003 Lauson Hen San Jose0.12 3002 Nick Rimando New York 100 5001 5002 Nail Knite Paris 0.13 3007 Brad Davis New York 200 5001 5005 Pit Alex London 0.11 3007 Brad Davis New York 200 5001 5006 Mc Lyon Paris 0.14 3007 Brad Davis New York 200 5001 5007 Paul Adam Rome 0.13 3007 Brad Davis New York 200 5001 5003 Lauson Hen San Jose0.12 3007 Brad Davis New York 200 5001 5001 James Hoog New York0.15 3005 Graham Zusi California 200 5002 5002 Nail Knite Paris 0.13 3005 Graham Zusi California 200 5002 5005 Pit Alex London 0.11 3005 Graham Zusi California 200 5002 5006 Mc Lyon Paris 0.14 3005 Graham Zusi California 200 5002 5007 Paul Adam Rome 0.13 3005 Graham Zusi California 200 5002 5003 Lauson Hen San Jose0.12 3005 Graham Zusi California 200 5002 5001 James Hoog New York0.15 3008 Julian Green London 300 5002 5002 Nail Knite Paris 0.13 3008 Julian Green London 300 5002 5006 Mc Lyon Paris 0.14 3008 Julian Green London 300 5002 5007 Paul Adam Rome 0.13 3008 Julian Green London 300 5002 5003 Lauson Hen San Jose0.12 3008 Julian Green London 300 5002 5001 James Hoog New York0.15 3004 Fabian Johnson Paris 300 5006 5005 Pit Alex London 0.11 3004 Fabian Johnson Paris 300 5006 5007 Paul Adam Rome 0.13 3004 Fabian Johnson Paris 300 5006 5003 Lauson Hen San Jose0.12 3004 Fabian Johnson Paris 300 5006 5001 James Hoog New York0.15 3009 Geoff Cameron Berlin 100 5003 5002 Nail Knite Paris 0.13 3009 Geoff Cameron Berlin 100 5003 5005 Pit Alex London 0.11 3009 Geoff Cameron Berlin 100 5003 5006 Mc Lyon Paris 0.14 3009 Geoff Cameron Berlin 100 5003 5007 Paul Adam Rome 0.13 3009 Geoff Cameron Berlin 100 5003 5003 Lauson Hen San Jose0.12 3009 Geoff Cameron Berlin 100 5003 5001 James Hoog New York0.15 3003 Jozy Altidor Moscow 200 5007 5002 Nail Knite Paris 0.13 3003 Jozy Altidor Moscow 200 5007 5005 Pit Alex London 0.11 3003 Jozy Altidor Moscow 200 5007 5006 Mc Lyon Paris 0.14 3003 Jozy Altidor Moscow 200 5007 5007 Paul Adam Rome 0.13 3003 Jozy Altidor Moscow 200 5007 5003 Lauson Hen San Jose0.12 3003 Jozy Altidor Moscow 200 5007
Explanation:
This SQL query is selecting all columns from two tables, salesman and customer, and joining them using a cross join. The query also has several conditions in the WHERE clause:
The city column in the salesman table is not null
The grade column in the customer table is not null
The city column in the salesman table is not equal to the city column in the customer table.
This query is likely returning a large set of data containing all combinations of rows from the two tables, filtered by the conditions in the WHERE clause. The result set will contain all rows where the salesman and customer are from different city.
Visual Explanation:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
For more Practice: Solve these Related Problems:
- Write a SQL query to produce a CROSS JOIN between salesman and customer and filter out rows where the salesman’s city equals the customer’s city, while ensuring customer.grade is not NULL.
- Write a SQL query to list every combination of salesman and customer where the cities differ and the customer has a defined grade.
- Write a SQL query to output a Cartesian product using a WHERE clause that requires salesman.city <> customer.city and customer.grade IS NOT NULL.
- Write a SQL query to create a result set of all salesman and customer pairs with non matching cities, including only customers with a valid grade.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Salesmen belongs to a city, customers have a grade
Next SQL Exercise: Display each item producer company.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.