If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Help me create a database query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,729
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2007
Location: Richmond, VA
Posts: 1,037
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.
Reply With Quote
Reply

Tags
database, database query, query

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On