Rdbms Practical Slips

BCA PRACTICAL
                                           
                                                         RDBMS 1.SLIPS

1.Consider the following entities and their relationship. [Marks 40]
Customer (c_no, c_name, c_city, c_ph_no)
Ticket (t_no, booking_date, fare, traveling_date)
Relationship between Customer and Ticket is one-to-many.
Constraints: primary key, foreign key
c_name should not be null,
fare should be greater than zero.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a procedure to display names of customer who have booked bus on given date.

2) Write a trigger that restricts insertion of ticket having traveling date smaller than
booking date.(Raise user defined exception and give appropriate message)

3) Write a function which will calculate and return total fare collected from customers
on given date.

4) Write a cursor which will display date wise ticket booked by customer. (Use
parameterized cursor)

SOLUTION

create table customer
(
cno number(10) primary key,
cname varchar2(20) NOT NULL,
city varchar2(10),
phno number(20)
);

create table ticket
(
tno number(20) primary key,
bdt date,
fare number(10)check(fare>0),
tdt date,
cno number(10) references c2(cno)
);

insert into customer values(1,'pramod','pune',9890339055);
insert into customer values(2,'rajesh','surat',9890436055);
insert into customer values(3,'smita','thane',9890539057);
insert into customer values(4,'sonam','nasik',9860839054);
insert into customer values(5,'mahesh','mumbai',9895939351);

insert into ticket values(11,'21 jan 2018',100,'28 aug 2018',1);
insert into ticket values(12,'21 jan 2018',150,'28 oct 2018',2);
insert into ticket values(13,'12 mar 2018',120,'20 july 2018',3);
insert into ticket values(14,'21 feb 2018',80,'22 sep 2018',4);
insert into ticket values(15,'20 july 2018',200,'28 apr 2018',5);
insert into ticket values(16,'21 jun 2018',100,'13 mar 2018',6);

Q.1) Write a procedure to display names of customer who have booked bus on given date.

Procedure
create or replace procedure p1
as
cursor c1 is select cname from c2,ti
where c2.cno=ti.cno and
bdt='21 jan 2018';
x  c1%rowtype;
begin
for x in c1
loop
dbms_output.put_line(x.cname);
end loop;
end p1;

execute p1;

Q.2) Write a trigger that restricts insertion of ticket having traveling date smaller than

booking date.(Raise user defined exception and give appropriate message)

Trigger

create or replace trigger t2
before insert on ti
for each row
begin
if:new.tdt<:new.bdt then
raise_application_error ('-20010','bdt also less than tdt');
end if;
end;
/


SQL> insert into ti values(16,'21 jun 2018',100,'13 mar 2018',6);
insert into ti values(16,'21 jun 2018',100,'13 mar 2018',6)
     *
ERROR at line 1:
ORA-20010: bdt also less than tdt
ORA-06512: at "HR.T2", line 3
ORA-04088: error during execution of trigger 'HR.T2'


Q.3) Write a function which will calculate and return total fare collected from customers

on given date.

Function

create or replace function f1
(vdt in date)
return number
as
total number;
begin
select sum(fare)into total from c2,ti
where c2.cno=ti.cno and
bdt=vdt;
if SQL%found then
return total;
else
return 0;
end if;
end;
/


OUTPUT
select f2(2018) from dual;
  F2(2018)
----------
         1

//Calling Programm//

declare
a date;
b number;
begin
a:&a;
b:f1(a);
if(b>0)then
dbms_output.put_line('total fare collected on given date is'||b);
else
dbms_output.put_line('no data found');
end if;
end;
/


Q.4) Write a cursor which will display date wise ticket booked by customer. (Use
parameterized cursor)

CURSOR

declare
cursor c1(vdt ti.bdt%type) is select cname,bdt from c2,ti
where c2.cno=ti.cno and
ti.bdt='21 jan 2018';
cdt ti.bdt%type;
x c1%rowtype;
begin
cdt:=&cdt;
for x in c1(cdt)
loop
dbms_output.put_line(x.cname||'  '||x.bdt);
end loop;
end;
/

OUTPUT

Enter value for cdt: '21 jan 2018'
old   8: cdt:=&cdt;
new   8: cdt:='21 jan 2018';
madhu  21-JAN-18
sagar  21-JAN-18

PL/SQL procedure successfully completed.