-- Relevant tables are:
--    * ACTOR (id, fname, lname, gender)
--    * MOVIE (id, name, year, rank)
--    * DIRECTORS (id, fname, lname)
--    * CAST (pid, mid, role)
--    * MOVIE_DIRECTORS (did, mid)
--
-- question is which movies have more females in the cast than males

SELECT  DISTINCT Movie.id, name
FROM    Actor, Cast, Movie
WHERE   Movie.id = mid AND
		(SELECT COUNT(*)
		 FROM Actor
         WHERE gender = 'F'
           AND ACTOR.id = pid) >
        (SELECT COUNT(*)
		 FROM Actor
         WHERE gender = 'M'
           AND Actor.id = pid)


-- Forget this one...
SELECT	DISTINCT m.id, m.name, 
		(SELECT COUNT(*)
		 FROM Actor a
		 WHERE m.id = c.mid
		   AND c.pid = a.id
		   AND a.gender = 'F') as fcount,
		(SELECT COUNT(*)
		 FROM Actor a
		 WHERE m.id = c.mid
		   AND c.pid = a.id
		   AND a.gender = 'M') as mcount
FROM Movie m, Cast c
HAVING fcount > mcount -- Leads to error message: Invalid column name fcount, mcount

--- Earlier efforts below....






SELECT	DISTINCT m.id, m.name 
FROM	Actor as a, Cast as c, Movie as m
WHERE	(SELECT COUNT(*)
	 WHERE a.gender = 'F'
	   AND m.id = c.mid
	   AND a.id = c.pid) >
	(SELECT COUNT(*)
	 WHERE a.gender = 'M'
	   AND m.id = c.mid
	   AND a.id = c.pid)

-- Aaaaand take two:
SELECT	DISTINCT Movie.id, Movie.name, 
		COUNT(fm.aid) as females,
		COUNT(mm.aid) as males
FROM	Movie, 
		(SELECT a.id as aid, m.id as mid
		 FROM Movie as m, Cast as c, Actor as a
		 WHERE m.id = c.mid
		   AND c.pid = a.id
		   AND a.gender = 'F') as fm,
		(SELECT a.id as aid, m.id as mid
		 FROM Movie as m, Cast as c, Actor as a
		 WHERE m.id = c.mid
		   AND c.pid = a.id
		   AND a.gender = 'M') as mm,
WHERE Movie.id = fm.mid OR Movie.id = mm.mid
HAVING COUNT(fm.aid) > COUNT(mm.aid)