-- Designate the year column in the movie table as a foreign key to the year column in the YearStats table

CREATE TABLE YearStats( 
    YEAR INTEGER PRIMARY KEY, 
    TotalGross BIGINT UNSIGNED, 
    Releases INTEGER
);

CREATE TABLE Movies( 
    ID INTEGER PRIMARY KEY,
    Title VARCHAR(255),
    Genre VARCHAR(255), 
    RatingCode VARCHAR(255), 
    Movie_YEAR INTEGER,
    FOREIGN KEY (Movie_YEAR) REFERENCES YearStats(YEAR)
);

INSERT INTO YearStats(YEAR, TotalGross, Releases) VALUES
(1997, 1720000000, 500),
(2008, 1004000000, 600),
(2009, 2847000000, 510),
(2011, 1341000000, 550),
(2012, 1519000000, 580),
(2013, 1215000000, 570),
(2015, 2058000000, 590),
(2017, 1263000000, 560),
(2018, 2048000000, 570),
(2019, 2798000000, 600),
(2021, 1922000000, 480),
(2023, 1359000000, 520);

INSERT INTO Movies(ID, Title, Genre, RatingCode, Movie_YEAR) VALUES 
(1, 'Avatar', 'Science Fiction', 'PG-13', 2009),
(2, 'Avengers: Endgame', 'Superhero', 'PG-13', 2019),
(3, 'Titanic', 'Romance/Drama', 'PG-13', 1997),
(4, 'Star Wars: The Force Awakens', 'Science Fiction', 'PG-13', 2015),
(5, 'Jurassic World', 'Science Fiction/Adventure', 'PG-13', 2015),
(6, 'The Lion King', 'Animation', 'PG', 2019),
(7, 'The Avengers', 'Superhero', 'PG-13', 2012),
(8, 'Furious 7', 'Action', 'PG-13', 2015),
(9, 'Frozen II', 'Animation', 'PG', 2019),
(10, 'Avengers: Infinity War', 'Superhero', 'PG-13', 2018),
(11, 'Spider-Man: No Way Home', 'Superhero', 'PG-13', 2021),
(12, 'Black Panther', 'Superhero', 'PG-13', 2018),
(13, 'Harry Potter and the Deathly Hallows – Part 2', 'Fantasy', 'PG-13', 2011),
(14, 'The Incredibles 2', 'Animation', 'PG', 2018),
(15, 'Beauty and the Beast', 'Fantasy/Musical', 'PG', 2017),
(16, 'The Fate of the Furious', 'Action', 'PG-13', 2017),
(17, 'Iron Man 3', 'Superhero', 'PG-13', 2013),
(18, 'Minions', 'Animation', 'PG', 2015),
(19, 'The Dark Knight', 'Superhero', 'PG-13', 2008),
(20, 'The Super Mario Bros. Movie', 'Animation/Adventure', 'PG', 2023);

ALTER TABLE Movies 
ADD CONSTRAINT fk_movie_year 
FOREIGN KEY (Movie_Year)
REFERENCES YearStats(Year);