problem with query

Hello ,

Actor

ANO	ANAME
A1	L Tree
A2	D Pond
A3	M Lake
A4	J Worthy
A5	T Gross
A6	L Leaf

Movie

MNO	MNAME
M1	Work a lot
M2	Sleep too little
M3	Too much fun
M4	Laugh and Cry

PLAY

ANO	MNO	Pay
A1	M1	$77.00
A1	M2	$80.00
A2	M2	$93.00
A2	M3	$88.00
A2	M4	$91.00
A3	M2	$74.00
A3	M3	$89.00
A4	M2	$75.00
A4	M3	$85.00
A4	M4	$55.00
A5	M1	$50.00
A5	M4	$61.00
A6	M2	$77.00
A6	M3	$88.00
A6	M4	$54.00

Here are my table,

I am trying to write a few queries with them,

1. The names of the actors who did not play in any movie that 'Jworthy" Played.

2. Find the names of movies that both 'J worthy' and "T Gross " played it

3 Find the names of actors who played in all movies that 'J worthy ' played

4. Find tyhe names of actors who got better pays than 'j worthy' in all movies that 'j worthy' played in

5. For each movie, list the movie number, the average pay of all actors and the total number of actors in the movie

6. extend question 5 by also including the number of actors whose pay is below average pay for the movie

7. for each actor , list the actor name and his/her average pay of all time recorded in the database

8. Find the names of the actors who have the highest average pay. This cannot be done using "order By"

IF any one could please gelp me with all or few of these queries would be greatly appreaciated.

Thank You,

gsk_phili

Are we supposed to do your homework?

--
SvenC

|||

Is it not what people do here. Bring their work problems and school problems.?

I figured out 5 of them, i only need help with query 4 , 6 and 8.

|||

Well, pasting the questions one to one and not even trying to solve one itself is not what makes many people here try to help. The homework actually should help you understand the topics. Try some solutions and ask questions what is wrong with your approach if it does not work.

But as you tried, here is a solution for 4:

SELECT Play.ANO , SUM(Play.Pay) As PaySum FROM play where Play.MNO in

( SELECT Play.MNO from Actor inner join Play ON Actor.ANO = Play.ANO where Actor.ANO = 'A4' )

GROUP BY Play.Ano

HAVING SUM(Play.Pay) > (SELECT SUM(Play.Pay) As PaySum FROM play where Play.MNO in

( SELECT Play.MNO from Actor inner join Play ON Actor.ANO = Play.ANO where Actor.ANO = 'A4' )

AND Play.ANO = 'A4')

How far have you got with 6 and 8

--
SvenC

|||

6 is a bit tricky. I give you a start by showing you the innermost select. You will need to put two more selects around it - at least my solution needs that, don't know if an easier one exists.

Here is my innermost select:

SELECT MNO as mno1, AVG(Pay) as AvgPay, count(ANO) As ActNum from play group by MNO

--
SvenC

|||

Hello svenc,

Thanks for the response. As to reply of '6'. This is the exact query that works for question 5.

i have it written as

SELECT MNO, Avg(PLAY.Pay) AS AvgOfPay, Count(PLAY.ANO) AS CountOfANO
FROM PLAY
GROUP BY MNO;

And as for query 4, the result of the query is A2 and A6 but A6 has lesser pay than j worthy in M4.

Thanks

gsk_phili

|||

HELLO svenc,

I got the 4th one.

The resulting table should only list D.pond

NOw i am struck at 6 and 8