This SQL exercise is a part of a section dedicated for data analysts , providing data analysis tasks that require both business understanding and technical skills.

 

About this Exercise

YouMDB is an online movie recommendation resource. YouMDB provides movie ratings based on the opinions of its members. In this exercise you’ll analyze different movies, their ratings, and data related to the users providing the reviews.

  • movies – lists the various movies
  • ratings – contains the different ratings for each movie
  • members – contains information about the users providing the reviews

 

Diagram



 

Download

The database creation script can be downloaded using the following link

 

Exercises

The following exercises are constructed in ascending difficulty order. First ones are pretty easy, made for warm-up. The advanced exercises, on the other hand, can be quite challenging.

Basic Analysis

  1. How many members are living in Texas ?
  2. Display movies that blends at least 4 different genres
  3. Count the number of movies for each amount of genres :
     

     
  4.  

  5. Which movie ID has the highest amount of ratings ?

Advanced Analysis

  1. Out of the total number registered members, how many have actually left a movie rating ? Display the result as a percentage
  2. Which gender has left most ratings ?
  3. Display the number of members in New York, for the gender you retrieved in previous exercise
  4. Display the top-5 favorite Sci-Fi movies
  5. Which gender prefers to watch the Sci-Fi genre?
  6. Display all members who rated at least one of the movies rated by member 106
  7. List Tom Stewart’s friends
  8. List the games Tom Stewart’s friends are playing
  9. Create a distinct list of all games played by Tom Stewart and his friends
  10. List all 2nd degree friends of Jami Whatson

 

Solutions