Last active
November 14, 2019 09:07
-
-
Save tshemsedinov/3a48d657b3a5f3a157a5946f9e951aa2 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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