Simple SQL help

Dist · 28 Oct 2011 at 17:02

I need a little help with an SQL exercise I'm doing, basically I have a small SQL database of movies, their ratings, the year they come out etc... and I have to add 25 years to any movie with an average rating of 4 or higher.

The names are quite straight forward, with mID being the movie ID and everything else is easily readable, but if needed I can copy the actual database (its tiny).

What i've wrote is:

update Movie
set year=year+25
where mID in
(select mID from Rating,
(select avg(stars) as avgstars
from Rating
group by mID)
where avgstars>=4)

but I can't work out how I'm doing the avg wrong, as I need to get the average for movies of the same mID (which is why I grouped by mID), but the result of running this adds 25 years to several more movies than it should, suggesting too many mIDs satisfy the condition avgstars>=4. so I must have done the avg command wrong.

The little web application that checks my results automatically performs a select * from Movie after any commands I enter, so I can't just do a step by step look at what I wrote and look at the results, as it always will just show me the entire movie table.

Any help on where I went wrong working out the average would be great

HungryHippos · 28 Oct 2011 at 17:24

Does this work?

Update Movie
Set Year = Year + 25
WHERE mID in (Select mID FROM Rating WHERE avg(stars) >= 4)

I'm also going to ask, are there multiple rows for each film? i.e. if one person rates a film one star and another 5 stars, the average should be 3 stars?

Dist · 28 Oct 2011 at 17:46

Eulogy said:
Does this work?

Update Movie
Set Year = Year + 25
WHERE mID in (Select mID FROM Rating WHERE avg(stars) >= 4)

I'm also going to ask, are there multiple rows for each film? i.e. if one person rates a film one star and another 5 stars, the average should be 3 stars?

When trying that I get "Query failed to execute: misuse of aggregate function avg()", SQLite perhaps doesn't like using avg() in WHERE. And yes there can be multiple rows for 1 film where it was rated multiple times, so I need to get the average stars for each film, and then see which of them is >= 4.

Dist · 28 Oct 2011 at 17:48

These are the tables if it helps

Movie
mID title year director
101 Gone with the Wind 1939 Victor Fleming
102 Star Wars 1977 George Lucas
103 The Sound of Music 1965 Robert Wise
104 E.T. 1982 Steven Spielberg
105 Titanic 1997 James Cameron
106 Snow White 1937 Null
107 Avatar 2009 James Cameron
108 Raiders of the Lost Ark 1981 Steven Spielberg

Reviewer
rID name
201 Sarah Martinez
202 Daniel Lewis
203 Brittany Harris
204 Mike Anderson
205 Chris Jackson
206 Elizabeth Thomas
207 James Cameron
208 Ashley White

Rating
rID mID stars ratingDate
201 101 2 2011-01-22
201 101 4 2011-01-27
202 106 4 Null
203 103 2 2011-01-20
203 108 4 2011-01-12
203 108 2 2011-01-30
204 101 3 2011-01-09
205 103 3 2011-01-27
205 104 2 2011-01-22
205 108 4 Null
206 107 3 2011-01-15
206 106 5 2011-01-19
207 107 5 2011-01-20
208 104 3 2011-01-02

shine · 28 Oct 2011 at 18:04

Update Movie Set Year = Year + 25 WHERE mID in (Select avg(mID) fROM Rating WHERE stars>= 4)

Try that.

HungryHippos · 28 Oct 2011 at 18:37

This should do it:

Code:

UPDATE Movie
SET Year = Year + 25
WHERE mID IN
	(
	SELECT mID FROM
			(
			SELECT
			AVG(Stars) AS [avgstars],
			mID
			FROM Rating
			GROUP BY mID
			) AS r
	WHERE r.avgstars >= 4
	)

Ciphon · 28 Oct 2011 at 18:38

Surely you want to use a HAVING clause?

UPDATE Movie
Set Year = Year + 25
WHERE mId IN
(
SELECT mId
FROM Rating
GROUP BY mId
HAVING AVG(stars) >= 4
)

Dist · 28 Oct 2011 at 18:51

Thanks for the help guys. That has got the correct result now. I'm slowly learning

On a slightly different thing, can any of you recommend a GUI of some sort for SQLite? The reason I'm using SQLite is that its a simple .exe that requires no installation (and some SQL implementations are complicated to install, and this is just an introduction to database that I'm doing). On its own SQLite is fine but requires me to create a file with the relations in, then load that file, then run another file that contains the commands etc... it would be nice is there was a good GUI that manages all that stuff behind the scenes and I just type what I want to run and can easily go back/reset stuff.