CMIS 320
Final exam

Name:____________________________________

Use the World database to answer these queries.
The query needs to be as precise as possible.
Paste the SQL and the resultset.


1. The head of state with the shortest real name.

2. The five least populous regions.

3. The number of (different) regions in each continent.

4. The letter that has the most number of city names that start with that letter
 (also show that number).

5. The number of different districts.

6. The top five districts with the most number of cities in them.

7. The top 5 countries (i.e. their codes) with the most number of people in cities.

7b. The heads of state of the top 5 countries with the most number of people in cities.

8. The counts by language of the languages that are supposedly used in
  more than one country but aren't spoken by anyone (i.e. that have a zero percentage).

9. The city names that are used for more than two cities, in decreasing
  order of the number of times the name is used and secondarily in
  increasing alphabetical order.

10. The number of different city names that consist of two separate words, 
  the first being 'San'.

11. The number of speakers of non-official languages in the world.

12. The five countries with the largest percentage growth in GNP, as measured from GNPOld to GNP.  
  Hint: the percentage change from X to Y is calculated by (Y-X)/X*100

13. The languages that have more than 100,000,000 speakers, in decreasing order of number of speakers.
  Note that languages can be in multiple countries;

14. The USA cities that have the same name as a foreign city.  Show either the distinct names
or every instance of "duplication".

15. Notice that Capital field of Country is the same as ID field of City.
 What is the total population of all the capitals of the world?

16. The "countries" that have the same head of state as the USA.  You can use that head of
state's value in a SET variable or use a subquery.

17. The city names that are the capital of more than one country.