SQL Exercises: Display all the data from the item_mast, including all the data for each item's producer company

SQL JOINS: Exercise-21 with Solution

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.

Sample table: company_mast

Sample table: item_mast

Sample Solution:

SELECT *
   FROM item_mast 
   INNER JOIN company_mast
   ON item_mast.pro_com= company_mast.com_id; 

Output of the Query:

pro_id	pro_name		pro_price	pro_com	com_id	com_name
101	Mother Board		3200.00		15	15	Asus
102	Key Board		450.00		16	16	Frontech
103	ZIP drive		250.00		14	14	Zebronics
104	Speaker			550.00		16	16	Frontech
105	Monitor			5000.00		11	11	Samsung
106	DVD drive		900.00		12	12	iBall
107	CD drive		800.00		12	12	iBall
108	Printer			2600.00		13	13	Epsion
109	Refill cartridge	350.00		13	13	Epsion
110	Mouse			250.00		12	12	iBall

Relational Algebra Expression:

Relational Algebra Tree:

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 must belong a city which is not the same as his customer and the customers should have an own grade.
Next: Write a SQL query to display the item name, price, and company name of all the products.