Saturday, May 30, 2026

Cohort 3 | Project CloudIgnite Topics: MySQL Database Fundamentals (Labs 268–271) Duration: ~3 hours

Key Takeaways

• Labs 268–271: Full hands-on MySQL practice on EC2 via SSM Session Manager
• Lab 268: CREATE DATABASE/TABLE with ENUM constraints, DESCRIBE, ALTER TABLE, DROP
• Lab 269: INSERT (positional and named columns), UPDATE with/without WHERE, DELETE, foreign key constraints
• Lab 270: SELECT, WHERE, ORDER BY, IS NULL, IN, LIMIT, column aliases
• Lab 271: BETWEEN (inclusive range), LIKE with wildcards (%), aggregate functions (COUNT, SUM, AVG)
• Common mistakes: using == instead of =, forgetting WHERE on UPDATE/DELETE, parent-child delete order
• Key insight: DROP TABLE is permanent; DELETE FROM keeps the table structure

Table of Contents

1. Lab 268 — Creating Databases & Tables
2. Lab 269 — Inserting & Manipulating Data
3. Lab 270 — Reading & Querying Data
4. Lab 271 — Conditional Queries & Aggregate Functions
5. Key Concepts Summary
6. Common Mistakes to Avoid
7. AWS CLF-C02 Relevance

Lab 268 — Creating Databases & Tables

Connecting to the Environment

• Labs run on AWS EC2 instances, accessed via SSM Session Manager (from the AWS Console → EC2 → Connect).
• Login as root inside the instance:

  sudo su
  cd /home/ec2-user
  

• Connect to MySQL:

  mysql -u root -p<password>
  

Database Basics

• Default databases present after any MySQL install: performance_schema, mysql, information_schema.
• These are system databases — do not modify them.

Creating a Database

CREATE DATABASE world;
SHOW DATABASES;   -- verify it was created

Creating a Table

CREATE TABLE world.country (
  Code       CHAR(3)        NOT NULL DEFAULT '',
  Name       CHAR(52)       NOT NULL DEFAULT '',
  Continent  ENUM('Asia','Europe','North America','Africa',
                  'Oceania','Antarctica','South America')
             NOT NULL DEFAULT 'Asia',
  Region     CHAR(26)       NOT NULL DEFAULT '',
  SurfaceArea FLOAT(10,2)   NOT NULL DEFAULT '0.00',
  IndepYear  SMALLINT(6)    DEFAULT NULL,
  Population INT(11)        NOT NULL DEFAULT '0',
  LifeExpectancy FLOAT(3,1) DEFAULT NULL,
  ...
);

Key Column Constraints

NULL vs Empty String: NULL means the value does not exist. An empty string '' is a value — it just happens to be empty.

CHAR(n) — What Does the Number Mean?

CHAR(25) means the column stores up to 25 characters. If a name is longer than 25 characters, the insert will fail. Increase the number if needed.

Inspecting a Table Schema

DESCRIBE world.country;
-- or equivalently:
SHOW COLUMNS FROM world.country;

Both commands show: column name, data type, nullable (yes/no), key, default value, and extra constraints.

Renaming a Column (ALTER TABLE)

ALTER TABLE world.country
  RENAME COLUMN carnitinent TO Continent;

Dropping (Deleting) Tables and Databases

DROP TABLE world.city;        -- deletes the city table
DROP TABLE world.country;     -- deletes the country table
DROP DATABASE world;          -- deletes the entire database + all tables

Lab 269 — Inserting & Manipulating Data

Switching to a Database

USE world;          -- now you don't need to prefix every table with "world."
SHOW TABLES;        -- lists all tables in current database

Inserting Data

-- Without specifying columns (values map positionally to columns)
INSERT INTO country VALUES ('IRL', 'Ireland', 'Europe', 'British Islands', 70273.00, 1921, 3775100, 76.8, ...);

-- Specifying columns (useful when you only have values for some columns)
INSERT INTO country (Code, Name, Continent, Population)
VALUES ('TST', 'TestCountry', 'Europe', 1000000);

Rule: If you don't specify column names, the number of values must exactly match the number of columns. If you skip a column, you must either pass NULL for it or specify only the columns you have data for.

Updating Data

-- Unconditional update (affects ALL rows)
UPDATE world.country SET Population = 0;

-- Conditional update (affects only matching rows)
UPDATE world.country SET Population = 100 WHERE Code = 'IRL';

Always use a WHERE clause unless you deliberately want to update every row.

Deleting Data

-- Delete all rows from a table
DELETE FROM world.country;

-- Delete specific rows
DELETE FROM world.country WHERE Code = 'TST';

Foreign Keys & Referential Integrity

• A foreign key is a column in one table that references the primary key of another table.
• Example: city.CountryCode references country.Code.
• If a foreign key constraint exists, you cannot delete a parent row while child rows still reference it.
  • You must delete child rows (city) first, then parent rows (country).
  • Or: temporarily disable foreign key checks:

    SET foreign_key_checks = 0;
    

Loading Data from an SQL File

mysql -u root -p<password> < world.sql

This executes all SQL statements in the file (bulk inserts, table creation, etc.).

Lab 270 — Reading & Querying Data

Basic SELECT

SELECT * FROM world.country LIMIT 10;

-- Select specific columns
SELECT Name, Capital, Region, SurfaceArea, Population FROM world.country;

Column Aliases

SELECT COUNT(*) AS "Number of Countries" FROM world.country;
-- Alias with spaces must be in quotes

Sorting with ORDER BY

-- Ascending (default)
SELECT Name, Population FROM world.country ORDER BY Population;

-- Descending
SELECT Name, Population FROM world.country ORDER BY Population DESC;

Filtering with WHERE

-- Single condition
SELECT Name, Population FROM world.country WHERE Population > 50000000;

-- Multiple conditions
SELECT Name FROM world.country WHERE Population > 50000000 ORDER BY Population DESC;

Checking for NULL Values

SELECT Name FROM world.country WHERE LifeExpectancy IS NULL;
-- Returns rows where LifeExpectancy has no value (17 rows in the dataset)

IN Clause

SELECT * FROM world.country WHERE Code IN ('IRL', 'AUS');
-- Returns rows where Code is either IRL or AUS

DB Browser for SQLite (Offline Practice Tool)

• A GUI tool to practice SQL locally without a server.
• Open or create a .db file → go to Execute SQL tab → write and run queries.
• You can ask an AI (e.g. Gemini) to generate sample table schemas and INSERT statements for practice data.
• Syntax note: SQLite uses = (not ==) for equality in WHERE clauses — same as MySQL.

Lab 271 — Conditional Queries & Aggregate Functions

BETWEEN Clause

-- Equivalent to: Population >= 50000000 AND Population <= 100000000
SELECT Name, Capital, Region, SurfaceArea, Population
FROM world.country
WHERE Population BETWEEN 50000000 AND 100000000;

Tip when typing large numbers: type the base number first, then count zeros in groups of 3 (e.g. 50 + 000000 = 50 million).

LIKE Clause (Pattern Matching)

-- Find all European countries (region contains 'Europe')
SELECT SUM(Population) FROM world.country
WHERE Region LIKE '%Europe%';

-- Case-insensitive version using LOWER()
SELECT SUM(Population) FROM world.country
WHERE LOWER(Region) LIKE '%europe%';

• % is a wildcard meaning zero or more of any character.
• LOWER() converts a string to lowercase before comparison.
• UPPER() converts to uppercase (use matching case in your pattern).

Aggregate Functions

-- Count all rows
SELECT COUNT(*) AS "Number of Countries" FROM world.country;

-- Sum population and surface area for North America
SELECT SUM(Population) AS total_population,
       SUM(SurfaceArea) AS total_area
FROM world.country
WHERE LOWER(Region) LIKE '%north america%';

Key Concepts Summary

SQL Command Categories (Quick Reference)

DESCRIBE vs SHOW

• DESCRIBE <table> and SHOW COLUMNS FROM <table> → same output (table schema/structure).
• SHOW TABLES → lists table names only (no schema detail).
• SHOW DATABASES → lists all databases.

NULL Rules

• DEFAULT NULL = column is optional.
• NOT NULL = column is required; an insert without a value will fail.
• To check for NULL: use IS NULL or IS NOT NULL — never use = NULL.

Common Mistakes to Avoid

AWS CLF-C02 Relevance

The majority of this lecture focuses on MySQL SQL fundamentals, which is not directly tested on the AWS Certified Cloud Practitioner (CLF-C02) exam. However, the following elements are relevant:

✅ Relevant to CLF-C02

1. Amazon EC2 (Elastic Compute Cloud)

• The labs ran on EC2 instances — a core AWS compute service.
• CLF-C02 tests your understanding of EC2 as an IaaS offering (virtual servers in the cloud).

2. AWS Systems Manager — Session Manager (SSM)

• Used throughout the labs to connect to EC2 instances without SSH keys or open port 22.
• CLF-C02 covers AWS Systems Manager as a management/operations tool.
• Session Manager is relevant to the security best practice of minimising open inbound ports.

3. Security Groups & Network ACLs (NACLs)

• The instructor demonstrated checking inbound rules on a security group (port 22, source 0.0.0.0/0) and subnet-level NACLs.
• CLF-C02 tests the difference between:
  • Security Groups — stateful, instance-level firewall.
  • Network ACLs — stateless, subnet-level firewall.

4. AWS Shared Responsibility Model (Indirect)

• Running a MySQL database on EC2 means you manage the database software (patching, backups, config).
• This contrasts with Amazon RDS (managed database service), where AWS handles engine patching and backups.
• CLF-C02 tests this distinction as part of the Shared Responsibility Model.

❌ Not on CLF-C02

• SQL syntax (CREATE TABLE, SELECT, INSERT, etc.)
• MySQL-specific commands (DESCRIBE, SHOW COLUMNS, etc.)
• Database schema design and foreign key constraints

💡 Bonus: Related AWS Services to Know for CLF-C02