Needing SQL Statements that works with the \"World\" Sample Database provided by

Question

Needing SQL Statements that works with the "World" Sample Database provided by MySql Server! (

1 . 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.

2. 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.

3. Find out the official languages of the top 10 GNP countries, and sort the result by GNP.

4. Find out the official languages spoke by the top 10 populated counties, and sort the result by population.

5. Find out the biggest and the smallest city of each continent (country.continent, country.name).

6. Find out what country has the highest population but has the minimum surface area.

The World Schema/DB can be downloaded from https://dev.mysql.com/doc/index-other.html

Explanation / Answer

--1 . 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.
SELECT c.Name,AVG(c.Population) AS AvgPopulation,COUNT(ci.Name) AS NumberOfCities FROM country c INNER JOIN City ci ON c.Code=ci.CountryCode
GROUP BY c.code
ORDER BY c.Continent,AVG(c.Population);

/*2. 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.*/
SELECT AVG(Population)AS AvgPopulation ,COUNT(Code) AS TotalNumberOfCountries FROM country GROUP BY Continent ORDER BY Continent,AVG(Population;

--3. Find out the official languages of the top 10 GNP countries, and sort the result by GNP.
SELECT TOP 10 cl.Language FROM countrylanguage cl INNER JOIN country c ON cl.countrycode=c.code
WHERE cl.IsOfficial='T'
ORDER BY c.GNP DESC;
--4. Find out the official languages spoke by the top 10 populated counties, and sort the result by population.
SELECT TOP 10 cl.Language FROM countrylanguage cl INNER JOIN country c ON cl.countrycode=c.code
WHERE cl.IsOfficial='T'
ORDER BY c.population DESC;
--5. Find out the biggest and the smallest city of each continent (country.continent, country.name).
SELECT MAX(SurfaceArea) AS biggestCity,MIN(SurfaceArea) AS smallestCity
FROM country GROUP BY Continent;
--6. Find out what country has the highest population but has the 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

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.