AWS re/Start Lab · Bases de Datos

Selecting Data from a Database

This lab focuses on reading data instead of changing it. The work starts with broad result sets and gradually narrows them using COUNT(*), aliases, ordering, and filtered conditions in the WHERE clause.

Lab Summary

Connected to the world database, queried full and partial result sets from world.country, counted rows with COUNT(*), renamed columns with AS, sorted by population, and solved a final challenge with combined WHERE conditions.

Schema Inspection

Started with broad queries to understand how many rows existed and which columns were available in the table.

Ordered Result Sets

Used aliases and ascending or descending ordering to make the output easier to read.

Filtered Queries

Applied numeric and text conditions to answer a concrete question about Southern Europe.

Step-by-Step Walkthrough

The queries move from general exploration to targeted filtering, which is the normal progression when analyzing a dataset.

01

Connect to the Command Host and confirm the world database

  • Opened the Command Host through Session Manager, switched to root, and connected with mysql -u root --password='re:St@rt!9'.
  • Ran SHOW DATABASES; to confirm that the world database was available before starting the query exercises.
This setup is the same as in the previous database labs. The difference here is that the focus stays on reading and filtering data, not on changing it.
02

Inspect the table structure and basic result sets

  • Queried the full table with SELECT * FROM world.country; to view the raw dataset.
  • Counted the rows with SELECT COUNT(*) FROM world.country; and checked the table definition with SHOW COLUMNS FROM world.country;.
  • Selected only the columns that mattered for the exercise with SELECT Name, Capital, Region, SurfaceArea, Population FROM world.country;.
Counting rows, showing columns, and selecting specific fields
Terminal: The query sequence moves from total row count to schema inspection and then to a narrower result set with only the requested columns.
03

Order and filter the result set

  • Renamed SurfaceArea with the alias AS "Surface Area" to make the output easier to read.
  • Sorted the countries by population using ORDER BY Population and then reversed the order with ORDER BY Population DESC.
  • Applied the condition WHERE Population > 50000000 to focus on larger countries.
  • Added a second condition with AND Population < 100000000 to narrow the results to a more specific range.
Ordered and filtered query results
Terminal: The result set is sorted by population and then filtered with WHERE and AND to keep only the rows that satisfy both conditions.
04

Answer the challenge question

  • Built a query for the question: Which country in Southern Europe has a population greater than 50 million?
  • Used WHERE Population > 50000000 AND Region = "Southern Europe" to combine the numeric and text filters in one statement.
  • The result returned Italy, which satisfies both conditions in the dataset.
Challenge query returning Italy
Terminal: The final challenge query returns Italy as the country in Southern Europe with a population greater than 50 million.

Query Reference

Main read-only statements and operators used to inspect, sort, and filter the dataset.

sql

SELECT * FROM world.country;

Returns every column and every row in the table.

sql

SELECT COUNT(*) FROM world.country;

Counts the total number of rows in the table.

sql

SHOW COLUMNS FROM world.country;

Lists the schema so you can see which columns are available for selection or filtering.

sql

SELECT Name, Capital, Region, SurfaceArea, Population FROM world.country;

Returns only the selected columns instead of the full table.

sql

ORDER BY Population DESC

Sorts the result set by population in descending order.

sql

WHERE Population > 50000000

Keeps only rows that match the numeric condition.

sql

WHERE Population > 50000000 AND Region = "Southern Europe"

Combines two conditions so the result answers a much more specific question.

Key Learnings

What Was Actually Learned

✓
COUNT(*) is a quick way to measure table size before doing more detailed analysis.
✓
Choosing only the needed columns makes the result set easier to read than using SELECT * all the time.
✓
WHERE, ORDER BY, and AND turn a general query into a precise answer to a real question.

Technical Conclusion

This lab showed that querying is not only about retrieving rows. It is about shaping the output so that it becomes useful for decision-making or validation.

The final challenge made that point clear. Instead of scanning the full table manually, a well-constructed SELECT statement narrowed the dataset to one answer with a clear and repeatable condition set.