MySQL - Ejercicio de agrupaciones

agrupaciones.sql

-- Video de Youtube: https://www.youtube.com/watch?v=aUZ6452xUOQ&feature=youtu.be

use world;

SELECT 
    *
FROM
    city;


-- 1. Create a report that displays the Code of a country along with the number of cities of that country. (GROUP BY, COUNT)
SELECT 
    CountryCode, COUNT(*) AS total_ciudades_por_pais
FROM
    city
GROUP BY CountryCode;



-- 2. Restrict the previous query to the countries with more than 200 cities. (GROUP BY, HAVING)
SELECT 
    CountryCode, COUNT(*) AS total_ciudades_por_pais
FROM
    city
GROUP BY CountryCode
HAVING COUNT(*) > 200;





-- 3. Run the next query and explain what happens:

SELECT 
    countrycode, COUNT(*)
FROM
    city
GROUP BY countrycode
HAVING COUNT(*) > 200;


-- 4. Create a report that displays the Code of a country along with the number of cities of that country and the total population of these cities. (GROUP BY, COUNT,SUM)
SELECT 
    countryCode,
    COUNT(*) AS total_ciudades,
    SUM(population) AS total_poblacion
FROM
    city
GROUP BY countryCode
ORDER BY countryCode ASC;




-- 5. Create a report that displays the district, the code of a country along with the number of cities of that district. (GROUP BY on two fields, COUNT)
SELECT 
    district, countryCode, COUNT(*) AS total_ciudades_distrito
FROM
    city
GROUP BY district , countryCode
ORDER BY countryCode ASC , district ASC;





-- 6. Create a report that displays the the code of a country along with the number of cities of that country, the total population of these cities and the average population (GROUP BY, COUNT, SUM, AVG)
SELECT 
    countryCode,
    COUNT(*) AS total_ciudades,
    SUM(population) AS total_poblacion,
    ROUND(AVG(population), 0) AS promedio_poblacion
FROM
    city
GROUP BY countryCode
ORDER BY CountryCode ASC;






-- 7. Create a report that displays the district, the code of a country along with the number of cities of that district, the total population of these cities and the average population. 
-- Select only the district starting with 'a' (GROUP BY on two fields, COUNT, SUM, AVG, HAVING)
SELECT 
    district,
    countryCode,
    COUNT(*) AS total_ciudades,
    SUM(population) AS total_poblacion,
    ROUND(AVG(population), 0) AS promedio_poblacion
FROM
    city
GROUP BY countryCode , district
HAVING district LIKE 'a%'
ORDER BY CountryCode ASC;



-- 8. Restrict the previous query to the districts with more than 10 cities. (HAVING, AND)
SELECT 
    district,
    countryCode,
    COUNT(*) AS total_ciudades,
    SUM(population) AS total_poblacion,
    ROUND(AVG(population), 0) AS promedio_poblacion
FROM
    city
GROUP BY countryCode , district
HAVING district LIKE 'a%' AND COUNT(*) > 10
ORDER BY CountryCode ASC;




-- 9. Create a report that displays the number of cities in the table, the total population, -- --  the average population, the minimal population value and the maximal population value. (COUNT, SUM, AVG, MIN, MAX)
SELECT 
    COUNT(*) AS total_ciudades,
    SUM(population) AS total_poblacion,
    AVG(population) AS promedio_poblacion,
    MIN(population) AS poblacion_minima,
    MAX(population) AS poblacion_maxima
FROM
    city;