Reviewrelation, then form groups or partitions by each user ID. This allows us to evaluate the set of ratings for each individual
User. We're going to count how many
Ratings there are in each group (or for each user). Then it's going to check to see if that number is greater than two, and if it is, it's going to return the
uIDof that user, and also the number of
Ratings that user has given.
uIDof 207. He is the only person to have gone over his rating limit, and has rated 3 movies. Since he has broken the hypothetical contract, we are going to have to delete him from the database.
DELETE FROM <table> WHERE <condition>statement, and turned our previous query into a subquery in the
WHEREclause. The query is saying to return all the users who have rated more than 2 movies (just like before), then
DELETEthose users from the
uID207, or Darrel Sherman, from the
Reviewrelation. However, we would NOT delete him from the
Userrelation. In order to do that we would just have to change the relation to
User. Now some SQL database systems don't allow you to delete data if the subquery includes the same relation that you are deleting from, so it can get a little tricky depending on the database you are using.