please help me with these queries!

From: Marissa Ponikvar <MPONIKVAR_at_crete.hsc.colorado.edu>
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
directed by either alfred hitchcock or john ford.

Select starname, mvtitle
from stars s, movies m, directors d, movstars r where s.starnumb=r.starnumb
and m.mvnumb=rmvnumb
and d.dirnumb=m.dirnumb
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