SQL SESSION 3

Session 3 : 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

Problems:-

1. Find the products with descriptions ‘1.44 Drive’ and ‘1.22 Drive’.

2. List the various products available from the product_master table.

3. Find the products whose selling_price is greater than 2000 and less than or equal to 5000.

4. Find the products whose selling price is more than 1500 and also find the new selling price as    

    New_selling_price) as original selling price*15. – 2^ND HALF NOT POSSIBLE

5. Rename the new column in the above query as new_selling_price. – NOT POSSIBLE

Solution:

First, prepare the table with all details

1. Find the products with descriptions ‘1.44 Drive’ and ‘1.22 Drive’.

SELECT Product_No, Description

FROM Product_Master

WHERE Description IN (‘1.44 Drive’,’1.22 Drive’);

2. List the various products available from the product_master table.

                SELECT Product_No, Description

                From Product_Master;

3. Find the products whose selling_price is greater than 2000 and less than or equal to 5000.

                SELECT Product_No, Description, Sell_Price

                From Product_Master

                WHERE Sell_Price>2000 AND Sell_Price<=5000;

4. Find the products whose selling price is more than 1500 and also find the new selling price as   

    New_selling_price) as original selling price*15.

                SELECT Product_No, Description, Sell_Price

                FROM Product_Master

                WHERE Sell_Price>1500;

                ALTER TABLE Product_Master

                ADD COLUMN New_Sell_Price Number NOT NULL;

               

                UPDATE Product_Master

                SET New_Sell_Price=(Sell_Price*.15);

5. Rename the new column in the above query as new_selling_price.

                NOT POSSIBLE IN SQL