SQL Exercise: All customers will be served by a salesman from a city
18. Cartesian Product with City Flag
Write a SQL statement to create a Cartesian product between salesperson and customer, i.e. each salesperson will appear for all customers and vice versa for that salesperson who belongs to that city.
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 the condition that the 'city' in 'salesman' is not null
WHERE a.city IS NOT NULL;
Output of the Query:
salesman_id name city commission customer_id cust_name city grade salesman_id 5001 James Hoog New York 0.15 3002 Nick Rimando New York 100 5001 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 Jose 0.12 3002 Nick Rimando New York 100 5001 5001 James Hoog New York 0.15 3007 Brad Davis New York 200 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 Jose 0.12 3007 Brad Davis New York 200 5001 5001 James Hoog New York 0.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 Jose 0.12 3005 Graham Zusi California 200 5002 5001 James Hoog New York 0.15 3008 Julian Green London 300 5002 5002 Nail Knite Paris 0.13 3008 Julian Green London 300 5002 5005 Pit Alex London 0.11 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 Jose 0.12 3008 Julian Green London 300 5002 5001 James Hoog New York 0.15 3004 Fabian Johnson Paris 300 5006 5002 Nail Knite Paris 0.13 3004 Fabian Johnson Paris 300 5006 5005 Pit Alex London 0.11 3004 Fabian Johnson Paris 300 5006 5006 Mc Lyon Paris 0.14 3004 Fabian Johnson Paris 300 5006 5007 Paul Adam Rome 0.13 3004 Fabian Johnson Paris 300 5006 5003 Lauson Hen San Jose 0.12 3004 Fabian Johnson Paris 300 5006 5001 James Hoog New York 0.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 Jose 0.12 3009 Geoff Cameron Berlin 100 5003 5001 James Hoog New York 0.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 Jose 0.12 3003 Jozy Altidor Moscow 200 5007 5001 James Hoog New York 0.15 3001 Brad Guzan London 5005 5002 Nail Knite Paris 0.13 3001 Brad Guzan London 5005 5005 Pit Alex London 0.11 3001 Brad Guzan London 5005 5006 Mc Lyon Paris 0.14 3001 Brad Guzan London 5005 5007 Paul Adam Rome 0.13 3001 Brad Guzan London 5005 5003 Lauson Hen San Jose 0.12 3001 Brad Guzan London 5005
Explanation:
The said SQL query selects all columns (*) from the salesman table alias a and the customer table alias b, and performs a cross join between the two tables. The query also includes a WHERE clause that filters the results to only include rows from the salesman table where the 'city' column is not null.
This means that the query will return all combinations of rows from the salesman table where the 'city' column is not null and the customer table, effectively creating a Cartesian product of the two tables.
Visual Explanation:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
For more Practice: Solve these Related Problems:
- Write a SQL query to create a Cartesian product between salesman and customer and include a computed column that indicates 'Yes' if the salesperson’s city matches the customer’s city, otherwise 'No'.
- Write a SQL query to perform a CROSS JOIN between the two tables and add a CASE expression to flag matching cities.
- Write a SQL query to list all combinations of salesman and customer, appending a column that returns the string 'Match' when salesman.city equals customer.city.
- Write a SQL query to output a Cartesian product and order the result by the computed flag indicating same city.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Salesman will appear for all customer and vice versa.
Next SQL Exercise: Salesmen belongs to a city, customers have a grade.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.