Here is my database:
create table Movie (
mID smallint auto_increment primary key,
title varchar(30),
director varchar(20),
year int
)engine innodb;
create table Reviewer (
rID smallint auto_increment primary key,
name varchar(30)
)engine innodb;
create table Rating (
mID smallint,
rID smallint,
stars int,
ratingDate date,
constraint fk_movie foreign key (mID) references movie(mID),
constraint fk_reviewer foreign key (rID) references reviewer(rID)
)engine innodb;
insert into movie (title,director,year) values ('Lincoln','Steven Spielberg',2012),('An Unexpected Journey','Peter Jackson',2012),('Jurassic Park','Steven Spielberg',1993),('The Godfather','Francis Ford Coppola',1972), ('Fight Club','David Fincher',1999),('War Horse','Steven Spielberg',2011),('Rise of guardians','Peter Ramson',2012),('The Shawshank Redemption','Frank Darabont',1994),('Jaws','Steven Spielberg',1975),('Raging Bull','Martin Scorsese',1980);
insert into reviewer (name) values ('Bill Goodykoontz'),('David Germain'),('Christy Lemire'),('Richard Lawson'),('Marjorie Baumgarten');
insert into rating (mID, rID, stars, ratingDate) values (1,4,3,'2012-04-09'),(3,5,4,'2000-03-05'),(4,2,5,'1900-09-15'),(7,3,5,'2012-08-25'),(6,1,4,'2011-09-11'),(8,5,5,'1995-10-15'),(9,3,3,'1980-02-20');
Here's SQL Fiddle with all this data inserted.
I have to: "For each rating that is the lowest (fewest stars) currently in the database, return the reviewer name, movie title and the number of stars."
My code is:
SELECT name REVIEWER, title MOVIE, stars FROM rating
INNER JOIN movie
USING(mID)
INNER JOIN reviewer
USING(rID)
GROUP BY stars
HAVING stars = min(stars);
My Output is
REVIEWER MOVIE stars
Christy Lemire Jaws 3
Bill Goodykoontz War Horse 4
David Germain The Godfather 5
Ideal Output
REVIEWER MOVIE stars
Christy Lemire Jaws 3
Richard Lawson Lincoln 3
Someone kindly correct my code.
Aren't you looking for this:
SELECT name REVIEWER, title MOVIE, stars FROM rating
INNER JOIN movie USING(mID)
INNER JOIN reviewer USING(rID)
WHERE stars =
(SELECT min(stars) FROM rating);