AWS re/Start Lab · Bases de Datos

Query the World Database with Functions and Conditions

This lab extends the earlier SELECT exercises by introducing inclusive ranges, the BETWEEN operator, pattern matching with LIKE, totals with SUM, column aliases, and case handling with LOWER in the WHERE clause.

Lab Summary

Connected to the Command Host, queried the world.country table with numeric ranges, rewrote the same range using BETWEEN, calculated Europe totals with SUM, added a readable alias, used LOWER for text matching, and solved the challenge by returning North America surface area and population totals.

Range Queries

Used both explicit comparison operators and BETWEEN to return countries within the same population interval.

Functions and Aliases

Calculated totals with SUM and assigned readable column names with AS.

Case Handling

Used LOWER and LIKE to search text more safely, then solved the final aggregation challenge.

Step-by-Step Walkthrough

This lab built on previous SELECT practice and showed how functions and clearer syntax make analytical queries easier to read.

01

Connect to the database and review the existing data

  • Opened the Command Host with Session Manager, switched to root, and connected to MariaDB with mysql -u root --password='re:St@rt!9'.
  • Verified that the world database existed with SHOW DATABASES;.
  • Reviewed the country table to understand the available columns before narrowing the result set.
This lab continues naturally after the previous SELECT exercises. The difference is that the queries now add more expressive operators and functions for better readability and analysis.
02

Use SUM, aliases, LOWER, and the final challenge query

  • Ran the Europe aggregation with SELECT sum(Population) ... WHERE Region LIKE "%Europe%"; and then repeated it with the alias AS "Europe Population Total" so the output label was easier to read.
  • Used LOWER(Region) LIKE "%central%" to return rows that contain central in the region name without depending on the original case.
  • Solved the challenge with SELECT SUM(SurfaceArea) AS "N. America Surface Area", SUM(Population) AS "N. America Population" FROM world.country WHERE Region = "North America";.
  • Confirmed that the final query returned both aggregated values in a single row, which was the expected result for the challenge.
SUM alias LOWER and challenge query results
Terminal: The output shows the Europe population total with an alias, the rows returned by the LOWER(...) search, and the final challenge query aggregating North America surface area and population.

Query Reference

Functions and operators that made the later SELECT statements more expressive.

sql

Population BETWEEN 50000000 AND 100000000

Returns an inclusive numeric range using a more readable operator.

sql

Region LIKE "%Europe%"

Matches region names containing the word Europe with wildcard characters.

sql

SUM(Population)

Calculates a total instead of returning individual row values.

sql

AS "Europe Population Total"

Assigns a readable alias to the aggregated result column.

sql

LOWER(Region) LIKE "%central%"

Normalizes text to lowercase before matching the pattern.

sql

SUM(SurfaceArea), SUM(Population) WHERE Region = "North America"

Returns both challenge totals in one query.

Key Learnings

What Was Actually Learned

✓
BETWEEN can replace paired comparison operators when the goal is to search within an inclusive range.
✓
Functions such as SUM and LOWER make queries more useful for analysis and more robust for text comparisons.
✓
Column aliases improve readability, especially when the result is an aggregate value rather than a raw column name.

Technical Conclusion

This lab showed that SQL becomes much more practical when the query language is used to express intent clearly. The same result can be reached in multiple ways, but some versions are easier to read and maintain.

The most valuable part was seeing how aggregation, pattern matching, and case normalization work together. Those features turn a basic SELECT into a more analytical query that answers broader questions about the dataset.