I have the following tables and their fields
They ask me for a query that seems to me quite complex, I have been going around for two days and trying things, it says:
It is desired to obtain the average age of female athletes, medal winners (gold, silver or bronze), for the different modalities of 'Artistic Gymnastics'. Analyze the possible contents of the result field in order to return only the expected values, even when there is no data of any specific value for the set of records displayed by the query. Specifically, we want to show the gender indicator of the athletes, the medal obtained, and the average age of these athletes. The age will be calculated by subtracting from the system date (SYSDATE), the date of birth of the athlete, dividing said value by 365. In order to avoid showing decimals, truncate (TRUNC) the result of the calculation of age. Order the results by the average age of the athletes.
Well right now I have this:
select person.gender,score.score from person,athlete,score,competition,sport where person.idperson = athlete.idathlete and athlete.idathlete= score.idathlete and competition.idsport = sport.idsport and person.gender='F' and competition.idsport=18 and score.score in ('Gold','Silver','Bronze') group by person.gender, score.score;
And I got this out
By adding the person.birthdate field instead of leaving 18 records of the 18 people who have a medal, I'm going to many more records.
Apart from that, I still have to draw the average age with SYSDATE and TRUNC that I try in many ways but I do not get it.
On the other hand I can not get the average of the ages but not with a simple query like this
select
trunc( avg( sysdate - to_date( person.birthdate, 'DD/MM/YYYY') ) / 365 ) trunc_diff_years
from person
give me this error
ORA-01839: date not valid for month specified
01839. 00000 - "date not valid for month specified"
Something light?