State the relationships between Movies, Genres, MovieGenres, Actors, MovieDirecors and Directors to show

Hire a tutor for this answer

We have Professional Tutors available for all subjects, Unlock Your Potential with Personalized Tutoring

LiveChat / WhatsApp

Our expert tutors are available 24/7 to help you achieve your academic goals

Ask a New Question

We provide personalized tutoring and homework assistance services to help students of all levels succeed.

Question 1

State the relationships between Movies, Genres, MovieGenres, Actors, MovieDirecors and Directors to show the constraints imposed in the database.

SQL Questions: The manager of IMDB wants to have a better insight about the company business to support his future decisions. He requests to the IT department to provide answers for the following questions. You are asked to write SQL code to retrieve the information. Fill in the table with the result table and code.

  1. What is the range of movie release years that are recorded in this dataset?
    (Hint: List the Min and Max value of Movie.mov_yr_rel.)
  2. Who have directed movie(s) released in USA?? Display the director’s first name and last name and the movie title.
    (Hint: The information is stored in Director, Movie, MovieDirection.)
  3. How many distinct female actors have stared at least one movie made in 1997?
    (Hint: The information is stored in Movie, Actor, MovieCast. )
  4. Which adventure movies have been made in ‘UK’? Show movie titles.
    (Hint: The information is stored in Movie, Genres, and MovieGenres.)
  5. How many English Crime movies are in the database?
    (Hint: The information is stored in Movie, Genres and MovieGenres.)
  6. Display the average ratings and the total number of reviews of each movie language made per year since 1990. In each year, show the results in decending order of the average rating.
    (Hint: The information is stored in Movie and Rating. To obtain the desired result, you need to first order the data by mov_year. In cases where there are multiple entries for a given year, the entries should be ordered by their average rating in descending order.)
  7. What are the top 5 genres that have the highest average rating stars in the dataset?
    (Hint: The information is stored in Movie, Genres, MovieGeners, Rating)
  8. Which actors have starred movies made in both 1999 and 2004?
    ** Use ‘INTERSECT’
    (Hint: The information is stored in Movie, MovieCast, and Actor. You need to use ‘INTERSECT’ in your SQL query.)
  9. Show the language and genres of movies that has not been made since 1990 in the database
    ** Use EXCEPT
    (Hint: The information is stored in Movie, Genres and MovieGenres. You should use EXCEPT in your SQL query.)
  10. Display movies that have rating that is above the country average
    * Use temp table
    (The information is stored in Movie and Rating. You should use temp table in your SQL codes.
    1) Firstly find the average rating of each country and store the result in a temp table.
    2) Display movies that have its average rating higher than the country average in the temp table.)
  11. For each movie language, show the movies genres that have the highest ratings.
    *Use temp table
    (Hint: The information is stored in Movie, Genres, MovieGenres, and Rating. You should use temp table in your SQL codes.
    1) Firstly find the average rating of each language and genres in a temp table.
    2) For each language in the temp table, show the genres that has the highest rating)
  12. What is the most-popular genres for each release country in this database?
    (Hint: The information is stored in Movie, Genres, and MovieGenres. You can use Nested query or temp table in your SQL codes. i.e.
    1) First find the total number of movies of each country and genres.
    2) For each country, show the genres that has the highest rating in the temp table.)

Solution

Hire Expert Tutors

Get Professional Tutoring at Low Price in Australia


Professional

Tutoring Services

25,187+

Orders Delivered

4.9/5

5 Star Rating

621

PhD Experts

 

Amazing Features

Plagiarism Free

Top Quality

Best Price

On-Time Delivery

100% Money Back

24 x 7 Support

You may also like

TOP