Not so easy SQL queries

Question

So, I have tasks, and there are some that I still can’t solve even with the help of Google. Maybe someone can suggest some solutions (should this 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 table "Photo" (Photo) and "Users" should be quite understandable. The Scoring table is for users who rate photos. Each user can rate each photo only once. The task was as follows:

• Write a query that will return the photo ID, file_name and AVG (rating) of all photos whose AVG rating exceeds 9, and create an index that will increase the performance of this request.

• Write a query that will return "LoginName", "Name", "Surname" and the AVG rating that the user gave to the photos that he rated for a specific user (for example, a user named "John"). Create an index that will increase the performance of this query.

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

 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 a group function", I never liked aggregate functions: D

In general, I realized that sucking SQL, and I would like to train to write more queries, but most of the tutorials that I find on the Internet have fairly simple examples that do not help in solving these problems. Perhaps some of you can offer me a resource that will have a more complex, ready-made database and tasks (which also have answers if I'm stuck) for writing queries?

Answer 1

The WHERE clause is used to compare values ​​in the base table, while the HAVING clause can be used to filter the results of a function set in the result set of a query.

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

(1) You must add HAVING to your request.

 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 the answer will give you the best experience for your next exam :)

Answer 2

One thing that can help is to learn how to use the explicit JOIN syntax (using the ON clause) instead of the old-fashioned implicit syntax (using the WHERE ). This should help make you clearer for you in the future.

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

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 study any database is the documentation pages for the specific database that you are using. In your case, see here .

Answer 3

Everything in the element must be in the GROUP BY function or aggregate.

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

Any column that you intend to enter or search can benefit from the index.