CMIS 320 Homework 3 Due: 4 May Use the product and vendor tables to answer these queries. ***Use descriptive column headers for ALL calculated and aggregated columns. E.g. SELECT name,sqrt(cuteness/weight) AS "Cuticality factor" FROM pet; NAME Cuticality factor --------------- ----------------- Fluffy 2.79284801 1. The products whose number of items in stock is less than twice its minimum number and whose inventory value is more than $500. 2. Products whose vendor's order flag is Y and is in either Florida or Georgia. 3. The number of products per vendor, sorted numerically. 4. 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? 5. The number of products per vendor for products that arrived in 2002. ******************************************************************* Use the country, city and countrylanguage tables to answer these queries. ***Use descriptive column headers for ALL calculated and aggregated columns. 6. List the top ten countries with the most number of languages. 7. List the top ten countries with the most number of languages that are spoken by at least five percent of the population. 8. List the top ten countries with the most number of official languages. List them in decreasing order by number of official languages and increasing order by country name. 9. List the top ten most urban countries (assume the cities in the City table is all the urban area of a country). ??? 10. List the country names and the number of cities of the top ten countries with the most number of cities. 11. List the country names, ISO country codes and Internet TLD code of the countries whose TLD code is neither the first two, nor the last two, nor the first and last characters of the ISO country code. Ordered by name. hint: concat() function 12. The number of rows in the Cartesian product of the three tables. 13. The number of rows in the Country,City and CountryLanguage tables joined on their common code fields. 14. The names of the countries and the languages of countries where a langauge is spoken by 100% of the population.