Sharon Thomas

This blog was started in loving memory of Christ Kengeri Campus,Bangalore and now dedicated to all my students ...

Saturday, December 11, 2010

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*’;

No comments:

Post a Comment