Skip to content
  1. Fetch Dataset from integrated database
Spinner
DataFrameas
df_base
variable
-- Start writing your SQL query here 
SELECT *
FROM 
CLIMATE;
  1. Query the country and water_stress_index_fields and order by desc of water stress index field
Spinner
DataFrameas
df
variable
select country,water_stress_index
from climate
order by water_stress_index desc;
  1. Query country, year, gdp_per_capita to get a list of distinct country names and their GDP in ascending order displaying the top 10 values.
Spinner
DataFrameas
df
variable
SELECT distinct country,year, gdp_per_capita as GDP
FROM climate
ORDER BY GDP
limit 10;

-- There are repeat values as there are multiple years - ideally one can perform a group by

Filter The data

  1. Filter the data to see country and year with stress index between 0.5 and 0.6
Spinner
DataFrameas
df
variable
SELECT country, year, water_stress_index
FROM climate
WHERE water_stress_index BETWEEN 0.5 AND 0.6;
-- water_stress_index > 0.5 AND water_stress_index < 0.6;
  1. Filter dataset for countries that start with the letter E or S and have water stress index > 0.5
Spinner
DataFrameas
df
variable
SELECT country, year, water_stress_index
FROM climate
WHERE (country like 'E%' OR country like 'S%') and water_stress_index > 0.5;

Aggregate group and sort data

  1. See what the average water_related_adaptation value is for each country for all the years and order by descending order of this average
Spinner
DataFrameas
df
variable
SELECT country, AVG(water_related_adaptation_tech) AS average_water_tech
FROM climate
GROUP BY country
ORDER BY average_water_tech desc;
  1. Find countries that have average water_related_adaptation values greater than 1 and list only those countries
Spinner
DataFrameas
df
variable
SELECT country
FROM climate
group by Country
having AVG(water_related_adaptation_tech) > 1;

-- having clause is used for performing filter operations on aggregated columns
‌
‌
‌