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.
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;
To achieve the desired results, you can follow the steps outlined below:
A. Create a query to display the number of countries per continent:
1. Write the SQL query:
```
SELECT continent, COUNT(DISTINCT country) AS number_of_countries
FROM your_table_name
GROUP BY continent
ORDER BY number_of_countries DESC;
```
2. Execute the query against your database.
3. Display the results in a table format with appropriate column headers, such as "Continent" and "Number of Countries".
B. Create a query to display the total population per continent, excluding continents with no population:
1. Write the SQL query:
```
SELECT continent, SUM(population) AS total_population
FROM your_table_name
WHERE population > 0
GROUP BY continent;
```
2. Execute the query against your database.
3. Display the results in a table format with appropriate column headers, such as "Continent" and "Total Population".
C. Create a query to display the top 10 most populated cities:
1. Write the SQL query:
```
SELECT name AS city_name, country_name, FORMAT(population, 0) AS formatted_population, life_expectancy
FROM cities_table
JOIN countries_table ON cities_table.country_code = countries_table.country_code
ORDER BY population DESC
LIMIT 10;
```
2. Execute the query against your database.
3. Display the results in an ordered list format, including the columns "City Name", "Country Name", "Population" (formatted with grouped thousands), and "Life Expectancy".
D. Create a query to include a pie chart of the top 10 most popular languages and a table displaying the names and populations of the five smallest countries in North America:
1. Write the SQL query for the pie chart:
```
SELECT language_name, SUM(population) AS total_population
FROM languages_table
GROUP BY language_name
ORDER BY total_population DESC
LIMIT 10;
```
2. Execute the query against your database.
3. Plot the results in a pie chart.
4. Write the SQL query for the table:
```
SELECT name AS country_name, population
FROM countries_table
WHERE continent = 'North America'
ORDER BY population ASC
LIMIT 5;
```
5. Execute the query against your database.
6. Display the results in a table format with appropriate column headers, such as "Country Name" and "Population".
7. Display a line chart below the table, representing the population of the five smallest countries in North America.
E. Create a query to display the countries that have been independent for less than or equal to 100 years:
1. Write the SQL query:
```
SELECT name AS country_name, YEAR(current_date()) - year_independence AS years_independent
FROM countries_table
WHERE YEAR(current_date()) - year_independence <= 100
ORDER BY years_independent ASC;
```
2. Execute the query against your database.
3. Display the results, including the columns "Country Name" and "Years Independent", in ascending order.
4. Double-space the output for easier readability.