Please submit your answer either in SQL code: The following questions are based
ID: 3699557 • Letter: P
Question
Please submit your answer either in SQL code:
The following questions are based on World sample database provided by MySql server (30 points in total, 3points/each except Q.17 & Q.18).
15. Find out the biggest and the smallest city of each continent (country.continent, country.name).
16. Find out the avg/lowest/highest life expectancy and the avg/lowest/highest GNP by region, and sort the result first by continent and second by GNP.
17. What else question you would like to know from the World database? Make one example and use SQL code to find the answer (5 points)
18. What insights can you discover from the above (or any other) questions? What actions would you suggest to make the world better? Make two examples. (4 points)
Explanation / Answer
--15
SELECT MAX(SurfaceArea) AS biggestCity,MIN(SurfaceArea) AS smallestCity
FROM country GROUP BY Continent;
--16
SELECT AVG(LifeExpectancy) AS AvgLifeExpectancy,
MIN(LifeExpectancy) AS MinLifeExpectancy,
MAX(LifeExpectancy) AS MaxLifeExpectancy,
AVG(GNP) AS AvgGNP,
MIN(GNP) AS MinGNP,
MAX(GNP) AS MaxGNP
FROM country
GROUP BY Region
ORDER By Continent,GNP;
--17 select the country with the highest population with minimum surface area.
Select continent, name, sum(population) As sumpopulation
From country
Group By continent
WHERE SurfaceArea = (SELECT MIN(SurfaceArea) FROM country GROUP BY Continent)
Order By sumpopulation Desc
Limit 1;
--18
From Query 17 we can able to get name of country having highest population, we can recommend to reduce population
From Query 16 we can able to find out maximum LifeExpectancy region, which can be recommended for leaving