SQL Exercises: Prepare a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for those salesmen who must belong a city which is not the same as his customer and the customers should have an own grade
SQL JOINS: Exercise-20 with Solution
Write a SQL statement to make a Cartesian product between salesman and customer i.e. each salesman will appear for all customers and vice versa for those salesmen who must belong to a city which is not the same as his customer and the customers should have their own 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
AND a.city<>b.city;
Output of the Query:
salesman_id name city commission customer_id cust_name city grade salesman_id 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 Jose0.12 3002 Nick Rimando New York 100 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 Jose0.12 3007 Brad Davis New York 200 5001 5001 James Hoog New York0.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 Jose0.12 3005 Graham Zusi California 200 5002 5001 James Hoog New York0.15 3008 Julian Green London 300 5002 5002 Nail Knite Paris 0.13 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 Jose0.12 3008 Julian Green London 300 5002 5001 James Hoog New York0.15 3004 Fabian Johnson Paris 300 5006 5005 Pit Alex London 0.11 3004 Fabian Johnson Paris 300 5006 5007 Paul Adam Rome 0.13 3004 Fabian Johnson Paris 300 5006 5003 Lauson Hen San Jose0.12 3004 Fabian Johnson Paris 300 5006 5001 James Hoog New York0.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 Jose0.12 3009 Geoff Cameron Berlin 100 5003 5001 James Hoog New York0.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 Jose0.12 3003 Jozy Altidor Moscow 200 5007
Explanation:
Pictorial presentation:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
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 for those salesmen who belongs to a city and the customers who must have a grade
Next: From the following tables write a SQL query to select all rows from both participating tables as long as there is a match between pro_com and com_id.
Test your Programming skills with w3resource's quiz.
What is the difficulty level of this exercise?
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework