Urgent plzz Help! [message #37372] |
Sun, 03 February 2002 19:36 |
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 |
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
|
|
|