Q.1 Retrieve the content of the column PRODUCT_CODE and DESCRIPTION
-------------------------------------------------------------------
from PRODUCT_MASTER table and compute 5% and 105% of the field
--------------------------------------------------------------
SELLING_PRICE for each row retrieved. Rename SELLING_PRICE * 0.05 as
--------------------------------------------------------------------
INCREASE_PRICE and sell price * 1.05 as NEW_PRICE.
--------------------------------------------------
SQL> select product_code, description, sell_price * 0.05 as Increase_Price,
2 sell_price * 1.05 as New_Price from product_master;
PRODUC DESCRIPTION INCREASE_PRICE NEW_PRICE
------ -------------------- -------------- ----------
P00001 1.44 FLOPPIES 26.25 551.25
P03453 MONITORS 600 12600
P06734 MOUSE 52.5 1102.5
P07865 1.22 FLOPPIES 26.25 551.25
P07868 KEYBOARDS 157.5 3307.5
P07885 CD DRIVE 262.5 5512.5
P07965 540 HDD 420 8820
P07975 1.44 DRIVE 52.5 1102.5
P08865 1.22 DRIVE 52.5 1102.5
9 rows selected.
Q.2 Retrieve information from SALES_ORDER_DETAILS for records
-------------------------------------------------------------
whose QTY_ORDERED * PRODUCT_RATE > 8000.
----------------------------------------
SQL> select * from sales_order_details where qty_ordered * product_rate > 8000;
ORDER_ PRODUC QTY_ORDERED PRODUCT_RATE
------ ------ ----------- ------------
O19001 P07965 2 8400
O19001 P07885 2 5250
O46865 P07868 3 5250
O46865 P07885 3 3150
O19003 P06734 1 12000
O46866 P07965 1 8400
6 rows selected.
Q.3 Retrieve information from PRODUCT_MASTER table and SALES_ORDER_DETAILS
--------------------------------------------------------------------------
table for product whose ORDER_NO is O19001 and O19008 respectively.
-------------------------------------------------------------------
SQL> select p.product_code, p.description, p.qty_on_hand, p.sell_price,
2 s.order_no, s.qty_ordered, s.product_rate
3 from product_master p, sales_order_details s
4 where p.product_code = s.product_code and
5 (s.order_no = 'O19001' or s.order_no = 'O19008');
PRODUC DESCRIPTION QTY_ON_HAND SELL_PRICE ORDER_ QTY_ORDERED
------ -------------------- ----------- ---------- ------ -----------
PRODUCT_RATE
------------
P00001 1.44 FLOPPIES 100 525 O19008 10
525
P00001 1.44 FLOPPIES 100 525 O19001 4
525
P07885 CD DRIVE 10 5250 O19001 2
5250
PRODUC DESCRIPTION QTY_ON_HAND SELL_PRICE ORDER_ QTY_ORDERED
------ -------------------- ----------- ---------- ------ -----------
PRODUCT_RATE
------------
P07965 540 HDD 10 8400 O19001 2
8400
P07975 1.44 DRIVE 10 1050 O19008 5
1050
Q.4 Retrieve PRODUCT_CODE and DESCRIPTION from PRODUCT_MASTER table,
---------------------------------------------------------------------
SALES_ORDER_DETAILS table for product whose QTY_ORDERED < 3.
------------------------------------------------------------
SQL> select p.product_code, p.description from product_master p,
2 sales_order_details s where p.product_code = s.product_code
3 and s.qty_ordered < 3;
PRODUC DESCRIPTION
------ --------------------
P03453 MONITORS
P06734 MOUSE
P07885 CD DRIVE
P07965 540 HDD
P07965 540 HDD
P07975 1.44 DRIVE
6 rows selected.
Q.5 Delete all records from PRODUCT_MASTER table corresponding to ORDER_NO = 'O46865'.
--------------------------------------------------------------------------------------
SQL> delete from product_master where product_code in
2 (select product_code from sales_order_details where order_no='O46865');
delete from product_master where product_code in
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.SYS_C005455) violated - child record
found
Q.6 Update SALES_ORDER_DETAILS table, change the PRODUCT_RATE of 'P06734' to the
--------------------------------------------------------------------------------
SELLING_PRICE from PRODUCT_MASTER table.
----------------------------------------
SQL> update sales_order_details
2 set product_rate = (select sell_price from product_master
3 where product_master.product_code = sales_order_details.product_code)
4 where sales_order_details.product_code='P06734';
1 row updated.
SQL> select * from sales_order_details;
ORDER_ PRODUC QTY_ORDERED PRODUCT_RATE
------ ------ ----------- ------------
O19001 P00001 4 525
O19001 P07965 2 8400
O19001 P07885 2 5250
O19002 P00001 10 525
O46865 P07868 3 5250
O46865 P07885 3 3150
O46865 P00001 10 525
O46865 P03453 4 1050
O19003 P03453 2 1050
O19003 P06734 1 1050
O46866 P07965 1 8400
ORDER_ PRODUC QTY_ORDERED PRODUCT_RATE
------ ------ ----------- ------------
O46866 P07975 1 1050
O19008 P00001 10 525
O19008 P07975 5 1050
14 rows selected.
SQL> select * from product_master;
PRODUC DESCRIPTION QTY_ON_HAND SELL_PRICE
------ -------------------- ----------- ----------
P00001 1.44 FLOPPIES 100 525
P03453 MONITORS 10 12000
P06734 MOUSE 20 1050
P07865 1.22 FLOPPIES 100 525
P07868 KEYBOARDS 10 3150
P07885 CD DRIVE 10 5250
P07965 540 HDD 10 8400
P07975 1.44 DRIVE 10 1050
P08865 1.22 DRIVE 2 1050
9 rows selected.
|
