Homework 06 answer - hw6_ans PDF

Preview

Summary

hw6_ans...

Description

#6 This homework use database world. Use your common sense on these. Show the SQL query and the output AND the answer to the question. DO NOT provide IDs in the final answer; all answers should be meaningful. 1. How many California cities are on record? select count(distinct id) from world.City where District = ‘California’; 68 2. Which East Coast cities (Maine, New Hampshire, Massachusetts, Rhode Island, Connecticut, New York, New Jersey, Delaware, Maryland, Virginia, North Carolina, South Carolina, Georgia, and Florida) have less than a million people? select Name from world.City where Population < 1000000 and District in (‘Maine’ ,’New Hampshire’ ,’Massachusetts’ ,’Rhode Island’ ,’Connecticut’ ,’New York’ ,’New Jersey’ , ’Delaware’ ,’Maryland’ ,’Virginia’ ,’North Carolina’ ,’South Carolina’ ,’Georgia’ ,’Florida’) ; 65 3. Which Asian cities have more than 8 million people and are in a country where the life expectancy is under 65? select ci.name from world.City ci inner join world.Country co on ci.CountryCode = co.Code where co.Continent = 'Asia' and ci.Population > 8000000 and co.LifeExpectancy < 65; Mumbai (Bombay) Karachi 4. How many countries outside Europe have French as their official language? select count(distinct Code) from world.Country as co inner join world.CountryLanguage as cl on co.Code = cl.CountryCode where cl.IsOfficial = 'T' and cl.Language = 'French' and co.Continent != 'Europe'; 13 5. Which cities of at least 750,000 but no more than a million people are in countries where Spanish is the official language? select ci.name from world.City ci inner join world.CountryLanguage co on ci.CountryCode = co.CountryCode where ci.Population >= 750000 and ci.Population...