CMIS 320 Homework 3 Due: Use the product and vendor tables to answer these queries. ***Use descriptive column headers for ALL calculated and aggregated columns. The products whose number of items in stock is less than twice its minimum number and whose inventory value is more than $500. SELECT p_descript,p_onhand,p_min,p_onhand*p_price AS "Inventory" FROM product,vendor WHERE product.v_code=vendor.v_code AND p_onhand<2*p_min AND p_onhand*p_price>500; P_DESCRIPT P_ONHAND P_MIN Inventory ----------------------------------- ---------- ---------- ---------- Power painter, 15 psi., 3-nozzle 8 5 879.92 Hrd. cloth, 1/4-in., 2x50 15 8 599.25 B&D jigsaw, 12-in. blade 8 5 879.36 B&D jigsaw, 8-in. blade 6 5 599.22 Products whose vendor's order flag is Y and is in either Florida or Georgia. SELECT p_descript,v_state,v_order FROM product,vendor WHERE product.v_code=vendor.v_code AND v_order='Y' AND v_state IN ('GA','FL'); P_DESCRIPT V_ V ----------------------------------- -- - Power painter, 15 psi., 3-nozzle FL Y Hrd. cloth, 1/4-in., 2x50 GA Y Hrd. cloth, 1/2-in., 3x50 GA Y B&D cordless drill, 1/2-in. FL Y Steel matting, 4'x8'x1/6", .5" mesh FL Y The number of products per vendor, sorted numerically. SELECT v_name,Count(*) AS "Number of products" FROM product,vendor WHERE product.v_code=vendor.v_code GROUP BY v_name ORDER BY Count(*); V_NAME Number of products ----------------------------------- ------------------ D&E Supply 1 Randsets Ltd. 2 Bryson, Inc. 2 Rubicon Systems 3 ORDVA, Inc. 3 Gomez Bros. 3 6 rows selected. 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? SELECT SUM(p_onhand*p_price),SUM(p_min*p_price),SUM(p_onhand*p_price)/SUM(p_min*p_price) FROM product; SUM(P_ONHAND*P_PRICE) SUM(P_MIN*P_PRICE) --------------------- ------------------ SUM(P_ONHAND*P_PRICE)/SUM(P_MIN*P_PRICE) ---------------------------------------- 15084.52 6733.43 2.24024309 The number of products per vendor for products that arrived in 2002. SELECT v_name,COUNT(*) AS "#products arrived in 2002" FROM product,vendor WHERE product.v_code=vendor.v_code AND Extract(Year from p_indate)=2002 GROUP BY v_name ORDER BY v_name; V_NAME #products arrived in 2002 ----------------------------------- ------------------------- Bryson, Inc. 1 Gomez Bros. 3 ORDVA, Inc. 1 Randsets Ltd. 1 Rubicon Systems 1 ******************************************************************* Use the country, city and countrylanguage tables to answer these queries. ***Use descriptive column headers for ALL calculated and aggregated columns. List the top ten countries with the most number of languages. SELECT * FROM (SELECT Name,COUNT(*) AS NumLangs FROM Country,CountryLanguage WHERE Code=CountryCode GROUP BY Name ORDER BY NumLangs DESC) WHERE Rownum<=10; NAME NUMLANGS ---------------------------------------------------- ---------- China 12 India 12 United States 12 Russian Federation 12 Canada 12 South Africa 11 Tanzania 11 Kenya 10 Uganda 10 Sudan 10 10 rows selected. List the top ten countries with the most number of languages that are spoken by at least five percent of the population. SELECT * FROM (SELECT Name,COUNT(*) AS NumLangs FROM Country,CountryLanguage WHERE Code=CountryCode AND percentage>=5 GROUP BY Name ORDER BY NumLangs DESC) WHERE Rownum<=10; NAME NUMLANGS ---------------------------------------------------- ---------- Liberia 8 Chad 8 South Africa 7 Benin 7 Mozambique 7 Kenya 7 Togo 6 Mali 6 Congo, The Democratic Republic of the 6 India 6 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. SELECT * FROM (SELECT Name,COUNT(*) AS NumLangs FROM Country,CountryLanguage WHERE Code=CountryCode AND isofficial='T' GROUP BY Name ORDER BY NumLangs DESC,Name) WHERE Rownum<=10; NAME NUMLANGS ---------------------------------------------------- ---------- South Africa 4 Switzerland 4 Belgium 3 Bolivia 3 Luxembourg 3 Peru 3 Singapore 3 Vanuatu 3 Afghanistan 2 American Samoa 2 List the top ten most urban countries (assume the cities in the City table is all the urban area of a country). ??? SQL> select * from (select country.name,("urban"/country.population*100) as "urban%" from (select countrycode as "country",sum(city.population) as "urban" from city group by countrycode),country where country.code="country" order by "urban%" desc) where rownum<=10; NAME urban% -------------------- ---------- Singapore 112.636193 Cocos (Keeling) Isla 111.666667 Gibraltar 108.1 Macao 92.4947146 Pitcairn 84 South Korea 83.2548309 Saint Pierre and Miq 82.9714286 Falkland Islands 81.8 United Arab Emirates 70.8044244 Chile 63.8877786 mysql: SELECT Country.Name,SUM(City.Population),Country.Population,SUM(City.Population)/Country.Population * 100 as Urbanity FROM Country,City WHERE Code=CountryCode GROUP BY Name ORDER BY Urbanity DESC LIMIT 10; List the country names and the number of cities of the top ten countries with the most number of cities. SELECT * FROM (SELECT Country.Name,COUNT(*) AS NumCities FROM Country,City WHERE Code=CountryCode GROUP BY Country.Name ORDER BY NumCities DESC) WHERE Rownum<=10; NAME NUMCITIES ---------------------------------------------------- ---------- China 363 India 341 United States 274 Brazil 250 Japan 248 Russian Federation 189 Mexico 173 Philippines 136 Germany 93 Indonesia 85 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 SELECT Name, Code, TLDCode FROM Country WHERE TLDcode!=Substr(code,1,2) AND TLDcode!=Substr(code,2,2) AND TLDcode!=Concat(Substr(code,1,1),Substr(code,3,1)) ORDER BY name; NAME COD TL ---------------------------------------------------- --- -- Antarctica ATA AQ Belarus BLR BY Benin BEN BJ Bosnia and Herzegovina BIH BA Cayman Islands CYM KY Comoros COM KM Estonia EST EE Guinea-Bissau GNB GW Ireland IRL IE Israel ISR IL Macao MAC MO North Korea PRK KP Slovenia SVN SI Ukraine UKR UA 14 rows selected. The number of rows in the Cartesian product of the three tables. SELECT Count(*) FROM Country,city,countrylanguage; 900 million... The number of rows in the Coountry,City and CountryLanguage tables joined on their common code fields. SELECT Count(*) FROM Country,city,countrylanguage WHERE code=city.countrycode AND code=countrylanguage.countrycode; COUNT(*) ---------- 30670 The names of the countries and the languages of countries where a langauge is spoken by 100% of the population. SELECT name,language,percentage FROM country,countrylanguage WHERE code=countrycode AND percentage=100; NAME LANGUAGE PERCENTAGE --------------------- ------------------------------ ---------- Bermuda English 100 Dominica Creole English 100 El Salvador Spanish 100 Faroe Islands Faroese 100 Grenada Creole English 100 Haiti Haiti Creole 100 Cape Verde Crioulo 100 Cuba Spanish 100 Western Sahara Arabic 100 Maldives Dhivehi 100 Rwanda Rwanda 100 Saint Kitts and Nevis Creole English 100 San Marino Italian 100 13 rows selected.