Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE cities ( name ) AS
SELECT 'Paris'     FROM DUAL UNION ALL
SELECT 'Berlin'    FROM DUAL UNION ALL
SELECT 'Rome'      FROM DUAL UNION ALL
SELECT 'London'    FROM DUAL UNION ALL
SELECT 'Amsterdam' FROM DUAL UNION ALL
SELECT 'Beijing'   FROM DUAL UNION ALL
SELECT 'Tokyo'     FROM DUAL
7 rows affected

CREATE TABLE worker ( id, city ) AS
SELECT  1, 'Paris'     FROM DUAL UNION ALL
SELECT  2, 'Paris'     FROM DUAL UNION ALL
SELECT  3, 'Paris'     FROM DUAL UNION ALL
SELECT  4, 'Berlin'    FROM DUAL UNION ALL
SELECT  5, 'Rome'      FROM DUAL UNION ALL
SELECT  6, 'Amsterdam' FROM DUAL UNION ALL
SELECT  7, 'Amsterdam' FROM DUAL UNION ALL
SELECT  8, 'Beijing'   FROM DUAL UNION ALL
SELECT  9, 'Tokyo'     FROM DUAL UNION ALL
SELECT 10, 'Tokyo'     FROM DUAL;
10 rows affected

CREATE VIEW city_workers ( city_name, workers_count ) AS
SELECT c.name,
       COUNT(w.id)
FROM   cities c
       LEFT OUTER JOIN worker w
       ON ( c.name = w.city )
GROUP BY c.name
SELECT * FROM city_workers;


CITY_NAME
WORKERS_COUNT


Tokyo
2

London
0

Berlin
1

Rome
1

Beijing
1

Paris
3

Amsterdam
2