Assessment 1 — quiz 2 questions
Database retrieval using SQL
We will make use of the data contained in the imdb_ijs schema within the gateway.database.windows.net. Please refer to the Week 1 content for instructions on how to access the server via DBeaver if you have not accessed it already.
Start by exploring the contents of the tables and understanding the meaning of the attributes of each table. You will need to connect to a database on the MS SQL Server with the following connection strings:
Server=tcp: gateway.database.windows.net,1433; Database=bus5dwr
User ID=<StudentId>;Password=<Password>
The ER diagram of the imdb_ijs is presented in the figure below. Please note that DBeaver uses different notations to represent the associations between entities (tables).
Question 1 [2 mark]
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.
| Question | Returned Table (Screen shot) | Code (plain text in MySQL ) | Mark | |
| a. | 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.) |
1 | ||
| b. | 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.) |
1 | ||
| c. | How many distinct female actors have stared at least one movie made in 1997? | 1 |
| (Hint: The information is stored in Movie, Actor, MovieCast.) | ||||
| d. | Which adventure movies have been made in ‘UK’? Show movie titles.
(Hint: The information is stored in Movie, Genres, and MovieGenres.) |
1 | ||
| e. | How many English Crime movies are in the database?
(Hint: The information is stored in Movie, Genres and MovieGenres.) |
1 | ||
| f. | 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.) |
1 | ||
| g. | 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) |
1 | ||
| h. | 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.) |
2 | ||
| i. | 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 |
2 |
| and MovieGenres. You should use EXCEPT in your SQL query.) | ||||
| j. | 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.) |
2 | ||
| k. | 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) |
2 | ||
| l. | 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.) |
3 |
Total: 20 marks
Our Academic Assistance: service is all about doing research and being good at it. The more research one will do, the better the paper will turn out.
Expert's Answer
Chat with our Experts
Want to contact us directly? No Problem. We are always here for you
Your future, our responsibilty submit your task on time.
Order Now
Get Online
Assignment Help Services
