Advanced SQL Queries for Country Data Analysis

The following examples demonstrate complex SQL techniques using conditional logic (IF, CASE), subqueries, variable assignment, and string manipulation to analyze geographical and economic data stored in tables like Pais (Country), Ciudad (City), and LenguaPais (Country Language).

1. Independence Year Formatting for African Countries

This query retrieves the name of countries in Africa and formats their independence year (AnyIndep) to display whether it was B.C. (a.C.) or A.D. (d.C.).

SELECT Nombre,
    IF(AnyIndep > 0,
        CONCAT(AnyIndep,' d.C'),
        IF(AnyIndep < 0,
            CONCAT(ABS(AnyIndep),' a.C'),
            'N/A')) AS 'Año de Independencia'
FROM Pais
WHERE Continente = 'Africa'

2. City Population Comparison Against Country Average

This query determines if a city's Población (Population) is greater than, equal to, or less than the average population of all cities within its respective country, utilizing a correlated subquery.

SELECT Nombre,
    IF(Poblacion >=(SELECT AVG(Poblacion)
                    FROM Ciudad AS CiudadInt
                    WHERE Ciudad.CodigoPais = CiudadInt.CodigoPais),
        'Mayor o igual',
        'Menor') AS 'Población'
FROM Ciudad
GROUP BY 1

Note: The original query used 'Menor o igual' in the IF statement. Since the condition handles 'Mayor o igual', the alternative label is simplified to 'Menor' for clarity in distinguishing between above/at average and below average.

3. Economic Status Classification Using GDP Growth

Using the CASE statement, this query classifies the economic status of a country based on the ratio of its current Gross National Product (PNB) to its previous year's PNB (PNBAnt).

• Less than 0.8: Gran desaceleración (Great slowdown)
• 0.8 to 0.95: Desaceleración (Slowdown)
• 0.95 to 1.05: Estable (Stable)
• 1.05 to 1.2: Crecimiento (Growth)
• Greater than 1.2: Gran crecimiento (Great growth)

SELECT Nombre AS 'País',
    CASE
        WHEN PNB/PNBAnt < 0.8 THEN 'Gran desaceleración'
        WHEN PNB/PNBAnt BETWEEN 0.8 AND 0.95 THEN 'Desaceleración'
        WHEN PNB/PNBAnt BETWEEN 0.95 AND 1.05 THEN 'Estable'
        WHEN PNB/PNBAnt BETWEEN 1.05 AND 1.2 THEN 'Crecimiento'
        WHEN PNB/PNBAnt > 1.2 THEN 'Gran crecimiento'
        ELSE 'n/a'
    END AS 'Estado de su Economía'
FROM Pais

4. Dynamic Data Selection Based on Independence Year Digit

This query dynamically selects one of three metrics (Superficie, Población, or PNB) based on the last digit of the country's independence year (AnyInDep).

SELECT Nombre AS 'País',
    CASE
        WHEN RIGHT(AnyInDep,1)>=1 AND RIGHT(AnyInDep,1)<=3 THEN Superficie
        WHEN RIGHT(AnyInDep,1)>=4 AND RIGHT(AnyInDep,1)<=6 THEN Poblacion
        WHEN RIGHT(AnyInDep,1)>=7 AND RIGHT(AnyInDep,1)<=9 THEN PNB
        ELSE "n/a"
    END AS 'Datos'
FROM Pais

5. Complex Index Calculation and Population Filtering

This example calculates a composite index (@indice) based on three distinct ratios: geographical regions per continent, city zones per country, and official languages versus non-official languages. It then filters countries whose population is less than this index multiplied by 1000.

SET @a := (SELECT COUNT(DISTINCT Region)/COUNT(DISTINCT Continente)
           FROM Pais);
SET @b := (SELECT COUNT(DISTINCT Zona)/COUNT(DISTINCT CodigoPais)
           FROM Ciudad);
SET @c := (SELECT (SELECT COUNT(DISTINCT Lengua)
                   FROM LenguaPais
                   WHERE EsOficial = 'T')/
                  (SELECT COUNT(DISTINCT Lengua)
                   FROM LenguaPais
                   WHERE EsOficial = 'F'));
SET @indice := (@a+@b+@c)*1000;
SELECT Nombre
FROM Pais
WHERE Poblacion < @indice

6. Composite Index Calculation Using Session Variables

This final query calculates three variables (@a, @b, @c) within the SELECT statement itself, representing GDP growth ratio, city density per zone, and population density, respectively. It then computes a final Índice based on these variables.

SELECT Nombre,
    @a:=ROUND((SELECT PNB/PNBAnt),2) AS 'Variable a',
    @b:=(SELECT ROUND(COUNT(DISTINCT Nombre)/COUNT(DISTINCT Zona),2)
          FROM Ciudad
          WHERE Ciudad.CodigoPais = Pais.Codigo) AS 'Variable b',
    @c:=ROUND(Poblacion/Superficie,2) AS 'Variable c',
    ROUND((@a+@b)/@c,3) AS 'Índice'
FROM Pais;