Joins Coding Practice 4

 In this practice set, let's get the hold of SQL Joins operations using the following database.

Database: The database given is similar to IMDb, which consists data related to various movies, directors and actors.

The database is designed to cover the below business requirements. - A movie can have more than one actor casted and vice versa. - A movie can have more than one director and vice versa.

Note

You can assume that actors in the database have acted in at least one movie (in the database), where as some directors might not have the movies they directed(in the given database). So, do take care of this detail while writing various queries on the database, especially, when deciding on INNER JOIN / LEFT JOIN.

Junction Tables

movie_cast

is a junction table which stores the many-to-many relationship between

movie

and

actor

. And

role

of an

actor

for a

movie

is stored in the table. Similarly

movie_director

table stores the many-to-many relationship between

movie

and

director

Refer the tables in the code playground for a better understanding of the database.

1.

For all the movies, get the actor_ids of the cast.

Note:

• Sort the output in the ascending order of movie_name, and then in the ascending order of the actor_id.

Expected Output Format

movie_name	actor_id
...	...

2.

Get the number of movies in which "Daniel Radcliffe" has acted.

Expected Output Format

no_of_movies
...

3.

For each actor, get the number of movies in which they are casted.

Note:

• Sort the output in the ascending order of the 
  actor_name
  .

Expected Output Format

actor_name	no_of_movies
...	...

4.

Get the actors who acted in at least 5 movies.

Note:

• Sort the output in the ascending order of the 
  actor_name
  .

Expected Output Format

actor_name	no_of_movies
...	...

5.

For each director in the database, get the number of movies they have directed.

Note:

• If a director did not direct any movie (in the database), consider the count as 0.
• Sort the output in descending order of 
  no_of_movies
  , and then in the ascending order of 
  director_name
  .

Expected Output Format

director_name	no_of_movies
...	...

6.

Get all the ids of directors who directed at least two movies, with rating greater than 6

Sort the output in descending order of

no_of_movies

, and then in the ascending order of

director_id

Expected Output Format

director_id	no_of_movies
...	...

7.

Get all the director_ids who directed at least two movies that have a profit at least 50 crores.

Note:

• Profit is the difference between collection and budget of movies
• Sort the output in the descending order of
  no_of_movies_with_atleast_profit_50_cr
  and then in the ascending order of
  director_id
  .

Expected Output Format

director_id	no_of_movies_with_atleast_profit_50_cr
...	...

8.

Get all the director_ids who directed at least two movies and have an average profit greater than 50 crores.

Note:

• Profit is the difference between collection and budget of movies
• Sort the output in the descending order of
  no_of_movies
  and then in the ascending order of
  director_id
  .

Expected Output Format

director_id	no_of_movies	avg_profit
...	...	...

9.

Fetch the directors who directed at least two movies, and has an average rating (for all his/her movies) greater than 8

Note

• Sort the output in descending order of 
  no_of_movies
  , and then in the ascending order of 
  director_id

Expected Output Format

director_id	no_of_movies	avg_rating
...	...	...

10.

Get all the distinct actors who casted in any of the Harry Potter movies.

Note:

• Consider the movie names that contain "Harry Potter ".
• Sort the output in the ascending order of the 
  actor_name
  .

Expected Output Format

actor_name
...

11.

Get all the distinct directos who directed any of the Harry Potter movies.

Note:

• Consider the movie names that contain "Harry Potter ".
• Sort the output in the ascending order of the 
  director_name
  .

director_name
...