Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE City (name, country) AS
SELECT 'City1', 'C' FROM DUAL UNION ALL
SELECT 'City2', 'C' FROM DUAL;
2 rows affected

CREATE TABLE Country (name, code) AS
SELECT 'Country1', 'C' FROM DUAL;
1 rows affected

CREATE TABLE Continent (country, continent) AS
SELECT 'C', 'Continent1' FROM DUAL UNION ALL
SELECT 'C', 'Continent2' FROM DUAL;
2 rows affected

SELECT MAX(country.name) AS Country,
       country.Code,
       MIN(continent.continent) AS Continent1,
       MAX(continent.continent) AS Continent2,
       (SELECT COUNT(*) FROM city WHERE city.country = country.code)
         AS Count_of_Cities
FROM   country
       INNER JOIN continent
       ON country.code = continent.country
GROUP BY
       country.code
HAVING COUNT(DISTINCT continent.continent) > 1


COUNTRY
CODE
CONTINENT1
CONTINENT2
COUNT_OF_CITIES


Country1
C
Continent1
Continent2
2


SELECT country.name AS Country,
       country.Code,
       continent.continent1,
       continent.continent2,
       city.Count_of_Cities
FROM   ( SELECT country,
                MIN(continent) AS continent1,
                MAX(continent) AS continent2
         FROM   continent
         GROUP BY country
         HAVING COUNT(*) > 1
       ) continent
       INNER JOIN country
       ON country.code = continent.country
       INNER JOIN (
          SELECT country,
                 COUNT(*) AS count_of_cities
          FROM   city
          GROUP BY country
       ) city
       ON city.country = country.code


COUNTRY
CODE
CONTINENT1
CONTINENT2
COUNT_OF_CITIES


Country1
C
Continent1
Continent2
2