CODEWITHSUNDEEP
mysql
user2380034
user2380034

Reputation: 73

MYSQL Query with percentage and other restrictions

Hello I am new to MySQL and am in school right now trying to figure out a question that my instructor has asked. Here is the question:

Select ID, name, Country and Population from City Table, Select Life Expectancy, Name, SurfaceArea and GNP from the Country Table.

Restrict the result set using the following,

  1. Country SurfaceArea between 3,000,000 and 40,000,000 (Using the between operator)
  2. The length of the City.District field is greater than 4
  3. Create the calculated field 'CityPopulationPercentageOFCountryPopulation' that calculates what the field suggests"

This is what the City Table is described as:

ID, name, Country, District, Population

This is what the Country Table is described as:

Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LifeExpectancy, GNP, LocalName, GovernmentForm, HeadOfState, Capital, Code2

I have tried this and other variations with no luck:

Select City.ID, City.Name, City.Country, City.Population, Country.LifeExpectancy, Country.Name, Country.SurfaceArea, Country.GNP, 
(Select City.Population / Country.Population * 100, Count(City.Population / Country.population *100) AS "CityPopulationPercentageofCountryPopulation")
From City, Country
Where Country.SurfaceArea BETWEEN 3000000 and 40000000;

Like I said I am new at this and trying my best to figure it out by looking online etc. Some help and maybe and explanation of how you figure it out would really help

Regards,

Upvotes: 1

Views: 406

Answers (2)

user2380034
user2380034

Reputation: 73

Select City.ID, City.Name, City.Country, City.Population, Country.LifeExpectancy, Country.SurfaceArea, Country.Name, Country.GNP, (Select City.Population / Country.Population * 100) AS CityPopulationPercentageOfCountryPopulation From City INNER JOIN Country ON City.Country=Country.Code Where Country.SurfaceArea BETWEEN 3000000 and 40000000 and LENGTH(City.District)>4 limit 200;

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26343

Here's how you'd get the city-to-country percentage, by country. I'm OK with telling you this because you were very close with the query you published above.

SELECT
  City.Country,
  SUM(City.Population) / Country.Population * 100 AS CityPopulationPercentageOFCountryPopulation
FROM City
INNER JOIN Country ON City.Country = Country.Code
GROUP BY City.Country

Has your instructor covered inline views, where a subquery is used like a table? I hope so, because that's how you'd include the calculated column with each city. Here's a simplified version with City ID, City Name, City Population, Country Code, and City Population percent:

SELECT
  City.ID,
  City.Name,
  City.Country,
  City.Population,
  PopPercent.CityPopulationPercentageOFCountryPopulation
FROM City
INNER JOIN (
    SELECT
      City.Country,
      SUM(City.Population) / Country.Population * 100 AS CityPopulationPercentageOFCountryPopulation
    FROM City
    INNER JOIN Country ON City.Country = Country.Code
    GROUP BY City.Country
) PopPercent ON City.Country = PopPercent.Country

You can take the query above and add another join to Country to get the country values, and then you should have all the information you need to filter by surface area as well as the length of the city district name. I'll leave that part to you :)

A final note: I don't have MySQL available this afternoon so this is from memory, untested. I apologize if there are any errors.

Upvotes: 1

Related Questions