Using the world database and the pictured ERD, complete all 8 of the below queries.
We will use the world database that we loaded into MySQL workbench in the lesson titled "MySQL Workbench" For reference, the file can be found again here. (download the file here)
The goal of this assignment is for you to get practice with SQL queries and to get familiar with using MySQL workbench. The questions are of varying difficulty levels. Some require joins.
Write and run these queries in MySQL workbench to obtain the answers.
Take a screenshot of each query with the results and paste it into a single document. Post the document on your GitHub repository and share the link below.
- What query would you run to get all the countries with a Surface Area below 501 and a Population greater than 100,000? Include the country name, surface area, and population in your results.
- What query would you run to get countries with only a Constitutional Monarchy with a capital greater than 200 and a life expectancy greater than 75 years? Include the country name, form of government, and capital in your results.
- What query would you run to summarize the number of countries in each region? The query should display the name of the region and the number of countries. Also, the query should arrange the result by the number of countries in descending order.
- What query would you run to get all the countries that speak Slovene? Your query should return the name of the country, language, and language percentage. Your query should arrange the result by language percentage in descending order.
- What query would you run to display the total number of cities for each country? Your query should return the name of the country and the total number of cities. Your query should arrange the result by the number of cities in descending order.
- What query would you run to get all the cities in Mexico with a population of greater than 500,000? Your query should arrange the result by population in descending order.
- What query would you run to get all languages in each country with a percentage greater than 89%? Include the country name, language, and percentage. Your query should arrange the result by percentage in descending order.
- What query would you run to get all the cities of Argentina inside the Buenos Aires district and have a population greater than 500,000? The query should return the Country Name, City Name, District, and Population.
