Q1) Write down actors (or actress, your choice, but not both) who have won at least two (2) Academy Awards for best actor/actress. Provide the actor name, movie title & year. Order the result by actor name."
SELECT STARNAME,MVTITLE, MVYEAR, (COUNT)BESTM AS BEST_MALE
FROM STAR, MOVSTAR, MOVIE
WHERE STAR.STARNUM = MOVSTAR.STARNUM AND
MOVIE.MVNUM = MOVSTAR.MVNUM AND
BESTM="W" AND SEX="M"
GROUP BY STAR.STARNAME
HAVING (COUNT)BESTM>2
ORDER BY STARNAME
Q2) List all movies that have won all the awards they were nominated for (nominations = awards), and who have more than one nomination. Give the movie title, year, the number of nominations and the number of awards won. Order by the nominations (descending)."SELECT MVTITLE, MVYEAR, CATID, SUM(NOMS) AS NOM, SUM(AWRDS) AS AWRD
FROM MOVIE, STAR, MOVSTAR
WHERE MOVIE.MVNUM=MOVSTAR.MVNUM AND STAR.STARNUM=MOVSTAR.STARNUM AND
CATEGORY.MVNUM=MOVIE.MVNUM
HAVING COUNT(NOMS) >1
ORDER BY, NOM DESC;