Shows(Sid, title, synopsis, genre, language, dateOfRelease) Users(login, passwor

Question

Shows(Sid, title, synopsis, genre, language, dateOfRelease)

Users(login, password, country, DOB, name, email)

Review(Rid, dateAndTime, text, rating, Sid, login)

castAndCrew(Pid, name, DOB, placeOfBirth, Sid)

Episodes(Sid, Snum, duration, releaseDate, SeasonNum)

Roles(Pid, roles, character)

ReviewRating(Rid, login, rating)

ShowRating(Sid, login, rating)

ShowTags(Sid, tags)

Movies(Sid, rating, duration)

TVShows(Sid, status, network)

VideoBlogs(Sid, duration)

Review(Sid) references Shows(Sid)

Review(login) references Users(login)

castAndCrew(Sid) references Show(Sid)

Episodes(Sid) references Show(Sid)

Roles(Pid) references castAndCrew(Pid)

Roles(Sid) references Shows(Sid)

ReviewRating(Rid) references Review(Rid)

ReviewRating(login) references Users(login)

ShowRating(Sid) references Shows(Sid)

ShowRating(login) references Users(login)

ShowTags(Sid) references Shows(Sid)

Movies(Sid) references Shows(Sid)

TVShows(Sid) references Shows(Sid)

VideoBlogs(Sid) references Shows(Sid)

Write the following MySQL Queries:

a) Find users who have never written a review b) Create a ranked list of sci-fi movies in descending order of user ratings c) What is the average rating of TV shows from BBC e) What is the longest running TV show f) Find the maximum rating of video blogs more than 10 minutes long g) List all shows (title, synopsis, genre and release date) that have the tag magic h) Who is the highest ranked reviewer i) Which cast members have played multiple roles in shows Find all shows in which Emma Watson

Explanation / Answer

a)select Sid from Shows
where Sid not in
(select Sid from Review )

b)SELECT ROW_NUMBER() OVER(ORDER BY RATING DESC) AS RANK
FROM MOVIES M
INNER JOIN SHOWS S
ON M.Sid=S.Sid
where genre='Sci-Fi'

c)select avg(R.rating) from
TvShows T
inner join Shows S
on T.Sid=S.Sid
inner join ShowRating R
on T.Sid=R.Sid
where network='BBC'

d)select name,count(R.Rid) as Most_no_of_times
from Users U
inner join Review R
on U.login=R.login
group by u.name
order by Most_no_of_times desc

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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