Slip 2
Item (itemno, itemname )
Supplier (supplier_No , supplier_name,
address, city )
Relationship between Item and Supplier is
many-to-many with descriptive attribute rate and quantity
Constraints: itemno ,supplier_No primary
key
SQL>create table item(ino
int primary key,iname varchar(11));
SQL>insert into item
values(1,'soap');
SQL>create table
supplier(sno int primary key,sname varchar(11),address varchar(11),city
varchar(11));
SQL>insert into
supplier
values(101,'ram',’loni’,’pune’);
SQL>create table i_s(ino
int,sno int,rate int,quantity int);
SQL>insert into i_s
values(1,101,250,3);
1)Write function to print the total number
of suppliers of a particular item
set serveroutput on
create or replace function
f1(abc in varchar) return number as xyz number;
begin
select sum(supplier.sno)
into xyz from item,supplier,i_s where item.ino=i_s.ino and supplier.sno=i_s.sno
and iname='soap';
if sql %found then
return (xyz);
else
return null;
end if;
end f1;
/
begin
dbms_output.put_line
(‘item’||f1(‘soap’));
end;
/
2)Write a trigger which will fire before
insert or update on rate and quantity less than or equal to zero. (Raise user
defined exception and give appropriate message)
SQL> set serveroutput
on
SQL> create or
replace trigger t1 before insert or update on i_s
2 for
each row
3 begin
4 if(:new.quantity<=0)then
5 raise_application_error(-20001,’quantity
>0');
6 end
if;
7 end;
8 /
