-- select 	[distinct] select-list
-- from 	from-list
-- where 	qualification
-- group by grouping-list
-- having	group-qualification

-- Find the age of the youngest sailor for each rating level
select s.rating, min(s.age)
from sailors s
group by s.rating;

-- Find the age of the youngest sailor who is eligible to vote (i.e. is at least 18 years old)
-- for each rating level with at least  two such sailors
select s.rating, min(s.age) as minage
from sailors s
where s.age >= 18
group by s.rating
having count(*) > 1;

-- For each red boat, find the number of reservation for this boat
select b.bid, count(*) as reservationcount
from boats b, reserves r
where r.bid = b.bid and b.color = 'red'
group by b.bid;

select b.bid, count(*) as reservationcount
from boats b, reserves r
where r.bid = b.bid
group by b.bid
having b.color = 'red'; -- illegal

-- Find the average age of sailors for each rating level that has atleast two sailors
select s.rating, avg(s.age) as avage
from sailors s
group by s.rating
having count(*) > 1;

select s.rating, avg(s.age) as avgage
from sailors s
group by s.rating
having 1 < (
	select count(*)
    from sailors s2
    where s.rating = s2.rating
);

-- Find those ratings for which the average age of sailors is the minimum over all ratings
select temp.rating, temp.avgage
from (
	select s.rating, avg(s.age) as avgage
    from sailors s
    group by s.rating
) as temp
where temp.avgage = (select min(temp.avgage) from temp);
-- not allowed in mysql

select temp.rating, min(temp.avgage)
from (
	select s.rating, avg(s.age) as avgage
    from sailors s
    group by s.rating
) as temp
group by temp.rating;