SQL Queries Exercise: Assume the following Tables: Table: ACTORS Columns: id, fi

Question

SQL Queries Exercise:

Assume the following Tables:

Table: ACTORS Columns: id, first_name, last_name, gender

Table: MOVIES Columns: id, name, year, rank Table: ROLES Columns: actor_id, movie_id

Table: DIRECTORS Columns: id, first_name, last_name

Table: MOVIES_DIRECTORS Columns: director_id, movie_id

Table: MOVIES_GENRES Columns: genre, movie_id

Table: DIRECTORS_GENRES Columns: genre, director_id, prob

Please use the above tables and indicate the exact SQL function/script for the following question: List the director that worked with the largest number of actors.

Explanation / Answer

If you have any doubts, please give me comment...

SELECT D.id, first_name, last_name

FROM DIRECTORS D, MOVIES_DIRECTORS MD, MOVIES M, ROLES R

WHERE D.id = MD.director_id AND MD.movie_id = M.id AND M.id = R.movie_id

GROUP BY R.actor_id

HAVING COUNT(*) = (

SELECT MAX(COUNT(*))

FROM DIRECTORS D1, MOVIES_DIRECTORS MD1, MOVIES M1, ROLES R1

WHERE D1.id = MD1.director_id AND MD1.movie_id = M1.id AND M1.id = R1.movie_id

GROUP BY R1.actor_id

);

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.