Please submit your answer either in SQL code: The following questions are based
ID: 3699554 • 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).
10. Find out the cities in CHN with the population above the average population of the City table, and sort the result by population in descending order.
11. Find out the average population and the total number of cities for each country, and sort the result first by continent and second by average population.
12. Find out the average population and the total number of countries for each continent, and sort the result first by continent and second by average population.
13. Find out the official languages of the top 10 GNP countries, and sort the result by GNP.
14. Find out the official languages spoke by the top 10 populated counties, and sort the result by population.
Explanation / Answer
10.select cities,population from city
where cities in('CHN') and
population>(select avg(population) from city)
order by Population desc
11.select avg(population),count(cities)
from city
group by country
order by continent desc,avg(population) desc
12.select avg(population),count(cities)
from city
group by continent
order by continent desc,avg(population) desc
13.select official_language
from city
where Country in (select distinct top 10 country from city)
order by GNP
14.select official_language
from city
where Country in (select distinct top 10 country from city
order by Population desc)
order by population desc