CMIS 320

Homework. Due: 25 April

Execute the worldascii.sql to create the three tables Country, City,
and CountryLanguage and populate them with data.


What are the primary keys of each of the tables?
What are the foreign keys of each of the tables?
Answer this by drawing a crow's foot ERD for this group of tables.
Indicate which table is the parent, which the child in each
relationship.  Indicate any optionalities.


Answer these queries:
How many rows are in each of the tables?  (This can be done with one
query. Hint: subqueries and DUAL table.) But 3 queries will produce
the answers.

1. What is the actual range of values of the ID field?

2. How many different names are in the City table?

3. Which name occurs the most number of times in the City table? (extra fun query)

4. How many different Languages are in the CountryLanguage table?

5. How many different CountryCodes are in the CountryLanguage table?

6. So what are the "countries" that don't have any languages?

7. Why don't those "countries" have any languages? (Show why with a query
 that shows the reason.)

8. How many countrycodes are in the City table?

9. So what are the "countries" that don't have any cities?

10. And why don't these "countries" have any cities?

11. What are the countries with zero population?

12. What countries don't have a capital?

13. What is the world's population?

14. What percentage of the world's people live in cities? Hint: subqueries
 and dual.

15. How many "countries" are not independent (0 for IndepYear) and what is
their average population? 

16. Of the countries that are independent, what is the average year of indepedence?

17. What are the countries that were independent before 1000?