RDBMS 10 Slips
Q.1) Consider the following entities and their relationship. [Marks 40]
Crop (c_no, c_name, c_season, pesticides)
Farmer (f_no, f_name, f_location)
Relationship between Crop and Farmer is many-to-many with descriptive attribute
year.
Constraints: primary key, foreign key,
primary key for third table(c_no, f_no, year),
c_name and f_name should not be null,
c_season can be rabi or kharif.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will return total number of farmers harvesting given crop in a
given year.
2) Write a cursor which will display season wise information of crops harvested by the
farmers. (Use parameterized cursor)
3) Write a procedure which will display crop detail harvested by given farmer in given
year.
4) Write a trigger which will restricts insertion or updation of crop_farmer table having
year greater than current year. (Raise user defined exception and give appropriate
message)
create table cro
(
cno number(5) primary key,
cname varchar2(10) NOT NULL,
season varchar2(10)check(season in('rabi','kharif')),
pesti varchar2(10)
);
create table farm
(
fno number(10) primary key,
fname varchar2(20) NOT NULL,
loc varchar2(20)
);
create table cr_far
(
cno number(5)references cro(cno),
fno number(10)references farm(fno),
year number(10)
);
insert into cro values(12,'rice','kharif','tchi');
insert into cro values(13,'gourd','kharif','altazine');
insert into cro values(14,'soyabean','kharif','fluridone');
insert into cro values(15,'wheat','rabi','propazine');
insert into cro values(16,'potato','rabi','dicofol');
insert into farm values(21,'sharma','pune');
insert into farm values(22,'varma','bid');
insert into farm values(23,'seth','jalna');
insert into farm values(24,'mangde','nasik');
insert into farm values(25,'yadav','thane');
insert into cr_far values(12,21,2000);
insert into cr_far values(13,22,2001);
insert into cr_far values(14,23,2012);
insert into cr_far values(15,24,2011);
insert into cr_far values(16,25,2017);
Q.1) Write a function which will return total number of farmers harvesting given crop in a
given year.
FUNCTION
create or replace function f10
(fname in varchar2,fyr in number)
return number
as
cnt number;
begin
select count(farm.fno)into cnt from cro,farm,cr_far
where cro.cno=cr_far.cno and
farm.fno=cr_far.fno and
cro.cname='rice' and
cr_far.year=2000;
if SQL%found then
return cnt;
else
return 0;
end if;
end f10;
/
OUTPUT-
Function created.
SQL> select f10('rice',2000) from dual;
F10('RICE',2000)
----------------
1
//calling//
declare
a varchar2(10);
b number(10);
c number(10);
begin
a:=&a;
b:=&b;
c:=f10(a,b);
dbms_output.put_line('total no of farmer harvesting crop in year is'|| c);
end;
/
OUTPUT-
Enter value for a: 'rice'
old 6: a:=&a;
new 6: a:='rice';
Enter value for b: 2000
old 7: b:=&b;
new 7: b:=2000;
total no of farmer harvesting crop in year is1
PL/SQL procedure successfully completed.
Q.2) Write a cursor which will display season wise information of crops harvested by the
farmers. (Use parameterized cursor)
CURSOR
declare
cursor c6(vseason in varchar2)is select cname,fname,season from cro,farm,cr_far
where cro.cno=cr_far.cno and
farm.fno=cr_far.fno order by season;
x c6%rowtype;
vseason cro.season%type;
begin
vseason:=&vseason;
for x in c6(vseason)
loop
dbms_output.put_line(x.cname||' '||x.fname||' '||x.season);
end loop;
end c6;
/
OUTPUT-
Enter value for vseason: 'kharif'
old 8: vseason:=&vseason;
new 8: vseason:='kharif';
rice sharma kharif
gourd varma kharif
soyabean seth kharif
wheat mangde rabi
potato yadav rabi
PL/SQL procedure successfully completed.
Q.3) Write a procedure which will display crop detail harvested by given farmer in given
year.
PROCEDURE
create or replace procedure p10
(pname in varchar2,pyr in number)
as
cursor c2 is select cro.* from cro,farm,cr_far
where cro.cno=cr_far.cno and
farm.fno=cr_far.fno and
farm.fname='varma' and
cr_far.year=2001;
x c2%rowtype;
begin
for x in c2
loop
dbms_output.put_line(x.cno||' '||x.cname||' '||x.season||' '||x.pesti);
end loop;
end p10;
/
OUTPUT-
Procedure created.
SQL> execute p10('varma',2001);
13 gourd kharif altazine
PL/SQL procedure successfully completed.
Q.4) Write a trigger which will restricts insertion or updation of crop_farmer table having
year greater than current year. (Raise user defined exception and give appropriate
message)
TRIGGER
create or replace trigger t16
before insert on cr_far
for each row
declare
a number(10);
b number(10);
begin
a:=to_char(:new.year,'yyyy');
b:=to_char(sysdate,'yyyy');
if(a>b) then
raise_application_error('-20010','year is greater than current year');
end if;
end t16;
/
OUTPUT-
insert into cr_far values(16,25,2019);
*
ERROR at line 1:
ORA-01481: invalid number format model
ORA-06512: at "SYSTEM.T16", line 5
ORA-04088: error during execution of trigger 'SYSTEM.T16'
Tags:
RDBMS