Slip
3
Consider
the following entities and their relationship.
Newspaper
(name,language , publisher , cost )
Cities
(pincode , city, state)
Relationship
between Newspaper and Cities is
many-to-many with descriptive attribute daily required
Constraints:
name and pincode primary key
SQL>
create table newspaper(nno int,name varchar(15) primary key,language
varchar(10),publisher varchar(10),cost
int);
SQL> insert into newspaper
values(1,'punetimes','marathi','abc',5);
SQL>
create table citys(cno int primary key,city varchar(10),state varchar(15));
SQL>
insert into citys values(1,'pune','mh');
SQL>
create table nc(nno int,cno int,daily_required varchar(10));
SQL>
insert into nc values(1,1,'yes');
1)Write a trigger which will fire
before insert on the cities table which check that the pincode must be of 6
digit. (Raise user defined exception and give appropriate message).
SQL> set serveroutput
on
SQL> create or
replace trigger t1 before insert or update on CITYS
2 for
each row
3 begin
4 if(:new.pincode=6)then
5 raise_application_error(-20001,’Insert
Invalid Record');
6 end
if;
7 end;
8 /
2)Write a procedure to calculate
city wise total cost of each newspaper
SQL>
set serveroutput on
SQL> create or replace procedure p6(t in
varchar)as cursor c2 is select city,cost
from newspaper,c
itys,nc
where cost=(select sum(cost)from newspaper) and newspaper.nno=nc.nno and
citys.cno=nc.cno;
2
c c2 %rowtype;
3
begin
4
open c2;
5
dbms_output.put_line('city'||''||'cost');
6
loop
7
fetch c2 into c;
8
exit when c2 %notfound;
9
if(c.city=t) then
10
dbms_output.put_line(c.city||''||c.cost);
11
end if;
12
end loop;
13
close c2;
14
end;
15 /
Procedure
created.
SQL>
begin
2
p6('pune');
3 end;
4 /
citycost
pune5
PL/SQL
procedure successfully completed.
slip
3-
SQL>
set serveroutput on
SQL> create or replace procedure p5(n in varchar)
as sum_cost newspaper.cost %type;
2
begin
3
select sum(cost) into sum_cost from newspaper,citys,nc where
newspaper.nno=nc.nno and citys.
cno=nc.cno;
4
if(sum_cost > 0) then
5
dbms_output.put_line('sum cost='||sum_cost);
6
else
7
dbms_output.put_line('cost cannot be calculated');
8
end if;
9
end p5;
10 /
Procedure
created.
SQL>
execute p5('5');
sum
cost=15
PL/SQL
procedure successfully completed.
