w3resource

SQL Exercises: Make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come

SQL JOINS: Exercise-7 with Solution

Write a SQL statement to make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come.

Sample table: orders


Sample table: customer


Sample table : salesman


Sample Solution:

SELECT * 
FROM orders 
NATURAL JOIN customer  
NATURAL JOIN salesman;

Output of the Query:

salesman_id	city		customer_id	ord_no	purch_amt	ord_date	cust_name	grade	name		commission
5005		London		3001		70009	270.65		2012-09-10	Brad Guzan		Pit Alex	0.11
5001		New York	3002		70002	65.26		2012-10-05	Nick Rimando	100	James Hoog	0.15
5001		New York	3007		70005	2400.60		2012-07-27	Brad Davis	200	James Hoog	0.15
5001		New York	3002		70008	5760.00		2012-09-10	Nick Rimando	100	James Hoog	0.15
5006		Paris		3004		70010	1983.43		2012-10-10	Fabian Johnson	300	Mc Lyon		0.14
5001		New York	3002		70013	3045.60		2012-04-25	Nick Rimando	100	James Hoog	0.15

Relational Algebra Expression:

Relational Algebra Expression: Make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come.

Relational Algebra Tree:

Relational Algebra Tree: Make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come.

Explanation:

Syntax of join within the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come

Pictorial presentation:

Result of join within the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come
Result of join within the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come

Practice Online


Query Visualization:

Duration:

Query visualization of Make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come - Duration

Rows:

Query visualization of Make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come - Rows

Cost:

Query visualization of Make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come - Cost

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

Previous: Write a SQL statement to find the details of a order i.e. order number, order date, amount of order, which customer gives the order and which salesman works for that customer and commission rate he gets for an order.
Next: From the following tables write a SQL query to display the cust_name, customer city, grade, Salesman, salesman city. The result should be ordered by ascending on customer_id.

What is the difficulty level of this exercise?