Write a SQL query that lists the top-10 actors from the US by number of films th

Question

Write a SQL query that lists the top-10 actors from the US by number of films they played in. The result should show each actor by first name and last name, and then show the number of films the actor has played in:

The top-10 list should be an ordinal ranking ordered be descending order of number of films; for actors who played in the same number of films, show those in alphabetical order of their name (first and last name).

Note that we are asking for an ordinal ranking, meaning even if two actors played in the same number of films, they should be considered as separate entries within our top-10 list.

Explanation / Answer

SELECT * FROM (SELECT C.first_name||' '||C.last_name AS actorfullname, COUNT(film_id) films FROM Film A, Film_Actor B, Actor C WHERE c.natonality='US' AND A.film_id=B.film_id AND B.actor_id=C.actor_id GROUP BY film_id ORDER BY films desc) WHERE ROWNUM

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---