CMIS 320
Names:____________________
Joins.

Use the product_vendor database to answer these queries.
Run product_vendorCreate.sql and product_vendorInsert.sql
to create and populate the tables.
Use meaningful names for calculated or aggregated columns.

1. The number of records in the Cartesian product of the vendor and product tables.

2. The number of records in the join of the vendor and product tables
joined by their common column v_code.

3. The number of products per vendor, sorted numerically.

4. Products whose vendor's reorder flag is Y and is in either Florida or Georgia.

5. The number of products per vendor for products that arrived in December 2001.

6. The number of products per state.


The following aren't joins but are good business-y kind of queries.
"Inventory value" is the worth of the stuff on hand, e.g. if you
have 2 widgets priced at $10 each and 3 geegaws at $20 each, your inventory
value is $80.

1. The inventory value of each product, in order by value.

2. The total inventory value of all the products that are on hand.

3. In business, excessive inventory is bad.  Assume that the minimum
number of items in stock is the ideal and thus anything above that
minimum is excess. What is the ratio of the total actual inventory
value to the total value of the ideal inventory? 

4. The products whose number of items in stock is less than twice its
minimum number and whose inventory value is more than $500. 




Using the world database.

The number of rows in:
City: 
Country:
CountryLanguage:

8abc. The number of rows in the Cartesian products of 
the Country and City tables,
the Country and CountryLanguage tables, and 
the City and CountryLanguage tables.

9abc. The number of rows in the join of 
the Country and City tables and,
the Country and CountryLanguage tables,
the City and CountryLanguage tables.

10. List the names of the top ten countries with the most number of languages
(and the number of those languages).

11. List the names of the top ten countries with the most number of languages that are
spoken by at least five percent of the population (and the number of those languages).

12. List the names of the top ten countries with the most number of official languages
(and the number of those languages).

13. List the country names and the number of cities of the top ten
countries with the most number of cities.

14. List the names of the top ten most urban countries (assume the cities in the City
table is all the urban area of a country).  Includes the country's population, 
it's urban population, and the percentage of the population that is urban.
There is some anomalous data.