Question
Consider two relation instances below, with the following schemas: Country (country_name, continent); City (city_name, country_name, isCapital, population). First write your own script to create these
Consider two relation instances below, with the following schemas: Country (country_name, continent); City (city_name, country_name, isCapital, population). First write your own script to create these two tables and insert the tuples showed in the instances below (similar to the content in script provided in Part 1), and then based on which, for each question (a)-(d) below, write a SQL query and show (copy and paste) its result when the query is executed with the given instances.
Country City Country_Name Continent US North America Canada North America England Europe Germany Europe France Europe Mali Africa City_Name Country_Name isCapital Population New York, NY US no 8,000,000 Washington, DC US yes 600,000 Philadelphia, PA US no 1,500,000 Ottawa Canada yes 800,000 T oronto Canada no 2,500,000 Berlin Germany yes 3,500,000 Hamburg Germany no 2,000,000 Bonn Germany no 300,000 Paris France yes 2,000,000 Lyon France no 700,000 Bamako Mali yes 2,000,000 Timbuktu Mali no 50,000 Mopti Mali no 100,000
(a) List the names of all countries for which no cities have been entered into the City table.
(b) For each continent, list the number of countries on that continent and the maximum population among its cities.
(c) For countries with at least 3 cities, compute average city population. Return country name, continent name, and average city population. Sort results in increasing order of average city population.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started