MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE flight_connection (
  `flight number` INTEGER,
  `departure` VARCHAR(3),
  `arrival` VARCHAR(3)
);

INSERT INTO flight_connection
  (`flight number`, `departure`, `arrival`)
VALUES
  ('310', 'NUE', 'TXL'),
  ('926', 'FRA', 'NUE'),
  ('312', 'TXL', 'NUE');
CREATE TABLE airport (
  `code` VARCHAR(3),
  `description` VARCHAR(12)
);

INSERT INTO airport
  (`code`, `description`)
VALUES
  ('NUE', 'Nuremberg'),
  ('FRA', 'Frankfurt'),
  ('TXL', 'Berlin-Tegel');
SELECT `code`,`description`,IFNULL(departures,0) as departures,IFNULL(arrivals,0) as arrivals FROM airport a
LEFT JOIN (SELECT COUNT(*) arrivals,`arrival` FROM flight_connection GROUP BY `arrival`) f1 ON a.`code` = f1.`arrival` 
LEFT JOIN (SELECT COUNT(*) departures,`departure` FROM flight_connection GROUP BY `departure`) f2 ON a.`code` = f2.`departure`


code
description
departures
arrivals


NUE
Nuremberg
1
2

FRA
Frankfurt
1
0

TXL
Berlin-Tegel
1
1