AWS re/Start Lab · Bases de Datos

Create and Drop Databases and Tables

This lab practices SQL structure management from the terminal. Using a MariaDB session opened on the Command Host, the workflow creates a database, defines tables, corrects a schema mistake, and then removes the temporary objects again.

Lab Summary

Connected to the Command Host through Session Manager, opened a MariaDB shell, created the world database, defined the country table, corrected the misspelled Conitinent column, created a second table for the challenge, and finally dropped the temporary tables and database.

Connection Setup

Opened the Command Host, switched to the required user context, and connected to MariaDB from the terminal.

Schema Creation

Created a new database and tables, then inspected the resulting structure to confirm the columns and keys.

Cleanup Validation

Dropped the temporary tables and the world database, then verified that only the default schemas remained available.

Step-by-Step Walkthrough

The lab focused on database definition language operations, starting from a new schema and ending with a controlled cleanup.

01

Connect to the Command Host and open MariaDB

  • Opened the EC2 Command Host from the AWS console and connected through the Session Manager tab.
  • Ran sudo su and cd /home/ec2-user/ to work with the required permissions and paths.
  • Connected to the database engine with mysql -u root --password='re:St@rt!9'.
These first commands are repeated in the database labs because the terminal session is the actual workspace for all SQL operations.
02

Create the world database and fix the country table schema

  • Listed the existing schemas with SHOW DATABASES; and then created a new database with CREATE DATABASE world;.
  • Created the world.country table with its full schema, including the Code primary key and the geographic columns used later in the course.
  • Selected the database with USE world;, confirmed the table with SHOW TABLES;, and inspected the schema using SHOW COLUMNS FROM world.country;.
  • Detected that the continent column had been created as Conitinent, then corrected it with ALTER TABLE world.country RENAME COLUMN Conitinent TO Continent;.
Schema before and after renaming the Continent column
Terminal: The country table initially shows the misspelled Conitinent column, then the schema is checked again after the rename to confirm Continent.
03

Create the city table and remove the temporary schema

  • Completed the challenge by creating world.city with two character columns: Name and Region.
  • Dropped the city table and then dropped world.country with separate DROP TABLE statements.
  • Ran SHOW TABLES; to confirm the database was empty before removing the full schema with DROP DATABASE world;.
  • Finished with SHOW DATABASES; to verify that only the default schemas remained available.
Dropping tables and the world database
Terminal: The city and country tables are dropped, SHOW TABLES; returns an empty set, and DROP DATABASE world; removes the schema completely.
Note: DROP TABLE and DROP DATABASE remove structure, not only data. Without a backup, those objects cannot be recovered automatically.

Query Reference

Main commands and SQL statements used to build, inspect, modify, and remove the schema.

cmd

mysql -u root --password='re:St@rt!9'

Opens the MariaDB command-line client with the root account configured for the lab.

sql

SHOW DATABASES;

Lists the schemas currently available on the server.

sql

CREATE DATABASE world;

Creates a new database named world.

sql

SHOW COLUMNS FROM world.country;

Displays the table structure, including column names, data types, keys, and defaults.

sql

ALTER TABLE world.country RENAME COLUMN Conitinent TO Continent;

Renames the misspelled column without recreating the full table.

sql

DROP TABLE world.country;

Deletes the table definition and all rows stored in it.

sql

DROP DATABASE world;

Removes the database and every object still contained inside it.

Key Learnings

What Was Actually Learned

✓
CREATE defines new schemas and tables, while DROP removes them completely.
✓
SHOW DATABASES, SHOW TABLES, and SHOW COLUMNS are the fastest way to confirm the current structure before and after a change.
✓
ALTER TABLE can correct schema mistakes without rebuilding the whole table from scratch.

Technical Conclusion

This lab was focused on structure rather than row data. The main lesson was that database work is not only about storing values, but also about defining the shape of the information correctly.

The verification steps mattered as much as the creation steps. By checking the schema before and after each change, it became easy to confirm that the table definition matched the intended design and that the cleanup really returned the environment to its initial state.