✅ Day 19 of My Cloud Journey — Mastering Advanced MySQL Queries & Concepts for Cloud Projects 🧠🛠️

After learning the basics of MySQL, today I deep-dived into the advanced features of SQL — the kind that powers real-world applications like WordPress and large-scale cloud-native systems.

Understanding advanced MySQL is essential for anyone building scalable and secure systems on AWS (or any cloud), especially when working with LAMP/LEMP stacks.

---

📘 Topics Covered Today:

1. 🔄 SQL JOINS — Combining Data from Multiple Tables

SQL JOINs allow you to retrieve related data from multiple tables.

🔹 INNER JOIN

SELECT employees.name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.id;

Returns only matching records in both tables.

🔹 LEFT JOIN

SELECT employees.name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.id;

Returns all records from the left table and matched records from the right.

🔹 RIGHT JOIN

SELECT employees.name, departments.dept_name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.id;

Opposite of LEFT JOIN.

🔹 FULL OUTER JOIN (Not supported in MySQL directly)

SELECT name, dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.id
UNION
SELECT name, dept_name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.id;

---

2. 🔐 FOREIGN KEYS — Maintaining Data Integrity

Foreign keys ensure that the relationship between two tables remains consistent.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

• Prevents inserting a value in dept_id unless it exists in departments.

---

3. 🏗️ DATABASE NORMALIZATION — Keeping Data Clean and Efficient

Normalization helps avoid redundancy and inconsistency.

📌 1NF: Eliminate Repeating Groups

Each column should have atomic values.

📌 2NF: Eliminate Partial Dependency

Every non-key attribute should depend on the whole primary key.

📌 3NF: Eliminate Transitive Dependency

Attributes should depend only on the primary key.

🔍 Benefit: Easier updates, consistency, and better performance at scale.

---

4. ✅ CONSTRAINTS — Validating Data at the DB Level

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE NOT NULL,
  age INT CHECK (age >= 18)
);

---

5. 🧰 BACKUP & RESTORE — Protecting Your Data

🔸 Exporting (Backup):

mysqldump -u root -p mydb > mydb_backup.sql

🔸 Importing (Restore):

mysql -u root -p mydb < mydb_backup.sql

💡 Always backup before major changes or upgrades!

---

🧪 Practical Use Case — Preparing for WordPress Hosting

Since WordPress uses MySQL as its backend, today’s learning is crucial before deployment. I’ve created:

• A normalized DB schema

• Setup foreign keys for posts, users, and categories

• Backed up the database for disaster recovery

• Understood how WordPress will connect using LAMP/LEMP

---

🚀 Realizations from Today

• SQL is powerful when you understand how tables relate

• JOINs and Constraints help build enterprise-grade systems

• Backup & normalization are non-negotiable in production

• You can’t deploy apps like WordPress effectively without solid DB knowledge

---

📅 What’s Next?

Day 20 — I’ll deploy WordPress on EC2 using a custom LAMP/LEMP setup:

• Setup Apache/Nginx web server

• Connect WordPress to a Mariadb database

• Explore wp-config.php, database creation, and secure deployment

We’re getting closer to launching real apps from scratch! 🎯

---

🧠 Key Terms

• SQL JOINs (INNER, LEFT, RIGHT, FULL)

• Foreign Keys

• Database Normalization (1NF, 2NF, 3NF)

• Constraints

• Backup & Restore

---

---

#Day19 #MySQL #DatabaseDesign #WordPressPrep #JOINs #Normalization #LAMP #LEMP #AWS #DevJourney #90DaysOfCloud #shubhamlondhe