please help me with these queries!
Date: 1995/09/08
Message-ID: <MPONIKVAR.11.00531020_at_crete.hsc.colorado.edu>#1/1
I am having problems getting the following four queries to run. Could someone/anyone please help me!!!
Table structures:
Table Columns Movstars mvnumb, starnumb Stars starnumb, starname, brthplce, starborn, stardied Movies mvnumb, mvtitle, yrmde, mvtype, crit, mmpaa, noms, award, dirnumb Rented rentdate, tpnumb, mmbnumb Category mvtype, mvtypedesc Members mmbnumb, mmbname, mmbaddr, mmbcity, mmst, joindate Ratings mmpaa, mmpaadesc Directors dirnumb, dirname, dirborn, dirdied Tapes tpnumb, mvnumb, purdate, price
(1) I need to list the stars' name(s) and the movie's title of all movies
Select starname, mvtitle
directed by either alfred hitchcock or john ford.
from stars s, movies m, directors d, movstars r
where s.starnumb=r.starnumb
and m.mvnumb=rmvnumb
and dirname = 'Hitchchock, Alfred' or
dirname = 'Ford, John';
(2) Count the number of movies in each rating class (mmpaa) and list that
count with the rating description (mmpaadesc).
Select count (mmpaa), mmpaadesc
from movies, ratings
where ratings.mmpaa=movies.mmpaa
group by mmpaa;
(3) List the movie titles and movie numbers of all movie that reside on more
than one tape.
Select mvtitle, m.mvnumb
from tapes t, movies m, rented r
where t.mvnumb=m.mvnumb
and r.tpnumb=t.tpnumb
group by mvnumb
having count (*)>1;
(4) List the names of all members who have rented more than the average number
of tapes.
Select mmbname
from members
where mmbnumb>
(Select avg(mmbnumb) from rented group by mmbnumb);
**This is a sample query that I did get to work, it may help... I listed the name of the movie that has been rented the most often by using the following query:
Select mvtitle
from movies
where mvnumb= (select mvnumb
from rented r, tapes t where r.tpnumb=t.tpnumb group by t.mvnumb having count (*)= (select max(count(*)) from rented a, tapes b where a.tpnumb=b.tpnumb group by b.mvnumb));
Please help me!! If you can solve the above queries this weekend please feel free to e-mail at the address above or at ponima01_at_aol.com. Thanks so much for any help!! Received on Fri Sep 08 1995 - 00:00:00 CEST