Let us consider the following relational database.
Accounts (memberID, fName, IName, email, creditCardNo, password)
CreatesProfiles (memberID, name, gender, dob)
pGenres (memberID, name, genre)
Movies (movieID, title, yearOfRelease, lengthInMinutes, avgRating)
MGenres (movieID, genre)
Streams (movieID, memberID, name, rating, dateTimeWatched)
Directors (dID, fName, IName, dob, gender)
Directs (movieID, dID)
Actors (alD, fName, IName, gender)
Casts (movieID, alD, role)
The primary keys are underlined. The referential integrity constraints are as follows:
the column memberID of relation CreatesProfiles that references table Accounts,
the columns memberID and name of relation PGenres that references table CreatesProfiles,
the column movieID of relation MGenres that references table Movies,
the column movieID of relation Streams that references table Movies,
the columns memberID and name of relation Streams that references table CreatesProfiles,
the column movieID of relation Directs that references table Movies,
the column dID of relation Directs that references table Directors,
the column movieID of relation Casts that references table Movies, and
the column alD of relation Cast that references table Actors.
The following query is intended to list the member ID of all profiles that rate movies 6.0 or above. However, this query does not work properly. Briefly explain why:
and show the corrected SQL query
SELECT member ID
FROM Streams
WHERE rating >=6.0;
