Skip to content

Instantly share code, notes, and snippets.

@tshemsedinov
Last active November 14, 2019 09:07
Show Gist options
  • Save tshemsedinov/3a48d657b3a5f3a157a5946f9e951aa2 to your computer and use it in GitHub Desktop.
Save tshemsedinov/3a48d657b3a5f3a157a5946f9e951aa2 to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment