Some Queries are Listed Below

Query 1

SQL Query to compute a report which shows the year when most of the Action movies produces, and number of such movies and their average rating

SELECT MOV_YEAR, count(GENRE_ID), avg(RATINGS) FROM MoviesData NATURAL JOIN Movie_Genres NATURAL JOIN Genres NATURAL JOIN Performance WHERE GENRE_TITLE='Action' GROUP BY mov_year,GENRE_ID ORDER BY MOV_YEAR; Copy Run

Query 2

SQL query to compute the average time and count number of movies for each genre. Return genre title, average time and number of movies for each genre.

SELECT GENRE_TITLE, AVG(MOV_DURATION), COUNT(GENRE_TITLE) FROM MoviesData NATURAL JOIN Movie_Genres NATURAL JOIN Genres GROUP BY GENRE_TITLE; Copy Run

Query 3

SQL query to find the movies released in last 2 years. Sort the result-set in descending order by date of release. Return movie title, release year, duration, and name

SELECT mov_title, mov_year, mov_duration, dir_name FROM MoviesData JOIN movie_direction ON MoviesData.mov_id = movie_direction.mov_id JOIN directors ON movie_direction.dir_id=directors.dir_id WHERE mov_year>=2020 ORDER BY mov_year desc; Copy Run

Query 4

SQL query to find the Mystery Movies rated greater than 5. Return the title, year, and rating.

SELECT m.MOV_ID, m.MOV_TITLE, p.RATINGS FROM MoviesData m, Performance p, Genres g WHERE m.Mov_Genre_ID= g.Genre_ID AND g.GENRE_TITLE="Mystery" AND p.RATINGS>=5 AND p.Mov_ID= m.Mov_ID ORDER BY m.MOV_TITLE; Copy Run

Query 5

SQL query to find the directors with number of genres movies. Group the result set on director name and generic title. Sort the result-set in ascending order by director first name and last name. Return director name and number of genres movies.

SELECT DIR_NAME, GENRE_TITLE, count(GENRE_TITLE) FROM Directors NATURAL JOIN Movie_Direction NATURAL JOIN Movie_Genres NATURAL JOIN Genres GROUP BY DIR_NAME, GENRE_TITLE ORDER BY DIR_NAME; Copy Run

Query 6

SQL query to find the Directors who have directed in any movie between 2005 and 2010 (Begin and end values are included). Return director name, movie title and release year.

SELECT dir_name, mov_title, mov_year FROM moviesdata, Directors JOIN moviecast ON directors.dir_id=moviecast.dir_id WHERE MovieCast.CAST_id=moviesdata.MOV_CAST_id AND mov_year BETWEEN 2005 and 2010; Copy Run

Query 7

SQL query to find who directed a movie that casted a role as ‘TomCruise’. Return director name, movie title and movie year.

SELECT dir_name, mov_title FROM directors JOIN movie_direction ON directors.dir_id=movie_direction.dir_id JOIN moviesdata ON movie_direction.mov_id=moviesdata.mov_id JOIN MovieCast ON MovieCast.cast_id=moviesdata.mov_cast_id WHERE act1_name='Tom Cruise'; Copy Run

Query 8

SQL Query to find Block Buster film of last 15 years

SELECT p.MOV_ID, m.MOV_TITLE, m.MOV_YEAR, MAX(p.Revenue) FROM Performance p, MoviesData m WHERE m.MOV_ID=p.MOV_ID GROUP BY m.MOV_YEAR ORDER BY m.MOV_YEAR; Copy Run

Query 9

SQL Query to find movie title and release year using IN operator

SELECT MOV_TITLE, MOV_YEAR FROM MoviesData JOIN Genres ON MoviesData.MOV_GENRE_ID=Genres.GENRE_ID WHERE GENRE_TITLE IN ('War', 'Sci-Fi', 'Sport', 'Documentary', 'Musical'); Copy Run

Query 10

SQL Query to find total capital generated by movies in each year

SELECT m.MOV_YEAR, SUM(p.REVENUE) AS TotalEarning FROM Performance p, MoviesData m WHERE m.MOV_ID=p.MOV_ID GROUP BY m.MOV_YEAR ORDER BY m.MOV_YEAR; Copy Run