SQL Exercise: Display the item name, price, and company name
22. Product & Company Details
Write a SQL query to display the item name, price, and company name of all the products.
Sample table: company_mast
COM_ID COM_NAME
------ -------------
11 Samsung
12 iBall
13 Epsion
14 Zebronics
15 Asus
16 Frontech
Sample table: item_mast
PRO_ID PRO_NAME PRO_PRICE PRO_COM
------- ------------------------- -------------- ----------
101 Mother Board 3200.00 15
102 Key Board 450.00 16
103 ZIP drive 250.00 14
104 Speaker 550.00 16
105 Monitor 5000.00 11
106 DVD drive 900.00 12
107 CD drive 800.00 12
108 Printer 2600.00 13
109 Refill cartridge 350.00 13
110 Mouse 250.00 12
Sample Solution:
-- Selecting specific columns from the result of an inner join between 'item_mast' and 'company_mast' tables
SELECT item_mast.pro_name, pro_price, company_mast.com_name
-- Specifying the tables to retrieve data from ('item_mast' as the left table and 'company_mast' as the right table)
FROM item_mast
-- Performing an inner join based on the equality of 'pro_com' in 'item_mast' and 'com_id' in 'company_mast'
INNER JOIN company_mast
ON item_mast.pro_com = company_mast.com_id;
Output of the Query:
pro_name pro_price com_name Mother Board 3200.00 Asus Key Board 450.00 Frontech ZIP drive 250.00 Zebronics Speaker 550.00 Frontech Monitor 5000.00 Samsung DVD drive 900.00 iBall CD drive 800.00 iBall Printer 2600.00 Epsion Refill cartridge 350.00 Epsion Mouse 250.00 iBall
Explanation:
The said SQL query that retrieves specific columns from the table item_mast and company_mast and the INNER JOIN clause to combine the data based on the join condition specified in the ON clause.
The selected columns are 'pro_name' from item_mast, 'pro_price' from item_mast and 'com_name' from company_mast. The join condition is that the 'pro_com' column in the item_mast table matches the 'com_id' column in the 'company_mast' table.
The result of the query is a new table that contains the specified columns from both item_mast and company_mast, with rows that have matching values in the specified columns.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
For more Practice: Solve these Related Problems:
- Write a SQL query to join item_mast and company_mast on com_id = pro_com, and select the product name, product price, and company name.
- Write a SQL query to display each product’s name and price along with its associated company name using an INNER JOIN.
- Write a SQL query to list product details by retrieving pro_name, pro_price, and com_name from the joined tables.
- Write a SQL query to output the item name, price, and corresponding company name using a join condition between the two tables.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Display each item producer company.
Next SQL Exercise: Display the average price of items of each company.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.