Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Question Help me create a database query

    Hello Guys,
    I have been trying to work on a DB query but it doesnt give me proper result.

    The statement for which I am trying to create a query is

    For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer's name and the title of the movie.

    The schema of the tables are as follows
    Movie ( mID, title, year, director )
    English: There is a movie with ID number mID, a title, a release year, and a director.

    Reviewer ( rID, name )
    English: The reviewer with ID number rID has a certain name.

    Rating ( rID, mID, stars, ratingDate )
    English: The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate.

    I tried the following query
    select name,title from (reviewer natural join rating natural join movie) group by name,title having count(*)=2;

    But it gave me incorrect results as I failed to implement this condition "gave it a higher rating the second time".
    Though I understand that the check could be done by using the ratingDate and stars, I still fail to understand how to do that.

    Could you please help me in this regard. Your help is highly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,971
    Is this a "pure theory" relational algebra question or will you need to actually do this assignment using a database engine? In other words, is this something that you just need the RA for, or will you have to implement the solution using an existing database engine? The reason that I'm asking this is that the RA answer does things one way, but the real world may use a simpler (but different) method that a boss would love but a teacher might not.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,122
    you would have to join to your rating table a second time looking for the reviewer and movie to be the same and the date and rating greater than the prior.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •