SQL practice project analyzing a movie dataset to extract key insights.
-Find all movies released between 2015 and 2020.
Select title, genre, year FROM movies_sql_practice_dataset WHERE year BETWEEN 2015 AND 2020 ORDER BY year;
-- This allowed me to see the importance of the 'BETWEEN' function --
-Find all movies with genres Action, Drama, or Sci-Fi.
SELECT title, genre, rating FROM movies_sql_practice_dataset WHERE genre IN ('Action', 'Drama', 'Sci-Fi') ORDER BY genre, rating DESC;
--This shows the importance of the IN function. Instead of having to separately type the genre over and over this saves you time and makes it easy to sort through multiple categories.
-Show all movies whose title contains the word “Love”.
SELECT title, genre, year, rating FROM movies WHERE title LIKE '%Love%' ORDER BY rating DESC;
-List movies where genre = 'Comedy' and rating > 7.5.
SELECT title, rating, genre FROM movies_sql_practice_dataset WHERE genre = 'Comedy' AND rating > 7.5
--This questioned allowed me to sort to a specific type of movie which can be helpful for finding specific data. This applies to all data sets. Learning to find specific data is very important.
-Show all movies that are not Horror and box office > 100 million.
SELECT title, genre, box_office FROM movies_sql_practice_dataset WHERE genre <> 'Horror' AND box_office > '100000000';
-- Learning the '<>' symbol was very key in making this happen or I would've had to type every genre and I would have to make sure it's all spelled correctly. But knowing the not equal symbol helps with that.
-Find all movies with rating between 6 and 8, released after 2012.
SELECT rating, year, title FROM movies_sql_practice_dataset WHERE rating BETWEEN 6 AND 8 AND year > 2012 ORDER BY rating DESC;
-- The between function is a great short cut for this or I would have to spend more time sifting through
-List all movies with “War” or “Star” in the title.
SELECT* FROM movies_sql_practice_dataset WHERE title like'%WAR%' OR title LIKE'%STAR%';
-- The LIKE function allows me to find things in the data set without having to type them the exact same way.
-Find all Action or Adventure movies from 2010–2020 where the title includes “Battle”, sorted by box office.
SELECT title, genre, year, box_office FROM movies_sql_practice_dataset WHERE genre IN ('Action', 'Adventure') AND year BETWEEN 2010 AND 2020 AND title LIKE '%Battle%' ORDER BY box_office DESC;
-- This question was a great example of multiple layers to an SQL search. I was able to use the IN, AND, BETWEEN, and LIKE functions all in one. This is very helpful because it allows me to search more precisely.
Movies_SQL_Practice_Dataset.csv movie_id,title,year,genre,box_office,rating 1,Movie 1,2007,Adventure,398257942,7.7 2,Movie 2,2004,Sci-Fi,12888647,6.6 3,Movie 3,2001,Adventure,142907595,6.2 4,Movie 4,2015,Action,494765857,6.2 5,Movie 5,2023,Drama,252134185,7.9 6,Movie 6,2010,Action,418440424,5.5 7,Movie 7,2012,Comedy,280875408,8.9 8,Movie 8,2006,Drama,3215863,6.9 9,Movie 9,2002,Comedy,440697445,7.1 10,Movie 10,2019,Horror,139187345,6.9 11,Movie 11,2014,Horror,401165773,9.3 12,Movie 12,2000,Sci-Fi,238380547,6.1 13,Movie 13,2001,Adventure,36024126,8.9 14,Movie 14,2005,Action,154756564,7.6 15,Movie 15,2016,Comedy,184211344,6.4 16,Movie 16,2021,Action,58651795,4.1 17,Movie 17,2019,Drama,243796367,6.3 18,Movie 18,2019,Comedy,334452913,6.9 19,Movie 19,2004,Action,400146208,5.5 20,Movie 20,2013,Sci-Fi,161182683,7.2 21,Movie 21,2000,Action,290386572,7.5 22,Movie 22,2011,Drama,155692899,9.2 23,Movie 23,2023,Horror,454362168,6.6 24,Movie 24,2002,Comedy,48912936,8.9 25,Movie 25,2000,Horror,387016970,6.4 26,Movie 26,2010,Horror,318016484,4.6 27,Movie 27,2008,Drama,86127828,8.3 28,Movie 28,2012,Horror,377486451,9.1 29,Movie 29,2012,Comedy,432149674,7.8 30,Movie 30,2007,Adventure,178553934,4.1 31,Movie 31,2012,Drama,443068866,8.6 32,Movie 32,2007,Sci-Fi,495550349,4.5 33,Movie 33,2013,Sci-Fi,297571091,7.2 34,Movie 34,2017,Horror,128991109,9.1 35,Movie 35,2019,Adventure,327600496,5.2 36,Movie 36,2008,Sci-Fi,498386972,7.3 37,Movie 37,2018,Action,263724688,8.0 38,Movie 38,2016,Action,306095428,6.8 39,Movie 39,2004,Comedy,20082350,5.5 40,Movie 40,2006,Horror,124423556,7.3 41,Movie 41,2013,Sci-Fi,210567202,8.8 42,Movie 42,2019,Comedy,236481440,4.3 43,Movie 43,2021,Comedy,412429245,6.2 44,Movie 44,2007,Horror,281591397,7.8 45,Movie 45,2020,Drama,194313404,8.8 46,Movie 46,2012,Comedy,483806783,4.9 47,Movie 47,2016,Adventure,492582428,7.1 48,Movie 48,2021,Drama,243643777,9.0 49,Movie 49,2013,Adventure,73860603,7.2 50,Movie 50,2012,Action,373132911,7.4