DBMS Lab Cycle 7

 

  7. Create tables based on the following schema

Item(item_id,item_desc,qty_on_hand,unit_price,category);

Sales(sale_id,item_id,qty_sold, unit_price, total_price, tax_amount,grand_total);

item_id is primary key in Item table and item_id is foreign key in Sales table.

sale_id is primary key in Sales table.

    i. Create trigger for calculating total_price, tax_amount and grand_total in sales table while inserting values.

The tax rate of vaious category as shown below.

book (10%)

stationery (20%)

chocolate (30%)

other (5%)

    ii. Create a trigger to calculate the stock of an Item after sales of an Item.

select * from Item;

+---------+-----------+-------------+------------+------------+

| item_id | item_desc | qty_on_hand | unit_price | category   |

+---------+-----------+-------------+------------+------------+

|     100 | TextBook  |          20 |         20 | book       |

|     101 | milkybar  |          30 |          5 | chocolate  |

|     102 | apple     |          50 |         10 | fruit      |

|     103 | pen       |         100 |          8 | stationery |

+---------+-----------+-------------+------------+------------+

4 rows in set (0.00 sec)

insert into Sales(sale_id,item_id,qty_sold) values(200,100,10);

insert into Sales(sale_id,item_id,qty_sold) values(201,101,25);

insert into Sales(sale_id,item_id,qty_sold) values(202,102,10);

insert into Sales(sale_id,item_id,qty_sold) values(203,103,20);

 select * from Sales;

+---------+---------+----------+------------+-------------+------------+-------------+

| sale_id | item_id | qty_sold | unit_price | total_price | tax_amount | grand_total |

+---------+---------+----------+------------+-------------+------------+-------------+

|     200 |     100 |       10 |         20 |         200 |         20 |         220 |

|     201 |     101 |       25 |          5 |         125 |         38 |         163 |

|     202 |     102 |       10 |         10 |         100 |          5 |         105 |

|     203 |     103 |       20 |          8 |         160 |         32 |         192 |

+---------+---------+----------+------------+-------------+------------+-------------+

4 rows in set (0.00 sec)

select * from Item;

+---------+-----------+-------------+------------+------------+

| item_id | item_desc | qty_on_hand | unit_price | category   |

+---------+-----------+-------------+------------+------------+

|     100 | TextBook  |          10 |         20 | book       |

|     101 | milkybar  |           5 |          5 | chocolate  |

|     102 | apple     |          40 |         10 | fruit      |

|     103 | pen       |          80 |          8 | stationery |

+---------+-----------+-------------+------------+------------+

4 rows in set (0.00 sec)