A. Query for displaying the number of countries per continent:
SELECT continent, COUNT(country) AS number_of_countries
FROM countries
GROUP BY continent
ORDER BY number_of_countries DESC;
B. Query for displaying total population per continent (excluding continents with no population):
SELECT continent, SUM(population) AS total_population
FROM countries
WHERE population > 0
GROUP BY continent;
C. Query for displaying the top 10 most populated cities:
SELECT city.name, country.name AS country_name, FORMAT(city.population, '#,###,###') AS population, country.life_expectancy
FROM city
JOIN country ON city.country_code = country.code
ORDER BY city.population DESC
LIMIT 10;
D. Query for displaying a pie chart of the top 10 most popular languages in the world:
SELECT language.name, SUM(country_language.percentage) AS total_percentage
FROM language
JOIN country_language ON language.code = country_language.language
GROUP BY language.name
ORDER BY total_percentage DESC
LIMIT 10;
Query for displaying the names and populations of the five smallest countries in North America, with a line chart:
SELECT name, population
FROM country
WHERE continent = 'North America'
ORDER BY population ASC
LIMIT 5;
Query for displaying the countries that have been independent less than or equal to 100 years:
SELECT name, YEAR(CURRENT_DATE) - year_independence AS years_independent
FROM country
WHERE year_independence <= YEAR(CURRENT_DATE) - 100
ORDER BY years_independent ASC;
The title at the top of each page should clearly state what is displayed on the page. A. Create a query that will display the number of countries per continent. Display the results sorted by number of countries in descending order, in a table with appropriate column headers. B. Create a query that will display total population per continent. Using your awesome SQL skills, your result set should not include any continent that has no population. C. Create a query that will display the top 10 most populated cities. In an ordered list, display the name of the city, the country name (not the country code), population (format the numbers with grouped thousands such as 2,300,000 ), and the country's life expectancy. D. Create a query that will include a pie chart of the top 10 most popular languages in the world. Create a query that, in a table, displays the names and populations of the five smallest countries in North America. Also, include a line chart displaying this information below the table. Create a query that will display the countries that have been independent less than or equal to 100 years. Display the country name and, in ascending order, the number of years independent. Please double space output.
1 answer