Study SQL on the Avengers!

/* These tables were created by ShaySchool22 for learning purposes only! */
/* ANSWERS AND QUESTIONS ARE LOCATED AT THE BOTTOM */

CREATE TABLE actors(id INTEGER PRIMARY KEY  autoincrement,name text);

CREATE TABLE movies(id INTEGER PRIMARY KEY autoincrement,movieName text,movieTime INTEGER,YEAR INTEGER,phase INTEGER);

CREATE TABLE actorsInMovies(id INTEGER PRIMARY KEY autoincrement,actorId INTEGER, movieID INTEGER);

--ACTORS--
INSERT INTO actors (name) VALUES ("Hulk");
INSERT INTO actors (name) VALUES ("Bucky");
INSERT INTO actors (name) VALUES ("Hawkeye");
INSERT INTO actors (name) VALUES ("Loki");
INSERT INTO actors (name) VALUES ("Odin");
INSERT INTO actors (name) VALUES ("Pepper");
INSERT INTO actors (name) VALUES ("War Machine");
INSERT INTO actors (name) VALUES ("Captain America");
INSERT INTO actors (name) VALUES ("Black Widow");
INSERT INTO actors (name) VALUES ("Thunderbolt");
INSERT INTO actors (name) VALUES ("Red Skull");
INSERT INTO actors (name) VALUES ("Iron Man");
INSERT INTO actors (name) VALUES ("Thanos");
INSERT INTO actors (name) VALUES ("Thor");

--MOVIES--
INSERT INTO movies (movieName,movieTime,YEAR,phase) VALUES ("Civil War",147,2016,3);
INSERT INTO movies (movieName,movieTime,YEAR,phase) VALUES ("Doctor Strange",115,2016,3);
INSERT INTO movies (movieName,movieTime,YEAR,phase) VALUES ("Spider-Man Homecoming",133,2017,3);
INSERT INTO movies (movieName,movieTime,YEAR,phase) VALUES ("Thor: Ragnarok",130,2017,3);
INSERT INTO movies (movieName,movieTime,YEAR,phase) VALUES ("Black Panther",134,2018,3);
INSERT INTO movies (movieName,movieTime,YEAR,phase) VALUES ("Avengers: Infinity War",149,2018,3);

INSERT INTO movies (movieName,movieTime,YEAR,phase) VALUES ("Iron Man 3",131,2013,2);
INSERT INTO movies (movieName,movieTime,YEAR,phase) VALUES ("Thor: The Dark World",112,2013,2);
INSERT INTO movies (movieName,movieTime,YEAR,phase) VALUES ("The Winter Soldier",136,2014,2);
INSERT INTO movies (movieName,movieTime,YEAR,phase) VALUES ("Avengers: Age of Ultron",141,2015,2);
INSERT INTO movies (movieName,movieTime,YEAR,phase) VALUES ("Ant-Man",117,2015,2);

--ACTORS IN MOVIES--
--"Iron Man 3"--
INSERT INTO actorsInMovies(actorID,movieID) VALUES (1,7);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (6,7);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (7,7);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (12,7);

--"Thor: The Dark World"--
INSERT INTO actorsInMovies(actorID,movieID) VALUES (4,8);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (5,8);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (8,8);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (14,8);

--"The Winter Soldier"--
INSERT INTO actorsInMovies(actorID,movieID) VALUES (2,9);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (8,9);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (9,9);

--"Avengers: Age of Ultron"--
INSERT INTO actorsInMovies(actorID,movieID) VALUES (1,10);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (3,10);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (7,10);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (8,10);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (9,10);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (12,10);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (13,10);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (14,10);

--"Ant-Man"--
INSERT INTO actorsInMovies(actorID,movieID) VALUES (2,11);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (8,11);

--"Civil War"--
INSERT INTO actorsInMovies(actorID,movieID) VALUES (2,1);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (3,1);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (7,1);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (8,1);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (9,1);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (10,1);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (12,1);

--"Doctor Strange"--
INSERT INTO actorsInMovies(actorID,movieID) VALUES (14,2);

--"Spider-Man Homecoming"--
INSERT INTO actorsInMovies(actorID,movieID) VALUES (6,3);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (8,3);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (12,3);

--"Thor: Ragnarok"--
INSERT INTO actorsInMovies(actorID,movieID) VALUES (1,4);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (4,4);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (5,4);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (14,4);

--"Black Panther"--
INSERT INTO actorsInMovies(actorID,movieID) VALUES (2,5);

--"Avengers: Infinity War"--
INSERT INTO actorsInMovies(actorID,movieID) VALUES (1,6);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (2,6);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (4,6);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (6,6);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (7,6);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (8,6);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (9,6);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (10,6);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (11,6);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (12,6);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (13,6);
INSERT INTO actorsInMovies(actorID,movieID) VALUES (14,6);

--1) How many movies were created in 2013 and what are thier names?
SELECT m.movieName FROM movies m WHERE m.YEAR=2013;

--2) What's the longest movie? And how many minutes is it?
SELECT m.movieName,MAX(m.movieTime) FROM movies m;

--3) What's the longest movie in 2017? And how many minutes is it?
SELECT m.movieName,MAX(m.movieTime) FROM movies m WHERE m.YEAR=2017;

--4) In what movies did "Iron Man" play? (please order by year)
SELECT m.movieName FROM actorsInMovies aim JOIN movies m ON aim.movieid=m.id JOIN actors a ON aim.actorid=a.id AND a.name="Iron Man" ORDER BY m.YEAR;

--5) How many actors are there in every movie?
SELECT m.movieName,COUNT(a.id) AS numOfActors FROM actorsInMovies aim JOIN movies m ON aim.movieID=m.id JOIN actors a ON aim.actorID=a.id GROUP BY m.id ORDER BY numOfActors DESC;

--6) list the (names, movies, year) of all the actors who played in a movie after the year 2015
SELECT a.name,m.movieName,m.YEAR FROM actorsInMovies aim JOIN movies m ON (aim.movieID=m.id AND m.year>2015) JOIN actors a ON aim.actorID=a.id;

--7) How many actors are there in each movie for years above 2015?
SELECT m.movieName, COUNT(*) AS numOfActors FROM actorsInMovies aim JOIN movies m ON (aim.movieID=m.id AND m.year>2015) JOIN actors a ON aim.actorID=a.id GROUP BY m.movieName ORDER BY COUNT(*) DESC;

--8) What are the names of the movies that were in phase 2, and how many actors played in those movies? (Please sort by the most actors to the least actors)
SELECT m.movieName,COUNT(a.id) AS numOfActors FROM actorsInMovies aim JOIN movies m ON (aim.movieID=m.id AND m.phase=2) JOIN actors a ON aim.actorID=a.id GROUP BY m.movieName ORDER BY numOfActors DESC;

--9) Please list the names of all the actors and the amount of movies that they've played
SELECT a.name, COUNT(m.id) AS movies FROM actorsInMovies aim JOIN actors a ON aim.actorid=a.id JOIN movies m ON aim.movieid=m.id GROUP BY a.name ORDER BY a.id;

--10) What movies have more than 6 actors in them?
SELECT m.movieName, COUNT(m.id) AS actors FROM actorsInMovies aim JOIN movies m ON aim.movieid=m.id GROUP BY m.id HAVING COUNT(*)>6 ORDER BY COUNT(*) DESC;

--11) What movies have less than or equal to 6 actors in them?
SELECT m.movieName, COUNT(m.id) AS actors FROM actorsInMovies aim JOIN movies m ON aim.movieid=m.id GROUP BY m.id HAVING COUNT(*)<=6 ORDER BY COUNT(*) DESC;


/*
### ANSWERS ARE HERE ###

1):
Iron Man 3
Thor: The Dark World

2):
Avengers: Infinity War  149

3):
Spider-Man Homecoming   133

4):
Iron Man 3
Avengers: Age of Ultron
Civil War
Spider-Man Homecoming
Avengers: Infinity War

5):
Avengers: Infinity War  12
Avengers: Age of Ultron 8
Civil War   7
Thor: Ragnarok  4
Iron Man 3  4
Thor: The Dark World    4
Spider-Man Homecoming   3
The Winter Soldier  3
Ant-Man 2
Doctor Strange  1
Black Panther   1

6):
Bucky   Civil War   2016
Hawkeye Civil War   2016
War Machine Civil War   2016
Captain America Civil War   2016
Black Widow Civil War   2016
Thunderbolt Civil War   2016
Iron Man    Civil War   2016
Thor    Doctor Strange  2016
Pepper  Spider-Man Homecoming   2017
Captain America Spider-Man Homecoming   2017
Iron Man    Spider-Man Homecoming   2017
Hulk    Thor: Ragnarok  2017
Loki    Thor: Ragnarok  2017
Odin    Thor: Ragnarok  2017
Thor    Thor: Ragnarok  2017
Bucky   Black Panther   2018
Hulk    Avengers: Infinity War  2018
Bucky   Avengers: Infinity War  2018
Loki    Avengers: Infinity War  2018
Pepper  Avengers: Infinity War  2018
War Machine Avengers: Infinity War  2018
Captain America Avengers: Infinity War  2018
Black Widow Avengers: Infinity War  2018
Thunderbolt Avengers: Infinity War  2018
Red Skull   Avengers: Infinity War  2018
Iron Man    Avengers: Infinity War  2018
Thanos  Avengers: Infinity War  2018
Thor    Avengers: Infinity War  2018

7):
Avengers: Infinity War  12
Civil War   7
Thor: Ragnarok  4
Spider-Man Homecoming   3
Black Panther   1
Doctor Strange  1

8):
Avengers: Age of Ultron 8
Iron Man 3  4
Thor: The Dark World    4
The Winter Soldier  3
Ant-Man 2

9):
Hulk    4
Bucky   5
Hawkeye 2
Loki    3
Odin    2
Pepper  3
War Machine 4
Captain America 7
Black Widow 4
Thunderbolt 2
Red Skull   1
Iron Man    5
Thanos  2
Thor    5

10):
Avengers: Infinity War  12
Avengers: Age of Ultron 8
Civil War   7

11):
Thor: Ragnarok  4
Iron Man 3  4
Thor: The Dark World    4
Spider-Man Homecoming   3
The Winter Soldier  3
Ant-Man 2
Doctor Strange  1
Black Panther   1
*/