w3resource
SQL exercises

SQL Exercises: Prepare a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for that salesman who belongs to a city

SQL JOINS: Exercise-18 with Solution

Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for that salesman who belongs to a city.

Sample table: salesman


Sample table: customer

Sample Solution:

SELECT * 
FROM salesman a 
CROSS JOIN customer b 
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:

Syntax of a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for those customer who belongs to a city

Pictorial presentation:

Result of a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for those customer who belongs to a city

Query Visualization:

Duration:

Query visualization of Prepare a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for that salesman who belongs to a city - Duration

Rows:

Query visualization of Prepare a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for that salesman who belongs to a city - Rows

Cost:

Query visualization of Prepare a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for that salesman who belongs to a city - Cost

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

Practice Online


Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa.
Next: Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for those salesmen who belongs to a city and the customers who must have a grade.

What is the difficulty level of this exercise?



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