RDBMS Query

   Consider the following entities and their relationship.                             

Drug(d_no, d_name, company, price)

            Medical _store(m_no, m_name, m_city, ph_no)

Relationship between Drug and Medical_Store is many-to-many with descriptive attribute quantity.

Constraints:   primary key, foreign key,

                        m_name and d_name should not be null,
                        m_city can be pune or pimpri.

Answer

Drug Table:

Create table Query

Create Table drug(d_no int primary key,d_name varchar(20) NOT NULL,company varchar(20),price number);

Insert Query

1) insert into drug values(101,'Crosine','Cipala',10); 

 2)insert into drug values(102,'Paracetemol','Centur Pharma',15); 

Medical_Store Table:

Create table Query

Create Table Medical_Store(m_no int primary key,m_name varchar(20)NOT NULL,m_city varchar(20) check(m_city in('pune','Pimpri')) ,ph_no number);

Insert Query

1) insert into Medical_Store values(1,'Medi Point','Pune',02065894578); 

 2)insert into Medical_Store values(1,'OM Medical','Pimpri',02045781269);  

drug_Medical Table:

Create table Query

Create Table drug_Medical(d_no int references drug(d_no),m_no int references Medical_store(m_no),quntity number);

Insert Query

1) insert into drug_Medical values(101,1,500); 

 2)insert into drug_Medical values(102,2,1000);  

1)Write a package, which consists of one procedure and one function. Pass drug number as a parameter to procedure and display details of that drug. Pass city as a parameter to a function and return total number of medical_store in given city.

                          

create package D_MS as

     procedure p1 (dno number);

   function f1(city varchar2) return number;

   end D_MS;

   /

Package created.

create or replace package body D_MS as

 procedure p1 (dno number) is
   d drug%rowtype;
    begin
     select d_name,company,price into d.d_name,d.company,d.price
     from drug where d_no=dno;
     dbms_output.put_line(d.d_name||'    '||d.company||'     '||d.price);

    end;

function f1(city varchar2) return number is
 n number;
begin
 select count(*) into n
  from Medical_store
  where m_city=city;
   return n;
  end;
 end D_MS;

  /

Package body created.

2)    Write a trigger that restricts insertion and updation of drug having price less than zero. (Raise user defined exception and give appropriate message)

create or replace trigger lessthenzero1

before update on drug
for each row
declare
p  EXCEPTION;
begin
if(:new.price <= 0) then
raise  p;
end if;
 EXCEPTION
 when p then
  dbms_output.put_line('Enter Valid Price ,Price cannot be less than Zero');
END;


Q.1)     Consider the following entities and their relationship.                             

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,

                        c_name and f_name should not be null,

                        c_season can be rabi or kharif.

Crop Table:

Create table crop (c_no int primary key, c_name varchar (15) NOT NULL,

 c_season varchar(10) check(c_season in(‘rabi’,’kharif’)),pesticides varchar(20));

Insert into crop values(101,’Rice’,’rabi’,’Zineb’);

Insert into crop values(102,’bajara’,’kharif’,’phorate’);

Farmer Table:

Create table farmer(f_no int primary key, f_name varchar (15) NOT NULL,

 F_location varchar(10));

 Insert into farmer values(1,’Ram’,’pune’);

 Insert into crop values(2,’Sham’,’Nasik’);

Crop_farmer Table:

Create table crop_farmer(c_no int references crop(c_no),f_no int references farmer(f_no),

year int);


Insert into crop_farmer values(101,1,2018);

Insert into crop_farmer values(102,2,2017);

1) Write a procedure which will display crop detail harvested by given farmer in given year.

 Set serveroutput on;

Create or replace procedure p1(fn in varchar,y in int)           

 as

cursor crop_detail is select c_name, season,  pesticides, f_name, year from crop, farmer, crop_farmer

where crop.c_no=crop_farmer.c_no and farmer.f_no=crop_farmer.f_no and f_name=fn and year=y;

         crop_detail c1%rowtype;

begin

open crop_detail;

dbms_output.put_line('crop name'||' '||'season'||' '||'pesticides');

loop

fetch crop_detail into c;

exit when crop_detail %notfound;

dbms_output.put_line(c.c_name||' '||c.season||' '||c.pesticides);

end loop;

Close crop_detail;

end;

 /

Procedure created.

begin

         p1(’Ram’,2017);

 end;

 /

2)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)

 set serveroutput on;

create or replace trigger t4

before insert or update on crop_farmer

        for each row

declare

d1 varchar(10);

d2 varchar(10);

begin

d1:=to_char(:new.year,'yyyy');

         d2:=to_char(sysdate,'yyyy');

 if(d1>d2) then

raise_application_error(20001,'year should  be less than current date.');

end if;

end;

 /

Trigger created.

3)Write a function which will return total number of farmers harvesting given crop in a given year?

create or replace function farms(n number, y int)

return number

  as

pm number;

begin

select count(crop_farm.fno) into pm

from crop_farm

where crop_farm.c_no=n and crop_farm.year=y;

return pm;

  end;

  /

/*calling*/

 declare

 n number;

  begin

  n:=farms(3,2012);

  dbms_output.put_line('Count is'||n);

end;

4)Write a cursor which will display season wise information of crops harvested by the farmers. (Use parameterized cursor)

declare

cursor cur_c is

select * from crop;

cursor cur_f(d int) is

select f_no,f_name,f_location

from crop,farmer

where crop.c_no=d;

c crop%rowtype;

f farmer%rowtype;

  begin

open cur_c;

loop

fetch cur_c into c;

exit when cur_c%NOTFOUND;

dbms_output.put_line('Season  '||c.season);

open cur_f(c.c_no);

loop

fetch cur_f into f.f_no,f.f_name,f.f_location;

exit when cur_f%NOTFOUND;

dbms_output.put_line(f.f_no||'  '||f.f_name||'   '||f.f_location);

end loop;

close cur_f;

end loop;

close cur_c;

end;