Consider two relation instances below, with the following schemas: Country (coun

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 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 Country Name Continent US North America Canada North America England Europe Germany Europe France Europe Africa Ma City City Name Country Name isCapital Population New York, NY US 8,000,000 no Washington, DC US 600,000 yes Philadelphia, PA US 1,500,000 no Ottawa Canada 800,000 yes Toronto Canada 2,500,000 no Berlin Germany 3,500,000 es Hamburg Germany 2,000,000 no Bonn Germany 300,000 no Paris France 2,000,000 yes Lyon France 700,000 no Bamako Ma 2,000,000 yes 50,000 Timbuktu Ma no 100,000 Mopt Ma no

Explanation / Answer

Please give thumbs up, If it is helpful for you. Thankyou!!

a)

SELECT Country_Name
FROM City
WHERE City_Name IS NULL;

b)

SELECT A.Continent, count(A.Country_Name) as number_of_countries, max(B.Population)
FROM Country as A JOIN City as B
ON A.Country_Name=B.Country_Name
GROUP BY A.Continent;

C)

SELECT A.Country_Name, B.Continent, avg_population
from
(SELECT Country_Name, count(City_name), avg(Population) avg_population
FROM City WHERE count(City_name) >= 3) as A
JOIN Country as B
ON A.Country_Name=B.Country_Name
ORDER BY avg_population;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.