-- find athlete who was born before 1980-01-01
select * from games.athlete where dob < '1980-01-01';

-- for each athlete, find the total number of competitions he / she has participated
select aid, count(*) from games.results group by aid;

-- for each athlete, find the highest rank he / she has obtained.
select A.aid, A.aname, min(rank) 
from games.athlete A, games.results R 
where A.aid = R.aid
group by A.aid, A.aname;

-- (a wrong query) for each athlete, find the highest rank he / she has obtained.
select A.aid, A.aname, min(rank) 
from games.athlete A, games.results R 
where A.aid = R.aid
group by A.aid;