FileToUseToCopyIntoTerm

/* select all movies from my birthyear */
SELECT name FROM movies WHERE year=1971;

/* How many movies does our dataset have for the year 1982? */


-- Find actors who have "stack" in their last name.
-- WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
SELECT * FROM actors LIKE '%stack%'
SELECT * FROM actors WHERE last_name LIKE '%stack%';



/* Fame Name Game

We all want our kids to be actors (...right), so what's the best first name and last name to give them? What are the 10 most popular first names and last names in the business? And how many actors have each given first or last name? This can be multiple queries.
*/

-- by first name

SELECT *, COUNT(first_name) FROM actors GROUP BY first_name ORDER BY COUNT(first_name) DESC LIMIT 10;


-- by last name

SELECT *, COUNT(last_name) FROM actors GROUP BY last_name ORDER BY COUNT(last_name) DESC LIMIT 10;


-- by full name

SELECT first_name, FROM actors INNER JOIN last_name ORDER BY COUNT(first_name) DESC LIMIT 10;


/* Prolific

List the top 100 most active actors and the number of roles they have starred in.

SELECT first_name, last_name, COUNT(*) AS prolific
FROM roles
INNER JOIN actors.id = roles.actor_id
GROUP BY actors.id
ORDER BY prolific
LIMIT 100;


*/


SELECT first_name, last_name FROM actors INNER JOIN roles ON actors.id = roles.actor_id GROUP BY actors.id ORDER BY COUNT(actors.id) DESC LIMIT 100;


-- Bottom of the Barrel

-- How many movies does IMDB have of each genre, ordered by least popular genre?

-- SELECT name FROM movies INNER JOIN movies_genres ON movies.id = movie_id GROUP BY genre ORDER BY COUNT(genre) DESC LIMIT 10;

-- SELECT movie_id, genre FROM movies_genres INNER JOIN movies_genres ON movies.id = movie_id;

SELECT genre FROM movies_genres INNER JOIN movies ON movies.id = movie_id GROUP BY genre ORDER BY COUNT(genre) ASC;


-- Braveheart

-- List the first and last names of all the actors who played in the 1995 movie 'Braveheart', arranged alphabetically by last name.


SELECT first_name, last_name FROM actors INNER JOIN roles ON actors.id = roles.actor_id INNER JOIN movies ON movies.id = movie_id WHERE movies.name = 'Braveheart' AND movies.year=1995 ORDER BY last_name ASC;


-- Leap Noir

-- List all the directors who directed a 'Film-Noir'-genre movie in a leap year (for the sake of this challenge, pretend that all years divisible by 4 are leap years — which is not true in real life). Your query should return director name, the movie name, and the year, sorted by movie name.