Hotel bill system using SQL

Objective

Create a hostel bill calculating system

Program

SQL> create table hotel(cust_id varchar(6) primary key, cust_name varchar(20), item varchar(20), quantity number(6,2), price number(10,2));

Table created

SQL> insert into hotel values(‘H001’,’midhun’,’tea’,3,5);

1 row created

SQL> insert into hotel values(‘H002’,’praveen’,’broast’,2,100);

1 row created

SQL> insert into hotel values(‘H003’,’dileep’,’rice’,3,20);

1 row created

SQL> insert into hotel values(‘H004’,’shiju’,’coffee’,4,10);

1 row created

SQL> alter table hotel add (total number(10,2));

Table altered

SQL> declare

2 cursor hot is select * from hotel;

3 v_t hotel.total%type;

4 begin

5 for i in hot loop

6 v_t:=i.price*i.quantity;

7 update hotel set total=v_t where cust_id=hot.cust_id;

8 end loop;

9 end ;

11 /

PL/SQL successfully completed

SQL> select * from hotel;

CUST_ID    CUST_NAME     ITEM     QUANTITY     PRICE     TOTAL

H001             midhun             tea              3                  5            15

H002             praveen             broast       2                  100        200

H003             dileep                rice             3                  20          60

H004             shiju                   coffee         4                  10         40