AWS re/Start Lab · Bases de Datos

Insert, Update, and Delete Data in a Database

This lab focuses on row-level changes inside the world database. The workflow inserts sample countries, updates numeric values, deletes the rows again, and then restores the complete sample dataset from a SQL backup file.

Lab Summary

Connected to the existing world database, inserted two countries into world.country, updated the Population and SurfaceArea columns for all rows, deleted the records, and restored the full sample dataset with world.sql.

Controlled Inserts

Added Ireland and Australia to the country table and verified the result with a filtered SELECT query.

Mass Updates and Delete

Updated every row in the table, then removed all rows to observe the effect of DML statements without conditions.

Bulk Restore

Reloaded the sample environment from an SQL file instead of recreating rows one by one.

Step-by-Step Walkthrough

The workflow demonstrates how row data changes over time and why verification after each statement matters.

01

Connect to MariaDB and verify the existing database

  • Connected to the Command Host with Session Manager, switched to root, and opened the database shell with mysql -u root --password='re:St@rt!9'.
  • Ran SHOW DATABASES; to confirm that the world database was already available before modifying any rows.
In this lab the database structure already exists, so the focus is on changing data inside the tables rather than creating schemas.
02

Insert, update, and delete rows in the country table

  • Checked the table with SELECT * FROM world.country; and then inserted Ireland and Australia using two separate INSERT INTO world.country VALUES (...) statements.
  • Verified the inserted rows with SELECT * FROM world.country WHERE Code IN ('IRL', 'AUS');.
  • Ran UPDATE world.country SET Population = 0; and confirmed that both rows were affected because no WHERE clause limited the update.
  • Applied a second update with UPDATE world.country SET Population = 100, SurfaceArea = 100; to change two columns for every row in the table.
  • Disabled foreign key checks with SET FOREIGN_KEY_CHECKS = 0;, deleted the rows with DELETE FROM world.country;, and confirmed the empty result set with SELECT * FROM world.country;.
Insert, update, and delete operations in the country table
Terminal: Two rows are inserted, updated, and finally deleted from world.country, showing how each statement changes the stored data.
03

Restore the database from the SQL backup file

  • Exited the MariaDB shell with QUIT; and verified the backup file path with ls /home/ec2-user/world.sql.
  • Loaded the full sample dataset with mysql -u root --password='re:St@rt!9' < /home/ec2-user/world.sql.
  • Reconnected to MariaDB, selected the world database, and ran SHOW TABLES; to verify the restored city, country, and countrylanguage tables.
  • Queried SELECT * FROM country; to confirm that the backup repopulated the table with many more rows than the two test inserts.
Importing the world.sql file and verifying restored tables
Terminal: The world.sql file is loaded with input redirection, the three tables are restored, and the country table shows the larger dataset again.
Using an SQL file is much faster than rebuilding a populated database manually. The redirection operator sends the script directly into the MariaDB client for execution.

Query Reference

Key statements used to manipulate rows and reload data from a backup script.

sql

INSERT INTO world.country VALUES (...);

Adds a full row to the country table using the same column order defined by the schema.

sql

SELECT * FROM world.country WHERE Code IN ('IRL', 'AUS');

Filters the result set to verify only the two inserted rows.

sql

UPDATE world.country SET Population = 0;

Updates the Population column for every row in the table because there is no WHERE clause.

sql

UPDATE world.country SET Population = 100, SurfaceArea = 100;

Updates multiple columns in the same statement.

sql

SET FOREIGN_KEY_CHECKS = 0;

Temporarily disables foreign key enforcement so dependent rows can be removed during the lab.

sql

DELETE FROM world.country;

Deletes all rows in the table because no filter condition is provided.

cmd

mysql -u root --password='re:St@rt!9' < /home/ec2-user/world.sql

Executes every statement inside the SQL file to restore tables and data in bulk.

Key Learnings

What Was Actually Learned

✓
INSERT adds new rows, while UPDATE modifies existing values already stored in the table.
✓
Running UPDATE or DELETE without WHERE affects all rows in scope, which is useful for demonstrations but risky in real environments.
✓
Input redirection with < /home/ec2-user/world.sql is a practical way to reload a full sample database quickly.

Technical Conclusion

This lab showed the difference between changing structure and changing stored data. Here the important part was observing how the same table can move through insert, update, delete, and restore stages in a short sequence.

The most important operational lesson was to verify the impact of each statement immediately. A quick SELECT after every change makes it easy to detect whether the table contents still match the intended state.