80's movie trivia answers

Back to Portfolio

Our professor gave us a series of questions on how to sort this set of data. After sorting through the data and creating the proper pivot tables, here's how I answered his questions.

Trivia 1: What are the five lowest box office performers?
       
        SELECT `80s_movie`.`movie_title` AS title,
               `80s_movie`.`movie_gross` AS gross
          FROM `80s_movie` 
      ORDER BY gross ASC
         LIMIT 5
       
   
Title Gross (in million $'s)
Out of Africa 87
Dead Poets Society 96
Crocodile Dundee II 109
Twins 112
Ghostbusters II 112
Trivia 2: What are the top five box office performers?
        
          SELECT `movie_title`, 
                 `movie_plot`,  
                 `80s_rating`.`rating_name`
            FROM `80s_movie`
            JOIN `80s_rating`
              ON `80s_rating`.`rating_id` = `80s_movie`.`rating_id`
        ORDER BY `80s_movie`.`movie_gross` DESC
           LIMIT 5
        
    
Title Rating Summary
E.T. the Extra-Terrestrial PG A troubled child summons the courage to help a fri
Star Wars: Episode VI - Return of the Jedi PG After a daring mission to rescue Han Solo from Jab
Star Wars: Episode V - The Empire Strikes Back PG Skywalker begins Jedi training with Yoda, while hi
Batman PG-13 The Dark Knight of Gotham City begins his war on c
Raiders of the Lost Ark PG In 1936, archaeologist and adventurer Indiana Jone
Trivia 3: What are the top box office performing movies that are longer than 2 hours in duration?
        
     SELECT `movie_title`,
            `movie_runtime`, 
            GROUP_CONCAT(`category_name` SEPARATOR ", ") AS category
       FROM `80s_movie` 
       JOIN `80s_title_category`
         ON `80s_movie`.`movie_id` = `80s_title_category`.`movie_id`
       JOIN `80s_category`
         ON `80s_category`.`category_id` = `80s_title_category`.`category_id`
      WHERE `movie_runtime` >= 120
   GROUP BY `80s_movie`.`movie_title`
   ORDER BY `movie_gross` ASC
        
    
Movie Title Movie Runtime (min) Movie Category
Out of Africa 161 Biography, Drama, Romance
Dead Poets Society 128 Comedy, Drama
Rain Man 133 Drama
Indiana Jones and the Last Crusade 127 Action, Adventure
Batman 126 Action, Adventure
Star Wars: Episode V - The Empire Strikes Back 124 Action, Adventure, Fantasy
Star Wars: Episode VI - Return of the Jedi 131 Action, Adventure, Fantasy
Trivia 4: What are all of the movies rated PG?
        
      SELECT `movie_title`,
             `movie_year`
        FROM `80s_movie`
        JOIN `80s_rating`
          ON `80s_rating`.`rating_id` = `80s_movie`.`rating_id`
       WHERE `80s_movie`.`rating_id` = 1
    ORDER BY `80s_movie`.`movie_year` ASC
        
    
Movie Title Release Date
Star Wars: Episode V - The Empire Strikes Back 1980
Raiders of the Lost Ark 1981
E.T. the Extra-Terrestrial 1982
Rocky III 1982
Star Wars: Episode VI - Return of the Jedi 1983
Gremlins 1984
Ghostbusters 1984
Indiana Jones and the Temple of Doom 1984
Rocky IV 1985
Out of Africa 1985
Back to the Future 1985
Top Gun 1986
Who Framed Roger Rabbit 1988
Crocodile Dundee II 1988
Twins 1988
Back to the Future Part II 1989
Dead Poets Society 1989
Honey, I Shrunk the Kids 1989
Ghostbusters II 1989
Trivia 5: Which adventure film had the lowest box office numbers?
        
     SELECT `movie_title`, 
            `rating_name`, 
            MIN(`80s_movie`.`movie_gross`) AS Lowest,
            GROUP_CONCAT(`category_name` SEPARATOR ", ") AS category
      FROM `80s_movie`
      JOIN `80s_rating`
        ON `80s_rating`.`rating_id` = `80s_movie`.`rating_id`
      JOIN `80s_title_category`
        ON `80s_movie`.`movie_id` = `80s_title_category`.`movie_id`
      JOIN `80s_category`
        ON `80s_category`.`category_id` = `80s_title_category`.`category_id`
     WHERE `category_name` = 'Adventure'
  GROUP BY `movie_title`
  ORDER BY `movie_gross` ASC
     LIMIT 1
        
    
Movie Title Rating Gross (in million $'s) Category
Crocodile Dundee II PG 109 Adventure
Trivia 6: Who are the best money making directors?
        
     SELECT `movie_title`,
            `director_name`,
            COUNT(`director_name`) AS TotalMoviesFilmed
       FROM `80s_movie`
       JOIN `80s_title_director`
         ON `80s_movie`.`movie_id` = `80s_title_director`.`movie_id`
       JOIN `80s_director`
         ON `80s_director`.`director_id` = `80s_title_director`.`director_id` 
   GROUP BY `director_name`
   ORDER BY `movie_gross` DESC
        
    
Movie Title Director Name Total Movies Filmed
E.T. the Extra-Terrestrial Steven Spielberg 4
Star Wars: Episode VI - Return of the Jedi Richard Marquand 1
Star Wars: Episode V - The Empire Strikes Back Irvin Kershner 1
Batman Tim Burton 1
Ghostbusters Ivan Reitman 3
Beverly Hills Cop Martin Brest 1
Back to the Future Robert Zemeckis 3
Top Gun Tony Scott 2
Rain Man Barry Levinson 1
Crocodile Dundee Peter Faiman 1
Fatal Attraction Adrian Lyne 1
Who Framed Roger Rabbit Richard Williams 1
Rambo: First Blood Part II George P Cosmatos 1
Gremlins Joe Dante 1
Lethal Weapon 2 Richard Donner 1
Look Who's Talking Amy Heckerling 1
Honey, I Shrunk the Kids Joe Johnston 1
Coming to America John Landis 1
Rocky IV Sylvester Stallone 2
Crocodile Dundee II John Cornell 1
Dead Poets Society Peter Weir 1
Out of Africa Sydney Pollack 1
Trivia 7: Who are top 3 overall best money making directors per film?
        
     SELECT `director_name`,
            COUNT(`director_name`) AS TotalMoviesFilmed,
            ROUND(AVG(`80s_movie`.`movie_gross`)) AS Average
       FROM `80s_movie`
       JOIN `80s_title_director`
         ON `80s_movie`.`movie_id` = `80s_title_director`.`movie_id`
       JOIN `80s_director`
         ON `80s_director`.`director_id` = `80s_title_director`.`director_id` 
   GROUP BY `director_name`
   ORDER BY Average DESC
      LIMIT 3
        
    
Director Total Movies Filmed Average Per Movie (in million $'s)
Richard Marquand 1 309
Irvin Kershner 1 290
Steven Spielberg 4 265
Trivia 8: What is the lowest, highest, and average box office amount made by family films?
        
     SELECT MAX(`80s_movie`.`movie_gross`) AS Highest,
            MIN(`80s_movie`.`movie_gross`) AS Lowest,
            ROUND(AVG(`80s_movie`.`movie_gross`)) AS Average
       FROM `80s_movie`
       JOIN `80s_title_category`
         ON `80s_movie`.`movie_id` = `80s_title_category`.`movie_id`
       JOIN `80s_category`
         ON `80s_category`.`category_id` = `80s_title_category`.`category_id`
      WHERE `category_name` = 'Family'
        
    
Highest (in million $'s) Lowest (in million $'s) Average (in million $'s)
435 131 283
Trivia 9: What is the average box office money made by films in each category?
        
  SELECT `category_name`,
         COUNT('category_id') AS CategoryAmount,
         ROUND(AVG(`80s_movie`.`movie_gross`)) AS Average
    FROM `80s_movie`
    JOIN `80s_title_category`
      ON `80s_movie`.`movie_id` = `80s_title_category`.`movie_id`
    JOIN `80s_category`
      ON `80s_category`.`category_id` = `80s_title_category`.`category_id`
GROUP BY `category_name`
ORDER BY Average DESC
        
    
Category Amount of Movies Made Average Gross (in million $'s)
Family 2 283
Sci-Fi 3 255
Fantasy 5 220
Action 15 198
Adventure 13 194
Crime 4 162
Animation 1 156
Clmedy 15 151
Thriller 3 151
Horror 1 148
Drama 7 136
Sport 2 127
Romance 2 108
Biography 1 87
Trivia 10: What are all of the films directed by Steven Spielberg?
        
       SELECT `movie_title`, 
              `movie_year`, 
              `movie_runtime`, 
              `movie_plot`, 
              `movie_gross`, 
               GROUP_CONCAT(`category_name` SEPARATOR ", ") AS category,
              `rating_name`
        FROM  `80s_movie`
        JOIN  `80s_rating`
          ON  `80s_rating`.`rating_id` = `80s_movie`.`rating_id`
        JOIN  `80s_title_category`
          ON  `80s_movie`.`movie_id` = `80s_title_category`.`movie_id`
        JOIN  `80s_category`
          ON  `80s_category`.`category_id` = `80s_title_category`.`category_id`
        JOIN  `80s_title_director`
          ON  `80s_movie`.`movie_id` = `80s_title_director`.`movie_id`
        JOIN  `80s_director`
          ON  `80s_director`.`director_id` = `80s_title_director`.`director_id`
       WHERE  `director_name` = 'Steven Spielberg'
    GROUP BY  `80s_movie`.`movie_title`
    ORDER BY  `80s_movie`.`movie_year`
        
    
Title Year Release Runtime Plot Gross Category Rating
Raiders of the Lost Ark 1981 115 In 1936, archaeologist and adventurer Indiana Jones is hired by the U.S. government to find the Ark of the Covenant before Adolf Hitler's Nazis can obtain its awesome powers. 248 Action, Adventure PG
E.T. the Extra-Terrestrial 1982 115 A troubled child summons the courage to help a friendly alien escape Earth and return to his home world. 435 Family, Sci-Fi PG
Indiana Jones and the Temple of Doom 1984 118 In 1935, Indiana Jones arrives in India, still part of the British Empire, and is asked to find a mystical stone. He then stumbles upon a secret cult committing enslavement and human sacrifices in the catacombs of an ancient palace. 180 Adventure, Action PG
Indiana Jones and the Last Crusade 1989 127 In 1938, after his father Professor Henry Jones, Sr. goes missing while pursuing the Holy Grail, Professor Henry Indiana Jones, Jr. finds himself up against Adolf Hitler's Nazis again to stop them from obtaining its powers. 197 Action, Adventure PG-13
Trivia 11: how much money did the films directed by Steven Spielberg make in the 80s?
      
 SELECT SUM(`movie_gross`),
        COUNT(`80s_movie`.`movie_id`),
        ROUND(AVG(`80s_movie`.`movie_gross`)) AS Average
   FROM `80s_movie`
   JOIN `80s_title_director`
     ON `80s_movie`.`movie_id` = `80s_title_director`.`movie_id`
   JOIN `80s_director`
     ON `80s_director`.`director_id` = `80s_title_director`.`director_id`
  WHERE `director_name` = 'Steven Spielberg' 
      
  
Total Sum (in million $'s) Total Movies Average Gross (in million $'s)
1060 4 265