This page describes the scenario for querying using table joins and creates an 'inventory' table in the store database
The store has two tables that contain details of products in the store. The 'product' table stores details, including selling price, of products on sale in the store. This includes some "in house" products (products not ordered from other manufacturers but manufactured by the store itself). The 'inventory' table stores details, including cost price, of products ordered from the manufacturers i.e. the stock of products available in the store (that may be on sale or not). The inventory table does not contain a listing for in house products.
The store manager wants to compare the selling price and cost price of different products to analyse the profit made in order to make suitable modifications to the pricing structure of products in the store
Data from the two tables 'product' and 'inventory' can be queried using different types of joins, based on the information that needs to be retrieved. The joins mentioned above, are used to query such information.
Create the 'inventory' table containing details of products that are procured from the manufacturer. It also contains products that may not be up for sale yet in the store.
The 'inventory' table is created and data is populated into it.