Reference no: EM131156776

Your task for this final project will be to write queries against the World database tables. There are three tables used to store data about Countries, Cities and CountryLanguages. These tables along with the data exists in all the four group databases (IST210JamesJGrp1, IST210JamesJGrp2, IST210JamesJGrp3, IST210JamesJGrp4). You can work as a team or individually but each student must submit their own work. While working as a team you need to highlight what part of the queries you worked on. The ER diagram is shown at the end of this document along with the Database diagram and the sample output for each question. Answer the following queries listed below:

1. List the Continent, Country Name, District, City Name and City Population of for all cities with a population of over 3 million people. Sort the results by Continent, Country Name, District and City Name. (10 points)
2. List Country Name, Language Percentage and Country Population where language spoken is English and English is the official language. Sort the result by Percentage desc, and countryname asc. (10 points)
3. List the Life Expectancy, Population, CountryName, HeadOfState, Official Language where population > 100000000 and English is the official language. Sort the result by LifeExpectancy and population. (10 points)
4. List CountryName and their Capital City where country's surface area > 2000000. (10 points)
5. List the headofstate for the country = 'USA'. (10 points)
6. List all the CountryCodes where headofstate = 'George W. Bush'. (10 points)
7. List the name of the country and count of cities. (10 points)
8. List the name of the country and count of cities having count of cities > 10. (10 points)
9. List the name of the country and sum of population across all cities in the United States. (10 points)
10. List the name of the country and sum of total percentage of non-english speaking population (10 points)

Submission: Each student must submit their own Final Project document as FinalAssignment.sql file. Just write your queries against each question in this file. When working as a team explain in what specific queries you worked on? DO NOT INCLUDE OUTPUT/RESULT. ONLY THE QUERIES ARE SUFFICIENT