Not so simple SQL queries

Question

So I got the tasks and there are some that I am still failing to solve even with the help of Google. Maybe someone could suggest some solutions (does it have to be done with JOINS or not?, etc.).

Here is the database:

CREATE TABLE Foto (
    ID int PRIMARY KEY,
    FileName varchar(100),
    FileSize int,
    Created int
) DEFAULT CHARSET=utf8;

CREATE TABLE Users (
    LoginName varchar(10) PRIMARY KEY,
    Surname varchar(20),
    Name varchar(20),
    Created date,
    LastLoginDate date
) DEFAULT CHARSET=utf8;

CREATE TABLE Scoring (
    ID int,
    LoginName varchar(10),
    Score int,
    ScoreDate date,
    FOREIGN KEY (LoginName) REFERENCES Users(LoginName),
    FOREIGN KEY (ID) REFERENCES Foto(ID),
    PRIMARY KEY(ID, LoginName)
) DEFAULT CHARSET=utf8;

The "Foto"(photo) and Users table should be pretty clear. The table "Scoring" is for users who rate the photos. Every user can rate each photo only one time. The task was to:

1. Write query that would return photo ID, FileName and AVG(Score) of all the photos which AVG score is higher than 9 and create index that would increase the performance of this query.

2. Write query that would return "LoginName", "Name", "Surname" and the AVG score that the user has given to photos he has rated for a specific user (for example, user with name "John"). Create index that would increase the performance of this query.

I am using MySQL 5.5.16 and my idea for the first query was something like:

SELECT F.ID, F.FileName, AVG(SC.Score)
FROM Foto F, scoring SC
WHERE F.ID = SC.ID
AND AVG(SC.Score) > 9
GROUP BY SC.ID;

And it returns "#1111 - Invalid use of group function", I never really liked aggregate functons :D

Overall, I have realized I suck in SQL and I would like to train to write queries more, but most of the tutorials I find on internet have pretty simple examples that doesn't help much in solving these problems. Maybe some of You can suggest me a resource that would have a bit more complex, ready database and tasks (that also have answers, if I get stuck) for writing queries?

Answer 1

WHERE clause is used for comparing values in the base table, whereas the HAVING clause can be used for filtering the results of aggregate functions in the result set of the query.

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

(1) You have to add HAVING to your query.

SELECT F.ID, F.FileName, AVG(SC.Score)
FROM   Foto F INNER JOIN scoring S ON F.ID = S.ID
GROUP BY F.ID, F.FileName 
HAVING AVG(S.Score) > 9

(2)

SELECT U.LoginName, U.Name, U.Surname, AVG(S.Score)
FROM Scoring S INNER JOIN Users U ON S.LoginName = U.LoginName
GROUP BY S.LoginName

SQLFiddle Demo

Hope that answer gives you better background for the next exam :)

Answer 2

One thing that might help is to learn to use explicit JOIN syntax (using an ON clause) instead of the old fashioned implicit syntax (using a WHERE clause`). That should help make things clearer for you in the future.

Your error is that you need to use a HAVING clause to subset the result of aggregate functions. You cannot use aggregate functions in a WHERE clause.

So, try this instead:

SELECT F.ID
     , F.FileName
     , AVG(SC.Score)
FROM   Foto F
JOIN   scoring SC
ON     F.ID = SC.ID
GROUP BY F.ID, F.FileName 
HAVING AVG(SC.Score) > 9

Good luck!

EDIT: The best place to learn about any database is from the documentation pages for the particular database you are using. In your case, look here.