What kind of join should I implement in order to receive the output I am trying to get?

NOTE: Just a practice problem.. NOT looking for free homework answers.

The practice problem I have asks to report the number of flights by plane’s year in ascending order of plane’s year. This requires the joining of two tables, the flights table and planes table. I believe the SQL should be relatively simple, and I think the main issue with mine is the vague select statement I currently have. I have looked at the different join methods, both explicit and implicit, and have also tried a left join with no luck.

If more table information is needed, I can share. The column the two tables share is year.

Also, very new here, so if there is something undesirable or incorrect about this post, please let me know.

select *,  
count(*) as n_flights  
from flights, planes 
where flights.year = planes.year 
order by planes.year asc 
;

The output I am looking for:

The output I get:

Answer

Presumably, there is a column in the flights table that refers to the primary key of the planes table – let me assume plane_id: this is what you would use to join the tables.

Then, you want to aggregate by plane year, using a group by clause, and count the number of rows in each group:

select p.year, count(*) as n_flights  
from flights f
inner join planes p on p.plane_id = f.plane_id
group by p.year 
order by p.year