SQL Exercise: All customers will be served by a salesman from a city
SQL JOINS: Exercise-18 with Solution
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
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:
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:
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.
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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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