Question

I'm having problems with the problem 4a. I would appreciate some guidance. This is what I have until now.

Answer 1

this is the url: http://www.sqlzoo.net/3.htm

Answer 2

and this is what I have:
SELECT m.yr FROM
movie m INNER JOIN actor a INNER JOIN casting c
ON
m.id = c.movieid AND a.id = c.actorid
WHERE a.name = 'John Travolta'
ORDER BY yr;

Answer 3

I'm getting a list with the years Travolta made a movie. I know I need a GROUP BY and a COUNT. But the don't work when I put them into the SELECT.

Answer 4

This should yield your results:

select m.yr, count(m.id) as NumMovies
from movie m inner join casting c on c.movieid = m.id inner join actor a on a.id = c.actorid
where
a.name = 'John Travolta'
group by m.yr
order by NumMovies desc