CMIS 320 Lab 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 relational diagram and a crow's foot ERD for this group of tables. Indicate which table is the parent, which the child in each relationship. 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. SELECT (SELECT Count(*) FROM City) AS "#City",(SELECT Count(*) FROM Country) AS "#Country",(SELECT Count(*) FROM CountryLanguage) AS "#CountryLanguage" FROM dual; #City #Country #CountryLanguage ---------- ---------- ---------------- 4079 239 984 What is the actual range of values of the ID field? SELECT min(id),max(id) FROM city; MIN(ID) MAX(ID) ---------- ---------- 1 4079 How many different names are in the City table? SELECT Count(Distinct Name) FROM City; COUNT(DISTINCTNAME) ------------------- 3998 Which name occurs the most number of times in the City table? (extra fun query) SELECT * FROM (SELECT name,Count(*) FROM City GROUP BY name HAVING Count(*)>1 ORDER BY Count(*) DESC) WHERE Rownum<=1; NAME COUNT(*) ----------------------------------- ---------- San Jose 4 How many different Languages are in the CountryLanguage table? SELECT Count(DISTINCT language) FROM CountryLanguage; COUNT(DISTINCTLANGUAGE) ----------------------- 457 How many different CountryCodes are in the CountryLanguage table? SELECT Count(DISTINCT countrycode) FROM CountryLanguage; COUNT(DISTINCTCOUNTRYCODE) -------------------------- 233 So what are the "countries" that don't have any languages? SELECT country.name FROM Country LEFT JOIN CountryLanguage ON Country.code=CountryLanguage.countrycode WHERE CountryLanguage.countrycode IS NULL; NAME ---------------------------------------------------- Bouvet Island French Southern territories Antarctica South Georgia and the South Sandwich Islands Heard Island and McDonald Islands British Indian Ocean Territory 6 rows selected. Why don't those "countries" have any languages? (Show why with a query that shows the reason.) SELECT country.name,population FROM Country LEFT JOIN CountryLanguage ON Country.code=CountryLanguage.countrycode WHERE CountryLanguage.countrycode IS NULL; NAME POPULATION ---------------------------------------------------- ---------- Bouvet Island 0 French Southern territories 0 Antarctica 0 South Georgia and the South Sandwich Islands 0 Heard Island and McDonald Islands 0 British Indian Ocean Territory 0 How many countrycodes are in the City table? SELECT Count(DISTINCT countrycode) FROM City; OUNT(DISTINCTCOUNTRYCODE) -------------------------- 232 So what are the "countries" that don't have any cities? SELECT country.name FROM Country LEFT JOIN City ON Country.code=City.countrycode WHERE countrycode IS NULL; NAME ---------------------------------------------------- Bouvet Island French Southern territories Antarctica South Georgia and the South Sandwich Islands United States Minor Outlying Islands Heard Island and McDonald Islands British Indian Ocean Territory 7 rows selected. And why don't these "countries" have any cities? SELECT country.name,country.population FROM Country LEFT JOIN City ON Country.code=City.countrycode WHERE countrycode IS NULL; NAME POPULATION ---------------------------------------------------- ---------- Bouvet Island 0 French Southern territories 0 Antarctica 0 South Georgia and the South Sandwich Islands 0 United States Minor Outlying Islands 0 Heard Island and McDonald Islands 0 British Indian Ocean Territory 0 7 rows selected. What are the countries with zero population? SELECT name,population FROM Country WHERE population=0; What countries don't have a capital? SELECT name,population,capital FROM Country WHERE capital IS NULL; NAME POPULATION ---------------------------------------------------- ---------- Antarctica 0 Bouvet Island 0 British Indian Ocean Territory 0 South Georgia and the South Sandwich Islands 0 Heard Island and McDonald Islands 0 French Southern territories 0 United States Minor Outlying Islands 0 7 rows selected. What is the world's population? SELECT Sum(population) FROM country; SUM(POPULATION) --------------- 6078749450 What percentage of the world's people live in cities? Hint: subqueries and dual. SELECT (SELECT Sum(population) FROM city) / (SELECT Sum(population) FROM country) FROM dual; (SELECTSUM(POPULATION)FROMCITY)/(SELECTSUM(POPULATION)FROMCOUNTRY) ------------------------------------------------------------------ .235173352 How many "countries" are not independent (0 for IndepYear) and what is their average population? SELECT count(*),avg(population) FROM country WHERE indepyear=0; COUNT(*) AVG(POPULATION) ---------- --------------- 47 400113.83 Of the countries that are independent, what is the average year of indepedence? SELECT count(*),avg(indepyear) FROM country WHERE indepyear!=0; COUNT(*) AVG(INDEPYEAR) ---------- -------------- 192 1847.26042 What are the countries that were independent before 1000? SELECT name,indepyear FROM country WHERE indepyear<1000 AND indepyear!=0; AME INDEPYEAR ---------------------------------------------------- ---------- France 843 Sweden 836 San Marino 885 Denmark 800 Ethiopia -1000 Japan -660 China -1523 7 rows selected.