Preparation: Run the given script (on ICON next to this homework file) in your S
ID: 3595531 • Letter: P
Question
Preparation: Run the given script (on ICON next to this homework file) in your SQL Developer to create a new table “country” in your Oracle account. This table provides information about countries and political entities in the world. The schema of the table is given as follows. Here gdp represents the GDP growth in 2011 (e.g., 0.1=10%). Write SQL statements to answer each of the following questions.
Q1 (4 pts). List all the countries in ‘North America’ (region) and sort the results by area from high to low. Show all the columns.
Q2 (4 pts). How many countries are there in region ‘South America’? Show this number.
Q3 (4 pts). Find all the countries (show name and region) with the word ‘Republic’ in their names.
Q4 (6 pts). List the names of all the countries that satisfy the following criteria: population between 50000000 and 100000000, not in ‘Africa’ (region), and area less than 1000000. Sort the results by gdp in descending order.
Q5 (6 pts). List the regions (without duplicates) of all countries with gdp > 0.1.
Explanation / Answer
Q1 (4 pts). List all the countries in ‘North America’ (region) and sort the results by area from high to low. Show all the columns.
Q2 (4 pts). How many countries are there in region ‘South America’? Show this number.
Q3 (4 pts). Find all the countries (show name and region) with the word ‘Republic’ in their names.
Q4 (6 pts). List the names of all the countries that satisfy the following criteria: population between 50000000
and 100000000, not in ‘Africa’ (region), and area less than 1000000. Sort the results by gdp in descending order.
Q5 (6 pts). List the regions (without duplicates) of all countries with gdp > 0.1.