select b.name as "Band Name",s."Number of songs" from bands as b inner join(select a.band_id as "id",count(s.length) as "Number of songs" from albums as a inner join songs as s on a.id=s.album_id group by a.band_id) as s on b.id=s.id;

Please try with the query given inside the inner join first and then do the rest for easy understanding from beginner point of view