| |
 |
|
| |
SQL Tutorial:
(Based on M.G. University - MSc Computer Science) |
|
| |
|
|
| |
|
|
| |
|
Table - I ( PRODUCT_MASTER ) |
Data for Product Master |
PRODUCT_CODE |
DESCRIPTION |
QTY_ON_HAND |
SELLING_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 |
|
Table - II ( SALES_ORDER_DETAILS ) |
Data for Sale_Order |
ORDER_NO |
PRODUCT_CODE |
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 |
12000 |
O46866 |
P07965 |
1 |
8400 |
O46866 |
P07975 |
1 |
1050 |
O19008 |
P00001 |
10 |
525 |
O19008 |
P07975 |
5 |
1050 |
|
Write SQL commands for the following: |
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.
|
2. |
Retrieve information from SALES_ORDER_DETAILS for records whose QTY_ORDERED * PRODUCT_RATE > 8000.
|
3. |
Retrieve information from PRODUCT_MASTER table and SALES_ORDER_DETAILS table for product whose
ORDER_NO is O19001 and O19008 respectively.
|
4. |
Retrieve PRODUCT_CODE and DESCRIPTION from PRODUCT_MASTER table, SALES_ORDER_DETAILS table
for product whose QTY_ORDERED < 3.
|
5. |
Delete all records from PRODUCT_MASTER table corresponding to ORDER_NO = 'O46865'.
|
6. |
Update SALES_ORDER_DETAILS table, change the PRODUCT_RATE of 'P06734' to the
SELLING_PRICE from PRODUCT_MASTER table.
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
