search

GitGotGo1 / sem

Repository of GitGotGot Java application using SQL to output reports
Apache License 2.0
0 stars 5 forks source link

The top number of populated capital cities in a continent where the number is provided by the user. #33

Closed rosshardie closed 5 years ago

rosshardie commented 5 years ago

As a user I want to be able to see the top number of populated capital cities in a continent where the number is specified by me

Amckenzie93 commented 5 years ago

Capital City Report A capital city report requires the following columns:

Amckenzie93 commented 5 years ago

-- The top number of populated capital cities in a CONTINENT where the number is provided by the user. SELECT t1.Continent AS 'Continent Name', t1.name AS 'Country Name', t2.name AS 'Capital City', t2.population AS 'Capital City Population' FROM (select country.Name, country.Continent, country.capital FROM country) as t1, (select city.name, city.population, city.ID FROM city) as t2 where t1.capital = t2.id AND t1.Continent = 'Asia' -- CONTINENT PROVIDED BY THE USER order by t2.population DESC LIMIT 50; -- NUMBER PROVIDED BY THE USER, IN THIS CASE TOP 10 COUNTRIES