Tuesday, May 26, 2026

Cohort 3 | Project CloudIgnite Topics: SQL Fundamentals, Data Types, DDL & DML, CSV Import/Export Duration: ~3 hours

Key Takeaways

• SQL tools: SQLite (beginner), MySQL/PostgreSQL (production), DB Browser for SQLite (GUI)
• DDL: CREATE DATABASE/TABLE, ALTER TABLE, DROP TABLE, SHOW, DESCRIBE
• DML: INSERT INTO (with/without column names), UPDATE SET, DELETE FROM
• FLOAT cannot store exact values (5 may become 5.0001); use DECIMAL/REAL for precision
• CHAR(n) = fixed length; VARCHAR(n) = variable length — use CHAR when length is always fixed
• CSV import: LOAD DATA INFILE; export: SELECT INTO OUTFILE
• Data cleaning: common errors (data entry, wrong type, duplicates); TRIM, CONCAT, UPPER/LOWER functions
• EC2 + SSM Session Manager: connect to instances without SSH keys

Table of Contents

1. Tools for Practicing SQL
2. SQL Data Types
3. Database & Table Design
4. DDL — Data Definition Language
5. DML — Data Manipulation Language
6. CSV Files & Data Import/Export
7. Data Cleaning
8. Connecting to EC2 via SSM Session Manager
9. Lab Walkthrough Summary
10. Knowledge Check Answers
11. AWS CLF-C02 Relevance

1. Tools for Practicing SQL

• SQLite — recommended for beginners; no setup required, supports all common SQL commands.
• DB Browser for SQLite — a free, open-source GUI for SQLite. Available for Windows, Mac, Linux.
  • Website: sqlitebrowser.org
  • Just install and use — no server setup needed.
• MySQL / PostgreSQL — more feature-rich, used in production environments; requires server setup.
• Python — has built-in support for SQLite; you can run SQLite queries directly inside Python scripts.
• ORM (Object-Relational Mapper) — preferred in backend development for security (prevents SQL injection), but learn raw SQL first before using ORMs.
  • JavaScript: Sequelize
  • Golang: GORM
  • Python: SQLAlchemy (common)

2. SQL Data Types

Numeric Types

⚠️ Important: Floating-point numbers cannot store exact values. A stored value of 5 may be represented as 5.0001 or 4.99999. Use DECIMAL or REAL when precision matters (e.g. financial or scientific data).

Why does FLOAT work with less memory but more range?

• Integer (32-bit) → max ~10^10
• FLOAT (32-bit) → max ~10^38 digits
• Trade-off: larger range, but sacrifices exactness.

String / Character Types

Rule of thumb: Use CHAR when length is always fixed. Use VARCHAR when length varies — otherwise you waste memory.

Date & Time Types

Special Types

• ENUM — A predefined list of allowed values. The column only accepts values within the list.
  • Example: ENUM('Asia', 'Europe', 'Africa', 'North America', 'South America', 'Oceania', 'Antarctica')
  • If you try to insert a value not in the list, it violates the constraint and the insert will fail.

3. Database & Table Design

Relational Database

• A relational database is a collection of data items with predefined relationships.
• Tables are linked using primary keys and foreign keys.

Primary Key

• Uniquely identifies each record in a table.
• No two rows can have the same primary key value.

Foreign Key & Referential Integrity

• A foreign key in one table references a primary key in another table.
• Referential integrity rule: Foreign key values must match an existing primary key value in the referenced table.

Example Table Relationships (Any Company Books)

Customer ──< Orders >── Products
(one-to-many)   (one-to-many)

• Customer → places many Orders
• Orders → contains many Products

Suggested Columns per Table

Naming Conventions

• Use snake_case (underscores) — most common in SQL.
• Be consistent — pick one style and stick to it. Never mix styles.

4. DDL — Data Definition Language

DDL statements define and modify the structure of the database (schema). They do not insert or retrieve data.

Key DDL Commands

-- Create a new database
CREATE DATABASE world;

-- Select/use a database
USE world;

-- View all databases
SHOW DATABASES;

-- Create a table
CREATE TABLE city (
    name VARCHAR(30) NOT NULL,
    reason VARCHAR(30) NOT NULL
);

-- View all tables in current database
SHOW TABLES;

-- View column structure of a table
SHOW COLUMNS FROM country;
-- or
DESCRIBE country;

-- Rename a column
ALTER TABLE country RENAME COLUMN contintent TO continent;

-- Add a new column
ALTER TABLE city ADD COLUMN test VARCHAR(20) NOT NULL;

-- Modify an existing column (e.g. change length)
ALTER TABLE city MODIFY COLUMN test VARCHAR(100);

-- Delete (drop) a table — IRREVERSIBLE without backup!
DROP TABLE city;

-- Delete (drop) a database
DROP DATABASE world;

⚠️ DROP TABLE and DROP DATABASE are permanent. Always have a backup before running these.

DESCRIBE Statement

• Shows the schema (structure) of a table: column names, data types, constraints.
• Always use DESCRIBE <table> before inserting data so you know the expected types.

DESCRIBE country;

5. DML — Data Manipulation Language

DML statements manipulate data within existing tables (insert, update, delete, query).

INSERT

-- Insert a single row (with column names — best practice)
INSERT INTO country (code, name, continent, region, surface_area)
VALUES ('BRA', 'Brazil', 'South America', 'South America', 8547403.0);

-- Insert without column names (only when inserting ALL columns in exact order)
INSERT INTO country VALUES ('IRL', 'Ireland', 'Europe', 'British Isles', 70273.0, ...);

-- Insert multiple rows at once
INSERT INTO country (code, name) VALUES ('AUS', 'Australia'), ('NZL', 'New Zealand');

When to include column names:

• Always include if any field is optional (nullable).
• Always include if your data is not in the same order as the table columns.
• Safe practice: always include column names.

SELECT (basic usage in lab context)

-- Select all columns
SELECT * FROM country;

-- Select specific columns
SELECT code, name, continent FROM country;

-- Filter with WHERE
SELECT * FROM country WHERE code = 'IRL';

-- Filter with IN (multiple values)
SELECT * FROM country WHERE code IN ('IRL', 'AUS');

-- Check current database
SELECT DATABASE();

UPDATE

-- Update all rows (no condition)
UPDATE country SET population = 0;

-- Update multiple columns
UPDATE country SET population = 1000, surface_area = 100;

-- Update a specific row
UPDATE country SET population = 5000000, surface_area = 70273 WHERE code = 'IRL';

DELETE

-- Delete all rows from a table (table structure remains)
DELETE FROM country;

Note: DELETE FROM table removes all data but keeps the table. DROP TABLE removes the table entirely.

Semicolons

• Every SQL query must end with a semicolon (;).
• Without it, the database engine waits for more input on the next line.

6. CSV Files & Data Import/Export

What is a CSV?

• CSV = Comma-Separated Values
• A plain text file where each row is a record, and each value is separated by a comma.
• The first row is typically column headers (not actual data).
• File extension .csv — but it is just a text file; you can open it in any text editor or Excel.

Text Files vs Binary Files

• Text files — human-readable; can be opened in a text editor (e.g. .csv, .py, .json, .xml, .html, .sh).
• Binary files — not directly readable (e.g. .db, .exe, .mp3, .mp4).
• File extensions identify the type/purpose but do not change the underlying file format.
• On Linux/Unix, extensions are optional for text files (e.g. /etc/group has no extension).

Importing CSV to a Table

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE city
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;  -- Skip the header row

Steps:

1. Verify CSV structure matches the table schema (column count, data types, order).
2. Create the target table if it doesn't exist.
3. Run LOAD DATA INFILE.

Exporting a Table to CSV

SELECT id, name, country_code
FROM city
INTO OUTFILE '/path/to/output.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Importing from a .sql File

mysql -u root -p < world.sql

Or from within the MySQL/MariaDB shell, the instructor ran the source file to pre-populate the world database with 237 country records.

7. Data Cleaning

Before importing data, clean it to avoid errors and constraint violations.

Common Data Errors

Useful String Cleaning Functions in SQL

Note: SQL does not natively support full regular expressions like programming languages do. Use LIKE with wildcards for pattern matching.

8. Connecting to EC2 via SSM Session Manager

The lab uses AWS Systems Manager (SSM) Session Manager instead of SSH to connect to EC2.

Steps

1. Go to AWS Management Console → EC2
2. Select your running EC2 instance
3. Click Connect
4. Choose the Session Manager tab (not EC2 Instance Connect)
5. Click Connect

No SSH key required for Session Manager.

Paste in Session Manager Terminal

• Use Ctrl + Shift + V to paste in the browser-based terminal.

Basic Navigation Once Connected

# Switch to root user
sudo su

# Navigate to ec2-user home directory
cd /home/ec2-user

# List files
ls

9. Lab Walkthrough Summary

Lab: Creating Tables and Data Types (DDL)

1. Connect to EC2 via SSM Session Manager
2. Log in to MariaDB: mysql -u root -p<password>
3. Create database: CREATE DATABASE world;
4. Select database: USE world;
5. Create country table with columns including an ENUM for continent
6. Fix a typo in column name using ALTER TABLE ... RENAME COLUMN
7. Create a city table with name and reason columns (VARCHAR, NOT NULL)
8. Drop city table: DROP TABLE city;
9. Drop country table: DROP TABLE country;
10. Drop world database: DROP DATABASE world;
11. Submit lab before ending.

Lab: Insert, Update, Delete (DML)

1. Connect to MariaDB, navigate to world database
2. Insert rows manually into country table
3. Use SELECT * FROM country to verify
4. Use UPDATE country SET population = 0 to update all rows
5. Use UPDATE ... WHERE code = 'AUS' for conditional update
6. Use DELETE FROM country to remove all rows
7. Import data from world.sql file to repopulate the database
8. Use SELECT code, name FROM country — confirmed 237 rows of country data loaded
9. Submit lab before ending.

10. Knowledge Check Answers

11. AWS CLF-C02 Relevance

The majority of this lecture covers relational database concepts and SQL, which are foundational knowledge. The following items are directly or indirectly relevant to the AWS Certified Cloud Practitioner (CLF-C02) exam.

✅ Relevant Topics

📌 CLF-C02 Exam Tips Related to This Lecture

• Amazon RDS supports MySQL, MariaDB, PostgreSQL, Oracle, SQL Server, and Amazon Aurora. It is a managed relational database service — AWS handles backups, patching, and availability.
• Amazon Aurora is AWS's cloud-native relational DB, compatible with MySQL and PostgreSQL, with higher performance.
• Amazon DynamoDB is the AWS non-relational (NoSQL) database — contrasts with what was covered today.
• EC2 + self-installed DB (like today's lab) = unmanaged — you handle everything yourself. RDS = managed.
• AWS Systems Manager (SSM) is tested under the Management & Governance domain. Session Manager specifically removes the need for open SSH ports (port 22), improving security posture — a relevant security concept for CLF-C02.

❌ Not Directly Tested in CLF-C02

• Raw SQL syntax (INSERT, ALTER TABLE, etc.) is not tested in CLF-C02.
• SQLite is not an AWS service and is not tested.
• Detailed floating-point precision differences are not tested.

Notes compiled from lecture — May 26, 2026 Next class: Thursday (tomorrow is a public holiday — Eid Mubarak 🌙) Next topics: NULL statement, SELECT in depth, KC review