w3resource

SQL Exercise: Salesmen belongs to a city, customers have a grade

SQL JOINS: Exercise-19 with Solution

Write a SQL statement to create a Cartesian product between salesperson and customer, i.e. each salesperson will appear for every customer and vice versa for those salesmen who belong to a city and customers who require a grade.

Sample table: salesman


Sample table: customer


Sample Solution:

SELECT * 
FROM salesman a 
CROSS JOIN  customer b 
WHERE a.city IS NOT NULL 
AND b.grade 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

Explanation:

The said SQL query is selecting all columns (*) from the table salesman as table a and the table customer as table b. It is then performing a CROSS JOIN, which combines every row from table 'a' with every row from table 'b'. The query is also filtering the results by only including rows where the 'city' column in table 'a' is not null and the 'grade' column in table 'b' is not null. Essentially this query will give the cartesian product of salesman and customer table where city and grade are not null.

Visual Explanation:

Result to 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

Practice Online


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 those salesmen who belongs to a city and the customers who must have a grade - 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 those salesmen who belongs to a city and the customers who must have a grade - 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 those salesmen who belongs to a city and the customers who must have a grade - Cost

Note: In certain instances not null is removed in table structure, so results may vary and the pictorial represetation above is based on such a table for the purpose of explanation only.

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

Previous SQL Exercise: All customers will be served by a salesman from a city.
Next SQL Exercise: Customers from a different city than the salesmen.

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.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

Ref: https://bit.ly/3AfYwZI

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook