-- CREATE
CREATE TABLE CITY
(
    ID          INT PRIMARY KEY,
    NAME        VARCHAR(255) NOT NULL,
    COUNTRYCODE VARCHAR(3)   NOT NULL,
    DISTRICT    VARCHAR(255) NOT NULL,
    POPULATION  INT          NOT NULL
);

-- INSERT
INSERT INTO CITY (ID, NAME, COUNTRYCODE, DISTRICT, POPULATION)
VALUES (1, 'Tokyo', 'JPN', 'Tokyo', 13929286),
       (2, 'Delhi', 'IN', 'Delhi', 16787941),
       (3, 'Shanghai', 'CN', 'Shanghai', 24183300),
       (4, N'São Paulo', 'BR', N'São Paulo', 12106920),
       (5, 'Mumbai', 'IN', 'Maharashtra', 12442373),
       (6, 'Cairo', 'EG', 'Cairo', 9500000),
       (7, 'Dhaka', 'BD', 'Dhaka', 8906039),
       (8, 'Mexico City', 'MX', 'Mexico City', 9209944),
       (9, 'Beijing', 'CN', 'Beijing', 21542000),
       (10, 'Osaka', 'JPN', 'Osaka Prefecture', 8839469);

-- QUERY

SELECT ROUND(AVG(POPULATION), 0) AS average_population
FROM CITY;

SELECT SUM(POPULATION)
FROM CITY
WHERE COUNTRYCODE = 'JPN';


SELECT MAX(POPULATION) - MIN(POPULATION) AS population_difference
FROM CITY;



DROP TABLE CITY;
