Slip 28
Consider the following entities and their relationships.
Bill (billno, day, tableno, total)
Menu (dish_no, dish_desc, price)
The relationship between Bill and Menu is Many to Many with quantity as
descriptive attribute.
Constraint: Primary key, price should be > 0.
SQL>create table bill(bno int primary key,tableno int,total int);
SQL>insert into bill values(1,201,3);
SQL>create table menu(mno int primary key,dish_no int,discription
varchar(15),price int);
SQL>insert into menu values(101,1,'xyz','2000');
SQL>create table bm(bno int,mno int,quantity varchar(20));
SQL>insert into bm values(1,101,'aaa');
1)Write a function which
accept a table number and display total amount of bill for a specific table
SQL> set serveroutput on
SQL> create or replace function f1(abc in varchar) return number as
xyz number;
2
begin
3
select sum(bill.total)into xyz from bill where tableno='203';
4
if sql %found then
5
return (xyz);
6
else
7
return null;
8
end if;
9
end f1;
10
/
Function created.
SQL> begin
2
dbms_output.put_line('amt-'||f1('203'));
3
end;
4
/
2)Write a cursor which
will display table wise menu details
SQL> set
serveroutput on
SQL> declare
2
cursor c1 is select discription,price from bill,menu,bm where bill.bno=bm.bno and menu.mno=bm.mno order
by tableno;
3 c
c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into c;
8 exit when c1%notfound;
9 dbms_output.put_line(c. discription ||''||c. price);
10
end loop;
11
close c1;
12
end;
13 /
