sample_queries.sql

-- Sample SQL Queries for Sustainable Energy Database
-- Database: sustainable_energy.db
-- Table: sustainable_energy

-- 1. View all available columns
SELECT sql FROM sqlite_master WHERE type='table' AND name='sustainable_energy';

-- 2. Get total number of records
SELECT COUNT(*) as total_records FROM sustainable_energy;

-- 3. Get unique countries/entities
SELECT DISTINCT Entity FROM sustainable_energy ORDER BY Entity;

-- 4. Get data for a specific country (e.g., Italy)
SELECT 
    Entity,
    Year,
    Access_to_electricity_pct_of_population,
    Renewable_energy_share_in_the_total_final_energy_consumption_pct,
    Electricity_from_renewables_TWh,
    Value_co2_emissions_kt_by_country
FROM sustainable_energy
WHERE Entity = 'Italy'
ORDER BY Year;

-- 5. Get countries with highest renewable energy share in 2020
SELECT 
    Entity,
    Year,
    Renewable_energy_share_in_the_total_final_energy_consumption_pct,
    Electricity_from_renewables_TWh
FROM sustainable_energy
WHERE Year = 2020 
    AND Renewable_energy_share_in_the_total_final_energy_consumption_pct IS NOT NULL
ORDER BY Renewable_energy_share_in_the_total_final_energy_consumption_pct DESC
LIMIT 10;

-- 6. Get average CO2 emissions by country (latest year available)
SELECT 
    Entity,
    MAX(Year) as latest_year,
    AVG(Value_co2_emissions_kt_by_country) as avg_co2_emissions
FROM sustainable_energy
WHERE Value_co2_emissions_kt_by_country IS NOT NULL
GROUP BY Entity
ORDER BY avg_co2_emissions DESC
LIMIT 20;

-- 7. Get countries with 100% electricity access in 2020
SELECT 
    Entity,
    Year,
    Access_to_electricity_pct_of_population
FROM sustainable_energy
WHERE Year = 2020 
    AND Access_to_electricity_pct_of_population >= 100
ORDER BY Entity;

-- 8. Get year range for each country
SELECT 
    Entity,
    MIN(Year) as first_year,
    MAX(Year) as last_year,
    COUNT(*) as data_points
FROM sustainable_energy
GROUP BY Entity
ORDER BY Entity;

-- 9. Get countries with highest GDP per capita in 2020
SELECT 
    Entity,
    Year,
    gdp_per_capita,
    gdp_growth
FROM sustainable_energy
WHERE Year = 2020 
    AND gdp_per_capita IS NOT NULL
ORDER BY gdp_per_capita DESC
LIMIT 20;

-- 10. Compare renewable energy trends for multiple countries
SELECT 
    Entity,
    Year,
    Renewable_energy_share_in_the_total_final_energy_consumption_pct,
    Electricity_from_renewables_TWh
FROM sustainable_energy
WHERE Entity IN ('Germany', 'France', 'Italy', 'Spain', 'United Kingdom')
    AND Renewable_energy_share_in_the_total_final_energy_consumption_pct IS NOT NULL
ORDER BY Entity, Year;