SQL Exercise: Salesman will appear for all customer and vice versa
SQL JOINS: Exercise-17 with Solution
Write a SQL query to combine each row of the salesman table with each row of the customer table.
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;
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 is performing a CROSS JOIN (also known as a Cartesian product) between the salesman table aliased as a and the customer table aliased as b.
The result of this query will be a new table that contains every possible combination of rows from the salesman table and the customer table. The "SELECT *" statement is selecting all columns from the resulting table.
Visual Explanation:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Customer who is neither in the list nor have a grade.
Next SQL Exercise: All customers will be served by a salesman from a city.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics