Background
While we have been using the bookstore database in class, for this assignment, you will be using a different database, World, to practice writing SQL SELECT statements. The database consists of three tables. The first is the Country table, this table contains a list of countries names as well as codes to identify each one. The table also includes other information about each country. Secondly is the City table, this table lists cities as well as their district and population and maps them to countries. Finally is the CountryLanguage table, this table lists languages spoken in each country as well as how many speak that language and whether or not it is the primary language.
Instructions
Using the World database which you can download from Canvas, you need to write a query that gets the information asked in each question. When you think that you have found the answer, you need to put your query into a file titled hw9.txt. You will then submit this file along with your statement.txt to the Canvas dropbox.
1. Write a query that gets all the information out of the Countries table.
2. Write a query that will only get the head of state of all countries who have a population greater than one-million people.
3. Write a query that will get the list of city names that start with the letter B or the letter M, the list should be ordered by the city name in ascending order.
4. Write a query that will get the names of countries whose current GNP is less than one- million dollars. The list should be in descending order by GNP.
5. Write a query that will get a list of all cities in the United States or Argentina.
6. Write a query that will get a list of all languages spoken in the United States.
7. Write a query to find the total number of cities in each country.
8. Write a query to find the total number of cities in each country, but only shows countries with more than 3 cities.
9. Write a query that will get the average population of all countries.
10. Write a query that lists each country and its official language as well as what percentage of the population speaks it.
Submission
Submit your hw9.txt file along with your statement.txt to the Canvas dropbox prior to the deadline.