population.sql

CREATE TABLE Country (
  Id integer NOT NULL,
  Name character varying(25) NOT NULL
);

ALTER TABLE Country
  ADD CONSTRAINT pkCountryId PRIMARY KEY (id);

CREATE TABLE City (
  Id serial NOT NULL,
  Name character varying(30) NOT NULL,
  Population integer DEFAULT 0 NOT NULL,
  Countryid integer REFERENCES Country(Id),
  CONSTRAINT checkCityPopulation CHECK ((population >= 0))
);

ALTER TABLE City
  ADD CONSTRAINT pkCityId PRIMARY KEY (id);

INSERT INTO Country VALUES
  (1, 'China'),
  (2, 'Turkey'),
  (3, 'Pakistan'),
  (4, 'Japan'),
  (5, 'Bangladesh'),
  (6, 'Russia');

INSERT INTO City VALUES
  (1, 'Shanghai', 24183300, 1),
  (2, 'Beijing', 21707000, 1),
  (8, 'Guangzhou', 13081000, 1),
  (9, 'Shenzhen', 12528300, 1),
  (3, 'Istanbul', 15029231, 2),
  (4, 'Karachi', 14910352, 3),
  (7, 'Moscow', 13200000, 6),
  (5, 'Dhaka', 14399000, 5),
  (6, 'Tokyo', 13515271, 4);
    
SELECT
  (SELECT Name FROM Country where Id = CountryId) AS City,
  sum(Population) AS Total
FROM City
GROUP BY CountryId
ORDER BY City;