Movies Title Year Runtime Rating Django Unchained 2012 165 8.5 Goodfellas 1990 1

Question

Movies Title Year Runtime Rating Django Unchained 2012 165 8.5 Goodfellas 1990 146 8.8 Pulp Fiction 1994 154 9 The Departed 2006 151 8.5 Exercise 4 (Indexing, 4 pts) Count the costs of operations and find an optimal selection of indexes for the Movies table in Movie Database. Operations insertion l (70%): INSERT INTO Movies VALUES (A,B,C,D) Update U (30%): UPDATE Movies SET Rating-X WHERE Title-Y Assumption about data Data for the Movies table is stored in 10 blocks. . The Title attribute is a key for the Movies table. Any index for the Movies table is stored in 1 block. Two cases to analyze 1. 2. No indexes. Index on Title

Explanation / Answer

Answer:

        For each movie, go back the name and the 'rating spread', that is, the dissimilarity flanked by highest and lowest ratings given to that movie.

Sort by rating spread from highest to lowest, then by movie title.

Select movie.title, max(stars)-min(stars) as spread

From rating join movie on rating.mid=movie.mid

Group by rating.mid

Order by spread desc, title

Find the dissimilarity between the standard rating of movies free before 1980 and the average rating of movies released after 1980.

(Make sure to calculate the average rating for each movie, then the average of those averages for movies before 1980 and movies after. Don't just calculate the overall average rating before and after 1980.)

Select max(a1)-min(a1) from

(Select avg(av1) a1 from

(Select avg(stars) av1 from rating r join movie m on r.mid=m.mid where m.year < 1980

Group by r.mid)

Union
Select avg(av2) a1 from
(Select avg(stars) av2 from score r join movie m on r.mid=m.mid where m.year > 1980
Group by r.mid))
Some directors directed extra than one movie. For all such directors, go back the title of all cinema directed by them, along with the boss name.
Sort by director name, then film title. (As an extra challenge, try script the query both with and without COUNT.)

Select title, director

From movie
Where director in (select director
From (select director, count(title) as s
From movie
Group by director) as t
Where t.s>1)
Order by 2,1
Find the movie(s) with the highest average rating.
Return the movie title(s) and average rating.
(Hint: This query is more difficult to write in SQLite than other systems; you might think of it as finding the highest average rating and then choosing the movie(s) with that average rating.)

select m.title, avg(r.stars) as strs from rating r
join movie m on m.mid = r.mid group by r.mid
having strs = (select max(s.stars) as stars from (select mid, avg(stars) as stars from rating
group by mid) as s)
Find the movie(s) with the lowest average rating.
Return the movie title(s) and average rating.
(Hint: This query may be more difficult to write in SQLite than other systems; you might think of it as finding the highest average rating and then choosing the movie(s) with that average rating.)

select m.title, avg(r.stars) as strs from rating r
Join movie m on m.mid = r.mid group by r.mid
having strs = (select min (s.stars) as stars from (select mid, avg(stars) as stars from rating
group by mid) as s)
For each director, return the director's name jointly with the title(s) of the movie(s) they directed that conventional the uppermost rating among all of their movies, and the value of that rating.

Ignore movies whose director is NULL.

select director, title, stars

from movie m, rating r

where m.mid = r.mid and director is not null

group by director

order by stars desc                                                                                            

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.