Simple SQL exercises for Data Enthusiasts: Counting, Summing, and Averaging City Populations

Task 1

The Solution in MySQL

To query the count of the number of cities in the CITY Table having a population larger than 100,000, you can use the following SQL query:

SELECT COUNT(*)
FROM CITY
WHERE POPULATION > 100000;

This query counts the number of rows in the CITY table where the POPULATION the column has a value greater than 100,000.

Task 2

The Solution

To query the total population of all cities in the CITY table where the District is ‘California’, you can use the following SQL query:

SELECT SUM(POPULATION) AS TotalPopulation
FROM CITY
WHERE DISTRICT = 'California';

This query calculates the sum of the POPULATION column for all rows in the CITY table where the DISTRICT the column has the value ‘California.’

Task 3

The Solution

To query the average population of all cities in the CITY table where the District is ‘California’, you can use the following SQL query:

SELECT AVG(POPULATION) AS AveragePopulation
FROM CITY
WHERE DISTRICT = 'California';

This query calculates the average of the POPULATION column for all rows in the CITY table where the DISTRICT column has the value ‘California’.

Task 4

The Solution

To query the average population for all cities in the CITY table, rounded down to the nearest integer, you can use the following SQL query:

SELECT FLOOR(AVG(POPULATION)) AS AveragePopulation
FROM CITY;

This query calculates the average of the POPULATION column for all rows in the CITY table and then uses the FLOOR function to round down the result to the nearest integer.