Before anyone asks, yes, this is part of the homework, however I tried and looked for similar solutions, but no one worked.
It is also important to note that this must be done on the MySQL server, so any special syntax should be considered.
To do this, you will need four tables:
person (idperson INT, firstName VARCHAR(45)) beer (idbeer INT, name VARCHAR(45)) purchase(idpurchase INT, idperson INT) beerxpurchase(idpurchase INT, idbeer INT, quantity INT)
If each of the first columns is a primary key, except beerxpurchase , which is a compiled key from the first and second columns, and all other columns that use names are a foreign key.
In the end, I need to make a request that returns each individual with the beer identifier that they bought most in all of their purchases, and the total amount of the beer they bought.
Request, for example:
SELECT person.firstName NAME, beerxpurchase.idBeer BEER, SUM(beerxpurchase.quantity) TOTAL FROM person INNER JOIN purchase on person.idperson = purchase.idperson INNER JOIN beerxpurchase ON beerxpurchase.idpurchase = purchase.idpurchase GROUP BY person.firstName, beerxpurchase.idbeer;
It will return the total amount of beer purchased for each specific beer code per person, something like this:
NAME BEER TOTAL A 1 5 A 2 23 A 3 3 A 4 19 A 5 7 B 1 11 B 2 3 B 3 3 B 4 4 B 5 5 C 3 5 D 1 8 D 2 4 D 4 1 D 5 10 E 1 9 E 4 10
This is what I consider to be a good start, however, from this request I should get only the tuple that has the maximum number for each user. It sounds like GROUP BY NAME , however, something like:
SELECT preferenence.FIRST_NAME, preferenence.ID_BEER, MAX(preferenence.TOTAL_BOUGHT) FROM ( SELECT person.firstName FIRST_NAME, beerxpurchase.idBeer ID_BEER, SUM(beerxpurchase.quantity) TOTAL_BOUGHT FROM person INNER JOIN purchase on person.idperson = purchase.idperson INNER JOIN beerxpurchase ON beerxpurchase.idpurchase = purchase.idpurchase GROUP BY person.firstName, beerxpurchase.idbeer ) preferenence GROUP BY preferenence.FIRST_NAME ORDER BY MAX(preferenence.TOTAL_BOUGHT) DESC;
Loses the correct beer identifier, but retains the correct person’s name and the total amount of beer bought, something like this:
FIRST_NAME ID_BEER TOTAL_BOUGHT A 1 23 B 1 11 D 1 10 E 1 10 C 3 5
From what I read, most DBMSs will not track ID_BEER, because this is not part of GROUP (as I understand it). However, changing the last group to GROUP BY preference.FIRST_NAME, preference.ID_BEER will return the same tuples from the internal subquery.
So the question is: from this subquery, how could one get the whole tuple associated with this particular maximum value? Or in case you can offer the best solution for a subquery or query in general, this is more than welcome.
All that is not entirely clear or you need additional information about the tables and their relationships, let me know.