Problem Statement

The problem statement is:

• You are given a list of movies and their ratings from IMDb, a popular movie database website.

• You need to write a program that Picks movies with

  • Genre starts with C

  • Released in 2014

  • Budget bigger than 40 Million

Using SQL

https://github.com/virajmilinda/sql_problems.git

Dataset

CodingNinja offers us the .sql file to download. it has 3 tables.

IMDB

select *
from IMDB

Genre

SELECT  *
from genre

Earning

SELECT  *
from earning

Tools

CodingNinja has an in-built coding environment, but I used Azure Data Studio.

Azure Data Studio | Microsoft Azure

Code

• Budget bigger than 40 Million.

/* Condition 1*/

select *
from IMDB

where Budget > 40000000
ORDER BY CAST(IMDB.Movie_id as VARCHAR(Max)) ASC

for this purpose Budget > 40000000 condition is enough.

• Genre starts with C

Genre table

/* Condition 2*/

SELECT  *
from genre
where genre LIKE 'C%'
ORDER BY CAST(genre.Movie_id as VARCHAR(Max)) ASC

• Released in 2014

This was a tricky one for me. There is no column for release year in any of the above tables.

But the title column has released the year at the end of each title. It needs to be sliced and trimmed to extract the released year.

Then the movies released in 2014 need to be filtered.

Here's the code.

/* Condition 4*/


select Movie_id,  
CONVERT(INT, left(  RIGHT (CAST( Title as VARCHAR(Max)) ,5),4) )   as year
from IMDB 

WHERE 
CONVERT(INT, left(  RIGHT (CAST( IMDB.Title as VARCHAR(Max)) ,5),4) ) = 2014

Here's the result.

/* Inner Join*/

/* Budget bigger than 40 Million */

SELECT   DISTINCT CAST(Movie_id1 AS nvarchar(max)) as Movie_id4 , CAST(Title AS nvarchar(max)) as Title, Budget, year
FROM (  

select Movie_id as Movie_id1 , Title, Budget
from IMDB
where Budget > 40000000

) as table1

INNER JOIN (

/* Genre starts with C */
SELECT  Movie_id as Movie_id2, genre
from genre
where genre LIKE 'C%'

) as table2 ON table1.Movie_id1 = table2.Movie_id2

INNER JOIN (

/* Released in 2014 */
select 
Movie_id as Movie_id3,CONVERT(INT, left(RIGHT(CAST( Title as VARCHAR(Max)) ,5),4) )   
as year
from IMDB 
WHERE 
CONVERT(INT, left(  RIGHT (CAST( IMDB.Title as VARCHAR(Max)) ,5),4) ) = 2014

) as table3 ON table2.Movie_id2 = table3.Movie_id3

Here's the result.