Home » SQL & PL/SQL » SQL & PL/SQL » Urgent plzz Help!
Urgent plzz Help! [message #37372] Sun, 03 February 2002 19:36 Go to next message
Hem
Messages: 2
Registered: February 2002
Junior Member
Hi,

I have created three tables:

Movies(MovieNo*, Title, Type)
Videos(VideoNo*,MovieNo, TmsRented, ExpRDate)
Rental(VideoNo,DateHired,DateReturn, MemberID)

I want to write a pl/sql module which gives me out put for the title and TmsRented movies for yr 2001.

Like

REPORT FOR YR 2001
------------------
M_Title | TmsRented
------------------
Airforce | 20
Die Hard | 10
Dragon | 2
------------------

I have created a temp table (MovieTitle, TmsRented).
Re: Urgent plzz Help! [message #37381 is a reply to message #37372] Mon, 04 February 2002 06:34 Go to previous message
Jon
Messages: 483
Registered: May 2001
Senior Member
Forget the temp table.

Declare cursor c1
is
select title, count(*) tmsRented
from movies m, videos v, rental r
where m.movieno = v.movieno
and r.videono = v.videono
and (to_char(dateHired,'yyyy') = '2001'
or to_char(dateReturn,'yyyy') = '2001')
group by title;
begin
dbms_output.put_line('REPORT FOR YEAR 2001');
dbms_output.put_line('Title - Times Rented');
for c_rec in c1 loop
dbms_output.put_line(c_rec.title||' - '||to_char(c_rec.tmsRented));
end loop;
end;
REPORT FOR YEAR 2001
Title - Times Rented
DR DOOLITTLE - 2
MASH - 4
Previous Topic: DEAD LOCK
Next Topic: from text file to ora.8i database
Goto Forum:
  


Current Time: Thu Mar 28 06:52:32 CDT 2024