Query complex in Oracle SQL

Question

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.

I see it very complicated or I'm a bit saturated from so much spinning, I need some help.

Answer 1

Reading the task you got, it seems that you're quite close to the solution. Have a look at the following query and its explanation, note the differences from your query, see if it helps.

select p.gender,
       ((sysdate - p.birthday) / 365) age,
       s.score
from person p join athlete a on a.idathlete = p.idperson       
              left join score s on s.idathlete = a.idathlete
              left join competition c on c.idcompetition = s.idcompetition
where p.gender = 'F'
  and s.score in ('Gold', 'Silver', 'Bronze')
  and c.idsport = 18
order by age;

• when two dates are subtracted, the result is number of days. Dividing it by 365, you - roughly - get number of years (as each year has 365 days - that's for simplicity, of course, as not all years have that many days (hint: leap years)). The result is usually a decimal number, e.g. 23.912874918724. In order to avoid that, you were told to remove decimals, so - use TRUNC and get 23 as the result

• although data model contains 5 tables, you don't have to use all of them in a query. Maybe the best approach is to go step-by-step. The first one would be to simply select all female athletes and calculate their age:

  select p.gender,
         ((sysdate - p.birthday) / 365 age
  from person p
  where p.gender = 'F'
  

  Note that I've used a table alias - I'd suggest you to use them too, as they make queries easier to read (table names can have really long names which don't help in readability). Also, always use table aliases to avoid confusion (which column belongs to which table)

• Once you're satisfied with that result, move on to another table - athlete It is here just as a joining mechanism with the score table that contains ... well, scores. Note that I've used outer join for the score table because not all athletes have won the medal. I presume that this is what the task you've been given says:

  ... even when there is no data of any specific value for the set of records displayed by the query.

• It is suggested that we - as developers - use explicit table joins which let you to see all joins separated from filters (which should be part of the WHERE clause). So:

  NO : from person p, athlete a
       where a.idathlete = p.idperson
         and p.gender = 'F'
  
  YES: from person p join athlete a on a.idathlete = p.idperson
       where p.gender = 'F'
  

• Then move to yet another table, and so forth.

• Test frequently, all the time - don't skip steps. Move on to another one only when you're sure that the previous step's result is correct, as - in most cases - it won't automagically fix itself.