RDBMS SOLUTION
ASSIGNMENT 4
SET B Q. 1 SOLUTION
Write function to print the total number of suppliers whosupplies “Keyboard”.
Source code:
/*set b q 1 solution
in this first we will create a 3nf database with 3 tables
1:item table
2:supplier table
3: item_supplier table which will contain following columns:
item_number,supplier_number,rate and quantity*/
/*Creating table*/
create table item
(
itemno number not null primary key,
item_name varchar2(40)
)
create table supplier
(
supplierno number not null primary key,
supplier_name varchar2(40),
address varchar2(40),
city varchar2(40)
)
create table i_s
(
item_number number not null references item(itemno),
supplier_number number not null references supplier(supplierno),
rate number,
quantity number
)
/*inserting records*/
insert into item values(1,'keyboard')
insert into item values(2,'Harddisk')
insert into item values(3,'charger')
insert into supplier values(1,'mr.Patil','a1','c1')
insert into supplier values(2,'mr.Rajan','a2','c2')
insert into supplier values(3,'mr.shekhar','a3','c3')
insert into i_s values(1,1,2000,200)
insert into i_s values(1,2,3000,300)
insert into i_s values(1,3,4000,400)
insert into i_s values(2,1,6000,600)
insert into i_s values(3,2,7000,900)
insert into i_s values(3,1,1000,2000)
/*function*/
create or replace function get_sup(ino in out number)
return number is sno number;
begin
select itemno into ino from item where item_name='keyboard';
select count(supplier_number) into sno from supplier s,i_s si
where si.item_number=ino and si.supplier_number=s.supplierno;
return sno;
end;
/*execution*/
declare
ssno number;
ino number;
begin
ssno:=get_sup(ino);
dbms_output.put_line('total number of suppliers who sell keyboard = '||ssno);
end;
/*source code in description*/
0 Comments