I'm using data from a database from the IMDB website. The database consists of five relevant tables.
- Actor (id, fname, lname, gender)
- Movie (id, name, year, rank)
- Director (id, fname, lname)
- Cast (pid, mid, role)
- Movie_Director (did, mid)
It's worth noting that the
id column in Actor, Movie & Director tables is a key for the respective table.
Cast.pid refers to
Cast.mid refers to
Here's the prompt and my initial attempt at solving it. Any help in improving/speeding up the query would be greatly appreciated.
/* For every pair of male and female actors that appear together in some film, find the total number of films in which they appear together. Sort the answers in decreasing order of the total number of films. */ SELECT a1.fname, a1.lname, a2.fname, a2.lname, count(*) AS num_films FROM Actor AS a1, Actor AS a2, Cast AS c1, Cast AS c2 WHERE c1.mid = c2.mid AND a1.id = c1.pid AND a2.id = c2.pid AND a1.gender = 'M' AND a2.gender = 'F' GROUP BY a1.id, a2.id, a1.fname, a1.lname, a2.fname, a2.lname HAVING COUNT(*) > 0 ORDER BY COUNT(*) DESC;