SQL SESSION 4

Session 4:  Product Master

Product_no	Description	Profit_percent	UOM	Qty_on_hand	Reorder_lvl	Sell_price	Cost_ Price
P00001	1.44 Floppies	5	Piece	100	20	525	500
P03453	Monitors	6	Piece	10	3	12000	11280
P06734	Mouse	5	Piece	20	5	1050	1000
P07865	1.22 Floppies	5	Piece	100	20	525	500
P07868	Keyboard	2	Piece	10	3	3150	3050
P07885	CD Drive	2.5	Piece	10	3	5250	5100
P07965	540 HDD	4	Piece	10	3	8400	8000
P07975	1.44 Drive	5	Piece	10	3	1050	1000
P08865	1.22 Drive	5	Piece	2	3	1050	1000

Problem in SQL system command continues……

1. List products in sorted order of their description.

2. Find the products whose cost_price is less than 1500.

3. Find the average of prices of all products.

4. Divide the cost of product ‘540 HDD’ by difference between its price &100

5. List the product_no, description, sell_price of products whose description begins with letter ‘M’.

Solutions:

1. List products in sorted order of their description.

SELECT *

FROM Product_Master

ORDER BY Description;

NOTE: IN ORDER TO LIST PRODUCTS IN SORTED ORDER OF THEIR DESCRIPTION IN DESCENDING FORM

SELECT *

FROM Product_Master

ORDER BY Description DESC;

2. Find the products whose cost_price is less than 1500.

                SELECT *

                FROM Product_Master

                WHERE Cost_Price<1500;

3. Find the average of prices of all products.

SELECT AVG(Cost_price)

FROM Product_Master;

4. Divide the cost of product ‘540 HDD’ by difference between its price &100

                UPDATE Product_Master

SET Cost_price = (Cost_price/(Cost_price-100))

WHERE Description='540 HDD';

5. List the product_no, description, sell_price of products whose description begins with letter ‘M’.

SELECT Product_No, Description, Sell_Price

FROM Product_Master

WHERE Description LIKE ‘M*’;