Module Nine

📖 Every lesson in this course is free to read right here, no account needed. Create a free account to track your progress, take the exam, and earn your certificate.

Course Outline

Course Outline · MySQL for Data Analysis Expert

📘 Course Outline: MySQL for Data Analysis Expert

Welcome, young data explorer! This course will teach you how to use MySQL, a powerful tool for storing and analyzing data. MySQL is a type of database that helps us organize information so we can find answers quickly. Think of it as a giant digital filing cabinet. By the end of this course, you will be able to ask questions like "What are the top-selling products?" or "How many students passed the test?" and get answers from your data. Let's begin our adventure!

🎯 Course Learning Objectives

By the end of this course, you will be able to:

Understand what a database is and why we use MySQL.
Create tables to store data.
Insert, update, and delete data.
Write queries to retrieve specific information.
Filter and sort data.
Use functions to calculate totals and averages.
Join tables to combine information.
Group data to see summaries.
Write advanced queries for complex questions.
Analyze data like a pro!

📖 Warm-up Story: The Magic Library

Chidi loves reading. He has a huge collection of books at home. But he can't find his favorite book because they are all mixed up. His dad helps him organize them by genre, author, and title. Now he can find any book in seconds. A database is like this organized library. MySQL is the librarian that helps you find, add, and organize data quickly.

📚 Course Modules (15 Modules)

Module 1: What is a Database?

Definition: A database is a collection of organized data.

Why important: It helps us store and find information easily.

Simple explanation: It's like a giant digital filing cabinet.

Real-life example: A school database stores student names, grades, and attendance.

School example: Your school's library system is a database.

Home example: A list of your family members and their birthdays is a simple database.

Nigerian example: A bank database stores customer accounts and transactions.

Illustration:

Database = Organized Data
+-------+---------+--------+
| ID    | Name    | Age    |
+-------+---------+--------+
| 1     | Chidi   | 10     |
| 2     | Amina   | 12     |
| 3     | Bola    | 11     |
+-------+---------+--------+

Mini summary: A database is an organized way to store data.

Module 2: What is MySQL?

Definition: MySQL is a type of database that uses a language called SQL to manage data.

Why important: It's one of the most popular databases in the world.

Simple explanation: It's a tool that helps us talk to the database.

Real-life example: Many websites like Facebook use MySQL.

School example: A school might use MySQL for its student records.

Home example: A family budget could be stored in MySQL.

Nigerian example: A bank might use MySQL for its customer data.

Illustration:

MySQL = The Language to Talk to a Database
   You (User) → SQL → Database → Answer

Mini summary: MySQL is a popular database management system.

Module 3: Installing MySQL

Definition: Installing MySQL means setting it up on your computer.

Why important: You need MySQL to practice.

Simple explanation: It's like installing a game on your computer.

Real-life example: Download MySQL from the official website.

School example: Install MySQL on your school computer.

Home example: Install MySQL on your home PC.

Nigerian example: Install MySQL in your school's computer lab.

Illustration:

Steps:
1. Go to mysql.com.
2. Download MySQL Community Server.
3. Follow the installation steps.
4. Launch MySQL Workbench.

Mini summary: Install MySQL to get started.

Module 4: Creating a Database

Definition: A database is a container for tables.

Why important: You need a database to store your data.

Simple explanation: It's like creating a new folder on your computer.

Real-life example: Create a database called "School".

School example: Create a database for your class records.

Home example: Create a database for your family budget.

Nigerian example: Create a database for a small shop.

How to do it: CREATE DATABASE School;

Illustration:

CREATE DATABASE School;

Mini summary: Create a database to store your tables.

Module 5: Creating Tables

Definition: A table is a grid with rows and columns, like a spreadsheet.

Why important: Tables are where data is stored.

Simple explanation: It's like a spreadsheet with columns and rows.

Real-life example: A table for students: ID, Name, Age.

School example: A table for grades: StudentID, Subject, Score.

Home example: A table for expenses: Date, Category, Amount.

Nigerian example: A table for customers: CustomerID, Name, City.

How to do it: CREATE TABLE Students (ID INT, Name VARCHAR(50), Age INT);

Illustration:

CREATE TABLE Students (
    ID INT,
    Name VARCHAR(50),
    Age INT
);

Mini summary: Tables are where data is stored.

Module 6: Inserting Data

Definition: Insert means adding data into a table.

Why important: You need data to analyze.

Simple explanation: It's like adding a new row to a spreadsheet.

Real-life example: Add a new student to the Students table.

School example: Add a new grade to the Grades table.

Home example: Add a new expense to the Expenses table.

Nigerian example: Add a new customer to the Customers table.

How to do it: INSERT INTO Students VALUES (1, 'Chidi', 10);

Illustration:

INSERT INTO Students (ID, Name, Age) VALUES (1, 'Chidi', 10);

Mini summary: Insert adds data to a table.

Module 7: Selecting Data (SELECT)

Definition: SELECT retrieves data from a table.

Why important: You need to see your data.

Simple explanation: It's like asking "Show me the data."

Real-life example: SELECT * FROM Students;

School example: SELECT Name, Score FROM Grades;

Home example: SELECT * FROM Expenses;

Nigerian example: SELECT Name, City FROM Customers;

How to do it: SELECT * FROM Students;

Illustration:

SELECT * FROM Students;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 1  | Chidi | 10  |
| 2  | Amina | 12  |
+----+-------+-----+

Mini summary: SELECT retrieves data.

Module 8: Filtering with WHERE

Definition: WHERE filters rows based on a condition.

Why important: You often want only specific rows.

Simple explanation: It's like saying "Show me only students who are 10 years old."

Real-life example: SELECT * FROM Students WHERE Age = 10;

School example: SELECT * FROM Grades WHERE Score > 80;

Home example: SELECT * FROM Expenses WHERE Amount > 100;

Nigerian example: SELECT * FROM Customers WHERE City = 'Lagos';

How to do it: SELECT * FROM Students WHERE Age = 10;

Illustration:

SELECT * FROM Students WHERE Age = 10;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 1  | Chidi | 10  |
+----+-------+-----+

Mini summary: WHERE filters data.

Module 9: Sorting with ORDER BY

Definition: ORDER BY sorts rows by a column.

Why important: You want data in a specific order.

Simple explanation: It's like sorting books alphabetically.

Real-life example: SELECT * FROM Students ORDER BY Name;

School example: SELECT * FROM Grades ORDER BY Score DESC;

Home example: SELECT * FROM Expenses ORDER BY Amount DESC;

Nigerian example: SELECT * FROM Customers ORDER BY Name;

How to do it: SELECT * FROM Students ORDER BY Name;

Illustration:

SELECT * FROM Students ORDER BY Name;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 2  | Amina | 12  |
| 1  | Chidi | 10  |
+----+-------+-----+

Mini summary: ORDER BY sorts data.

Module 10: Functions (SUM, AVG, COUNT)

Definition: Functions perform calculations on data.

Why important: They help us answer questions like "What is the total?"

Simple explanation: It's like using a calculator.

Real-life example: SELECT SUM(Amount) FROM Expenses;

School example: SELECT AVG(Score) FROM Grades;

Home example: SELECT COUNT(*) FROM Expenses;

Nigerian example: SELECT COUNT(*) FROM Customers;

How to do it: SELECT SUM(Amount) FROM Expenses;

Illustration:

SELECT SUM(Amount) FROM Expenses;
+-------------+
| SUM(Amount) |
+-------------+
| 4500        |
+-------------+

Mini summary: Functions calculate totals, averages, and counts.

Module 11: Grouping with GROUP BY

Definition: GROUP BY groups rows that have the same values.

Why important: It helps us see summaries by category.

Simple explanation: It's like grouping candies by color.

Real-life example: SELECT Category, SUM(Amount) FROM Expenses GROUP BY Category;

School example: SELECT Subject, AVG(Score) FROM Grades GROUP BY Subject;

Home example: SELECT Category, COUNT(*) FROM Expenses GROUP BY Category;

Nigerian example: SELECT City, COUNT(*) FROM Customers GROUP BY City;

How to do it: SELECT Category, SUM(Amount) FROM Expenses GROUP BY Category;

Illustration:

SELECT Category, SUM(Amount) FROM Expenses GROUP BY Category;
+----------+-------------+
| Category | SUM(Amount) |
+----------+-------------+
| Food     | 2000        |
| Transport| 1500        |
| Others   | 1000        |
+----------+-------------+

Mini summary: GROUP BY groups data for summaries.

Module 12: Joining Tables

Definition: Joins combine data from two or more tables.

Why important: Data is often split across multiple tables.

Simple explanation: It's like putting two puzzle pieces together.

Real-life example: Join Students and Grades tables.

School example: Join Students and Subjects tables.

Home example: Join Expenses and Categories tables.

Nigerian example: Join Customers and Orders tables.

How to do it: SELECT * FROM Students JOIN Grades ON Students.ID = Grades.StudentID;

Illustration:

Students: ID, Name
Grades: StudentID, Subject, Score
Join on ID = StudentID

Mini summary: Joins combine tables.

Module 13: Advanced Filtering

Definition: Use AND, OR, NOT for complex filters.

Why important: Sometimes you need multiple conditions.

Simple explanation: It's like saying "Show me students who are 10 years old AND are in class 5A."

Real-life example: SELECT * FROM Students WHERE Age = 10 AND Class = '5A';

School example: SELECT * FROM Grades WHERE Score > 80 AND Subject = 'Math';

Home example: SELECT * FROM Expenses WHERE Amount > 100 AND Category = 'Food';

Nigerian example: SELECT * FROM Customers WHERE City = 'Lagos' AND Age > 18;

Illustration:

SELECT * FROM Students WHERE Age = 10 AND Name = 'Chidi';

Mini summary: Use AND, OR, NOT for advanced filtering.

Module 14: Updating and Deleting Data

Definition: UPDATE changes existing data. DELETE removes data.

Why important: Data changes over time.

Simple explanation: Update is like correcting a mistake. Delete is like removing something.

Real-life example: UPDATE Students SET Age = 11 WHERE ID = 1;

School example: DELETE FROM Grades WHERE Score < 40;

Home example: UPDATE Expenses SET Amount = 200 WHERE ID = 5;

Nigerian example: DELETE FROM Customers WHERE City = 'Abuja';

Illustration:

UPDATE Students SET Age = 11 WHERE ID = 1;
DELETE FROM Students WHERE ID = 3;

Mini summary: UPDATE changes data, DELETE removes it.

Module 15: Best Practices

Definition: Rules to write clean and efficient SQL.

Why important: Good habits make you a better analyst.

Simple explanation: It's like following a recipe for perfect cookies.

Always specify columns in SELECT (not *).
Use WHERE to filter before joining large tables.
Use proper data types.
Index columns used in WHERE and JOIN.
Write clear and readable SQL.

Illustration:

-- Good
SELECT Name, Age FROM Students WHERE Age > 10;

-- Bad
SELECT * FROM Students;

Mini summary: Follow best practices for clean SQL.

📖 Key Vocabulary (with simple definitions)

Database: Organized collection of data.
MySQL: A popular database management system.
Table: A grid with rows and columns.
Query: A question you ask the database.
SELECT: Fetches data from a table.
WHERE: Filters rows.
JOIN: Combines tables.
GROUP BY: Groups rows for summaries.
SUM: Adds numbers.
AVG: Calculates average.
COUNT: Counts rows.

🧠 Important Concepts

Databases organize data in tables.
SQL is the language to talk to MySQL.
SELECT retrieves data.
WHERE filters data.
JOIN combines tables.
GROUP BY creates summaries.
Functions like SUM and AVG calculate numbers.

🔢 Step-by-Step Explanations

How to Create a Database and Table:

Open MySQL Workbench.
Write: CREATE DATABASE School;
Select the database: USE School;
Create a table: CREATE TABLE Students (ID INT, Name VARCHAR(50), Age INT);
Insert data: INSERT INTO Students VALUES (1, 'Chidi', 10);
Query data: SELECT * FROM Students;

🌍 Real-life Examples

Retail: Use MySQL to track products and sales.
Healthcare: Use MySQL to store patient records.
Finance: Use MySQL to manage accounts and transactions.

🇳🇬 Nigerian Examples

Election: Use MySQL to store and analyze election results.
Agriculture: Use MySQL to track crop yields.
Telecom: Use MySQL to manage call records.

🎈 Fun Examples Children Can Relate To

Toys: Store toy inventory in MySQL.
Candy: Track candy sales in a MySQL database.
Games: Store game scores in MySQL.

🏠 Everyday Examples

Budget: Use MySQL to track monthly expenses.
Chores: Use MySQL to manage family chores.
School: Use MySQL to store grades.

👩‍🏫 Teacher Notes

Use hands-on exercises with sample data.
Encourage students to write and run queries.
Show real-world applications of MySQL.

👨‍👩‍👦 Parent Tips

Help your child install MySQL on the computer.
Encourage them to practice with small datasets.
Discuss how databases are used in daily life.

💡 Interesting Facts

MySQL was created in 1995.
Facebook, Twitter, and YouTube use MySQL.
MySQL is open-source, meaning it's free to use.

❓ Did You Know?

Did you know that MySQL can handle billions of rows of data? It's used by some of the biggest companies in the world!

🔔 Remember This

Databases organize data.
MySQL is a popular database.
SELECT retrieves data.
WHERE filters data.
JOIN combines tables.

⚠️ Common Mistakes

Forgetting to specify the database (USE).
Missing semicolons at the end of statements.
Using wrong data types.
Not using WHERE with UPDATE or DELETE.

✅ Best Practices

Always specify columns in SELECT.
Use WHERE to filter.
Use proper data types.
Index columns used in WHERE and JOIN.
Write clear and readable SQL.

📊 ASCII Illustrations

Database Structure

Database: School
   |
   +--- Table: Students
   |      +-------+---------+-----+
   |      | ID    | Name    | Age |
   |      +-------+---------+-----+
   |      | 1     | Chidi   | 10  |
   |      | 2     | Amina   | 12  |
   |      +-------+---------+-----+
   |
   +--- Table: Grades
          +-----------+---------+-------+
          | StudentID | Subject | Score |
          +-----------+---------+-------+
          | 1         | Math    | 80    |
          | 2         | English | 90    |
          +-----------+---------+-------+

SQL Query Flow

   User writes a query
          |
          V
   SELECT * FROM Students WHERE Age > 10
          |
          V
   MySQL processes the query
          |
          V
   Returns matching rows

📋 Comparison Tables

MySQL vs Excel

Feature	MySQL	Excel
Data size	Billions of rows	Limited (1M rows)
Speed	Very fast	Slower
Multi-user	Yes	No
Data integrity	High	Low

SQL Query Types

Type	Purpose	Example
SELECT	Retrieve data	SELECT * FROM Students;
INSERT	Add data	INSERT INTO Students VALUES (1, 'Chidi', 10);
UPDATE	Change data	UPDATE Students SET Age = 11 WHERE ID = 1;
DELETE	Remove data	DELETE FROM Students WHERE ID = 1;

📝 End-of-Course Summary

Congratulations! You have completed the course outline for MySQL for Data Analysis Expert. You will learn:

What a database is and why MySQL is used.
How to create databases and tables.
How to insert, update, and delete data.
How to retrieve data with SELECT.
How to filter, sort, and group data.
How to join tables.
How to use functions like SUM, AVG, COUNT.
Best practices for writing clean SQL.

By the end of this course, you will be a MySQL expert, ready to analyze data and answer any question!

❓ Frequently Asked Questions (10)

What is MySQL? – A popular database management system.
What is a database? – Organized collection of data.
What is a table? – A grid with rows and columns.
What is SQL? – The language used to talk to MySQL.
What is SELECT? – Retrieves data from a table.
What is WHERE? – Filters rows.
What is JOIN? – Combines tables.
What is GROUP BY? – Groups rows for summaries.
What is a primary key? – Unique identifier for a row.
How do I install MySQL? – Download from mysql.com.

📝 Review Questions (15)

What is a database?
What is MySQL?
What is a table?
What is SQL?
What does SELECT do?
What does WHERE do?
What does JOIN do?
What does GROUP BY do?
What is a primary key?
What is SUM used for?
What is AVG used for?
What is COUNT used for?
How do you insert data?
How do you update data?
How do you delete data?

✍️ Fill-in-the-Blank Exercises

A ______ is an organized collection of data.
______ is a popular database management system.
______ retrieves data from a table.
______ filters rows based on a condition.
______ combines data from two or more tables.

✅ True or False Exercises

MySQL is a type of spreadsheet. (False)
A table has rows and columns. (True)
SELECT deletes data. (False)
WHERE filters data. (True)
JOIN combines tables. (True)

🔘 Multiple Choice Questions (15 with answers)

What is a database?
a) A spreadsheet b) Organized data c) A game d) A website
Answer: b
What is MySQL?
a) A game b) A database management system c) A programming language d) A website
Answer: b
What is a table?
a) A grid with rows and columns b) A list c) A chart d) A graph
Answer: a
What does SELECT do?
a) Adds data b) Retrieves data c) Deletes data d) Changes data
Answer: b
What does WHERE do?
a) Filters rows b) Adds data c) Deletes data d) Sorts data
Answer: a
What does JOIN do?
a) Combines tables b) Adds data c) Deletes data d) Sorts data
Answer: a
What does GROUP BY do?
a) Groups rows b) Adds data c) Deletes data d) Sorts data
Answer: a
What is a primary key?
a) Unique identifier b) A column c) A row d) A table
Answer: a
What does SUM do?
a) Adds numbers b) Averages numbers c) Counts rows d) Finds max
Answer: a
What does AVG do?
a) Adds numbers b) Averages numbers c) Counts rows d) Finds max
Answer: b
What does COUNT do?
a) Adds numbers b) Averages numbers c) Counts rows d) Finds max
Answer: c
How do you insert data?
a) INSERT INTO b) SELECT c) UPDATE d) DELETE
Answer: a
How do you update data?
a) INSERT INTO b) SELECT c) UPDATE d) DELETE
Answer: c
How do you delete data?
a) INSERT INTO b) SELECT c) UPDATE d) DELETE
Answer: d
Which is a best practice?
a) Use SELECT * b) Use WHERE with UPDATE c) Use wrong data types d) Ignore indexes
Answer: b

🔗 Matching Exercises

Match the SQL command to its purpose:

SQL Command	Purpose
1. SELECT	A. Add data
2. INSERT	B. Retrieve data
3. UPDATE	C. Remove data
4. DELETE	D. Change data

Answers: 1-B, 2-A, 3-D, 4-C

✍️ Short Answer Questions

What is a database?
Explain the difference between SELECT and WHERE.
What is a JOIN and when do you use it?
What is GROUP BY used for?
What are the best practices for writing SQL?

📖 Scenario-based Exercises

Scenario 1: You are a teacher. You have a table of students and a table of grades. Write a SQL query to list all students with their grades.

Scenario 2: You are a store owner. You have a table of products and a table of sales. Write a SQL query to find the total sales for each product.

👥 Group Activity

In groups, create a database for a school. Create tables for students, teachers, and courses. Insert sample data. Write queries to retrieve data, filter, and join tables. Present your database to the class.

🧑‍🎓 Individual Activity

Create a database for a personal budget. Create a table for expenses. Insert 10 sample records. Write queries to calculate total expenses, average expense, and group by category.

💬 Classroom Discussion Questions

Why are databases important in our daily lives?
What would happen if a bank didn't use a database?
How can databases help in school administration?

🛠️ Mini Project

Project: Student Database

Create a database for a school. Include tables for students, teachers, classes, and grades. Write queries to:

List all students.
List all teachers.
Find students who scored above 80.
Calculate the average score per subject.
Join tables to show student names and their grades.

📋 Practical Assignment

Install MySQL on your computer. Create a database called "Library". Create tables for books, authors, and members. Insert sample data. Write at least 5 queries to retrieve and analyze the data. Submit your SQL script.

🏆 Challenge Exercise

Create a database for an e-commerce store. Tables: Customers, Products, Orders, OrderItems. Write a query to find the top 10 best-selling products. Use JOIN, GROUP BY, and ORDER BY. Optimize your query for performance.

📝 Quiz Answers

Fill-in-the-Blank: 1. database, 2. MySQL, 3. SELECT, 4. WHERE, 5. JOIN

True/False: 1-F, 2-T, 3-F, 4-T, 5-T

Matching: 1-B, 2-A, 3-D, 4-C

🔑 Key Takeaways

Databases organize data for easy retrieval.
MySQL is a powerful database management system.
SQL is the language to interact with MySQL.
SELECT retrieves, WHERE filters, JOIN combines, GROUP BY summarizes.
Practice writing queries to become an expert.

📖 Preparation for the Next Module

In the next module, we will dive deeper into MySQL and learn about advanced queries, subqueries, and indexing. You will become a true MySQL expert. Before the next class, practice writing basic SELECT queries with WHERE and ORDER BY.

Module One

Module 1 · MySQL for Data Analysis Expert

📚 Module One: Introduction to Databases and MySQL

Hello, young data explorer! Welcome to the world of databases. This is the first module of our MySQL for Data Analysis Expert course. In this module, we will learn what a database is, why we need it, and how MySQL helps us manage data. Think of a database as a giant digital filing cabinet where we store all our information in an organized way. By the end of this module, you will understand the basics of databases and be ready to start using MySQL!

🎯 Learning Objectives

By the end of this module, you will be able to:

Understand what a database is.
Explain why databases are important.
Know what MySQL is and why it is used.
Differentiate between databases, tables, rows, and columns.
Understand the concept of primary keys.
Identify real-world uses of databases.
Install MySQL on your computer.
Connect to a MySQL database.
Write your first SQL statement.
Understand the difference between SQL and MySQL.

📖 Warm-up Story: The Messy Book Collection

Chidi loves reading. He has a huge collection of books – over 500! But his books are all mixed up on his shelves. He can't find his favorite book because there is no order. His dad helps him organize the books. They sort them by genre, then by author, and then by title. Now Chidi can find any book in seconds. A database is exactly like this organized book collection. It stores data in a structured way so we can find information quickly. MySQL is like the librarian that helps us add, remove, and find books (data) easily.

📚 Main Lessons (15 Lessons)

Lesson 1: What is Data?

Definition: Data is information. It can be numbers, words, pictures, or anything else that tells us something.

Why important: Everything around us is data. We use data to make decisions.

Simple explanation: Your name is data. Your age is data. Your favorite food is data.

Real-life example: The temperature outside is data. The price of bread is data.

School example: Your test scores are data. Your attendance record is data.

Home example: Your family members' names are data. Your weekly allowance is data.

Nigerian example: The number of people in Lagos is data. The price of rice is data.

Illustration:

Data is all around us:
+----------+---------+----------+
| Name     | Age     | City     |
+----------+---------+----------+
| Chidi    | 10      | Lagos    |
| Amina    | 12      | Abuja    |
| Bola     | 11      | Ibadan   |
+----------+---------+----------+

Mini summary: Data is information that helps us understand the world.

Lesson 2: What is a Database?

Definition: A database is a collection of organized data.

Why important: It helps us store, find, and manage data easily.

Simple explanation: It's like a giant digital filing cabinet.

Real-life example: A school database stores student names, grades, and attendance.

School example: The library catalog is a database of books.

Home example: Your phone's contact list is a database.

Nigerian example: A bank database stores customer accounts and transactions.

Illustration:

Database = Organized Data
+----------+---------+----------+
| Name     | Age     | City     |
+----------+---------+----------+
| Chidi    | 10      | Lagos    |
| Amina    | 12      | Abuja    |
| Bola     | 11      | Ibadan   |
+----------+---------+----------+

Mini summary: A database organizes data so we can find it easily.

Lesson 3: Why Do We Need Databases?

Definition: We need databases to store and manage large amounts of data efficiently.

Why important: Without databases, finding information would be like looking for a needle in a haystack.

Simple explanation: Imagine trying to find one specific book in a library with no order. That's why we need databases.

Real-life example: A hospital uses a database to store patient records.

School example: A school uses a database to track student grades.

Home example: You use a database when you save your favorite songs in a playlist.

Nigerian example: The government uses a database to manage citizen records.

Illustration:

Without Database: Data is scattered everywhere.
With Database: Data is organized and easy to find.

Mini summary: Databases make it easy to store and find data.

Lesson 4: What is MySQL?

Definition: MySQL is a type of database management system. It helps us create, manage, and use databases.

Why important: MySQL is one of the most popular databases in the world.

Simple explanation: It's a tool that helps us talk to the database.

Real-life example: Many websites like Facebook and YouTube use MySQL.

School example: A school might use MySQL to store student records.

Home example: A family budget could be stored in MySQL.

Nigerian example: A bank might use MySQL for its customer accounts.

Illustration:

MySQL = The Language to Talk to a Database
   You (User) → SQL → MySQL → Database → Answer

Mini summary: MySQL is a popular tool for managing databases.

Lesson 5: SQL vs MySQL

Definition: SQL is the language we use to talk to the database. MySQL is the database system that understands SQL.

Why important: Knowing the difference helps you understand how they work together.

Simple explanation: SQL is like the words you speak. MySQL is like the person who listens and understands.

Real-life example: When you ask a question in English (SQL), MySQL (the person) gives an answer.

School example: You ask your teacher (MySQL) a question in English (SQL).

Home example: You ask your parent (MySQL) a question in your language (SQL).

Nigerian example: You ask a bank teller (MySQL) a question in English (SQL).

Illustration:

SQL = Language (like English)
MySQL = Database System (like a person who understands the language)

Mini summary: SQL is the language, MySQL is the system that understands it.

Lesson 6: Tables, Rows, and Columns

Definition: A table is a grid that stores data. Rows are horizontal lines. Columns are vertical lines.

Why important: Tables are the building blocks of databases.

Simple explanation: A table is like a spreadsheet. Rows are like lines in a notebook. Columns are like categories.

Real-life example: A student table has columns: ID, Name, Age. Each student is a row.

School example: A grade table has columns: StudentID, Subject, Score.

Home example: A budget table has columns: Date, Category, Amount.

Nigerian example: A customer table has columns: CustomerID, Name, City.

Illustration:

+----------+---------+----------+
| Name     | Age     | City     |  ← Columns (vertical)
+----------+---------+----------+
| Chidi    | 10      | Lagos    |  ← Row (horizontal)
| Amina    | 12      | Abuja    |  ← Row
| Bola     | 11      | Ibadan   |  ← Row
+----------+---------+----------+

Mini summary: Tables store data in rows and columns.

Lesson 7: Primary Keys

Definition: A primary key is a special column that uniquely identifies each row in a table.

Why important: It ensures that each row is unique and can be found easily.

Simple explanation: It's like a student ID number – each student has a unique number.

Real-life example: In a customer table, CustomerID is the primary key.

School example: In a student table, StudentID is the primary key.

Home example: In a budget table, TransactionID could be the primary key.

Nigerian example: In a citizen table, NIN could be the primary key.

Illustration:

+----------+---------+----------+
| ID (PK)  | Name    | Age      |  ← ID is the primary key
+----------+---------+----------+
| 1        | Chidi   | 10       |
| 2        | Amina   | 12       |
| 3        | Bola    | 11       |
+----------+---------+----------+

Mini summary: A primary key uniquely identifies each row.

Lesson 8: Installing MySQL

Definition: Installing MySQL means setting it up on your computer so you can use it.

Why important: You need MySQL installed to practice and learn.

Simple explanation: It's like installing a game on your computer.

Real-life example: Download MySQL from the official website.

School example: Install MySQL on your school computer.

Home example: Install MySQL on your home PC.

Nigerian example: Install MySQL in your school's computer lab.

Steps: Go to mysql.com, download MySQL Community Server, run the installer, and follow the instructions.

Illustration:

Steps to Install:
1. Go to mysql.com
2. Click "Downloads"
3. Choose MySQL Community Server
4. Download the installer
5. Run the installer
6. Follow the steps
7. Launch MySQL Workbench

Mini summary: Install MySQL to start learning.

Lesson 9: Connecting to MySQL

Definition: Connecting means opening a connection to the MySQL server.

Why important: You need to connect before you can work with databases.

Simple explanation: It's like opening the door to enter a room.

Real-life example: Use MySQL Workbench to connect.

School example: Connect to the school's MySQL server.

Home example: Connect to your local MySQL installation.

Nigerian example: Connect to a remote MySQL server for a project.

How to do it: Open MySQL Workbench, click on the connection, and enter your password.

Illustration:

MySQL Workbench
   |   (Click on connection)
   V
Enter Password
   |
   V
Connected to MySQL!

Mini summary: Connect to MySQL to start using it.

Lesson 10: Your First SQL Statement

Definition: A SQL statement is a command you give to the database.

Why important: You communicate with the database using SQL.

Simple explanation: It's like asking a question.

Real-life example: SELECT * FROM Students; (shows all students)

School example: SELECT Name FROM Students; (shows only names)

Home example: SELECT * FROM Expenses; (shows all expenses)

Nigerian example: SELECT Name FROM Customers; (shows customer names)

How to do it: Type the SQL statement and run it.

Illustration:

SELECT * FROM Students;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 1  | Chidi | 10  |
| 2  | Amina | 12  |
+----+-------+-----+

Mini summary: SQL statements are commands to the database.

Lesson 11: Creating a Database

Definition: Creating a database means making a new container for tables.

Why important: You need a database to store your tables.

Simple explanation: It's like creating a new folder on your computer.

Real-life example: CREATE DATABASE School;

School example: CREATE DATABASE GradesDB;

Home example: CREATE DATABASE Budget;

Nigerian example: CREATE DATABASE Shop;

How to do it: Type CREATE DATABASE database_name;

Illustration:

CREATE DATABASE School;
Query OK, 1 row affected.

Mini summary: CREATE DATABASE makes a new database.

Lesson 12: Using a Database

Definition: Using a database means telling MySQL which database you want to work with.

Why important: You must select a database before creating tables.

Simple explanation: It's like opening a folder before adding files.

Real-life example: USE School;

School example: USE GradesDB;

Home example: USE Budget;

Nigerian example: USE Shop;

How to do it: Type USE database_name;

Illustration:

USE School;
Database changed.

Mini summary: USE selects the database to work with.

Lesson 13: Creating a Table

Definition: Creating a table means making a new grid to store data.

Why important: Tables are where your data lives.

Simple explanation: It's like drawing a new table in a notebook.

Real-life example: CREATE TABLE Students (ID INT, Name VARCHAR(50), Age INT);

School example: CREATE TABLE Grades (StudentID INT, Subject VARCHAR(50), Score INT);

Home example: CREATE TABLE Expenses (Date DATE, Category VARCHAR(50), Amount INT);

Nigerian example: CREATE TABLE Customers (CustomerID INT, Name VARCHAR(50), City VARCHAR(50));

How to do it: Type CREATE TABLE table_name (column1 datatype, column2 datatype);

Illustration:

CREATE TABLE Students (
    ID INT,
    Name VARCHAR(50),
    Age INT
);
Query OK, 0 rows affected.

Mini summary: CREATE TABLE makes a new table.

Lesson 14: Inserting Data

Definition: Inserting data means adding rows to a table.

Why important: You need data in your table to work with.

Simple explanation: It's like writing a new line in your notebook.

Real-life example: INSERT INTO Students VALUES (1, 'Chidi', 10);

School example: INSERT INTO Grades VALUES (1, 'Math', 80);

Home example: INSERT INTO Expenses VALUES ('2025-01-01', 'Food', 500);

Nigerian example: INSERT INTO Customers VALUES (1, 'Bola', 'Lagos');

How to do it: INSERT INTO table_name VALUES (value1, value2, ...);

Illustration:

INSERT INTO Students VALUES (1, 'Chidi', 10);
Query OK, 1 row affected.

Mini summary: INSERT adds data to a table.

Lesson 15: Selecting Data

Definition: Selecting data means retrieving information from a table.

Why important: You want to see your data.

Simple explanation: It's like asking "Show me what's in the table."

Real-life example: SELECT * FROM Students;

School example: SELECT Name, Score FROM Grades;

Home example: SELECT * FROM Expenses;

Nigerian example: SELECT Name, City FROM Customers;

How to do it: SELECT * FROM table_name;

Illustration:

SELECT * FROM Students;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 1  | Chidi | 10  |
| 2  | Amina | 12  |
+----+-------+-----+

Mini summary: SELECT retrieves data from a table.

📖 Key Vocabulary (with simple definitions)

Data: Information like numbers, words, or pictures.
Database: An organized collection of data.
MySQL: A popular database management system.
SQL: The language used to talk to databases.
Table: A grid with rows and columns.
Row: A horizontal line in a table (one record).
Column: A vertical line in a table (one category).
Primary Key: A unique identifier for each row.
Query: A question you ask the database.
INSERT: Add data to a table.
SELECT: Retrieve data from a table.

🧠 Important Concepts

Data is information that helps us understand things.
A database organizes data so we can find it easily.
MySQL is a tool that helps us manage databases.
SQL is the language we use to talk to MySQL.
Tables store data in rows and columns.
A primary key uniquely identifies each row.
We use INSERT to add data and SELECT to see data.

🔢 Step-by-Step Explanations

How to Install MySQL:

Open your web browser and go to mysql.com.
Click on "Downloads".
Scroll down and click on "MySQL Community Server".
Choose your operating system (Windows, Mac, or Linux).
Download the installer.
Run the installer and follow the instructions.
Set a password for the root user (remember it!).
Finish the installation.
Launch MySQL Workbench.

How to Create a Database and Table:

Open MySQL Workbench.
Connect to your MySQL server.
In the query editor, type: CREATE DATABASE School;
Run the query.
Type: USE School;
Run the query.
Type: CREATE TABLE Students (ID INT, Name VARCHAR(50), Age INT);
Run the query.
Type: INSERT INTO Students VALUES (1, 'Chidi', 10);
Run the query.
Type: SELECT * FROM Students;
Run the query and see the data.

🌍 Real-life Examples

Retail: A store uses a database to track products, sales, and customers.
Healthcare: A hospital uses a database to store patient records and appointments.
Finance: A bank uses a database to manage accounts and transactions.

🇳🇬 Nigerian Examples

Election: INEC uses a database to store voter information and election results.
Agriculture: Farmers use databases to track crop yields and weather data.
Telecom: MTN uses a database to manage call records and customer accounts.

🎈 Fun Examples Children Can Relate To

Toys: A toy store uses a database to know which toys are in stock.
Candy: A candy shop uses a database to track which candies sell the most.
Games: A video game uses a database to store player scores and levels.

🏠 Everyday Examples

Budget: You can use a database to track your monthly spending.
Chores: You can use a database to track who does which chore.
School: You can use a database to store your homework assignments and grades.

👩‍🏫 Teacher Notes

Use analogies like "filing cabinet" and "notebook" to explain databases.
Encourage students to practice creating databases and tables.
Show real-world examples of databases in action.

👨‍👩‍👦 Parent Tips

Help your child install MySQL on your home computer.
Encourage them to create a small database for family data (e.g., budget, members).
Discuss how databases are used in daily life (e.g., school, hospital, bank).

💡 Interesting Facts

MySQL was created in 1995 by a Swedish company.
MySQL is used by some of the biggest websites in the world, including Facebook, Twitter, and YouTube.
The "My" in MySQL is the name of the creator's daughter!

❓ Did You Know?

Did you know that MySQL can handle billions of rows of data? That's like storing information for all the people in the world many times over!

🔔 Remember This

Data is information.
A database organizes data.
MySQL is a database management system.
SQL is the language used to talk to MySQL.
Tables store data in rows and columns.
Primary keys uniquely identify rows.

⚠️ Common Mistakes

Forgetting to use semicolons at the end of SQL statements.
Forgetting to select a database before creating tables.
Using the wrong data types for columns.
Typing commands in the wrong case (SQL is case-insensitive but it's good practice to use uppercase for commands).

✅ Best Practices

Always end SQL statements with a semicolon.
Use uppercase for SQL commands (e.g., SELECT, INSERT).
Use lowercase for table and column names.
Always specify the database with USE before creating tables.
Write comments to explain your code.

📊 ASCII Illustrations

Database Structure

Database: School
   |
   +--- Table: Students
   |      +-------+---------+-----+
   |      | ID    | Name    | Age |
   |      +-------+---------+-----+
   |      | 1     | Chidi   | 10  |
   |      | 2     | Amina   | 12  |
   |      +-------+---------+-----+
   |
   +--- Table: Teachers
          +-------+---------+--------+
          | ID    | Name    | Subject|
          +-------+---------+--------+
          | 1     | Mr. Ojo | Math   |
          | 2     | Mrs. De | English|
          +-------+---------+--------+

SQL Query Flow

   User writes a query
          |
          V
   SELECT * FROM Students WHERE Age > 10
          |
          V
   MySQL processes the query
          |
          V
   Returns matching rows

📋 Comparison Tables

MySQL vs Excel

Feature	MySQL	Excel
Data size	Billions of rows	Limited (1M rows)
Speed	Very fast	Slower
Multi-user	Yes	No
Data integrity	High	Low

SQL vs MySQL

Feature	SQL	MySQL
What it is	A language	A database system
Purpose	To communicate with databases	To manage databases
Example	SELECT * FROM Students;	MySQL Server, MySQL Workbench

📝 End-of-Module Summary

Congratulations! You have completed Module One of MySQL for Data Analysis Expert. In this module, you learned:

What data is and why it's important.
What a database is and how it organizes data.
What MySQL is and why it's used.
The difference between SQL and MySQL.
What tables, rows, and columns are.
What a primary key is.
How to install MySQL.
How to create a database and table.
How to insert and select data.

You are now ready to move to Module Two, where you will learn more about SQL queries!

❓ Frequently Asked Questions (10)

What is data? – Information like numbers, words, or pictures.
What is a database? – An organized collection of data.
What is MySQL? – A popular database management system.
What is SQL? – The language used to talk to databases.
What is a table? – A grid with rows and columns.
What is a row? – A horizontal line in a table.
What is a column? – A vertical line in a table.
What is a primary key? – A unique identifier for each row.
How do I install MySQL? – Download from mysql.com and run the installer.
What is the first SQL statement to learn? – SELECT * FROM table_name;

📝 Review Questions (15)

What is data?
What is a database?
What is MySQL?
What is SQL?
What is the difference between SQL and MySQL?
What is a table?
What is a row?
What is a column?
What is a primary key?
Why do we need databases?
How do you create a database?
How do you create a table?
How do you insert data?
How do you select data?
What is the purpose of USE?

✍️ Fill-in-the-Blank Exercises

______ is information like numbers, words, or pictures.
A ______ is an organized collection of data.
______ is a popular database management system.
______ is the language used to talk to databases.
A ______ is a grid with rows and columns.

✅ True or False Exercises

Data is information. (True)
A database is a collection of unordered data. (False)
MySQL is a type of spreadsheet. (False)
SQL is used to talk to databases. (True)
A table has rows and columns. (True)

🔘 Multiple Choice Questions (15 with answers)

What is data?
a) Information b) A game c) A website d) A book
Answer: a
What is a database?
a) Organized data b) A video game c) A website d) A book
Answer: a
What is MySQL?
a) A database management system b) A game c) A website d) A book
Answer: a
What is SQL?
a) A language b) A game c) A website d) A book
Answer: a
What is a table?
a) A grid with rows and columns b) A game c) A website d) A book
Answer: a
What is a row?
a) A horizontal line b) A vertical line c) A game d) A website
Answer: a
What is a column?
a) A vertical line b) A horizontal line c) A game d) A website
Answer: a
What is a primary key?
a) Unique identifier b) A game c) A website d) A book
Answer: a
How do you create a database?
a) CREATE DATABASE b) SELECT c) INSERT d) DELETE
Answer: a
How do you create a table?
a) CREATE TABLE b) SELECT c) INSERT d) DELETE
Answer: a
How do you insert data?
a) INSERT INTO b) SELECT c) CREATE d) DELETE
Answer: a
How do you select data?
a) SELECT b) INSERT c) CREATE d) DELETE
Answer: a
What is the purpose of USE?
a) Select a database b) Insert data c) Create a table d) Delete data
Answer: a
Which is a best practice?
a) End statements with ; b) Ignore errors c) Use lowercase for commands d) Skip comments
Answer: a
What is the difference between SQL and MySQL?
a) SQL is a language, MySQL is a system b) They are the same c) MySQL is a language, SQL is a system d) Neither
Answer: a

🔗 Matching Exercises

Match the term to its description:

Term	Description
1. Database	A. Grid with rows and columns
2. Table	B. Organized collection of data
3. Row	C. Unique identifier
4. Primary Key	D. Horizontal line

Answers: 1-B, 2-A, 3-D, 4-C

✍️ Short Answer Questions

What is a database and why is it important?
Explain the difference between SQL and MySQL.
What is a primary key and why is it important?
How do you create a table in MySQL?
What is the purpose of the SELECT statement?

📖 Scenario-based Exercises

Scenario 1: You are a librarian. You need to create a database to store book information. The table should have columns for BookID, Title, Author, and Year. Write the SQL statements to create the database, create the table, and insert three books.

Scenario 2: You are a teacher. You have a table of students. You want to see the names and ages of all students. Write the SQL statement.

👥 Group Activity

In groups of 3-4, create a database for a small business. Decide on the type of business (e.g., grocery store, bookshop, school). Design tables, define primary keys, and insert sample data. Present your database to the class.

🧑‍🎓 Individual Activity

Install MySQL on your computer. Create a database called "MyData". Create a table called "Friends" with columns: ID, Name, Age, City. Insert 5 of your friends into the table. Write a SELECT statement to retrieve all data.

💬 Classroom Discussion Questions

Why are databases important in our daily lives?
What would happen if a hospital didn't use a database?
How can databases help in school administration?

🛠️ Mini Project

Project: School Database

Create a database for a school. Include tables for:

Students (StudentID, Name, Age, Class)
Teachers (TeacherID, Name, Subject)
Grades (GradeID, StudentID, Subject, Score)

Insert sample data (at least 5 students, 3 teachers, and 10 grades). Write a SELECT statement to show all students and their grades.

📋 Practical Assignment

Install MySQL on your computer. Create a database called "Library". Create tables for books, authors, and members. Insert sample data. Write at least 5 SELECT queries to retrieve different information. Submit your SQL script.

🏆 Challenge Exercise

Create a database for an e-commerce store. Tables: Customers, Products, Orders, OrderItems. Insert sample data. Write a query to find all orders with the customer name and product name. Use joins (we will learn joins later, but try to research).

📝 Quiz Answers

Fill-in-the-Blank: 1. Data, 2. database, 3. MySQL, 4. SQL, 5. table

True/False: 1-T, 2-F, 3-F, 4-T, 5-T

Matching: 1-B, 2-A, 3-D, 4-C

🔑 Key Takeaways

Data is information that helps us understand the world.
A database organizes data so we can find it easily.
MySQL is a popular tool for managing databases.
SQL is the language used to talk to MySQL.
Tables store data in rows and columns.
Primary keys uniquely identify rows.
Practice is the key to becoming good at MySQL.

📖 Preparation for the Next Module

In Module Two, we will learn about SELECT and WHERE – how to retrieve specific data from tables. We will also learn about filtering, sorting, and using functions. Practice the basics from this module to be ready!

Before the next class, practice creating databases, tables, inserting data, and selecting data. The more you practice, the better you'll become.

Module Two

Module 2 · MySQL for Data Analysis Expert

🔍 Module Two: SELECT and WHERE – Retrieving Data

Hello, young data explorer! Welcome to Module Two. In the first module, we learned what a database is and how to create tables. Now, it's time to learn how to retrieve data from those tables. Retrieving data means asking the database questions and getting answers. We will use the SELECT statement to get data, and the WHERE clause to filter the data. By the end of this module, you will be able to ask your database any question and get the answer you need!

🎯 Learning Objectives

By the end of this module, you will be able to:

Use the SELECT statement to retrieve data.
Select specific columns instead of all columns.
Use the WHERE clause to filter rows.
Use comparison operators: =, >, <, >=, <=, <>.
Use logical operators: AND, OR, NOT.
Use the BETWEEN operator.
Use the IN operator.
Use the LIKE operator for pattern matching.
Handle NULL values with IS NULL and IS NOT NULL.
Write clean and efficient SELECT queries.

📖 Warm-up Story: The Treasure Hunt

Chidi and his friends are on a treasure hunt. They have a map with many clues. But they don't want to read all the clues – they only want the clues that lead to the treasure. They use a magnifying glass to filter the clues. In MySQL, the SELECT statement is like reading the map, and the WHERE clause is like the magnifying glass that helps you focus on only the important clues!

📚 Main Lessons (15 Lessons)

Lesson 1: The SELECT Statement

Definition: SELECT is used to retrieve data from a table.

Why important: It's the most used SQL statement.

Simple explanation: It's like saying "Show me the data."

Real-life example: SELECT * FROM Students; – shows all students.

School example: SELECT Name FROM Students; – shows only names.

Home example: SELECT * FROM Expenses; – shows all expenses.

Nigerian example: SELECT Name FROM Customers; – shows customer names.

How to write: SELECT column1, column2 FROM table_name;

Illustration:

SELECT Name, Age FROM Students;
+-------+-----+
| Name  | Age |
+-------+-----+
| Chidi | 10  |
| Amina | 12  |
+-------+-----+

Mini summary: SELECT retrieves data from a table.

Lesson 2: Selecting All Columns (*)

Definition: The asterisk (*) means all columns.

Why important: It's a quick way to see everything.

Simple explanation: It's like saying "Give me everything."

Real-life example: SELECT * FROM Students;

School example: SELECT * FROM Grades;

Home example: SELECT * FROM Expenses;

Nigerian example: SELECT * FROM Customers;

How to write: SELECT * FROM table_name;

Illustration:

SELECT * FROM Students;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 1  | Chidi | 10  |
| 2  | Amina | 12  |
+----+-------+-----+

Mini summary: * selects all columns.

Lesson 3: Selecting Specific Columns

Definition: You can select only the columns you want.

Why important: It's more efficient and gives only what you need.

Simple explanation: It's like only picking the apples you want from a basket.

Real-life example: SELECT Name, Age FROM Students;

School example: SELECT Subject, Score FROM Grades;

Home example: SELECT Date, Amount FROM Expenses;

Nigerian example: SELECT Name, City FROM Customers;

How to write: SELECT column1, column2 FROM table_name;

Illustration:

SELECT Name, Age FROM Students;
+-------+-----+
| Name  | Age |
+-------+-----+
| Chidi | 10  |
| Amina | 12  |
+-------+-----+

Mini summary: Select specific columns to get only what you need.

Lesson 4: The WHERE Clause

Definition: WHERE filters rows based on a condition.

Why important: You often want only certain rows.

Simple explanation: It's like a sieve that only keeps certain items.

Real-life example: SELECT * FROM Students WHERE Age = 10;

School example: SELECT * FROM Grades WHERE Score > 80;

Home example: SELECT * FROM Expenses WHERE Amount > 100;

Nigerian example: SELECT * FROM Customers WHERE City = 'Lagos';

How to write: SELECT columns FROM table WHERE condition;

Illustration:

SELECT * FROM Students WHERE Age = 10;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 1  | Chidi | 10  |
+----+-------+-----+

Mini summary: WHERE filters rows.

Lesson 5: Comparison Operators (=, >, <, >=, <=, <>)

Definition: Comparison operators compare values.

Why important: They are used in WHERE to filter data.

Simple explanation: They are like the symbols you use in math: =, >, <.

= (equal to)
> (greater than)
< (less than)
>= (greater than or equal to)
<= (less than or equal to)
<> (not equal to)

Real-life example: WHERE Age > 10, WHERE Score >= 80.

School example: WHERE Score < 50, WHERE Subject <> 'Math'.

Home example: WHERE Amount <= 500, WHERE Category <> 'Food'.

Nigerian example: WHERE Population > 1,000,000, WHERE State <> 'Lagos'.

Illustration:

SELECT * FROM Students WHERE Age > 10;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 2  | Amina | 12  |
+----+-------+-----+

Mini summary: Comparison operators help filter data.

Lesson 6: Logical Operators (AND, OR, NOT)

Definition: Logical operators combine multiple conditions.

Why important: You often need more than one condition.

Simple explanation: They are like connecting words in a sentence: and, or, not.

AND: Both conditions must be true.
OR: At least one condition must be true.
NOT: Reverses a condition.

Real-life example: WHERE Age = 10 AND City = 'Lagos'.

School example: WHERE Score > 80 AND Subject = 'Math'.

Home example: WHERE Amount > 100 OR Category = 'Food'.

Nigerian example: WHERE City = 'Lagos' OR City = 'Abuja'.

Illustration:

SELECT * FROM Students WHERE Age = 10 AND Name = 'Chidi';
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 1  | Chidi | 10  |
+----+-------+-----+

Mini summary: AND, OR, NOT combine conditions.

Lesson 7: The BETWEEN Operator

Definition: BETWEEN checks if a value is within a range.

Why important: It's a clean way to check ranges.

Simple explanation: It's like saying "between these two numbers."

Real-life example: WHERE Age BETWEEN 10 AND 12;

School example: WHERE Score BETWEEN 70 AND 90;

Home example: WHERE Amount BETWEEN 100 AND 500;

Nigerian example: WHERE Population BETWEEN 500000 AND 1000000;

How to write: WHERE column BETWEEN value1 AND value2;

Illustration:

SELECT * FROM Students WHERE Age BETWEEN 10 AND 12;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 1  | Chidi | 10  |
| 2  | Amina | 12  |
+----+-------+-----+

Mini summary: BETWEEN checks if a value is in a range.

Lesson 8: The IN Operator

Definition: IN checks if a value is in a list.

Why important: It's a shorthand for multiple OR conditions.

Simple explanation: It's like saying "in this list."

Real-life example: WHERE City IN ('Lagos', 'Abuja', 'Ibadan');

School example: WHERE Subject IN ('Math', 'English', 'Science');

Home example: WHERE Category IN ('Food', 'Transport');

Nigerian example: WHERE State IN ('Lagos', 'Ogun', 'Oyo');

How to write: WHERE column IN (value1, value2, ...);

Illustration:

SELECT * FROM Students WHERE City IN ('Lagos', 'Abuja');
+----+-------+------+
| ID | Name  | City |
+----+-------+------+
| 1  | Chidi | Lagos|
| 2  | Amina | Abuja|
+----+-------+------+

Mini summary: IN checks if a value is in a list.

Lesson 9: The LIKE Operator

Definition: LIKE is used to search for a pattern in a text column.

Why important: It helps you find data when you don't know the exact value.

Simple explanation: It's like searching for a word with a wildcard.

%: any sequence of characters
_: a single character

Real-life example: WHERE Name LIKE 'Ch%'; – names starting with "Ch".

School example: WHERE Subject LIKE '%Math%'; – subjects containing "Math".

Home example: WHERE Category LIKE 'F%'; – categories starting with "F".

Nigerian example: WHERE Name LIKE 'A%'; – names starting with "A".

Illustration:

SELECT * FROM Students WHERE Name LIKE 'Ch%';
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 1  | Chidi | 10  |
+----+-------+-----+

Mini summary: LIKE searches for patterns in text.

Lesson 10: Handling NULL Values

Definition: NULL means no value, missing, or unknown.

Why important: You need to handle missing data.

Simple explanation: It's like an empty box.

Real-life example: WHERE Age IS NULL; – finds rows where age is missing.

School example: WHERE Score IS NOT NULL; – finds rows with scores.

Home example: WHERE Amount IS NULL; – finds missing amounts.

Nigerian example: WHERE Phone IS NOT NULL; – finds customers with phone numbers.

How to write: WHERE column IS NULL / IS NOT NULL;

Illustration:

SELECT * FROM Students WHERE Age IS NULL;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 3  | Bola  | NULL|
+----+-------+-----+

Mini summary: IS NULL checks for missing values.

Lesson 11: ORDER BY – Sorting Results

Definition: ORDER BY sorts the result set.

Why important: You often want data in a specific order.

Simple explanation: It's like sorting books alphabetically.

Real-life example: SELECT * FROM Students ORDER BY Name;

School example: SELECT * FROM Grades ORDER BY Score DESC;

Home example: SELECT * FROM Expenses ORDER BY Amount DESC;

Nigerian example: SELECT * FROM Customers ORDER BY Name;

How to write: ORDER BY column [ASC|DESC];

Illustration:

SELECT * FROM Students ORDER BY Name;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 2  | Amina | 12  |
| 1  | Chidi | 10  |
+----+-------+-----+

Mini summary: ORDER BY sorts results.

Lesson 12: LIMIT – Restricting Results

Definition: LIMIT restricts the number of rows returned.

Why important: You often only want a few rows (e.g., top 10).

Simple explanation: It's like only taking the first few apples from a basket.

Real-life example: SELECT * FROM Students LIMIT 3;

School example: SELECT * FROM Grades ORDER BY Score DESC LIMIT 5;

Home example: SELECT * FROM Expenses ORDER BY Amount DESC LIMIT 3;

Nigerian example: SELECT * FROM Customers ORDER BY Name LIMIT 10;

How to write: LIMIT number;

Illustration:

SELECT * FROM Students LIMIT 2;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 1  | Chidi | 10  |
| 2  | Amina | 12  |
+----+-------+-----+

Mini summary: LIMIT restricts the number of rows.

Lesson 13: DISTINCT – Removing Duplicates

Definition: DISTINCT returns only unique values.

Why important: It helps you see the different values in a column.

Simple explanation: It's like removing duplicate items from a list.

Real-life example: SELECT DISTINCT City FROM Students;

School example: SELECT DISTINCT Subject FROM Grades;

Home example: SELECT DISTINCT Category FROM Expenses;

Nigerian example: SELECT DISTINCT State FROM Customers;

How to write: SELECT DISTINCT column FROM table;

Illustration:

SELECT DISTINCT City FROM Students;
+-------+
| City  |
+-------+
| Lagos |
| Abuja |
+-------+

Mini summary: DISTINCT removes duplicates.

Lesson 14: Combining SELECT, WHERE, ORDER BY, LIMIT

Definition: You can combine all these clauses together.

Why important: Real-world queries need to be powerful and flexible.

Simple explanation: It's like having a Swiss Army knife with many tools.

Real-life example: SELECT * FROM Students WHERE Age > 10 ORDER BY Name LIMIT 5;

School example: SELECT Name, Score FROM Grades WHERE Score > 80 ORDER BY Score DESC LIMIT 10;

Home example: SELECT * FROM Expenses WHERE Amount > 100 ORDER BY Amount DESC LIMIT 5;

Nigerian example: SELECT Name, City FROM Customers WHERE City = 'Lagos' ORDER BY Name LIMIT 10;

Illustration:

SELECT * FROM Students WHERE Age > 10 ORDER BY Name LIMIT 2;
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 2  | Amina | 12  |
+----+-------+-----+

Mini summary: Combine clauses for powerful queries.

Lesson 15: Writing Clean SELECT Queries

Definition: Tips for writing easy-to-read queries.

Why important: Clean code is easy to understand and fix.

Simple explanation: It's like writing neat handwriting.

Use uppercase for SQL keywords (SELECT, FROM, WHERE).
Use lowercase for table and column names.
Put each clause on a new line.
Use comments to explain complex queries.

Illustration:

SELECT
    Name,
    Age
FROM
    Students
WHERE
    Age > 10
ORDER BY
    Name;

Mini summary: Write clean and organized SQL.

📖 Key Vocabulary (with simple definitions)

SELECT: Retrieves data from a table.
WHERE: Filters rows based on a condition.
Comparison Operator: Symbols like =, >, <.
Logical Operator: AND, OR, NOT.
BETWEEN: Checks if a value is in a range.
IN: Checks if a value is in a list.
LIKE: Searches for patterns in text.
NULL: Represents missing or unknown data.
ORDER BY: Sorts results.
LIMIT: Restricts the number of rows.
DISTINCT: Removes duplicates.

🧠 Important Concepts

SELECT is used to retrieve data.
WHERE filters data.
Comparison operators compare values.
Logical operators combine conditions.
BETWEEN checks ranges.
IN checks lists.
LIKE searches patterns.
NULL means missing data.
ORDER BY sorts data.
LIMIT restricts rows.
DISTINCT removes duplicates.

🔢 Step-by-Step Explanations

How to Write a SELECT Query:

Start with SELECT.
Choose the columns you want (or * for all).
Add FROM and the table name.
Add WHERE with a condition (optional).
Add ORDER BY (optional).
Add LIMIT (optional).
End with a semicolon.

Example: SELECT Name, Age FROM Students WHERE Age > 10 ORDER BY Name LIMIT 3;

🌍 Real-life Examples

Retail: SELECT ProductName, Price FROM Products WHERE Price < 100 ORDER BY Price;
Healthcare: SELECT PatientName, Disease FROM Patients WHERE Disease LIKE 'C%';
Finance: SELECT AccountID, Balance FROM Accounts WHERE Balance > 5000 ORDER BY Balance DESC;

🇳🇬 Nigerian Examples

Election: SELECT Party, Votes FROM Results WHERE State = 'Lagos' ORDER BY Votes DESC;
Agriculture: SELECT Crop, Yield FROM Farms WHERE Yield BETWEEN 100 AND 500 ORDER BY Yield DESC;
Telecom: SELECT CustomerName, Minutes FROM Calls WHERE Minutes > 100 ORDER BY Minutes DESC;

🎈 Fun Examples Children Can Relate To

Toys: SELECT ToyName, Price FROM Toys WHERE Price < 50 ORDER BY Price;
Candy: SELECT CandyName, Flavor FROM Candies WHERE Flavor LIKE '%chocolate%';
Games: SELECT PlayerName, Score FROM Scores WHERE Score > 1000 ORDER BY Score DESC LIMIT 5;

🏠 Everyday Examples

Budget: SELECT Category, Amount FROM Expenses WHERE Amount > 100 ORDER BY Amount DESC;
Chores: SELECT Task, AssignedTo FROM Chores WHERE AssignedTo = 'Chidi' ORDER BY Task;
School: SELECT Subject, Score FROM Grades WHERE Score >= 80 ORDER BY Score DESC;

👩‍🏫 Teacher Notes

Start with simple SELECT queries and gradually introduce WHERE.
Use real data to make the lessons interesting.
Encourage students to practice with different conditions.

👨‍👩‍👦 Parent Tips

Help your child create a small database with family data.
Encourage them to ask questions and write SELECT queries to answer them.
Discuss how companies use SELECT and WHERE to get answers.

💡 Interesting Facts

SELECT is the most used SQL command.
WHERE was introduced in the early days of SQL to make queries more powerful.
LIKE is often used in search engines to find similar results.

❓ Did You Know?

Did you know that you can use SELECT without a table? For example: SELECT 1 + 1; This returns 2. It's useful for testing!

🔔 Remember This

SELECT retrieves data.
WHERE filters data.
Use =, >, <, >=, <=, <> to compare.
Use AND, OR, NOT to combine conditions.
Use BETWEEN for ranges.
Use IN for lists.
Use LIKE for patterns.
Use IS NULL for missing values.
ORDER BY sorts.
LIMIT restricts rows.
DISTINCT removes duplicates.

⚠️ Common Mistakes

Forgetting the semicolon at the end of the query.
Using = instead of LIKE for pattern searches.
Forgetting quotes around text values.
Using AND and OR incorrectly (needs parentheses for complex conditions).

✅ Best Practices

Always use uppercase for SQL keywords.
Use lowercase for table and column names.
Put each clause on a new line for readability.
Use comments to explain complex queries.
Always test your queries with a small subset of data.

📊 ASCII Illustrations

SELECT Query Flow

   User writes a query
          |
          V
   SELECT * FROM Students WHERE Age > 10
          |
          V
   MySQL processes the query
          |
          V
   Returns matching rows

WHERE Clause Filtering

All Students:
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 1  | Chidi | 10  |
| 2  | Amina | 12  |
| 3  | Bola  | 11  |
+----+-------+-----+

WHERE Age > 10:
+----+-------+-----+
| ID | Name  | Age |
+----+-------+-----+
| 2  | Amina | 12  |
| 3  | Bola  | 11  |
+----+-------+-----+

📋 Comparison Tables

Comparison Operators

Operator	Meaning	Example
=	Equal to	Age = 10
>	Greater than	Age > 10
<	Less than	Age < 10
>=	Greater than or equal	Age >= 10
<=	Less than or equal	Age <= 10
<>	Not equal to	Age <> 10

LIKE Patterns

Pattern	Meaning	Example
'A%'	Starts with A	Name LIKE 'A%'
'%a'	Ends with a	Name LIKE '%a'
'%at%'	Contains 'at'	Name LIKE '%at%'
'A_'	Starts with A, then one character	Name LIKE 'A_'

📝 End-of-Module Summary

Congratulations! You have completed Module Two on SELECT and WHERE. In this module, you learned:

How to use SELECT to retrieve data.
How to select all columns or specific columns.
How to use WHERE to filter rows.
Comparison operators: =, >, <, >=, <=, <>.
Logical operators: AND, OR, NOT.
BETWEEN for ranges.
IN for lists.
LIKE for patterns.
IS NULL and IS NOT NULL.
ORDER BY to sort.
LIMIT to restrict rows.
DISTINCT to remove duplicates.

You now have the skills to retrieve specific data from any database. In Module Three, you will learn about functions and aggregations!

❓ Frequently Asked Questions (10)

What does SELECT do? – Retrieves data from a table.
What does WHERE do? – Filters rows.
What is the difference between = and LIKE? – = matches exact value, LIKE matches patterns.
What is BETWEEN used for? – Checks if a value is in a range.
What is IN used for? – Checks if a value is in a list.
What is NULL? – Means missing or unknown data.
What does ORDER BY do? – Sorts results.
What does LIMIT do? – Restricts the number of rows.
What does DISTINCT do? – Removes duplicates.
Can I combine SELECT and WHERE? – Yes, WHERE is used with SELECT.

📝 Review Questions (15)

What does SELECT do?
What does WHERE do?
What is the difference between = and LIKE?
What is BETWEEN used for?
What is IN used for?
What is NULL?
What does ORDER BY do?
What does LIMIT do?
What does DISTINCT do?
How do you select all columns?
How do you select specific columns?
How do you filter rows with a condition?
How do you sort results?
How do you restrict the number of rows?
How do you remove duplicates?

✍️ Fill-in-the-Blank Exercises

______ retrieves data from a table.
______ filters rows based on a condition.
______ checks if a value is in a range.
______ checks if a value is in a list.
______ is used to search for patterns in text.

✅ True or False Exercises

SELECT retrieves data. (True)
WHERE is used to sort data. (False)
LIKE is used for pattern searching. (True)
BETWEEN checks if a value is in a list. (False)
NULL means zero. (False)

🔘 Multiple Choice Questions (15 with answers)

What does SELECT do?
a) Adds data b) Retrieves data c) Deletes data d) Updates data
Answer: b
What does WHERE do?
a) Filters rows b) Sorts data c) Adds data d) Deletes data
Answer: a
Which operator checks if a value is in a range?
a) IN b) BETWEEN c) LIKE d) =

Answer: b

Which operator checks if a value is in a list?
a) IN b) BETWEEN c) LIKE d) =

Answer: a

Which operator is used for pattern searching?
a) IN b) BETWEEN c) LIKE d) =

Answer: c

What does NULL mean?
a) Zero b) Missing c) Empty string d) One
Answer: b
What does ORDER BY do?
a) Filters rows b) Sorts data c) Adds data d) Deletes data
Answer: b
What does LIMIT do?
a) Restricts rows b) Adds data c) Deletes data d) Sorts data
Answer: a
What does DISTINCT do?
a) Removes duplicates b) Adds data c) Deletes data d) Sorts data
Answer: a
How do you select all columns?
a) SELECT ALL b) SELECT * c) SELECT ALL * d) SELECT

Answer: b

How do you filter rows?
a) SELECT b) WHERE c) ORDER BY d) LIMIT
Answer: b
Which operator is used for "not equal to"?
a) = b) <> c) > d) <
Answer: b
Which logical operator means "both conditions must be true"?
a) OR b) AND c) NOT d) BETWEEN
Answer: b
Which logical operator means "at least one condition is true"?
a) OR b) AND c) NOT d) BETWEEN
Answer: a
What is the wildcard for any sequence of characters in LIKE?
a) _ b) % c) * d) ?
Answer: b

🔗 Matching Exercises

Match the operator to its description:

Operator	Description
1. =	A. Greater than
2. >	B. Equal to
3. LIKE	C. Pattern search
4. BETWEEN	D. Range check

Answers: 1-B, 2-A, 3-C, 4-D

✍️ Short Answer Questions

What is the purpose of the SELECT statement?
What is the difference between WHERE and ORDER BY?
How do you filter rows with multiple conditions?
What is the difference between LIKE and =?
How do you handle NULL values in queries?

📖 Scenario-based Exercises

Scenario 1: You have a table of students with columns: ID, Name, Age, City. Write a query to find all students who are 10 years old and live in Lagos.

Scenario 2: You have a table of products with columns: ProductID, Name, Price, Category. Write a query to find products with a price between 100 and 500, sorted by price descending.

👥 Group Activity

In groups, create a table called "Employees" with columns: ID, Name, Age, Department, Salary. Insert at least 10 rows. Write SELECT queries to find:

All employees.
Employees in the 'Sales' department.
Employees older than 30.
Employees with salary above 5000.
Employees sorted by salary descending.

🧑‍🎓 Individual Activity

Create a table called "Books" with columns: ID, Title, Author, Year, Price. Insert at least 10 books. Write SELECT queries to find:

All books.
Books by a specific author.
Books published after 2010.
Books with price less than 500.
Books sorted by year.

💬 Classroom Discussion Questions

How can SELECT and WHERE help in making decisions?
What are some real-world scenarios where filtering data is important?
How would you handle a large dataset with SELECT?

🛠️ Mini Project

Project: Library Database

Create a database called "Library". Create a table called "Books" with columns: ID, Title, Author, Year, Price, Genre. Insert at least 20 books. Write 10 different SELECT queries to answer questions like:

List all books.
List books by a specific author.
List books published after 2010.
List books in a specific genre.
List books with price between 200 and 800.
List books sorted by year.
List the most expensive books.
List the cheapest books.
List books whose title contains a specific word.
List distinct genres.

📋 Practical Assignment

Install MySQL on your computer. Create a database called "Sales". Create a table called "Orders" with columns: OrderID, CustomerName, Product, Quantity, Price, OrderDate. Insert at least 20 orders. Write 15 SELECT queries with WHERE, ORDER BY, LIMIT, and DISTINCT. Submit your SQL script.

🏆 Challenge Exercise

Write a SELECT query that finds all customers who have placed orders worth more than 1000 total. You will need to use SUM and GROUP BY (which we will learn later). Try to research and write the query. Hint: Use GROUP BY CustomerName, SUM(Quantity * Price).

📝 Quiz Answers

Fill-in-the-Blank: 1. SELECT, 2. WHERE, 3. BETWEEN, 4. IN, 5. LIKE

True/False: 1-T, 2-F, 3-T, 4-F, 5-F

Matching: 1-B, 2-A, 3-C, 4-D

🔑 Key Takeaways

SELECT retrieves data from tables.
WHERE filters data based on conditions.
Use comparison operators (=, >, <, >=, <=, <>).
Use logical operators (AND, OR, NOT).
Use BETWEEN for ranges.
Use IN for lists.
Use LIKE for patterns.
Handle NULL with IS NULL and IS NOT NULL.
ORDER BY sorts results.
LIMIT restricts rows.
DISTINCT removes duplicates.
Practice is key to mastering SELECT.

📖 Preparation for the Next Module

In Module Three, we will learn about Functions and Aggregations. You will learn how to calculate totals, averages, and counts. You will also learn about grouping data with GROUP BY. Get ready to analyze your data like a pro!

Before the next class, practice writing SELECT queries with WHERE, ORDER BY, and LIMIT. Try to create your own tables and query them.

Module Three

Module 3 · MySQL for Data Analysis Expert

🧮 Module Three: Functions and Aggregations

Hello, young data explorer! Welcome to Module Three. In the previous modules, you learned how to retrieve data with SELECT and filter it with WHERE. Now, it's time to make your queries even more powerful with functions and aggregations. Functions help you change or calculate data (like converting text to uppercase or finding the length of a word). Aggregations help you summarize data (like finding the total, average, or count). By the end of this module, you will be able to answer questions like "What is the total sales?" or "What is the average score of students?" Let's dive in!

🎯 Learning Objectives

By the end of this module, you will be able to:

Use text functions: UPPER, LOWER, LENGTH, CONCAT.
Use numeric functions: ROUND, ABS, CEILING, FLOOR.
Use date functions: CURDATE, YEAR, MONTH, DAY.
Use aggregation functions: COUNT, SUM, AVG, MAX, MIN.
Use GROUP BY to group data.
Use HAVING to filter groups.
Combine functions with SELECT and WHERE.
Write complex queries that answer real-world questions.

📖 Warm-up Story: The Class Report Card

Chidi's teacher wants to create a report card for the class. She has a table of student scores. She needs to calculate the average score for each subject, find the highest score, and count how many students passed. She uses SQL functions to do this quickly. The functions are like magical calculators that do the hard work for her. With a few simple commands, she has a complete report card!

📚 Main Lessons (15 Lessons)

Lesson 1: What are SQL Functions?

Definition: Functions are built-in operations that perform calculations on data.

Why important: They make your queries more powerful and flexible.

Simple explanation: They are like tools in a toolbox that help you do specific jobs.

Real-life example: UPPER() converts text to uppercase.

School example: LENGTH() tells you how many characters are in a name.

Home example: ROUND() rounds a number to a certain number of decimal places.

Nigerian example: YEAR() extracts the year from a date.

Illustration:

Function: UPPER('chidi') → 'CHIDI'
Function: LENGTH('chidi') → 5

Mini summary: Functions are built-in tools for calculations.

Lesson 2: Text Functions – UPPER, LOWER, LENGTH

Definition: Text functions change or give information about text.

Why important: They help you clean and format text.

UPPER(text): Converts text to uppercase.
LOWER(text): Converts text to lowercase.
LENGTH(text): Returns the number of characters.

Real-life example: SELECT UPPER('chidi') → 'CHIDI'

School example: SELECT LENGTH('Chidi') → 5

Home example: SELECT LOWER('CHIDI') → 'chidi'

Nigerian example: SELECT LENGTH('Lagos') → 5

Illustration:

SELECT UPPER('chidi');
+--------------+
| UPPER('chidi')|
+--------------+
| CHIDI        |
+--------------+

Mini summary: Text functions help you work with text.

Lesson 3: Text Functions – CONCAT

Definition: CONCAT combines two or more strings into one.

Why important: It helps you join text together.

Simple explanation: It's like gluing words together.

Real-life example: CONCAT('Chidi', ' ', 'Okonkwo') → 'Chidi Okonkwo'

School example: CONCAT('Hello, ', 'World!') → 'Hello, World!'

Home example: CONCAT('My name is ', 'Chidi') → 'My name is Chidi'

Nigerian example: CONCAT('Lagos', ', ', 'Nigeria') → 'Lagos, Nigeria'

Illustration:

SELECT CONCAT('Chidi', ' ', 'Okonkwo');
+----------------------------------+
| CONCAT('Chidi', ' ', 'Okonkwo')  |
+----------------------------------+
| Chidi Okonkwo                    |
+----------------------------------+

Mini summary: CONCAT joins text together.

Lesson 4: Numeric Functions – ROUND, ABS

Definition: Numeric functions perform calculations on numbers.

Why important: They help you round, convert, and manipulate numbers.

ROUND(number, decimals): Rounds a number.
ABS(number): Returns the absolute (positive) value.

Real-life example: ROUND(15.678, 2) → 15.68

School example: ROUND(10.5, 0) → 11

Home example: ABS(-10) → 10

Nigerian example: ROUND(500.75, 0) → 501

Illustration:

SELECT ROUND(15.678, 2);
+-------------------+
| ROUND(15.678, 2)  |
+-------------------+
| 15.68             |
+-------------------+

Mini summary: Numeric functions help you work with numbers.

Lesson 5: Numeric Functions – CEILING and FLOOR

Definition: CEILING rounds up, FLOOR rounds down.

Why important: Useful for rounding to the nearest whole number.

Simple explanation: CEILING goes up, FLOOR goes down.

Real-life example: CEILING(15.2) → 16, FLOOR(15.9) → 15

School example: CEILING(10.1) → 11

Home example: FLOOR(5.99) → 5

Nigerian example: CEILING(100.01) → 101

Illustration:

SELECT CEILING(15.2), FLOOR(15.9);
+--------------+-------------+
| CEILING(15.2)| FLOOR(15.9) |
+--------------+-------------+
| 16           | 15          |
+--------------+-------------+

Mini summary: CEILING rounds up, FLOOR rounds down.

Lesson 6: Date Functions – CURDATE, YEAR, MONTH, DAY

Definition: Date functions work with dates.

Why important: They help you extract parts of a date.

CURDATE(): Returns today's date.
YEAR(date): Returns the year.
MONTH(date): Returns the month.
DAY(date): Returns the day.

Real-life example: YEAR('2025-01-15') → 2025

School example: MONTH('2025-01-15') → 1

Home example: DAY('2025-01-15') → 15

Nigerian example: CURDATE() → '2025-01-15' (if today is Jan 15)

Illustration:

SELECT YEAR('2025-01-15'), MONTH('2025-01-15'), DAY('2025-01-15');
+------------------+-------------------+-----------------+
| YEAR('2025-01-15')| MONTH('2025-01-15')| DAY('2025-01-15')|
+------------------+-------------------+-----------------+
| 2025             | 1                 | 15              |
+------------------+-------------------+-----------------+

Mini summary: Date functions extract parts of a date.

Lesson 7: Aggregations – COUNT

Definition: COUNT returns the number of rows.

Why important: It tells you how many records you have.

Simple explanation: It's like counting how many items are in a list.

Real-life example: SELECT COUNT(*) FROM Students;

School example: SELECT COUNT(*) FROM Grades WHERE Score > 80;

Home example: SELECT COUNT(*) FROM Expenses;

Nigerian example: SELECT COUNT(*) FROM Customers WHERE City = 'Lagos';

Illustration:

SELECT COUNT(*) FROM Students;
+----------+
| COUNT(*) |
+----------+
| 10       |
+----------+

Mini summary: COUNT counts rows.

Lesson 8: Aggregations – SUM

Definition: SUM adds up all the values in a column.

Why important: It gives you the total.

Simple explanation: It's like adding up all your money.

Real-life example: SELECT SUM(Price) FROM Products;

School example: SELECT SUM(Score) FROM Grades;

Home example: SELECT SUM(Amount) FROM Expenses;

Nigerian example: SELECT SUM(Revenue) FROM Sales;

Illustration:

SELECT SUM(Score) FROM Grades;
+------------+
| SUM(Score) |
+------------+
| 850        |
+------------+

Mini summary: SUM adds numbers.

Lesson 9: Aggregations – AVG

Definition: AVG calculates the average (mean) of a column.

Why important: It gives you the typical value.

Simple explanation: It's like finding the middle of a group of numbers.

Real-life example: SELECT AVG(Price) FROM Products;

School example: SELECT AVG(Score) FROM Grades;

Home example: SELECT AVG(Amount) FROM Expenses;

Nigerian example: SELECT AVG(Salary) FROM Employees;

Illustration:

SELECT AVG(Score) FROM Grades;
+------------+
| AVG(Score) |
+------------+
| 85         |
+------------+

Mini summary: AVG calculates the average.

Lesson 10: Aggregations – MAX and MIN

Definition: MAX finds the highest value, MIN finds the lowest value.

Why important: They give you the extremes.

Simple explanation: MAX is like the tallest person, MIN is like the shortest.

Real-life example: SELECT MAX(Price), MIN(Price) FROM Products;

School example: SELECT MAX(Score), MIN(Score) FROM Grades;

Home example: SELECT MAX(Amount), MIN(Amount) FROM Expenses;

Nigerian example: SELECT MAX(Salary), MIN(Salary) FROM Employees;

Illustration:

SELECT MAX(Score), MIN(Score) FROM Grades;
+------------+------------+
| MAX(Score) | MIN(Score) |
+------------+------------+
| 100        | 50         |
+------------+------------+

Mini summary: MAX finds the highest, MIN finds the lowest.

Lesson 11: GROUP BY – Grouping Data

Definition: GROUP BY groups rows that have the same values.

Why important: It helps you see summaries by category.

Simple explanation: It's like grouping candies by color.

Real-life example: SELECT Category, SUM(Amount) FROM Expenses GROUP BY Category;

School example: SELECT Subject, AVG(Score) FROM Grades GROUP BY Subject;

Home example: SELECT Category, COUNT(*) FROM Expenses GROUP BY Category;

Nigerian example: SELECT City, COUNT(*) FROM Customers GROUP BY City;

Illustration:

SELECT Category, SUM(Amount) FROM Expenses GROUP BY Category;
+----------+-------------+
| Category | SUM(Amount) |
+----------+-------------+
| Food     | 2000        |
| Transport| 1500        |
| Others   | 1000        |
+----------+-------------+

Mini summary: GROUP BY groups data for summaries.

Lesson 12: GROUP BY with Multiple Columns

Definition: You can group by more than one column.

Why important: It gives you more detailed summaries.

Simple explanation: It's like grouping by color and then by size.

Real-life example: SELECT Category, Month, SUM(Amount) FROM Expenses GROUP BY Category, Month;

School example: SELECT Subject, Teacher, AVG(Score) FROM Grades GROUP BY Subject, Teacher;

Home example: SELECT Category, Month, COUNT(*) FROM Expenses GROUP BY Category, Month;

Nigerian example: SELECT State, LGA, COUNT(*) FROM Citizens GROUP BY State, LGA;

Illustration:

SELECT Category, Month, SUM(Amount) FROM Expenses GROUP BY Category, Month;
+----------+-------+-------------+
| Category | Month | SUM(Amount) |
+----------+-------+-------------+
| Food     | Jan   | 1000        |
| Food     | Feb   | 800         |
| Transport| Jan   | 500         |
+----------+-------+-------------+

Mini summary: GROUP BY can group by multiple columns.

Lesson 13: HAVING – Filtering Groups

Definition: HAVING filters groups after GROUP BY.

Why important: Sometimes you only want groups that meet a condition.

Simple explanation: It's like WHERE but for groups.

Real-life example: SELECT Category, SUM(Amount) FROM Expenses GROUP BY Category HAVING SUM(Amount) > 1000;

School example: SELECT Subject, AVG(Score) FROM Grades GROUP BY Subject HAVING AVG(Score) > 80;

Home example: SELECT Category, COUNT(*) FROM Expenses GROUP BY Category HAVING COUNT(*) > 5;

Nigerian example: SELECT City, COUNT(*) FROM Customers GROUP BY City HAVING COUNT(*) > 10;

Illustration:

SELECT Category, SUM(Amount) FROM Expenses GROUP BY Category HAVING SUM(Amount) > 1000;
+----------+-------------+
| Category | SUM(Amount) |
+----------+-------------+
| Food     | 2000        |
| Transport| 1500        |
+----------+-------------+

Mini summary: HAVING filters groups.

Lesson 14: Combining Functions and Aggregations

Definition: You can combine functions and aggregations in one query.

Why important: It makes your queries very powerful.

Simple explanation: It's like using multiple tools at once.

Real-life example: SELECT Category, ROUND(SUM(Amount), 2) FROM Expenses GROUP BY Category;

School example: SELECT Subject, ROUND(AVG(Score), 0) FROM Grades GROUP BY Subject;

Home example: SELECT Category, COUNT(*), MAX(Amount) FROM Expenses GROUP BY Category;

Nigerian example: SELECT State, ROUND(AVG(Salary), 0) FROM Employees GROUP BY State;

Illustration:

SELECT Category, ROUND(AVG(Amount), 0) FROM Expenses GROUP BY Category;
+----------+----------------------+
| Category | ROUND(AVG(Amount), 0) |
+----------+----------------------+
| Food     | 150                  |
| Transport| 200                  |
+----------+----------------------+

Mini summary: Combine functions and aggregations for powerful queries.

Lesson 15: Best Practices for Functions and Aggregations

Definition: Tips for writing clean and efficient queries with functions and aggregations.

Why important: Good habits make your queries faster and easier to understand.

Simple explanation: It's like following a recipe for perfect cookies.

Use meaningful column aliases (AS).
Use WHERE before GROUP BY to filter rows.
Use HAVING to filter groups.
Use functions to clean and format data.

Illustration:

SELECT 
    Category, 
    SUM(Amount) AS Total
FROM 
    Expenses
WHERE 
    Year = 2025
GROUP BY 
    Category
HAVING 
    SUM(Amount) > 1000
ORDER BY 
    Total DESC;

Mini summary: Follow best practices for clean SQL.

📖 Key Vocabulary (with simple definitions)

Function: A built-in operation that performs a calculation.
UPPER: Converts text to uppercase.
LOWER: Converts text to lowercase.
LENGTH: Returns the number of characters in a text.
CONCAT: Joins text together.
ROUND: Rounds a number.
ABS: Returns the absolute value.
CEILING: Rounds up.
FLOOR: Rounds down.
CURDATE: Returns today's date.
Aggregation: A function that summarizes data (COUNT, SUM, AVG, MAX, MIN).
GROUP BY: Groups rows with the same values.
HAVING: Filters groups after GROUP BY.

🧠 Important Concepts

Functions help you change or calculate data.
Aggregations summarize data.
GROUP BY groups data for summaries.
HAVING filters groups.
Combine functions and aggregations for powerful queries.

🔢 Step-by-Step Explanations

How to Use GROUP BY:

Start with SELECT.
Choose the columns you want to group by and the aggregations.
Add FROM and the table name.
Add WHERE (optional) to filter rows before grouping.
Add GROUP BY with the column(s) you want to group by.
Add HAVING (optional) to filter groups.
Add ORDER BY (optional) to sort results.

Example: SELECT Category, SUM(Amount) FROM Expenses WHERE Year = 2025 GROUP BY Category HAVING SUM(Amount) > 1000 ORDER BY SUM(Amount) DESC;

🌍 Real-life Examples

Retail: SELECT Category, SUM(Price * Quantity) FROM Sales GROUP BY Category;
Healthcare: SELECT Disease, COUNT(*) FROM Patients GROUP BY Disease;
Finance: SELECT Branch, AVG(TransactionAmount) FROM Transactions GROUP BY Branch;

🇳🇬 Nigerian Examples

Election: SELECT Party, SUM(Votes) FROM Results GROUP BY Party ORDER BY SUM(Votes) DESC;
Agriculture: SELECT State, AVG(Yield) FROM Farms GROUP BY State;
Telecom: SELECT Region, COUNT(*) FROM Calls WHERE Duration > 5 GROUP BY Region;

🎈 Fun Examples Children Can Relate To

Toys: SELECT ToyType, COUNT(*) FROM Toys GROUP BY ToyType;
Candy: SELECT Flavor, SUM(Pieces) FROM Candies GROUP BY Flavor;
Games: SELECT Game, AVG(Score) FROM Scores GROUP BY Game;

🏠 Everyday Examples

Budget: SELECT Category, SUM(Amount) FROM Expenses GROUP BY Category;
Chores: SELECT Person, COUNT(*) FROM Chores GROUP BY Person;
School: SELECT Subject, AVG(Score) FROM Grades GROUP BY Subject;

👩‍🏫 Teacher Notes

Start with simple functions like UPPER and LENGTH.
Gradually introduce aggregations and GROUP BY.
Use real data to make the lessons interesting.

👨‍👩‍👦 Parent Tips

Help your child create a table and use functions on it.
Encourage them to ask questions like "What is the average expense?"
Discuss how functions are used in everyday life (like calculating tips).

💡 Interesting Facts

CONCAT is used in many programming languages to join text.
AVG is one of the most used aggregation functions.
GROUP BY is essential for data analysis and reporting.

❓ Did You Know?

Did you know that you can use functions inside functions? For example: ROUND(AVG(Price), 2) rounds the average price to 2 decimal places.

🔔 Remember This

Functions help you calculate and format data.
Aggregations summarize data (COUNT, SUM, AVG, MAX, MIN).
GROUP BY groups data for summaries.
HAVING filters groups.
Combine functions and aggregations for powerful queries.

⚠️ Common Mistakes

Forgetting to use GROUP BY when using aggregations.
Using HAVING without GROUP BY.
Using functions incorrectly (e.g., wrong number of arguments).
Forgetting to use quotes around text values in CONCAT.

✅ Best Practices

Always use GROUP BY when using aggregations with non-aggregated columns.
Use WHERE to filter rows before grouping.
Use HAVING to filter groups after grouping.
Use AS to give clear names to columns.

📊 ASCII Illustrations

GROUP BY Flow

All Expenses:
+----+----------+--------+
| ID | Category | Amount |
+----+----------+--------+
| 1  | Food     | 100    |
| 2  | Transport| 50     |
| 3  | Food     | 200    |
| 4  | Others   | 30     |
+----+----------+--------+

GROUP BY Category:
+----------+-------------+
| Category | SUM(Amount) |
+----------+-------------+
| Food     | 300         |
| Transport| 50          |
| Others   | 30          |
+----------+-------------+

Function Example

SELECT UPPER('chidi'), LENGTH('chidi');
+--------------+--------------+
| UPPER('chidi')| LENGTH('chidi')|
+--------------+--------------+
| CHIDI        | 5            |
+--------------+--------------+

📋 Comparison Tables

Aggregation Functions

Function	What it does	Example
COUNT	Counts rows	COUNT(*)
SUM	Adds numbers	SUM(Amount)
AVG	Calculates average	AVG(Score)
MAX	Finds highest	MAX(Price)
MIN	Finds lowest	MIN(Price)

Text Functions

Function	What it does	Example
UPPER	Converts to uppercase	UPPER('chidi')
LOWER	Converts to lowercase	LOWER('CHIDI')
LENGTH	Returns character count	LENGTH('chidi')
CONCAT	Joins text	CONCAT('Hello', 'World')

📝 End-of-Module Summary

Congratulations! You have completed Module Three on Functions and Aggregations. In this module, you learned:

How to use text functions: UPPER, LOWER, LENGTH, CONCAT.
How to use numeric functions: ROUND, ABS, CEILING, FLOOR.
How to use date functions: CURDATE, YEAR, MONTH, DAY.
How to use aggregations: COUNT, SUM, AVG, MAX, MIN.
How to group data with GROUP BY.
How to filter groups with HAVING.
How to combine functions and aggregations.

You now have the skills to summarize and analyze data like a pro. In Module Four, you will learn about Joins!

❓ Frequently Asked Questions (10)

What is a function? – A built-in operation that performs a calculation.
What does UPPER do? – Converts text to uppercase.
What does LENGTH do? – Returns the number of characters in a text.
What does CONCAT do? – Joins text together.
What is an aggregation? – A function that summarizes data (COUNT, SUM, etc.).
What does GROUP BY do? – Groups rows with the same values.
What does HAVING do? – Filters groups after GROUP BY.
What is the difference between WHERE and HAVING? – WHERE filters rows, HAVING filters groups.
What does ROUND do? – Rounds a number.
What does CURDATE do? – Returns today's date.

📝 Review Questions (15)

What is a function?
What does UPPER do?
What does LENGTH do?
What does CONCAT do?
What does ROUND do?
What does CURDATE do?
What is an aggregation?
What does COUNT do?
What does SUM do?
What does AVG do?
What does MAX do?
What does MIN do?
What does GROUP BY do?
What does HAVING do?
What is the difference between WHERE and HAVING?

✍️ Fill-in-the-Blank Exercises

______ converts text to uppercase.
______ returns the number of characters in a text.
______ joins text together.
______ adds numbers together.
______ groups rows with the same values.

✅ True or False Exercises

UPPER converts text to lowercase. (False)
LENGTH returns the number of characters. (True)
SUM adds numbers. (True)
AVG finds the maximum. (False)
GROUP BY groups rows. (True)

🔘 Multiple Choice Questions (15 with answers)

Which function converts text to uppercase?
a) LOWER b) UPPER c) LENGTH d) CONCAT
Answer: b
Which function returns the number of characters?
a) LOWER b) UPPER c) LENGTH d) CONCAT
Answer: c
Which function joins text together?
a) LOWER b) UPPER c) LENGTH d) CONCAT
Answer: d
Which function adds numbers?
a) SUM b) AVG c) COUNT d) MAX
Answer: a
Which function calculates the average?
a) SUM b) AVG c) COUNT d) MAX
Answer: b
Which function counts rows?
a) SUM b) AVG c) COUNT d) MAX
Answer: c
Which function finds the highest value?
a) SUM b) AVG c) COUNT d) MAX
Answer: d
Which function finds the lowest value?
a) SUM b) AVG c) COUNT d) MIN
Answer: d
What does GROUP BY do?
a) Groups rows b) Filters rows c) Sorts rows d) Adds rows
Answer: a
What does HAVING do?
a) Filters groups b) Filters rows c) Sorts groups d) Adds groups
Answer: a
What does ROUND do?
a) Rounds a number b) Adds numbers c) Counts rows d) Finds maximum
Answer: a
What does CURDATE do?
a) Returns today's date b) Returns yesterday's date c) Returns tomorrow's date d) Returns a date
Answer: a
Which is a text function?
a) SUM b) AVG c) UPPER d) MAX
Answer: c
Which is a numeric function?
a) UPPER b) LENGTH c) ROUND d) CONCAT
Answer: c
Which is a date function?
a) UPPER b) LENGTH c) YEAR d) ROUND
Answer: c

🔗 Matching Exercises

Match the function to its description:

Function	Description
1. UPPER	A. Joins text
2. LENGTH	B. Converts to uppercase
3. CONCAT	C. Returns character count
4. SUM	D. Adds numbers

Answers: 1-B, 2-C, 3-A, 4-D

✍️ Short Answer Questions

What is the purpose of functions in SQL?
Explain the difference between WHERE and HAVING.
What is GROUP BY and when do you use it?
How do you use CONCAT to join text?
What is the difference between COUNT and SUM?

📖 Scenario-based Exercises

Scenario 1: You have a table of sales with columns: Product, Category, Amount. Write a query to find the total sales for each category.

Scenario 2: You have a table of students with columns: Name, Subject, Score. Write a query to find the average score for each subject, but only include subjects with an average score above 80.

👥 Group Activity

In groups, create a table called "Orders" with columns: OrderID, Customer, Product, Quantity, Price. Insert at least 20 rows. Write queries to find:

Total sales for each product.
Total number of orders for each customer.
Average quantity for each product.
Maximum price for each product.
Customers who have made more than 5 orders.

🧑‍🎓 Individual Activity

Create a table called "Employees" with columns: ID, Name, Department, Salary. Insert at least 15 employees. Write queries to find:

Total salary paid in each department.
Average salary in each department.
Highest salary in each department.
Number of employees in each department.
Departments with average salary above 5000.

💬 Classroom Discussion Questions

Why are aggregations important in data analysis?
How can GROUP BY help in making business decisions?
What are some real-world scenarios where you would use HAVING?

🛠️ Mini Project

Project: Sales Analysis

Create a database called "SalesDB". Create a table called "Sales" with columns: SaleID, Product, Category, Quantity, Price, SaleDate. Insert at least 50 sales records. Write queries to answer:

Total sales per product.
Total sales per category.
Average price per product.
Number of sales per month.
Top 5 products by sales.
Categories with total sales above 1000.

📋 Practical Assignment

Download a sample dataset (e.g., sales data from Kaggle). Import it into MySQL. Write 20 queries using functions and aggregations. Include at least one query with each: COUNT, SUM, AVG, MAX, MIN, GROUP BY, HAVING.

🏆 Challenge Exercise

Write a query that finds the top 3 selling products by total revenue. Use GROUP BY, SUM, ORDER BY, and LIMIT. Also include the average price per product and the number of units sold.

📝 Quiz Answers

Fill-in-the-Blank: 1. UPPER, 2. LENGTH, 3. CONCAT, 4. SUM, 5. GROUP BY

True/False: 1-F, 2-T, 3-T, 4-F, 5-T

Matching: 1-B, 2-C, 3-A, 4-D

🔑 Key Takeaways

Functions help you calculate and format data.
Aggregations summarize data.
GROUP BY groups data for summaries.
HAVING filters groups.
Combine functions and aggregations for powerful queries.
Practice is key to mastering SQL.

📖 Preparation for the Next Module

In Module Four, you will learn about Joins. Joins allow you to combine data from multiple tables. This is essential for working with real-world databases where data is split across many tables. Get ready to connect the dots!

Before the next class, practice using functions and aggregations with different datasets. Try to answer at least 5 questions using GROUP BY.

Module Four

Module 4: MySQL for Data Analysis Expert – Fun with Data!

📘 Module 4: MySQL for Data Analysis Expert

“Asking questions and getting answers from data”

🧭 Module Introduction

Welcome, young data explorer! In this module, we will learn how to talk to a database using a special language called SQL (say it like “see-quell”).

Imagine you have a giant box full of toys. You want to find all the red cars, or count how many dolls you have. You need a way to ask for that information quickly. That’s what SQL does with data!

By the end of this module, you will be able to ask MySQL questions like: “Show me all customers from Lagos” or “What is the average score of students?”. You will become a Data Analysis Expert!

🎯 Learning Objectives

Understand what a database is and why we use MySQL.
Learn how to write simple SQL queries to SELECT data.
Use WHERE to filter data (like a sieve!).
Sort data with ORDER BY.
Group data using GROUP BY and use COUNT, SUM, AVG.
Combine tables using JOIN (like joining two puzzle pieces).
Understand primary keys and foreign keys.
Feel confident to answer questions with data.

📖 Warm-up Story: Aisha’s Toy Shop

Aisha has a small toy shop in Abuja. She sells cars, dolls, and puzzles. Every day, she writes down what she sells in a big notebook. But the notebook is getting heavy and messy!

One day, her friend Tunde said, “Why don’t you use a computer to store everything? You can use MySQL. It’s like a magic notebook that answers your questions instantly.”

Aisha tried it. She typed: “Show me all toys that cost more than 500 Naira” – and boom! The computer showed her only the expensive toys. She was so happy.

Now Aisha can ask: “How many dolls did I sell this week?” or “Which toy is the most popular?” She became a data analysis expert!

This module will teach you exactly how to do what Aisha did. Let’s go! 🚀

📚 Main Lessons

Lesson 1: What is a Database?

Definition: A database is like a digital cupboard where we keep information in an organised way.

Why important: Without a database, we would have papers everywhere. Databases help us find things fast.

Simple explanation: Imagine your school has a list of all students. That list is a database. It has names, classes, and ages.

Real-life example: Your school’s library uses a database to know which books are borrowed.

School example: A teacher uses a database to store grades.

Home example: Your mum might have a list of groceries in a notebook – that’s a tiny database!

Nigerian example: The Nigerian Railway uses a database to track train times and passengers.

Illustration:

+------------------+
|  DATABASE        |
|  +------------+  |
|  | Students   |  |
|  | Name, Age  |  |
|  +------------+  |
|  +------------+  |
|  | Books      |  |
|  | Title,Author| |
|  +------------+  |
+------------------+

Mini summary: A database is an organised collection of data. It helps us store and find information easily.

Lesson 2: What is SQL and MySQL?

Definition: SQL stands for Structured Query Language. It is the language we use to talk to databases.

Why important: SQL is like a remote control for your database. You press buttons (write queries) and get results.

Simple explanation: MySQL is a type of database software that understands SQL. It is free and very popular.

Real-life example: When you search for a video on YouTube, YouTube uses SQL-like queries to find it.

School example: Your school’s attendance system uses MySQL to record who is present.

Home example: If you have a list of your friends’ birthdays in a computer file, you could use SQL to sort them by month.

Nigerian example: Many banks in Nigeria use MySQL to keep customer records safe.

Illustration:

[You]  ---SQL Query--->  [MySQL]  --->  [Answer]
"SELECT name FROM students"   →   returns "Chioma, Tunde, etc."

Mini summary: SQL is the language, MySQL is the database system. We use SQL to ask MySQL questions.

Lesson 3: The SELECT statement – getting data

Definition: SELECT is a command that fetches data from a table.

Why important: It is the most used command. You will always use SELECT to see your data.

Simple explanation: It’s like saying: “Show me this information.”

Real-life example: “SELECT name, age FROM students” shows all names and ages.

School example: A teacher uses SELECT to see the list of students in class.

Home example: You could have a table of your toys and use SELECT to list them.

Nigerian example: A shopkeeper uses SELECT * FROM products to see all items.

Illustration:

Table: toys
+-------+---------+-------+
| name  | colour  | price |
+-------+---------+-------+
| car   | red     | 300   |
| doll  | pink    | 450   |
+-------+---------+-------+

SELECT name FROM toys;
Result:
+------+
| name |
+------+
| car  |
| doll |
+------+

Mini summary: SELECT lets you choose which columns you want to see from a table.

Lesson 4: WHERE – filtering like a sieve

Definition: WHERE is used to filter rows that meet a certain condition.

Why important: Sometimes you don’t want all data, only specific ones. WHERE helps you pick.

Simple explanation: It’s like putting a sieve over your data and only keeping what you want.

Real-life example: “SELECT * FROM toys WHERE price > 200” gives toys that cost more than 200.

School example: “SELECT name FROM students WHERE class = ‘Primary 5’” gives only Primary 5 students.

Home example: “SELECT * FROM grocery WHERE item = ‘milk’” shows only milk.

Nigerian example: “SELECT customer FROM orders WHERE city = ‘Lagos’”

Illustration:

+----------+-------+
| fruit    | price |
+----------+-------+
| apple    | 100   |
| banana   | 50    |
| orange   | 80    |
+----------+-------+

SELECT * FROM fruit WHERE price >= 80;
Result:
+--------+-------+
| fruit  | price |
+--------+-------+
| apple  | 100   |
| orange | 80    |
+--------+-------+

Mini summary: WHERE filters data based on a condition. It helps us get exactly what we need.

Lesson 5: ORDER BY – sorting your data

Definition: ORDER BY sorts the result in ascending (A-Z, 1-9) or descending (Z-A, 9-1) order.

Why important: Sometimes you want to see the highest score first, or names in alphabetical order.

Simple explanation: It’s like arranging your books by size on a shelf.

Real-life example: “SELECT name, score FROM students ORDER BY score DESC” puts highest score first.

School example: A teacher uses ORDER BY to rank students.

Home example: You can sort your video games by release date.

Nigerian example: “SELECT product, price FROM store ORDER BY price ASC” shows cheapest first.

Illustration:

+------+-------+
| name | score |
+------+-------+
| Ama  | 85    |
| Ben  | 92    |
| Chu  | 78    |
+------+-------+

SELECT name, score FROM scores ORDER BY score DESC;
Result:
+------+-------+
| name | score |
+------+-------+
| Ben  | 92    |
| Ama  | 85    |
| Chu  | 78    |
+------+-------+

Mini summary: ORDER BY sorts your results. ASC is small to big, DESC is big to small.

Lesson 6: GROUP BY and aggregation (COUNT, SUM, AVG)

Definition: GROUP BY groups rows that have the same values. Then we can use COUNT (number of rows), SUM (total), or AVG (average).

Why important: It helps us answer questions like: “How many students in each class?” or “What is the total sales?”

Simple explanation: Imagine you have a bag of mixed sweets. You group them by colour, then count how many of each colour.

Real-life example: “SELECT class, COUNT(*) FROM students GROUP BY class” gives number of students per class.

School example: A teacher wants to know the average test score per subject.

Home example: You group your toys by type and count how many cars, dolls, etc.

Nigerian example: “SELECT state, SUM(amount) FROM sales GROUP BY state” shows total sales per state.

Illustration:

Table: sales
+--------+--------+
| product| amount |
+--------+--------+
| book   | 200    |
| pen    | 50     |
| book   | 150    |
| pen    | 30     |
+--------+--------+

SELECT product, SUM(amount) FROM sales GROUP BY product;
Result:
+--------+------------+
| product| SUM(amount)|
+--------+------------+
| book   | 350        |
| pen    | 80         |
+--------+------------+

Mini summary: GROUP BY combines rows with the same value, and we can use COUNT, SUM, AVG on them.

Lesson 7: JOIN – connecting two tables

Definition: JOIN combines rows from two or more tables based on a related column.

Why important: Often data is split across tables. JOIN brings them back together like puzzle pieces.

Simple explanation: If you have a table of students and a table of classes, JOIN can show each student with their class name.

Real-life example: “SELECT students.name, classes.class_name FROM students JOIN classes ON students.class_id = classes.id”

School example: Joining a teacher table with a subject table to see which teacher teaches which subject.

Home example: You have a table of family members and a table of birthdays. JOIN shows each person’s birthday.

Nigerian example: A hospital joins patients and doctors tables to see which doctor treats which patient.

Illustration:

Table: students          Table: classes
+----+-------+--------+  +----+------------+
| id | name  | class_id| | id | class_name |
+----+-------+--------+  +----+------------+
| 1  | Ada   | 101     |  | 101| Primary 4  |
| 2  | Bola  | 102     |  | 102| Primary 5  |
+----+-------+--------+  +----+------------+

SELECT students.name, classes.class_name 
FROM students 
JOIN classes ON students.class_id = classes.id;

Result:
+------+------------+
| name | class_name |
+------+------------+
| Ada  | Primary 4  |
| Bola | Primary 5  |
+------+------------+

Mini summary: JOIN lets us combine tables using a shared column (like a bridge).

Lesson 8: Primary Key and Foreign Key

Definition: A Primary Key is a unique ID for each row. A Foreign Key is a column that links to a primary key in another table.

Why important: They keep data clean and allow JOINs to work properly.

Simple explanation: Primary key is like your admission number – no two students have the same. Foreign key is like your class ID that connects you to your class.

Real-life example: In a school, student ID is primary key. Class ID in student table is a foreign key.

School example: Each book in the library has a unique barcode (primary key). The borrower’s card number is a foreign key.

Home example: Each family member has a unique phone number (primary key). In a chores table, the person’s phone number is a foreign key.

Nigerian example: In a bank, account number is primary key. Transaction table uses account number as foreign key.

Illustration:

Table: Customer (Primary Key: customer_id)
+-------------+----------+
| customer_id | name     |
+-------------+----------+
| 1           | Chidi    |
| 2           | Ngozi    |
+-------------+----------+

Table: Orders (Foreign Key: customer_id)
+----------+-------------+--------+
| order_id | customer_id | amount |
+----------+-------------+--------+
| 101      | 1           | 500    |
| 102      | 2           | 300    |
+----------+-------------+--------+

Mini summary: Primary key is unique per row. Foreign key links to another table’s primary key.

Lesson 9: More functions – MAX, MIN, COUNT

Definition: MAX gives the highest value, MIN gives the lowest, COUNT counts rows.

Why important: These help us find extremes and totals quickly.

Simple explanation: MAX = tallest person, MIN = shortest, COUNT = how many people.

Real-life example: SELECT MAX(price) FROM products; shows the most expensive product.

School example: Find the highest and lowest test scores.

Home example: Count how many eggs in the fridge.

Nigerian example: Find the minimum salary in a company.

Illustration:

+------+-------+
| name | score |
+------+-------+
| Ada  | 88    |
| Ben  | 72    |
| Chu  | 95    |
+------+-------+
SELECT MAX(score) FROM scores; → 95
SELECT MIN(score) FROM scores; → 72
SELECT COUNT(*) FROM scores; → 3

Mini summary: MAX, MIN, COUNT are useful to summarise data.

Lesson 10: DISTINCT – remove duplicates

Definition: DISTINCT shows only unique values in a column.

Why important: It removes repeated data so you can see all the different categories.

Simple explanation: If you list colours and have “red” many times, DISTINCT shows “red” once.

Real-life example: SELECT DISTINCT city FROM customers; shows all cities where customers live, without repetition.

School example: List all the different grades students got.

Home example: List all the types of fruits you have.

Nigerian example: Show all unique states in a sales table.

Illustration:

+---------+
| colour  |
+---------+
| red     |
| blue    |
| red     |
| green   |
+---------+
SELECT DISTINCT colour FROM items; 
Result: red, blue, green

Mini summary: DISTINCT gives you only one copy of each value.

📝 Key Vocabulary

Database: A place to store organised information.
SQL: A language to talk to databases.
MySQL: A popular database software.
Table: A set of rows and columns (like a spreadsheet).
Row: One record or entry.
Column: One category of information (like “name” or “age”).
Query: A question you ask the database.
SELECT: Command to get data.
WHERE: Filter data.
ORDER BY: Sort data.
GROUP BY: Group data for counting/summing.
JOIN: Combine tables.
Primary Key: Unique ID for a row.
Foreign Key: Link to another table.

🧠 Important Concepts

Data is everywhere: From school attendance to shop sales.
SQL helps us ask questions: We can find patterns, totals, and details.
Filtering is powerful: WHERE lets us focus on what matters.
Joining tables is like teamwork: It combines information from different sources.
Keys keep things tidy: Primary and foreign keys make sure data is consistent.

👣 Step-by-Step Explanations

How to write a SELECT query:

Start with SELECT.
List the columns you want (or use * for all).
Write FROM and the table name.
Add WHERE if you need to filter.
Add ORDER BY if you need sorting.
End with a semicolon ;.

Example: SELECT name, age FROM pupils WHERE age > 10 ORDER BY name;

🌍 Real-life Examples

A supermarket uses SQL to find which products are low in stock.
A hospital uses SQL to find patients who need a check-up.
A school uses SQL to calculate the average grade per class.
An airline uses SQL to see which flights are full.

🇳🇬 Nigerian Examples

A small boutique in Lagos uses SQL to track daily sales.
A farmer in Kaduna uses a database to record crop yields.
A teacher in Enugu uses SQL to store exam results and find top students.
An NGO in Abuja uses SQL to manage donor lists.

🧸 Fun Examples for Children

Your toy collection: table (toy_name, colour, price). Use SELECT to list all red toys.
Your class birthday list: use ORDER BY to see who has a birthday next.
Your candy jar: use GROUP BY to count how many of each colour.
Your video game scores: use MAX to find your highest score.

🏠 Everyday Examples

Phone contacts: you can use SELECT to find all contacts with “A” in their name.
Grocery list: you can use WHERE to see items costing less than 200 Naira.
Chores table: use ORDER BY to see chores sorted by deadline.
Pocket money: use SUM to total your weekly allowance.

🧑‍🏫 Teacher Notes

Encourage students to think of their own examples.
Use analogies like “filtering spaghetti” for WHERE.
Demonstrate JOIN with two paper lists.
Emphasise that practice is key – write many small queries.
Relate every concept to something they know (toys, food, friends).

👪 Parent Tips

Ask your child to create a simple table of family members and practice SELECT.
Use everyday questions: “What is the most expensive item in our fridge?”
Show them how databases help in businesses.
Play “data detective” – give them a small dataset and ask questions.
Remind them that data analysis is a superpower for decision-making.

✨ Interesting Facts

MySQL is named after the co-founder’s daughter, “My”.
SQL was created in the 1970s but is still used by almost every company.
Facebook and Google use huge databases, some with SQL.
The first database systems were on paper!

🤔 Did You Know?

Did you know that MySQL can handle billions of rows? That’s like storing all the grains of rice in a large bag!
Did you know that the word “query” means “question”? So every SQL query is a question to the data.
Did you know that many games use SQL to store player scores and inventories?

🔔 Remember This

Always use semicolon at the end of your SQL statements.
WHERE filters rows, not columns.
ORDER BY changes the order of results.
JOIN needs a condition (ON) to match the two tables.
Primary keys are unique – no duplicates.

❌ Common Mistakes

Forgetting the semicolon ; at the end.
Misspelling column names (MySQL is case-sensitive sometimes).
Using WHERE before FROM – always FROM first.
Forgetting the JOIN condition – you need ON.
Confusing COUNT(*) with COUNT(column) – COUNT(*) counts all rows, COUNT(column) counts non-null values.

✅ Best Practices

Write SQL keywords in UPPERCASE for readability.
Use meaningful table and column names.
Test your queries with small data first.
Use comments to explain complex queries.
Always back up your data.

📊 Illustrations & Diagrams

Data flow: from question to answer

[Your Question]
      |
      V
[Write SQL Query]
      |
      V
[MySQL runs it]
      |
      V
[Returns Answer]
      |
      V
[You see the result] 🎉

Table Relationship (Primary-Foreign Key)

+-----------+          +----------+
| CUSTOMERS |          | ORDERS   |
+-----------+          +----------+
| PK: id    |----------| FK: cust_id
| name      |          | order_id |
| phone     |          | amount   |
+-----------+          +----------+

Comparison: WHERE vs GROUP BY

WHERE	GROUP BY
Filters individual rows.	Groups rows and aggregates.
Used before GROUP BY.	Used after WHERE.
Example: WHERE price > 100	Example: GROUP BY city

📋 Comparison Tables

Function	What it does	Example
COUNT	Counts rows	COUNT(*)
SUM	Adds values	SUM(price)
AVG	Average	AVG(score)
MAX	Highest value	MAX(age)
MIN	Lowest value	MIN(age)

📌 End-of-Module Summary

In Module 4, we learned how to ask questions using SQL with MySQL. We covered SELECT, WHERE, ORDER BY, GROUP BY, JOIN, and keys. We discovered that data is everywhere and we can use it to make decisions. We practiced with many examples from school, home, and Nigeria. Now you can be a data analysis expert – just like Aisha in the toy shop!

❓ Frequently Asked Questions (10)

What is MySQL? It’s a software that stores data and lets you use SQL.
Do I need to install MySQL? For practice, you can use online editors, but for real projects you install it.
Is SQL hard? No, it’s like learning a few new words.
Can I use SQL on my phone? Yes, there are apps.
What is a table? A grid with rows and columns.
What is a query? A question you ask the database.
What is JOIN? Combining two tables using a common column.
Why use Primary Key? To uniquely identify each row.
Can I delete data? Yes, with DELETE, but be careful!
Will I use SQL in real life? Yes, many jobs need SQL.

📝 Review Questions (15)

What does SQL stand for?
What command do you use to get data?
How do you filter data?
What does ORDER BY do?
What is GROUP BY used for?
What is a primary key?
What is a foreign key?
How do you count rows?
How do you find the highest value?
What is the difference between WHERE and GROUP BY?
Write a query to select all columns from a table called “animals”.
Write a query to select names from “students” where age is greater than 10.
Write a query to sort “products” by price from low to high.
What does DISTINCT do?
Why do we use JOIN?

📝 Fill-in-the-Blank

SQL stands for __________ Query Language.
We use the __________ command to get data from a table.
__________ filters rows based on a condition.
__________ sorts the result.
A __________ key uniquely identifies a row.
A __________ key links to another table.
__________ counts the number of rows.
__________ shows only unique values.
__________ combines two tables.
MySQL is a type of __________ software.

✅ True or False

SQL is used to talk to databases. (True)
MySQL is a programming language. (False – it’s a DBMS)
WHERE is used to sort data. (False – ORDER BY sorts)
Primary key must be unique. (True)
JOIN is used to delete data. (False – JOIN combines)
GROUP BY is used with aggregate functions. (True)
DISTINCT removes duplicates. (True)
You can use SELECT without FROM. (False – need a table)

🔘 Multiple Choice (15)

Which command retrieves data?
A) INSERT B) SELECT C) UPDATE D) DELETE
Answer: B
Which clause filters data?
A) WHERE B) ORDER BY C) GROUP BY D) JOIN
Answer: A
Which sorts data?
A) WHERE B) ORDER BY C) GROUP BY D) SELECT
Answer: B
Which groups data?
A) WHERE B) ORDER BY C) GROUP BY D) JOIN
Answer: C
Which combines tables?
A) WHERE B) ORDER BY C) GROUP BY D) JOIN
Answer: D
What is a Primary Key?
A) A key that can be null B) Unique identifier C) A foreign key D) A sort key
Answer: B
What does COUNT(*) do?
A) Counts non-null values B) Counts all rows C) Sums values D) Averages values
Answer: B
What does DISTINCT do?
A) Shows duplicates B) Removes duplicates C) Sorts D) Filters
Answer: B
Which is an aggregate function?
A) SELECT B) WHERE C) SUM D) JOIN
Answer: C
What is a Foreign Key?
A) Unique ID B) Links to another table C) A sort key D) A filter
Answer: B
Which command is used to sort in descending order?
A) ORDER BY name ASC B) ORDER BY name DESC C) SORT BY name D) ORDER BY name
Answer: B
What does AVG do?
A) Counts B) Sums C) Average D) Maximum
Answer: C
Which is not an SQL statement?
A) SELECT B) UPDATE C) PRINT D) DELETE
Answer: C
Which keyword removes duplicates?
A) UNIQUE B) DISTINCT C) DIFFERENT D) SINGLE
Answer: B
JOIN uses which condition?
A) WHERE B) ON C) USING D) WITH
Answer: B

🔗 Matching

Term	Definition
SELECT	Gets data
WHERE	Filters rows
ORDER BY	Sorts data
GROUP BY	Groups rows
JOIN	Combines tables

✍️ Short Answer Questions

What is a database?
Why is SQL important?
Explain the difference between WHERE and GROUP BY.
What is a primary key? Give an example.
What is a foreign key? Give an example.

📖 Scenario-based Exercises

Scenario 1: You have a table of books with columns: id, title, author, year. Write a query to find books written by “Chinua Achebe”.

Scenario 2: You have a sales table: id, product, quantity, price. Write a query to show total revenue (quantity * price) per product.

Scenario 3: You have a students table and a subjects table. Write a query to show each student and their subject using JOIN.

👥 Group Activity

In groups of 3, create a simple table of your favourite movies (title, genre, rating). Practice writing SELECT, WHERE, ORDER BY, and GROUP BY queries. Share your results with the class.

🧑 Individual Activity

Create a table of your 10 favourite foods (name, type, price). Write 5 different SELECT queries that use WHERE, ORDER BY, and GROUP BY.

💬 Classroom Discussion Questions

Why do we need databases in real life?
Can you think of a place where MySQL is used?
What would happen if we didn’t have primary keys?
How can data analysis help a business?

📁 Mini Project

Project: Create a database for a small shop. Create tables: products (id, name, price, category) and sales (sale_id, product_id, quantity, date). Write queries to:

List all products.
Show products with price > 500.
Show total sales per product (using JOIN and SUM).
Find the most popular product (highest quantity sold).

Draw your schema and write SQL queries.

🛠️ Practical Assignment

Install MySQL (or use an online editor) and create a table “students” with columns: id, name, age, class. Insert 10 records. Write queries to:

Select all students.
Select students older than 10.
Sort by age descending.
Count number of students per class.

🔥 Challenge Exercise

Write a single SQL query that shows each product, total quantity sold, and the total revenue (quantity * price), but only for products that have sold more than 5 units. Use JOIN, GROUP BY, and HAVING (HAVING is like WHERE for groups).

📋 Quiz Answers

Fill-in-the-blank answers: 1. Structured, 2. SELECT, 3. WHERE, 4. ORDER BY, 5. Primary, 6. Foreign, 7. COUNT, 8. DISTINCT, 9. JOIN, 10. database.

True/False: 1T, 2F, 3F, 4T, 5F, 6T, 7T, 8F.

Multiple Choice: 1B,2A,3B,4C,5D,6B,7B,8B,9C,10B,11B,12C,13C,14B,15B.

🎯 Key Takeaways

SQL is the language for databases.
MySQL is a popular database system.
SELECT gets data, WHERE filters, ORDER BY sorts.
GROUP BY groups data for counting/summing.
JOIN combines tables using keys.
Primary and foreign keys keep data connected.
Practice makes perfect – write many queries!

🔜 Preparation for Module 5

In Module 5, we will learn how to change data – we will use INSERT (to add new rows), UPDATE (to change data), and DELETE (to remove data). We will also learn about transactions and backups. Keep your SQL skills sharp!

End of Module 4 – You are now a Data Analysis Expert! 🏆

Module Five

Module 5 · MySQL for Data Analysis Expert

🧙 Module Five · MySQL for Data Analysis Expert

📊 Asking Questions with SQL – The Art of Querying

🌟 Module Introduction

Welcome, young data explorer! In this module, we are going to learn how to ask questions to a database using SQL (say: S-Q-L or “sequel”). SQL is a special language that helps us talk to MySQL. Think of it like a magic wand that makes data appear!

We already know how to store data in tables. Now we will learn how to find the data we want. We will sort it, filter it, and even combine data from different tables. By the end, you will be a data analysis expert who can answer any question with data!

🎯 Learning Objectives

After this module, you will be able to:

Write SELECT statements to get data from a table.
Use WHERE to filter data (like a sieve).
Sort data with ORDER BY (put things in order).
Group data with GROUP BY and use aggregate functions (COUNT, SUM, AVG).
Join two tables together with INNER JOIN.
Understand primary keys and foreign keys (like best friends linking tables).

📖 Warm‑up Story · The Great Market Mystery

In a busy Lagos market, Mama Bisi sells fruits. She has a big notebook with hundreds of sales. One day, she asks her daughter, “How many mangoes did I sell on Monday?” Her daughter looks at the notebook and cries, “It will take me forever!”

Then a data wizard (that’s you!) arrives and says, “Let me teach you SQL! With one question — SELECT SUM(mangoes) FROM sales WHERE day = 'Monday'; — we will have the answer in a flash!”

And that is exactly what we will learn: how to ask the right questions to get the answers fast!

📘 Lesson 1 · What is SQL?

Definition: SQL stands for Structured Query Language. It is a language we use to talk to databases.

Why important: Without SQL, we cannot ask the database questions. It’s like trying to order food without speaking the waiter’s language!

Simple explanation: SQL is a set of words and rules that tell MySQL what we want. We write a query (that’s our question) and MySQL gives us the answer.

Real‑life example: A librarian uses a computer system to find books. She types the title, and the system shows where the book is. That system uses SQL behind the scenes.

School example: Your teacher asks, “Who scored above 80% in Maths?” She types an SQL query, and the computer shows the names.

Home example: Dad wants to know how many times he ate rice this month. He can ask the “food diary” app — it uses SQL!

Nigerian example: At the National Population Commission, they use SQL to count how many people live in each state.

+------------------+
|   SQL Query      |
|  "SELECT name    |
|   FROM students  |
|   WHERE score>80"|
+------------------+
        |
        V
+------------------+
|   Answer         |
|  "Ade, Tolu"     |
+------------------+

✅ Mini summary: SQL is the language we use to ask MySQL for data.

📘 Lesson 2 · The SELECT statement – your first question

Definition: SELECT is the most common SQL command. It means “show me this data”.

Why important: You cannot analyse data if you cannot see it! SELECT is your window into the table.

Simple explanation: You write SELECT * FROM table_name; to see everything. The star * means “all columns”.

Real‑life example: In a classroom, you say “show me all students” – SELECT does that.

School example: SELECT * FROM books; shows all books in the library.

Home example: SELECT * FROM grocery_list; shows every item on the list.

Nigerian example: SELECT * FROM states; shows all 36 states.

+-------------+-------------+------+
| id          | name        | age  |
+-------------+-------------+------+
| 1           | Chidi       | 12   |
| 2           | Ngozi       | 13   |
+-------------+-------------+------+

✅ Mini summary: SELECT * shows all columns in a table.

📘 Lesson 3 · Filtering with WHERE – using a sieve

Definition: WHERE filters rows. It’s like a sieve that only keeps the data we want.

Why important: We rarely want all data. We want specific things, like “students older than 10”.

Simple explanation: After SELECT, we add WHERE condition. Only rows that meet the condition appear.

Real‑life example: A shopkeeper wants to see sales above ₦5000. WHERE helps.

School example: SELECT * FROM students WHERE class = 'JSS2';

Home example: SELECT * FROM fridge WHERE food = 'ice cream';

Nigerian example: SELECT * FROM voters WHERE state = 'Lagos';

   Table: fruits
+---------+----------+
| name    | quantity |
+---------+----------+
| mango   | 100      |
| orange  | 50       |
+---------+----------+

SELECT * FROM fruits WHERE quantity > 60;
Result: mango  (because 100 > 60)

✅ Mini summary: WHERE keeps only rows that match our condition.

📘 Lesson 4 · ORDER BY – putting things in order

Definition: ORDER BY sorts the result by a column. It can be ASC (small to big, A to Z) or DESC (big to small, Z to A).

Why important: We like things neat! ORDER BY helps us see the highest scores first or the oldest first.

Simple explanation: Just like you arrange your toys by size, ORDER BY arranges data.

Real‑life example: A teacher wants to see the best students first – ORDER BY score DESC.

School example: SELECT name, age FROM students ORDER BY age ASC; (youngest first).

Home example: SELECT * FROM chores ORDER BY priority DESC; (most important first).

Nigerian example: SELECT * FROM cities ORDER BY population DESC; (Lagos first).

Before sort:
+------+-------+
| name | score |
+------+-------+
| Ada  | 70    |
| Bola | 90    |
| Chis | 85    |
+------+-------+

ORDER BY score DESC:
+------+-------+
| name | score |
+------+-------+
| Bola | 90    |
| Chis | 85    |
| Ada  | 70    |
+------+-------+

✅ Mini summary: ORDER BY sorts the answer rows.

📘 Lesson 5 · GROUP BY & aggregate functions – counting and summing

Definition: GROUP BY groups rows that have the same value in a column. Then we use aggregate functions like COUNT, SUM, AVG to summarise each group.

Why important: Instead of looking at every single row, we can see totals and averages.

Simple explanation: Imagine you have a bag of coloured marbles. GROUP BY colour, then COUNT the marbles in each colour.

Real‑life example: A supermarket wants total sales per day. GROUP BY day, SUM(sales).

School example: SELECT class, COUNT(*) FROM students GROUP BY class; (number of students per class).

Home example: SELECT type, SUM(amount) FROM expenses GROUP BY type; (how much spent on food vs. toys).

Nigerian example: SELECT state, COUNT(*) FROM voters GROUP BY state; (voters per state).

Table: sales
+---------+--------+
| product | amount |
+---------+--------+
| mango   | 100    |
| orange  | 50     |
| mango   | 200    |
+---------+--------+

SELECT product, SUM(amount)
FROM sales
GROUP BY product;
Result:
+---------+------------+
| product | SUM(amount)|
+---------+------------+
| mango   | 300        |
| orange  | 50         |
+---------+------------+

✅ Mini summary: GROUP BY + aggregate functions gives summaries per group.

📘 Lesson 6 · JOIN – connecting two tables

Definition: JOIN (or INNER JOIN) combines rows from two tables based on a common column.

Why important: Data is often split across tables. JOIN brings them together.

Simple explanation: Like matching shoes with their boxes – you need a common label (shoe size).

Real‑life example: A school has a table of students and a table of classes. JOIN them to see which student is in which class.

School example: SELECT students.name, classes.class_name FROM students JOIN classes ON students.class_id = classes.id;

Home example: One table for family members, another for birthdays. JOIN to see everyone’s birthday.

Nigerian example: Table of states and table of governors. JOIN to see which governor leads which state.

Table A (students)
+----+------+----------+
| id | name | class_id |
+----+------+----------+
| 1  | Ada  | 101      |
| 2  | Ben  | 102      |
+----+------+----------+

Table B (classes)
+-----+------------+
| id  | class_name |
+-----+------------+
| 101 | JSS1       |
| 102 | JSS2       |
+-----+------------+

JOIN result:
+------+------------+
| name | class_name |
+------+------------+
| Ada  | JSS1       |
| Ben  | JSS2       |
+------+------------+

✅ Mini summary: JOIN merges two tables using a common column.

📘 Lesson 7 · Primary key & Foreign key – the linking pins

Definition: A primary key is a unique identifier for each row in a table. A foreign key is a column that points to a primary key in another table.

Why important: They allow us to connect tables correctly.

Simple explanation: Think of a primary key like your student ID number – no two students share it. A foreign key is like writing your friend’s ID in your notebook to say “this friend is in my group”.

Real‑life example: In a hospital, each patient has a unique ID (primary key). The appointments table uses that ID (foreign key) to know which patient has which appointment.

School example: Student ID (primary key). Class table uses student ID (foreign key) to know which student is in which class.

Home example: Each family member has a unique phone number (primary key). The chore chart uses that number (foreign key) to assign chores.

Nigerian example: Each state has a unique code (primary key). The local government table uses that code (foreign key) to show which state it belongs to.

Table: students (primary key = id)
+----+------+
| id | name |
+----+------+
| 1  | Ada  |
| 2  | Ben  |
+----+------+

Table: scores (foreign key = student_id)
+------+--------+-------+
| id   | student_id | score |
+------+--------+-------+
| 101  | 1      | 90    |
| 102  | 2      | 85    |
+------+--------+-------+

✅ Mini summary: Primary key uniquely identifies a row; foreign key links to another table.

📘 Lesson 8 · AS – giving a nickname to a column

Definition: AS lets us give a temporary name (alias) to a column or table in the result.

Why important: Sometimes column names are long or unclear. AS makes them easier to read.

Simple explanation: If your friend’s name is Oluwafunmilayo, you might call her “Funmi” for short. AS does that for columns.

Real‑life example: SELECT SUM(amount) AS total_sales FROM sales; – now the result column is called “total_sales”.

School example: SELECT COUNT(*) AS number_of_students FROM students;

Home example: SELECT AVG(price) AS average_price FROM groceries;

Nigerian example: SELECT state_name AS state FROM states; (renames column).

SELECT COUNT(*) AS total
FROM fruits;
Result: total = 100

✅ Mini summary: AS renames a column in the result set.

📘 Lesson 9 · DISTINCT – removing duplicates

Definition: DISTINCT shows only unique values in a column.

Why important: We often need to know what different values exist, not how many times they repeat.

Simple explanation: If you have a list of fruits: mango, orange, mango, apple – DISTINCT gives you mango, orange, apple.

Real‑life example: A shop wants to know all the different products they sold, without repeating.

School example: SELECT DISTINCT class FROM students; (shows all classes, no duplicates).

Home example: SELECT DISTINCT chore FROM chores; (shows different chores).

Nigerian example: SELECT DISTINCT state FROM voters; (shows states that have voters).

+---------+
| product |
+---------+
| mango   |
| orange  |
| mango   |
+---------+
DISTINCT gives:
+---------+
| mango   |
| orange  |
+---------+

✅ Mini summary: DISTINCT shows only unique rows in a column.

📘 Lesson 10 · LIMIT – showing only a few rows

Definition: LIMIT restricts the number of rows returned.

Why important: Sometimes we only want to see the first few results, especially when testing.

Simple explanation: Like when you take only the top 5 toys from a big box. LIMIT does that.

Real‑life example: A website shows only 10 products per page.

School example: SELECT * FROM students LIMIT 5; (shows first 5 students).

Home example: SELECT * FROM movies ORDER BY rating DESC LIMIT 3; (top 3 movies).

Nigerian example: SELECT * FROM states ORDER BY population DESC LIMIT 5; (5 most populous states).

SELECT name FROM fruits LIMIT 2;
Returns only first 2 rows.

✅ Mini summary: LIMIT restricts how many rows we see.

📘 Lesson 11 · Combining conditions with AND and OR

Definition: AND and OR allow us to combine multiple conditions in WHERE.

Why important: We often want data that meets several criteria.

Simple explanation: AND means “both must be true”. OR means “at least one must be true”.

Real‑life example: “Show me students who are in JSS2 AND are older than 12.”

School example: SELECT * FROM students WHERE class='JSS2' AND age>12;

Home example: SELECT * FROM snacks WHERE (type='chocolate' OR type='candy') AND price<100;

Nigerian example: SELECT * FROM states WHERE region='South West' AND population>5e6;

+------+-------+------+
| name | class | age  |
+------+-------+------+
| Ada  | JSS2  | 13   |
| Ben  | JSS1  | 12   |
+------+-------+------+
WHERE class='JSS2' AND age>12  → Ada

✅ Mini summary: AND & OR let us build complex filters.

📘 Lesson 12 · NULL – what is it?

Definition: NULL means “no value” or “unknown”. It is not 0, not an empty string – it’s nothing.

Why important: We need to handle missing data carefully.

Simple explanation: If you don’t know someone’s birthday, you write NULL in the database.

Real‑life example: A patient’s allergy field is NULL if they have no known allergies.

School example: SELECT * FROM students WHERE phone_number IS NULL; (students without phone numbers).

Home example: A shopping list where the quantity is NULL (not decided yet).

Nigerian example: Voter’s email might be NULL if they didn’t provide it.

+------+-------+
| name | email |
+------+-------+
| Ada  | NULL  |
| Ben  | b@x.com|
+------+-------+
IS NULL check: Ada

✅ Mini summary: NULL = missing/unknown value.

📘 Lesson 13 · Data types – what kind of data?

Definition: Data types tell MySQL what kind of value a column stores: numbers, text, dates, etc.

Why important: We need to store data correctly so we can use it properly.

Simple explanation: Like separating toys into boxes: LEGO in one box, books in another.

Common types: INT (whole numbers), VARCHAR (text), DATE (calendar dates), DECIMAL (numbers with decimals).

Real‑life example: Age is INT, name is VARCHAR, birthday is DATE.

School example: A table for books: title (VARCHAR), pages (INT), published (DATE).

Home example: Grocery list: item (VARCHAR), quantity (INT), price (DECIMAL).

Nigerian example: State table: state_name (VARCHAR), population (INT), capital (VARCHAR).

+-----------+------------+----------+
| Column    | Data type  | Example  |
+-----------+------------+----------+
| id        | INT        | 1        |
| name      | VARCHAR(50)| "Chidi"  |
| birthdate | DATE       | 2010-05-12|
+-----------+------------+----------+

✅ Mini summary: Data types define the kind of values a column holds.

📘 Lesson 14 · Aliases for tables – short nicknames

Definition: We can give a table a short name (alias) using AS, especially when joining multiple tables.

Why important: It makes queries shorter and easier to read.

Simple explanation: Instead of writing “students” every time, we can call it “s”.

Real‑life example: In a big query, we use students AS s and classes AS c.

School example: SELECT s.name, c.class_name FROM students AS s JOIN classes AS c ON s.class_id = c.id;

Home example: SELECT f.food, p.price FROM fridge AS f JOIN prices AS p ON f.item = p.item;

Nigerian example: SELECT st.state_name, lga.local_gov FROM states AS st JOIN lgas AS lga ON st.id = lga.state_id;

SELECT s.name, c.name
FROM students AS s
JOIN classes AS c
ON s.class_id = c.id;

✅ Mini summary: Table aliases are short names for tables.

📘 Lesson 15 · Putting it all together – a complete query

Definition: A complete query can have SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

Why important: This is the real work of a data analyst!

Simple explanation: We can combine everything we learned to answer any question.

Real‑life example: “Show me top 5 selling products in Lagos in 2025, sorted by total sales.”

School example: SELECT class, AVG(score) AS avg_score FROM students GROUP BY class ORDER BY avg_score DESC LIMIT 3;

Home example: SELECT category, SUM(amount) AS total FROM expenses WHERE date > '2025-01-01' GROUP BY category ORDER BY total DESC;

Nigerian example: SELECT state, COUNT(*) AS voters FROM voters WHERE age>=18 GROUP BY state ORDER BY voters DESC LIMIT 5;

+-----------+-----------+
| state     | voters    |
+-----------+-----------+
| Lagos     | 5,000,000 |
| Kano      | 4,200,000 |
+-----------+-----------+

✅ Mini summary: Combining SELECT, JOIN, WHERE, GROUP, ORDER, LIMIT gives powerful answers.

📚 Key Vocabulary

SQL: Structured Query Language – the way we talk to databases.
Query: A question we ask the database using SQL.
SELECT: Command to get data from a table.
WHERE: Filters rows based on a condition.
ORDER BY: Sorts the result.
GROUP BY: Groups rows that have the same values.
JOIN: Combines two tables on a common column.
Primary Key: Unique identifier for a row.
Foreign Key: Column that refers to a primary key in another table.
NULL: Means no value / unknown.

🧠 Important Concepts

Filtering: Using WHERE to get only the rows you need.
Sorting: ORDER BY arranges data nicely.
Grouping: GROUP BY + aggregate functions summarises data.
Relating tables: JOIN, primary keys, foreign keys connect data.

🪜 Step‑by‑Step Explanations

How to write a basic SELECT:

Start with SELECT.
List columns or use * for all.
Write FROM and the table name.
Add WHERE if you need to filter.
Add ORDER BY if you want sorting.
End with ; (semicolon).

How to join two tables:

Write SELECT with columns you want from both tables.
Write FROM with the first table.
Write JOIN with the second table.
Write ON and the matching columns (foreign key = primary key).

🌍 Real‑life Examples

A bank uses SQL to find all customers who have more than ₦1,000,000 in their account.
A school uses SQL to get the average score of each class.
A hospital uses SQL to count how many patients visited in each month.

🇳🇬 Nigerian Examples

INEC (Independent National Electoral Commission) uses SQL to count registered voters per state.
MTN Nigeria uses SQL to see which data plans are most popular.
JAMB uses SQL to find the top 10 students in each state.

🎈 Fun Examples for Children

You have a table of your toys. Write SQL to find all toys that are red.
You have a table of your friends’ birthdays. Write SQL to find who has a birthday this month.
You have a table of your scores in games. Write SQL to find your highest score.

🏠 Everyday Examples

When you search for a video on YouTube, the search uses SQL.
When you check your phone contacts, the app uses SQL.
When your mom checks her bank balance online, the bank uses SQL.

👩‍🏫 Teacher Notes

Encourage students to think of SQL as a conversation. Start with simple SELECT statements. Use visual aids (tables on the board). Let students write queries on paper first. Emphasise that JOIN is like matching two sets of data. Use group activities where students act as tables and join together.

👨‍👩‍👧‍👦 Parent Tips

Help your child relate SQL to daily life: ask them to “query” the fridge (“show me all fruits”). Play sorting games at home. Use analogies: WHERE = choosing what you want; ORDER BY = arranging your toys. Be patient and celebrate small successes.

💡 Interesting Facts

SQL was invented in the 1970s, and it is still used today!
Almost every app you use on your phone uses SQL behind the scenes.
There are different “dialects” of SQL, but MySQL is one of the most popular.

🧐 Did You Know?

Did you know that the word “query” comes from the Latin word “quaerere” which means “to ask”?
Did you know that the largest database in the world is the ATLAS experiment at CERN, and it uses SQL?

🔔 Remember This

Always start a query with SELECT.
Use WHERE to filter, ORDER BY to sort, GROUP BY to summarise.
JOIN is used to combine tables – you need a common column.
A primary key is unique, a foreign key links to another table.
NULL means no value, not zero or empty text.

❌ Common Mistakes

Forgetting the semicolon: Always end your query with ;.
Spelling column names wrong: Check the exact spelling.
Using = NULL instead of IS NULL: For NULL, always use IS NULL or IS NOT NULL.
Joining without ON: Always specify the common column with ON.

✅ Best Practices

Write SQL keywords in uppercase (SELECT, FROM) to make it readable.
Use table aliases to shorten long table names.
Test your query with LIMIT first to see if it returns what you expect.
Always use meaningful names for columns and tables.

📊 ASCII Diagrams & Illustrations

Query flow:

+--------+      +---------+      +----------+      +---------+
| SELECT | ---> | FROM    | ---> | WHERE    | ---> | ORDER BY|
+--------+      +---------+      +----------+      +---------+
                                                         |
                                                         V
                                                  +----------+
                                                  | RESULT   |
                                                  +----------+

JOIN illustration:

+----------+        +----------+
| students |        | classes  |
+----------+        +----------+
| id  name |        | id  name |
| 1   Ada |        | 101 JSS1 |
| 2   Ben |        | 102 JSS2 |
+----------+        +----------+
       \\               //
        \\             //
         V           V
      +-------------------+
      | JOIN result       |
      | Ada – JSS1        |
      | Ben – JSS2        |
      +-------------------+

📋 Comparison Table: WHERE vs. HAVING

Feature	WHERE	HAVING
Used for	Filtering rows	Filtering groups
When it works	Before GROUP BY	After GROUP BY
Can use aggregate functions?	No	Yes

(We use HAVING after GROUP BY, e.g., HAVING COUNT(*) > 10)

📝 End‑of‑Module Summary

In this module, we learned how to talk to MySQL using SQL. We started with SELECT to get data. Then we used WHERE to filter, ORDER BY to sort, and GROUP BY with aggregate functions to summarise. We discovered how JOIN connects tables using primary and foreign keys. We also covered DISTINCT, LIMIT, NULL, and data types. With these tools, you can now answer any question from a database! You are becoming a true data analysis expert.

❓ Frequently Asked Questions

What is SQL? – It is a language to talk to databases.
What does SELECT do? – It gets data from a table.
How do I filter data? – Use WHERE.
How do I sort data? – Use ORDER BY.
What is a JOIN? – It combines two tables.
What is a primary key? – A unique ID for each row.
What is a foreign key? – A column that links to another table.
What is NULL? – It means no value.
Can I use AND and OR together? – Yes, with parentheses.
What is GROUP BY used for? – To group rows and use aggregates.

📝 Review Questions

What command do we use to get data from a table?
What does WHERE do?
How do you sort results from highest to lowest?
What is the difference between COUNT(*) and SUM(column)?
What is a JOIN?
What is a primary key?
What is a foreign key?
What does DISTINCT do?
How do you show only the first 10 rows?
What does NULL mean?
What is an alias?
Write a query to get all students from JSS2.
Write a query to count how many states are in Nigeria.
Write a query to get the average age of students.
Write a query to join students and classes.

✏️ Fill‑in‑the‑Blank

SELECT * ________ students;
SELECT name FROM students ________ age > 10;
SELECT COUNT(*) ________ students;
SELECT * FROM students ORDER BY name ________;
SELECT ________ product FROM sales; (to get unique products)

✅ True or False

SQL stands for Structured Query Language. (True)
WHERE is used to sort data. (False, it’s for filtering)
ORDER BY sorts data. (True)
NULL means zero. (False)
A primary key can be duplicated. (False)

🔢 Multiple Choice Questions

Which command gets data from a table?
A) SELECT B) FROM C) WHERE D) JOIN
Which keyword filters rows?
A) ORDER BY B) WHERE C) GROUP BY D) SELECT
Which keyword sorts data?
A) ORDER BY B) WHERE C) GROUP BY D) LIMIT
What does COUNT(*) do?
A) Sums values B) Counts rows C) Averages D) Sorts
What does SUM(column) do?
A) Counts rows B) Adds values C) Averages D) Sorts
Which clause groups rows?
A) ORDER BY B) WHERE C) GROUP BY D) LIMIT
What is a primary key?
A) Unique identifier B) A foreign key C) A sort key D) A filter
What does DISTINCT do?
A) Shows duplicates B) Shows unique values C) Sorts D) Groups
Which keyword limits rows?
A) LIMIT B) GROUP BY C) ORDER BY D) WHERE
What does JOIN do?
A) Combines tables B) Filters rows C) Sorts D) Groups
What does NULL represent?
A) Zero B) Empty string C) No value D) A space
Which is correct for checking NULL?
A) = NULL B) IS NULL C) == NULL D) LIKE NULL
What does AS do?
A) Renames column B) Sorts C) Filters D) Joins
Which of these is an aggregate function?
A) SUM B) WHERE C) ORDER BY D) DISTINCT
What is the correct order of clauses?
A) SELECT, FROM, WHERE, GROUP BY, ORDER BY, LIMIT
B) FROM, SELECT, WHERE, GROUP BY, LIMIT
C) SELECT, WHERE, FROM, GROUP BY, ORDER BY
D) LIMIT, SELECT, FROM, WHERE

Answers: 1-A, 2-B, 3-A, 4-B, 5-B, 6-C, 7-A, 8-B, 9-A, 10-A, 11-C, 12-B, 13-A, 14-A, 15-A

🔗 Matching Exercises

Match the SQL keyword to its job:

Keyword	Job
SELECT	Get data
WHERE	Filter rows
ORDER BY	Sort
GROUP BY	Group rows
JOIN	Combine tables

📝 Short Answer Questions

Write a SELECT statement to get all columns from a table called “pets”.
Write a query to get names of students older than 10.
What is the difference between WHERE and HAVING?
Explain what a foreign key is in your own words.
Why do we use DISTINCT?

🎭 Scenario‑based Exercises

You have a table “sales” with columns: product, amount, city. Write a query to find total sales per city.
You have a table “students” and “classes”. Write a query to list each student with their class name.
You have a table “employees” with columns: name, salary, department. Write a query to find the average salary per department.

👥 Group Activity

In groups of 4, each member writes a query. One person writes a SELECT, another a WHERE, another an ORDER BY, another a GROUP BY. Combine them into one query. Present to the class.

🧑 Individual Activity

Create a simple table (on paper) with 5 friends and their favourite snacks. Write 5 SQL queries: SELECT *, WHERE, ORDER BY, GROUP BY, and a JOIN with another table you create.

💬 Classroom Discussion Questions

Why is it important to filter data?
When would you use GROUP BY in real life?
How do primary keys help us keep data clean?

🛠️ Mini Project

Build a small database for a library. Create tables: books, members, loans. Write queries to: list all books, find books by a certain author, count how many books are loaned, and join books with loans to see who borrowed what.

💻 Practical Assignment

Using MySQL (or any SQL environment), create a table “students” with id, name, age, class. Insert 10 rows. Write 5 different SELECT queries with WHERE, ORDER BY, GROUP BY, JOIN (with a classes table), and LIMIT. Submit your queries and results.

⚡ Challenge Exercise

Write a single query that: selects student name, class name, and average score, only for students older than 12, grouped by class, sorted by average score descending, and shows only the top 3 classes. (Hint: you will need JOIN, WHERE, GROUP BY, ORDER BY, LIMIT, and AVG).

📌 Quiz Answers

(Multiple choice answers are given above. For fill‑in‑the‑blank: 1. FROM, 2. WHERE, 3. FROM, 4. ASC, 5. DISTINCT. True/False: 1T, 2F, 3T, 4F, 5F.)

🔑 Key Takeaways

SQL is the language to query databases.
SELECT, WHERE, ORDER BY, GROUP BY, JOIN are your main tools.
Primary keys and foreign keys help link tables.
Practice makes perfect – write lots of queries!

🚀 Preparation for the Next Module

In Module 6, we will learn about advanced SQL, including subqueries, views, and indexes. We will also learn how to make our queries run faster. Review JOIN and GROUP BY – they will be very important!

🎉 Congratulations! You have finished Module 5. You are now a MySQL data analysis expert! Keep asking questions and let the data answer you.

Module Six

Module 6 · MySQL for Data Analysis Expert

🚀 Module Six · MySQL for Data Analysis Expert

⚡ Supercharging Your Queries – Subqueries, Views, and Indexes

🌟 Module Introduction

Welcome back, young data wizard! In Module 5, we learned how to ask questions using SQL. Now we are going to make our questions even more powerful!

We will learn three super tools: subqueries (questions inside questions), views (saved questions that act like tables), and indexes (magic shortcuts to find data faster).

With these tools, you will be able to solve very hard problems easily and make your database run super fast! Let’s dive in.

🎯 Learning Objectives

After this module, you will be able to:

Write a subquery – a query inside another query.
Use views to save a query as a virtual table.
Create indexes to speed up searches.
Understand when to use each tool.
Combine subqueries with WHERE, FROM, and SELECT.

📖 Warm‑up Story · The Supermarket Mystery

In a big supermarket in Abuja, the manager wants to know: “Which product is the most expensive?”

She could find the highest price, then find the product with that price. That is two questions! But with a subquery, she can ask both questions at once.

She also wants to save her favourite report as a view so she can see it anytime. And she adds indexes to find products quickly – just like a book index helps you find a word.

This module will teach you these superpowers!

📘 Lesson 1 · What is a Subquery?

Definition: A subquery is a SQL query inside another SQL query. It is like asking a question inside a question.

Why important: Sometimes we need the result of one query to answer another. Subqueries help us do that in one go.

Simple explanation: Imagine you ask your friend: “Who has the most toys?” First you need to know who has the most, then you find that person. Subquery does both steps together.

Real‑life example: A teacher wants to find the student who scored the highest. She can use a subquery to find the max score, then get that student’s name.

School example: SELECT name FROM students WHERE score = (SELECT MAX(score) FROM students);

Home example: You want to know which snack is the most expensive. Subquery finds the highest price, then the snack with that price.

Nigerian example: Find the state with the highest population. Subquery: SELECT state FROM states WHERE population = (SELECT MAX(population) FROM states);

+-------+---------+
| name  | score   |
+-------+---------+
| Ada   | 90      |
| Ben   | 95      |
| Chi   | 88      |
+-------+---------+

Subquery: SELECT MAX(score) FROM students → 95
Outer query: SELECT name FROM students WHERE score = 95 → Ben

✅ Mini summary: A subquery is a query inside another query. It gives a value used by the outer query.

📘 Lesson 2 · Subqueries with WHERE

Definition: We can use a subquery on the right side of a WHERE condition.

Why important: It lets us compare a column to a result from another query.

Simple explanation: You say: “Show me products that cost more than the average price.” The subquery computes the average.

Real‑life example: “Which employees earn more than the average salary?”

School example: SELECT name FROM students WHERE age > (SELECT AVG(age) FROM students);

Home example: “Which toys are more expensive than the average toy price?”

Nigerian example: “Which states have more voters than the average number of voters per state?”

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

✅ Mini summary: Subqueries in WHERE compare a column to a single value from the subquery.

📘 Lesson 3 · Subqueries with IN

Definition: The IN operator lets you check if a value exists in a list. We can use a subquery to generate that list.

Why important: Sometimes we need to match against many values, not just one.

Simple explanation: You say: “Show me students who are in the same class as the top 3 students.” The subquery finds the top 3 classes.

Real‑life example: “Which products were sold in the top 5 cities?”

School example: SELECT name FROM students WHERE class_id IN (SELECT id FROM classes WHERE teacher = 'Mrs. A');

Home example: “Which snacks are in the most popular categories?”

Nigerian example: “Which states are in the geopolitical zones with more than 10 million people?”

SELECT product FROM sales
WHERE city IN (SELECT city FROM top_cities WHERE population > 5e6);

✅ Mini summary: IN with a subquery checks if a value matches any value in the subquery result.

📘 Lesson 4 · Subqueries in FROM – derived tables

Definition: A subquery in the FROM clause is called a derived table. It acts like a temporary table.

Why important: We can treat the result of a subquery as a table and join it or select from it.

Simple explanation: Imagine you first make a list of all students older than 10, then you ask questions about that list. The subquery in FROM creates that list.

Real‑life example: You want to find the average score of the top 5 students. First, you get the top 5, then you average their scores.

School example: SELECT AVG(score) FROM (SELECT score FROM students ORDER BY score DESC LIMIT 5) AS top_scores;

Home example: “Average price of the 3 most expensive toys.”

Nigerian example: “Average population of the 10 largest states.”

SELECT AVG(population)
FROM (SELECT population FROM states ORDER BY population DESC LIMIT 10) AS large_states;

✅ Mini summary: A subquery in FROM creates a temporary table we can query.

📘 Lesson 5 · Subqueries in SELECT – scalar subqueries

Definition: A subquery in the SELECT clause returns a single value (one row, one column).

Why important: It lets us compute an extra value for each row.

Simple explanation: For each student, show their score and also the average score of all students. The subquery computes the average.

Real‑life example: For each product, show its price and the average price of all products.

School example: SELECT name, score, (SELECT AVG(score) FROM students) AS avg_score FROM students;

Home example: For each chore, show its duration and the average duration.

Nigerian example: For each state, show its population and the average population across all states.

+------+-------+------------+
| name | score | avg_score  |
+------+-------+------------+
| Ada  | 90    | 85         |
| Ben  | 80    | 85         |
+------+-------+------------+

✅ Mini summary: A scalar subquery in SELECT adds a computed value to each row.

📘 Lesson 6 · Correlated subqueries (advanced but fun)

Definition: A correlated subquery is a subquery that uses values from the outer query. It runs once for each row of the outer query.

Why important: It allows very powerful comparisons, like “show each student’s score compared to the average of their class”.

Simple explanation: It’s like for each student, you look at their class, then you compute the average score of that class, and compare.

Real‑life example: “Which employees earn more than the average in their department?”

School example: SELECT name, class, score FROM students AS s1 WHERE score > (SELECT AVG(score) FROM students AS s2 WHERE s2.class = s1.class);

Home example: “Which snacks are more expensive than the average snack in their category?”

Nigerian example: “Which states have a population greater than the average population of their geopolitical zone?”

+------+-------+-------+
| name | class | score |
+------+-------+-------+
| Ada  | JSS1  | 90    |
| Ben  | JSS2  | 85    |
| Chi  | JSS1  | 70    |
+------+-------+-------+

Correlated subquery compares each student to the average of their class.
Ada: 90 > avg(JSS1=80) → yes
Ben: 85 > avg(JSS2=85) → no
Chi: 70 > avg(JSS1=80) → no

✅ Mini summary: Correlated subqueries use outer query values and run row by row.

📘 Lesson 7 · What is a View?

Definition: A view is a saved query. It looks like a table but it doesn’t store data – it just shows the result of a query when you use it.

Why important: Views let us save complicated queries and reuse them easily, like a shortcut.

Simple explanation: Imagine you write a long list of instructions to find your favourite toys. You save it as “my_favourite_toys”. Every time you say that name, the computer follows the instructions.

Real‑life example: A company creates a view called “active_customers” that shows customers who bought something in the last month.

School example: CREATE VIEW top_students AS SELECT name, score FROM students WHERE score > 80;

Home example: CREATE VIEW weekend_chores AS SELECT * FROM chores WHERE day IN ('Saturday','Sunday');

Nigerian example: CREATE VIEW large_states AS SELECT * FROM states WHERE population > 5e6;

CREATE VIEW my_view AS
SELECT name, score FROM students WHERE score > 90;

-- Now you can query the view like a table:
SELECT * FROM my_view;

✅ Mini summary: A view is a saved query that acts like a table.

📘 Lesson 8 · Why use Views?

Definition: Views are useful for security (hide columns), simplicity (hide complex joins), and reusability (use again and again).

Why important: They make life easier and safer.

Simple explanation: Instead of writing a long JOIN every time, you create a view once and then just SELECT from the view.

Real‑life example: A manager can have a view of employee salaries without seeing their personal data.

School example: CREATE VIEW class_summary AS SELECT class, AVG(score) AS avg_score FROM students GROUP BY class;

Home example: CREATE VIEW food_in_fridge AS SELECT name, expiry FROM fridge WHERE expiry > NOW();

Nigerian example: CREATE VIEW state_voters AS SELECT state, COUNT(*) AS total FROM voters GROUP BY state;

+-------------------+
| View: class_summary|
+-------------------+
| class | avg_score |
| JSS1  | 85        |
| JSS2  | 80        |
+-------------------+

✅ Mini summary: Views simplify, secure, and reuse queries.

📘 Lesson 9 · Updating Views – can we change them?

Definition: Some views are updatable – you can insert, update, or delete through them. But not all views are updatable (e.g., those with GROUP BY, joins, or aggregates).

Why important: It helps to know when you can change data through a view.

Simple explanation: If a view is simple (one table, no calculations), you can change the data. If it’s complex (joins, averages), you cannot.

Real‑life example: A view of customer names and emails can be updated, but a view of total sales per product cannot.

School example: CREATE VIEW student_names AS SELECT id, name FROM students; – this view is updatable.

Home example: CREATE VIEW snacks AS SELECT name, price FROM grocery WHERE category='snacks'; – updatable.

Nigerian example: CREATE VIEW state_capitals AS SELECT state, capital FROM states; – updatable.

+----------------------------------+
| Simple view (updatable)          |
| CREATE VIEW v AS SELECT id,name  |
| FROM students;                   |
| UPDATE v SET name='Tolu' WHERE id=1; |
+----------------------------------+
| Complex view (not updatable)     |
| CREATE VIEW v AS SELECT class,   |
| AVG(score) FROM students GROUP BY class; |
| UPDATE v ... will fail.          |
+----------------------------------+

✅ Mini summary: Some views can be updated, some cannot, depending on complexity.

📘 Lesson 10 · What is an Index?

Definition: An index is a special data structure that helps MySQL find rows faster, like a book index that helps you find a word quickly.

Why important: Indexes make SELECT queries very fast, especially on large tables.

Simple explanation: Without an index, MySQL has to read every row (like reading a whole book page by page). With an index, it jumps straight to the right place.

Real‑life example: In a library, books are arranged by subject – that’s an index. You find books on “animals” quickly.

School example: CREATE INDEX idx_name ON students (name); – now searching by name is super fast.

Home example: CREATE INDEX idx_food ON fridge (name); – fast search for any food.

Nigerian example: CREATE INDEX idx_state ON voters (state); – fast count of voters per state.

+------------------+------------------+
| Without Index    | With Index       |
| Scan all rows    | Jump to the row  |
| (slow)           | (fast)           |
+------------------+------------------+

✅ Mini summary: An index is a speed-up tool for searching columns.

📘 Lesson 11 · Creating an Index

Definition: Use CREATE INDEX index_name ON table_name (column_name);

Why important: We need to tell MySQL which columns to speed up.

Simple explanation: We say “MySQL, please make a quick-finder for this column”.

Real‑life example: On a customer table, create an index on the email column because we search by email often.

School example: CREATE INDEX idx_age ON students (age);

Home example: CREATE INDEX idx_expiry ON fridge (expiry_date);

Nigerian example: CREATE INDEX idx_lga ON local_govs (state_id);

CREATE INDEX idx_name ON students (name);
-- Now queries with WHERE name = 'Chidi' run faster.

✅ Mini summary: CREATE INDEX builds a speed-up structure on a column.

📘 Lesson 12 · When to use Indexes – and when not to

Definition: Indexes are great for columns used in WHERE, JOIN, ORDER BY, and GROUP BY. But they take extra space and slow down inserts/updates.

Why important: We must use indexes wisely.

Simple explanation: You wouldn’t index every page in a book – only the important words.

Real‑life example: Index the product ID but not the product description (which is long and rarely searched).

School example: Index student ID, class ID, but not the student’s bio.

Home example: Index the snack name, but not the snack description.

Nigerian example: Index state ID, voter ID, but not the voter’s full address if not used in searches.

+------------------------------------------+
| Good to index: columns used in WHERE,     |
| JOIN, ORDER BY, GROUP BY.                 |
| Avoid indexing: columns that are rarely   |
| used, or have many NULLs, or are very long|
| text columns.                             |
+------------------------------------------+

✅ Mini summary: Index columns that speed up searches, but don’t over-index.

📘 Lesson 13 · Dropping an Index

Definition: Use DROP INDEX index_name ON table_name; to remove an index.

Why important: If an index is not useful, we can remove it to save space and speed up inserts/updates.

Simple explanation: If you no longer need a quick-finder, throw it away.

Real‑life example: If you stop searching by a column, remove its index.

School example: DROP INDEX idx_age ON students;

Home example: DROP INDEX idx_expiry ON fridge;

Nigerian example: DROP INDEX idx_lga ON local_govs;

DROP INDEX idx_name ON students;
-- Now the index is gone, and searches on name may be slower.

✅ Mini summary: DROP INDEX removes an index.

📘 Lesson 14 · Combining Subqueries, Views, and Indexes

Definition: We can use all three together to build fast and powerful queries.

Why important: In real life, we combine tools for the best results.

Simple explanation: Use subqueries for complex questions, views to save them, and indexes for speed.

Real‑life example: Create a view of top customers using a subquery, and index the customer ID.

School example: Create a view of top students, with an index on score.

Home example: Create a view of expired food, with an index on expiry.

Nigerian example: Create a view of high-population states, with an index on population.

-- 1. Index the column
CREATE INDEX idx_score ON students (score);
-- 2. Create a view with a subquery
CREATE VIEW top_students AS
SELECT name, score FROM students
WHERE score > (SELECT AVG(score) FROM students);
-- 3. Query the view quickly!
SELECT * FROM top_students;

✅ Mini summary: Combining subqueries, views, and indexes makes you a master.

📘 Lesson 15 · Putting it all together – a real-world example

Definition: We will write a complete query that uses subquery, view, and index.

Why important: This is how experts work!

Simple explanation: We solve a big problem step by step.

Real‑life example: Find the top 5 products in each category.

School example: Find the top 3 students in each class.

Home example: Find the most expensive snack in each category.

Nigerian example: Find the most populous state in each geopolitical zone.

-- Step 1: Index the category and sales columns
CREATE INDEX idx_category ON products (category);
CREATE INDEX idx_sales ON sales (amount);
-- Step 2: Create a view of total sales per product
CREATE VIEW product_sales AS
SELECT product_id, SUM(amount) AS total
FROM sales
GROUP BY product_id;
-- Step 3: Use a subquery to rank products by category
SELECT category, product_id, total
FROM products p
JOIN product_sales ps ON p.id = ps.product_id
WHERE ps.total = (SELECT MAX(total) FROM product_sales ps2 WHERE ps2.product_id = p.id);

✅ Mini summary: In practice, we use all tools together to solve real problems.

📚 Key Vocabulary

Subquery: A query inside another query.
Derived table: A subquery in the FROM clause that acts like a temporary table.
Scalar subquery: A subquery that returns a single value.
Correlated subquery: A subquery that uses values from the outer query.
View: A saved query that acts like a table.
Index: A structure that speeds up searches on a column.

🧠 Important Concepts

Subqueries can be used in WHERE, FROM, and SELECT.
Views are saved queries – they are like virtual tables.
Indexes make searches faster but take extra space.
Always consider if a subquery, view, or index is the right tool.

🪜 Step‑by‑Step Explanations

How to write a subquery in WHERE:

Write the outer query with WHERE.
In the condition, put a subquery in parentheses.
The subquery must return one value (for =, >, etc.) or a list (for IN).

How to create a view:

Write CREATE VIEW view_name AS.
Write the SELECT query you want to save.
Now you can SELECT from the view like a table.

How to create an index:

Write CREATE INDEX index_name ON table_name (column_name);
Use it – MySQL will automatically use the index for searches.

🌍 Real‑life Examples

A bank uses a subquery to find customers with above-average balances.
An e‑commerce site uses views to show “today’s orders” without writing the query each time.
A social media app uses indexes to find users by username quickly.

🇳🇬 Nigerian Examples

INEC uses subqueries to find states with above-average voter turnout.
Nigerian banks use views to show daily transaction summaries.
JAMB uses indexes to speed up searches for candidates by registration number.

🎈 Fun Examples for Children

Use a subquery to find which of your toys is the most expensive.
Create a view of your favourite games so you can see them anytime.
Index the “name” column in your friend list to find friends quickly.

🏠 Everyday Examples

When you search for a contact on your phone, it uses an index on the name.
When you check your online shopping order history, it uses a view.
When you ask “which student scored above average?”, it uses a subquery.

👩‍🏫 Teacher Notes

Begin with simple subqueries. Use plenty of visual aids – draw tables and show the subquery results step by step. Relate views to saving a document. Explain indexes with the book index analogy. Encourage students to write queries and see the results.

👨‍👩‍👧‍👦 Parent Tips

Help your child create simple views at home, like a view of the weekly menu. Explain indexes using a phone contact list. Encourage them to think of subqueries as “questions within questions”. Be patient and practise together.

💡 Interesting Facts

Indexes can make queries hundreds of times faster!
Views are sometimes called “virtual tables”.
The first version of SQL did not have subqueries – they were added later.

🧐 Did You Know?

Did you know that MySQL automatically creates an index on the primary key?
Did you know that you can create indexes on multiple columns (composite index)?

🔔 Remember This

Subqueries are queries inside queries.
Views save a query for later use.
Indexes speed up searches.
Use subqueries for complex conditions, views for reuse, indexes for speed.

❌ Common Mistakes

Subquery returns more than one value: If you use =, the subquery must return one value. Use IN if it returns many.
Forgetting AS for derived tables: In FROM, always give a subquery an alias: ... FROM (SELECT ...) AS t.
Over-indexing: Too many indexes slow down inserts/updates.
Creating views on top of views: Can get confusing and slow.

✅ Best Practices

Use meaningful names for views and indexes.
Test subqueries separately before putting them inside another query.
Index columns that appear in WHERE, JOIN, ORDER BY, and GROUP BY.
Document your views – explain what they do.

📊 ASCII Diagrams & Illustrations

Subquery flow:

+-----------------+       +------------------+
| Outer Query     |       | Subquery         |
| SELECT name     |       | SELECT MAX(score)|
| FROM students   |  +--> | FROM students    |
| WHERE score =   |  |    +------------------+
|    (subquery)   |  |            |
+-----------------+  |            V
                     |    +------------------+
                     |    | Returns 95       |
                     |    +------------------+
                     +----+ (used by outer query)

View as a saved query:

+------------------+
| CREATE VIEW      |
| my_view AS       |
| SELECT ...       |
+------------------+
        |
        V
+------------------+
| my_view acts     |
| like a table     |
| SELECT * FROM    |
| my_view;         |
+------------------+

Index speed comparison:

+----------------------------------+
| Without Index                    |
| +-------+                        |
| | table | (scan all rows)        |
| +-------+                        |
|         |                         |
|         V                         |
| Slow ☹️                          |
+----------------------------------+
| With Index                       |
| +-------+   +----------+        |
| | table |-->| Index    | (jump) |
| +-------+   +----------+        |
|                     |            |
|                     V            |
| Fast 😊                          |
+----------------------------------+

📋 Comparison Table: Subquery vs. View vs. Index

Feature	Subquery	View	Index
Purpose	Compute a value or list for a condition	Save a query for reuse	Speed up searches
Stores data?	No (result is temporary)	No (just the query definition)	Yes (structure is stored)
Can be used in	WHERE, FROM, SELECT	SELECT, JOIN	Automatically used by MySQL

📝 End‑of‑Module Summary

We have covered three powerful tools: subqueries, views, and indexes. Subqueries let us ask questions inside questions – we used them in WHERE, FROM, and SELECT. Views let us save queries as virtual tables, which makes our work easier and more secure. Indexes are speed boosters – they help MySQL find data quickly, like a book index.

We also learned about correlated subqueries, updatable views, and when to use indexes. With these tools, you can handle even the most complex data analysis tasks. Keep practising, and remember – every expert started as a beginner!

❓ Frequently Asked Questions

What is a subquery? – A query inside another query.
Where can I use a subquery? – In WHERE, FROM, SELECT, and HAVING.
What is a view? – A saved query that acts like a table.
Can I update data through a view? – Only if the view is simple and updatable.
What is an index? – A structure that speeds up searches.
How do I create an index? – Use CREATE INDEX idx_name ON table (column);
When should I use a subquery instead of a JOIN? – When you need a single value or a list for a condition.
Can a view contain a subquery? – Yes, views can have subqueries.
Does an index always make things faster? – For SELECT yes, but it slows down INSERT/UPDATE/DELETE.
Can I have multiple indexes on a table? – Yes, you can have many.

📝 Review Questions

What is a subquery?
Name two places where you can put a subquery.
What does IN do with a subquery?
What is a derived table?
What is a scalar subquery?
What is a correlated subquery?
What is a view?
Why are views useful?
What is an index?
Why do we use indexes?
When should you NOT use an index?
How do you drop a view?
How do you create an index?
Can a view have a subquery?
Give one real‑life use of a subquery.

✏️ Fill‑in‑the‑Blank

SELECT name FROM students WHERE score = (SELECT ________(score) FROM students);
CREATE ________ my_view AS SELECT * FROM students;
CREATE ________ idx_name ON students (name);
SELECT * FROM (SELECT * FROM students) ________ temp;
DROP ________ idx_age ON students;

✅ True or False

A subquery can return multiple rows. (True)
A view stores data permanently. (False)
Indexes speed up SELECT queries. (True)
You can always update a view. (False)
A correlated subquery runs once for each row of the outer query. (True)

🔢 Multiple Choice Questions

Which clause can contain a subquery?
A) WHERE B) FROM C) SELECT D) All of the above
What does IN with a subquery do?
A) Compares to a list B) Checks for NULL C) Sorts D) Joins
A subquery in FROM is also called a:
A) Correlated subquery B) Derived table C) Scalar subquery D) View
Which tool saves a query for reuse?
A) Index B) Subquery C) View D) Join
Which tool speeds up searches?
A) Index B) View C) Subquery D) Join
Can a view contain a subquery?
A) Yes B) No
Which of these is a scalar subquery?
A) Returns one value B) Returns a list C) Returns a table D) Returns nothing
Which subquery uses outer query values?
A) Correlated B) Non-correlated C) Scalar D) Derived
What happens if a subquery returns multiple rows and you use =?
A) Error B) Works fine C) Returns NULL D) Returns the first row
Which command creates an index?
A) CREATE VIEW B) CREATE INDEX C) CREATE TABLE D) SELECT
Which command drops an index?
A) DROP INDEX B) DELETE INDEX C) REMOVE INDEX D) DROP VIEW
Which is NOT a reason to use a view?
A) Security B) Simplicity C) Speed up searches D) Reusability
Which is NOT true about indexes?
A) Speed up SELECT B) Slow down INSERT C) Always needed D) Take extra space
What does CREATE VIEW v AS SELECT ... do?
A) Creates a table B) Saves a query C) Creates an index D) Deletes data
Which of these can be used in a subquery?
A) ORDER BY B) GROUP BY C) HAVING D) All of the above

Answers: 1-D, 2-A, 3-B, 4-C, 5-A, 6-A, 7-A, 8-A, 9-A, 10-B, 11-A, 12-C, 13-C, 14-B, 15-D

🔗 Matching Exercises

Match the concept to its description:

Concept	Description
Subquery	A query inside a query
View	A saved query that acts like a table
Index	Speeds up searches
Derived table	Subquery in FROM
Correlated subquery	Uses outer query values

📝 Short Answer Questions

Write a subquery to find the student with the highest score.
Create a view that shows all students with scores above 80.
Create an index on the “class” column of the “students” table.
Explain the difference between a view and a table.
When would you use a correlated subquery?

🎭 Scenario‑based Exercises

You have a table “sales” (product, amount, city). Write a query to find products that sold more than the average amount in their city. (Use a correlated subquery).
You have a table “students” and “classes”. Create a view that shows each student with their class name.
You have a table “books” with columns: id, title, author, year. Create an index on the author column to speed up searches by author.

👥 Group Activity

In groups of 4, each member writes a different type of subquery (WHERE, FROM, SELECT, correlated). Then combine them into one complex query. Share with the class.

🧑 Individual Activity

Create a table of your favourite books (id, title, author, rating). Write a query to find books with rating above average. Create a view of books with rating > 4. Create an index on the title column.

💬 Classroom Discussion Questions

Why might a view be better than writing the same query many times?
Can you think of a situation where an index might not help?
How does a correlated subquery differ from a normal subquery?

🛠️ Mini Project

Build a small database for a music store. Tables: artists, albums, songs. Create views: top_selling_albums, top_artists. Write subqueries to find songs longer than average, and albums with more tracks than average. Create indexes on artist name and album title.

💻 Practical Assignment

Using MySQL (or any SQL environment), create a table “employees” (id, name, department, salary). Insert 10 rows. Write a subquery to find employees who earn more than the average in their department (correlated subquery). Create a view of employees in the “Sales” department. Create an index on the department column.

⚡ Challenge Exercise

Write a single query that: lists each department, the number of employees, and the highest salary in that department, but only for departments where the highest salary is above the overall average salary. Use a subquery, a view (if needed), and an index to speed it up. (Hint: you will need subqueries in SELECT and WHERE, and probably a derived table).

📌 Quiz Answers

Multiple choice answers are given above. Fill-in-the-blank: 1. MAX, 2. VIEW, 3. INDEX, 4. AS, 5. INDEX. True/False: 1T, 2F, 3T, 4F, 5T.

🔑 Key Takeaways

Subqueries let you nest queries for powerful conditions.
Views save queries as virtual tables for reuse.
Indexes speed up data retrieval.
Use each tool wisely for the best performance and simplicity.

🚀 Preparation for the Next Module

In Module 7, we will learn about transactions and locking. We will see how to make sure data stays safe when multiple people use the database at the same time. We will also learn about backups and restoring databases. Get ready to become a database guardian!

🎉 Congratulations! You have finished Module 6. You are now a master of subqueries, views, and indexes. Keep exploring and experimenting with your new superpowers!

Module Seven

Module 7 · MySQL for Data Analysis Expert

🛡️ Module Seven · MySQL for Data Analysis Expert

🔒 Protecting Data – Transactions, Locking, Backups & More

🌟 Module Introduction

Hello, data guardian! In the last modules, you learned how to ask questions and make your queries super fast. But what happens when many people use the database at the same time? Or if something goes wrong?

This module is about safety and reliability. We will learn about transactions (groups of changes that happen together), locking (to prevent chaos), and backups (to save data if something breaks).

By the end, you will be a database hero who keeps data safe and sound!

🎯 Learning Objectives

After this module, you will be able to:

Understand what a transaction is and why we use it.
Use COMMIT and ROLLBACK to save or undo changes.
Explain locking and why it prevents problems.
Know the ACID properties (Atomicity, Consistency, Isolation, Durability).
Learn how to backup and restore a database.

📖 Warm‑up Story · The Bank Transfer Trouble

In a small village in Nigeria, there is a bank. One day, a man named Mr. Ade wants to send ₦10,000 to his friend. The bank’s computer does two things:

It takes ₦10,000 from Mr. Ade’s account.
It adds ₦10,000 to his friend’s account.

But what if the power goes out after step 1 but before step 2? Mr. Ade loses money, and his friend gets nothing! That is a disaster.

The bank uses transactions to solve this. A transaction makes sure that both steps happen together. If one fails, the whole thing is cancelled – like it never happened. This keeps data safe and correct!

📘 Lesson 1 · What is a Transaction?

Definition: A transaction is a group of one or more SQL statements that are treated as one single unit. Either all of them happen, or none of them happen.

Why important: Transactions keep data consistent and prevent partial changes.

Simple explanation: Imagine you are building a LEGO house. A transaction is like saying: “I will put all the pieces together, and if I drop one piece, I will start over from scratch.”

Real‑life example: In an online shop, when you buy something, the system reduces the stock and charges your card – both must happen, or neither.

School example: A teacher updates the grade of a student. She changes the grade in two tables: one for the subject and one for the overall average. Both must update together.

Home example: You move toys from one box to another. You take the toy out of the first box and put it into the second. If you forget to put it in the second, that’s a problem. A transaction does both.

Nigerian example: When you buy airtime from a vendor, the system deducts your money and adds the airtime. Both steps are in a transaction.

+----------------------------------------+
| Transaction (all or nothing)            |
| 1. Deduct money from Mr. Ade            |
| 2. Add money to friend                  |
| If either fails, both are undone!       |
+----------------------------------------+

✅ Mini summary: A transaction groups SQL statements so they all succeed or all fail together.

📘 Lesson 2 · COMMIT – Saving the Transaction

Definition: COMMIT is the command that makes all changes in a transaction permanent.

Why important: Without COMMIT, changes are not saved permanently.

Simple explanation: After you finish building your LEGO house, you say “I’m done!” – that is COMMIT.

Real‑life example: After you finish a transaction at the bank, you press “Confirm”. That is COMMIT.

School example: After you enter all grades, you click “Save”. That is COMMIT.

Home example: After you put all the toys in the box, you close the box. That is COMMIT.

Nigerian example: After you transfer money with USSD, you get a confirmation message – that is COMMIT.

START TRANSACTION;
  UPDATE accounts SET balance = balance - 10000 WHERE name = 'Ade';
  UPDATE accounts SET balance = balance + 10000 WHERE name = 'Friend';
COMMIT;  -- Now the changes are permanent!

✅ Mini summary: COMMIT saves all changes in the transaction permanently.

📘 Lesson 3 · ROLLBACK – Undoing the Transaction

Definition: ROLLBACK undoes all changes made in the current transaction.

Why important: If something goes wrong, ROLLBACK lets us cancel everything and go back to the start.

Simple explanation: If you make a mistake in your LEGO house, you can take it apart and start over – that is ROLLBACK.

Real‑life example: If you realise you transferred money to the wrong person, you can cancel the transaction (if it hasn’t been committed).

School example: If you enter the wrong grades, you can undo before saving.

Home example: If you put a toy in the wrong box, you can take it back.

Nigerian example: If you make a mistake during a bank transfer, you can cancel before confirming.

START TRANSACTION;
  UPDATE accounts SET balance = balance - 10000 WHERE name = 'Ade';
  -- Oops! We deducted from the wrong person!
ROLLBACK;  -- Undo the change, balance is back to normal.

✅ Mini summary: ROLLBACK cancels all changes in the transaction.

📘 Lesson 4 · ACID Properties – The Four Super Rules

Definition: ACID stands for Atomicity, Consistency, Isolation, Durability. These are rules that guarantee transactions are reliable.

Why important: ACID makes sure databases are safe and trustworthy.

Simple explanation: Think of ACID as the four pillars that hold up a safe database.

Atomicity: All or nothing. (Like a transaction.)
Consistency: Data must always be correct. (No broken rules.)
Isolation: Transactions do not interfere with each other.
Durability: Once COMMIT, the change stays even if power goes out.

Real‑life example: A bank follows ACID – your money is safe and transactions are reliable.

School example: The school database uses ACID to keep student records accurate.

Home example: Your game saves follow ACID – if you save, the progress stays.

Nigerian example: The Nigerian banking system uses ACID to protect your money.

+------------------+
|  A  Atomicity    |
|  C  Consistency  |
|  I  Isolation    |
|  D  Durability   |
+------------------+

✅ Mini summary: ACID are the rules that make transactions safe and reliable.

📘 Lesson 5 · What is Locking?

Definition: Locking is a mechanism that prevents two transactions from changing the same data at the same time.

Why important: Without locking, data can become messy and incorrect.

Simple explanation: Imagine two people want to sit on the same chair at the same time. Locking makes sure only one person can use the chair at a time.

Real‑life example: In a bank, if two tellers try to update the same account simultaneously, locking prevents errors.

School example: Two teachers trying to update the same student’s grade – locking ensures one goes first.

Home example: Two siblings trying to update the same game save – locking avoids conflicts.

Nigerian example: In a busy bank branch, locking ensures transactions happen one after another to avoid mistakes.

+----------+          +----------+
| User 1   |          | User 2   |
| wants to |          | wants to |
| update   |          | update   |
| row 5    |          | row 5    |
+----------+          +----------+
        \                 /
         \               /
          V             V
      +-------------------------+
      | LOCK: row 5 is locked   |
      | Only one can update     |
      | The other must wait     |
      +-------------------------+

✅ Mini summary: Locking prevents two transactions from interfering with the same data.

📘 Lesson 6 · Types of Locks – Shared and Exclusive

Definition: There are two main types of locks: shared locks (for reading) and exclusive locks (for writing).

Why important: They let multiple people read at the same time, but only one writes.

Simple explanation: Shared lock is like a book that many can read, but exclusive lock is like a book that only one can write in.

Real‑life example: In a library, many people can read the same book (shared), but only one can check it out (exclusive).

School example: Many students can read the notice board (shared), but only the teacher can put up a new notice (exclusive).

Home example: Many family members can look at the TV schedule (shared), but only one can change the channel (exclusive).

Nigerian example: In a market, many can look at the same goods (shared), but only the seller can change the price (exclusive).

+------------------+------------------+
| Shared Lock      | Exclusive Lock   |
| (Read)           | (Write)          |
| Multiple allowed | One at a time    |
+------------------+------------------+

✅ Mini summary: Shared locks allow many readers; exclusive locks allow one writer.

📘 Lesson 7 · Deadlocks – When Transactions Wait Forever

Definition: A deadlock happens when two transactions are waiting for each other to release a lock, so neither can proceed.

Why important: Deadlocks can freeze your database.

Simple explanation: Imagine two people need each other’s pencils. They both wait forever. That’s a deadlock.

Real‑life example: Two bank tellers each need to update two accounts, but they lock them in different orders – deadlock.

School example: Two students need to borrow each other’s erasers – both wait and nothing happens.

Home example: You and your sibling each need a toy the other has – neither plays.

Nigerian example: In a busy bank, if transactions lock accounts in different orders, deadlock can occur.

+----------+      +----------+
| Txn 1    |      | Txn 2    |
| locks A  |      | locks B  |
| waits for|      | waits for|
| B        |      | A        |
+----------+      +----------+
        \          /
         \        /
          V      V
      +------------------+
      | DEADLOCK!        |
      | Both wait forever|
      +------------------+

✅ Mini summary: Deadlock occurs when two transactions wait for each other’s locks.

📘 Lesson 8 · Handling Deadlocks – MySQL to the Rescue

Definition: MySQL can detect deadlocks and automatically choose one transaction to rollback (undo), so the other can continue.

Why important: This keeps the database running smoothly.

Simple explanation: MySQL acts like a referee who says “One of you must step back, so the game can continue.”

Real‑life example: If two people are stuck in a doorway, one person steps back to let the other pass.

School example: If two students are stuck, the teacher tells one to wait.

Home example: If you and your sibling are both trying to use the same toy, a parent steps in.

Nigerian example: In a busy market, if two people are stuck, someone yields.

+------------------+
| MySQL detects    |
| deadlock         |
+------------------+
        |
        V
+------------------+
| Rolls back one   |
| transaction      |
| Other continues  |
+------------------+

✅ Mini summary: MySQL automatically resolves deadlocks by rolling back one transaction.

📘 Lesson 9 · What is a Backup?

Definition: A backup is a copy of your database that you can use to restore data if something goes wrong.

Why important: It protects against data loss due to accidents, failures, or mistakes.

Simple explanation: Like taking a photograph of your LEGO creation – if it breaks, you can rebuild it.

Real‑life example: A company backs up its database every night.

School example: The school backs up student records every week.

Home example: You back up your game saves on a USB drive.

Nigerian example: A bank in Lagos backs up all customer data daily.

+------------------+
| Original DB      |
+------------------+
        |
        V
+------------------+
| Backup copy      |
| (saved elsewhere)|
+------------------+

✅ Mini summary: A backup is a saved copy of the database for safety.

📘 Lesson 10 · How to Backup a MySQL Database

Definition: We use the mysqldump tool to create a backup. It exports the database schema and data into a SQL file.

Why important: It’s the standard way to back up MySQL.

Simple explanation: mysqldump takes everything in the database and writes it to a text file.

Real‑life example: A system administrator runs mysqldump every night.

School example: The IT teacher uses mysqldump to back up the school database.

Home example: You use a similar tool to back up your game data.

Nigerian example: A bank uses mysqldump to back up customer accounts.

mysqldump -u username -p database_name > backup.sql

✅ Mini summary: mysqldump creates a backup file of the database.

📘 Lesson 11 · How to Restore a MySQL Database

Definition: To restore, we use the mysql command to import the backup file back into the database.

Why important: It’s how we recover from data loss.

Simple explanation: You take the backup file and say “put this back into the database”.

Real‑life example: If the system crashes, the administrator restores from the latest backup.

School example: If grades are lost, the teacher restores from the backup.

Home example: If your game save is corrupted, you restore from your USB.

Nigerian example: A bank restores customer data from a backup after a power outage.

mysql -u username -p database_name < backup.sql

✅ Mini summary: Restore imports the backup file back into the database.

📘 Lesson 12 · What is a Savepoint?

Definition: A savepoint is a marker within a transaction that you can roll back to, without undoing the whole transaction.

Why important: It gives more control – you can undo part of a transaction.

Simple explanation: Like saving your game at a checkpoint – if you make a mistake, you can go back to that checkpoint.

Real‑life example: During a complex bank transfer, you can set a savepoint after each step.

School example: While entering grades, you set a savepoint after each subject.

Home example: When cleaning your room, you take a break and save your progress.

Nigerian example: During a multi-step transaction, you can roll back to a savepoint.

START TRANSACTION;
  UPDATE accounts SET balance = balance - 10000 WHERE name = 'Ade';
  SAVEPOINT step1;
  UPDATE accounts SET balance = balance + 10000 WHERE name = 'Friend';
  -- Oops! Friend doesn't exist.
  ROLLBACK TO SAVEPOINT step1;  -- Undo only the friend update
  -- Now Ade is deducted, but friend is not updated.
COMMIT;

✅ Mini summary: Savepoints allow rolling back part of a transaction.

📘 Lesson 13 · Transaction Isolation Levels – How Strict?

Definition: Isolation levels control how much one transaction can see the changes of another transaction before it is committed.

Why important: They balance performance vs. consistency.

Simple explanation: It’s like how much privacy your transaction has – strict privacy (serializable) vs. more relaxed (read uncommitted).

Real‑life example: A bank uses high isolation to prevent reading uncommitted money.

School example: Grade updates use high isolation to avoid confusion.

Home example: Your game uses isolation to prevent other users from seeing your unsaved progress.

Nigerian example: Nigerian banks use strict isolation for financial data.

+------------------+------------------+
| Isolation Level  | Description      |
+------------------+------------------+
| READ UNCOMMITTED | See uncommitted  |
| READ COMMITTED   | See only committed |
| REPEATABLE READ  | Consistent reads |
| SERIALIZABLE     | Strict, like sequential |
+------------------+------------------+

✅ Mini summary: Isolation levels control how transactions see each other’s changes.

📘 Lesson 14 · Log Files – The Diary of the Database

Definition: Log files are records of all changes made to the database. They help with recovery and auditing.

Why important: If the database crashes, the log helps rebuild data.

Simple explanation: It’s like a diary that writes down every change. If you forget something, you can read the diary.

Real‑life example: Banks keep transaction logs to track all money movements.

School example: The school keeps a log of all grade changes.

Home example: Your game keeps a log of your actions.

Nigerian example: Nigerian banks keep logs for auditing purposes.

+------------------+
| Log File         |
| 1. Deduct Ade   |
| 2. Add Friend   |
| 3. ...          |
+------------------+

✅ Mini summary: Log files record every change for recovery and auditing.

📘 Lesson 15 · Putting It All Together – A Safe Database

Definition: By using transactions, locking, backups, and logs, we create a safe and reliable database system.

Why important: This is how real-world systems ensure data is never lost or corrupted.

Simple explanation: We use all the tools we learned to protect data like a superhero.

Real‑life example: An e‑commerce site uses transactions for orders, locks for inventory, backups daily, and logs for auditing.

School example: The school database uses all these to keep student records safe.

Home example: Your game uses transactions and saves to protect progress.

Nigerian example: A Nigerian fintech company uses all these to ensure your money is safe.

+------------------+------------------+
| Transactions     | Group changes    |
| Locking          | Prevent conflicts|
| Backups          | Save copies      |
| Logs             | Record changes   |
+------------------+------------------+
        |
        V
+------------------+
| Safe Database    |
+------------------+

✅ Mini summary: Combining transactions, locks, backups, and logs creates a safe database.

📚 Key Vocabulary

Transaction: A group of SQL statements treated as one unit.
COMMIT: Makes transaction changes permanent.
ROLLBACK: Undoes all changes in a transaction.
ACID: Four rules: Atomicity, Consistency, Isolation, Durability.
Locking: Prevents conflicting changes.
Deadlock: Two transactions waiting on each other.
Backup: A copy of the database for safety.
Savepoint: A marker to roll back to in a transaction.
Isolation level: How strict the transaction privacy is.
Log file: A diary of all changes.

🧠 Important Concepts

Transactions ensure all-or-nothing changes.
COMMIT saves, ROLLBACK undoes.
ACID makes databases reliable.
Locking prevents data conflicts.
Backups protect against data loss.
Savepoints give fine control.

🪜 Step‑by‑Step Explanations

How to use a transaction:

Start with START TRANSACTION;
Write your SQL statements (UPDATE, INSERT, etc.).
If all is well, use COMMIT; to save.
If something goes wrong, use ROLLBACK; to undo.

How to backup and restore:

Backup: mysqldump -u user -p dbname > backup.sql
Restore: mysql -u user -p dbname < backup.sql

🌍 Real‑life Examples

Online shopping uses transactions for payment and inventory.
Banks use transactions for money transfers.
Social media uses transactions to update posts and likes.

🇳🇬 Nigerian Examples

GTBank uses transactions to handle account transfers.
JAMB uses transactions to process exam registrations.
INEC uses transactions to update voter records.

🎈 Fun Examples for Children

When you trade cards with a friend, both must agree – that’s a transaction.
When you save your game, you are doing a COMMIT.
If you make a mistake in a game, you can undo – that’s ROLLBACK.

🏠 Everyday Examples

ATM withdrawal: deducts money and counts the notes – all in one transaction.
Buying airtime: deducts money and adds airtime.
Booking a flight: reserves a seat and charges your card.

👩‍🏫 Teacher Notes

Use the bank transfer story to introduce transactions. Emphasize the “all or nothing” idea. Use physical activities – like moving tokens between cups – to simulate transactions. Show how locking prevents chaos by having students “lock” items. Explain deadlock with a simple role-play.

👨‍👩‍👧‍👦 Parent Tips

Relate transactions to everyday chores: “Taking out the trash and closing the bin” – both must happen. Use games to explain saving and undoing. Discuss the importance of backups – like keeping a photo of a LEGO creation.

💡 Interesting Facts

The concept of transactions was introduced in the 1970s.
MySQL automatically wraps single SQL statements in a transaction.
Deadlocks are more common in large databases with many users.

🧐 Did You Know?

Did you know that MySQL’s default isolation level is REPEATABLE READ?
Did you know that you can back up a database while it is running?

🔔 Remember This

Transactions are all or nothing.
COMMIT saves, ROLLBACK undoes.
ACID makes databases reliable.
Locking prevents conflicts.
Backups protect against loss.

❌ Common Mistakes

Forgetting to COMMIT: Changes are not saved.
Using ROLLBACK after COMMIT: Can’t undo after commit.
Not using transactions for multiple updates: Can cause inconsistency.
Ignoring locking: Can lead to data corruption.

✅ Best Practices

Always use transactions for critical operations.
Keep transactions short to reduce locking time.
Back up your database regularly.
Use savepoints for complex transactions.

📊 ASCII Diagrams & Illustrations

Transaction flow:

START TRANSACTION
        |
        V
  SQL statements
        |
        V
   +-------+-------+
   |               |
 All OK?        Error?
   |               |
   V               V
COMMIT          ROLLBACK
(save)          (undo)

Deadlock illustration:

+----------+      +----------+
| Txn A    |      | Txn B    |
| locks X  |      | locks Y  |
| waits for|      | waits for|
| Y        |      | X        |
+----------+      +----------+
        \          /
         \        /
          V      V
      +------------------+
      |   DEADLOCK!      |
      +------------------+

Backup and restore:

+------------+     +------------+
| Database   |     | Backup     |
| (live)     | --> | (file)     |
+------------+     +------------+
        |
        V
+------------+
| Restore    |
| (import)   |
+------------+

📋 Comparison Table: COMMIT vs ROLLBACK vs SAVEPOINT

Command	Effect	When to use
COMMIT	Save all changes permanently	When everything is correct
ROLLBACK	Undo all changes	When a mistake happens
SAVEPOINT + ROLLBACK TO	Undo to a specific point	When you need partial undo

📝 End‑of‑Module Summary

In this module, we learned how to keep data safe and reliable. Transactions group changes together, and COMMIT saves them while ROLLBACK undoes them. ACID rules ensure transactions are atomic, consistent, isolated, and durable. Locking prevents conflicts, and deadlocks are resolved by MySQL. We also learned about backups (mysqldump) and restores to protect against data loss. Savepoints give us fine control, and isolation levels balance performance and consistency. With these tools, you are now a true data guardian!

❓ Frequently Asked Questions

What is a transaction? – A group of SQL statements treated as one unit.
What does COMMIT do? – Saves all changes permanently.
What does ROLLBACK do? – Undoes all changes.
What is ACID? – Rules: Atomicity, Consistency, Isolation, Durability.
What is locking? – Prevents conflicts between transactions.
What is a deadlock? – Two transactions waiting for each other.
How does MySQL handle deadlocks? – Rolls back one transaction.
What is a backup? – A copy of the database.
How do I backup? – Use mysqldump.
What is a savepoint? – A marker in a transaction to roll back to.

📝 Review Questions

What is a transaction?
What is the difference between COMMIT and ROLLBACK?
What does ACID stand for?
What is locking?
What is a deadlock?
How does MySQL resolve a deadlock?
What is a backup?
How do you backup a MySQL database?
What is a savepoint?
What are the common isolation levels?
What is a log file?
Why are transactions important in banking?
What happens if you don’t use transactions?
How can you restore a database from a backup?
Give an example of a transaction at home.

✏️ Fill‑in‑the‑Blank

START ________; (starts a transaction)
________; (saves changes)
________; (undoes changes)
The 'C' in ACID stands for ________.
________ is a copy of the database.

✅ True or False

A transaction can be partially saved. (False)
COMMIT makes changes permanent. (True)
ROLLBACK undoes changes. (True)
ACID makes databases unreliable. (False)
Deadlocks are automatically resolved by MySQL. (True)

🔢 Multiple Choice Questions

Which command starts a transaction?
A) START TRANSACTION B) BEGIN C) Both A and B D) COMMIT
Which command saves changes?
A) ROLLBACK B) COMMIT C) SAVEPOINT D) LOCK
Which command undoes changes?
A) ROLLBACK B) COMMIT C) SAVEPOINT D) LOCK
Which is NOT an ACID property?
A) Atomicity B) Consistency C) Availability D) Durability
What does locking prevent?
A) Deadlocks B) Conflicts C) Backups D) Commits
Which tool creates a backup?
A) mysqldump B) mysql C) mysqlimport D) mysqladmin
Which command restores a backup?
A) mysql B) mysqldump C) mysqlimport D) mysqlrestore
What is a savepoint?
A) A marker to roll back to B) A type of lock C) A backup D) A command
Which isolation level is strictest?
A) READ UNCOMMITTED B) READ COMMITTED C) REPEATABLE READ D) SERIALIZABLE
What is a deadlock?
A) Two transactions waiting on each other B) A backup failure C) A lock timeout D) A commit error
What does Durability mean?
A) Changes persist after crash B) All or nothing C) Data is correct D) Transactions are isolated
Which command creates a savepoint?
A) SAVEPOINT name B) CREATE SAVEPOINT C) SET SAVEPOINT D) MARK SAVEPOINT
What is a log file used for?
A) Recording changes B) Creating backups C) Locking tables D) Committing transactions
Which is NOT a common isolation level?
A) READ COMMITTED B) REPEATABLE READ C) SERIALIZABLE D) READ LOCKED
Why are transactions important?
A) To ensure data consistency B) To speed up queries C) To create indexes D) To backup data

Answers: 1-C, 2-B, 3-A, 4-C, 5-B, 6-A, 7-A, 8-A, 9-D, 10-A, 11-A, 12-A, 13-A, 14-D, 15-A

🔗 Matching Exercises

Match the term to its description:

Term	Description
COMMIT	Save changes
ROLLBACK	Undo changes
SAVEPOINT	Marker for partial undo
Deadlock	Two transactions waiting
Backup	Copy of database

📝 Short Answer Questions

Write the SQL to start a transaction and then commit it.
Explain ACID in your own words.
What is the difference between a shared lock and an exclusive lock?
How do you create a backup of a database called "school"?
Why do we use savepoints?

🎭 Scenario‑based Exercises

You have a banking database. Write a transaction to transfer ₦50,000 from account A to account B. Use COMMIT.
During a transaction, you realize you made a mistake. Write the commands to undo everything.
You want to back up your database "store" and then restore it to a new database called "store_backup". Write the commands.

👥 Group Activity

In groups, role-play a banking scenario. One person is the database, others are transactions. Simulate a deadlock and then let MySQL resolve it. Explain how each ACID property is applied.

🧑 Individual Activity

Create a transaction for a small shop. Use SQL to deduct stock and add a sale. Include a SAVEPOINT and roll back to it. Then COMMIT.

💬 Classroom Discussion Questions

Why is it important to back up data regularly?
What could happen if we didn't have locking?
How do transactions make online shopping safer?

🛠️ Mini Project

Build a simple banking system. Create tables: accounts (id, name, balance). Write a script that transfers money between accounts using transactions. Include COMMIT and ROLLBACK. Also, create a backup script using mysqldump.

💻 Practical Assignment

Using MySQL, create a table "products" (id, name, stock). Insert some data. Write a transaction that updates stock when a product is sold. Use SAVEPOINT and ROLLBACK to handle errors. Then backup the database.

⚡ Challenge Exercise

Write a transaction that transfers money between two accounts, but only if both accounts exist and have sufficient balance. If any condition fails, rollback the entire transaction. Also, include a savepoint before each update.

📌 Quiz Answers

Multiple choice answers are given above. Fill-in-the-blank: 1. TRANSACTION, 2. COMMIT, 3. ROLLBACK, 4. Consistency, 5. Backup. True/False: 1F, 2T, 3T, 4F, 5T.

🔑 Key Takeaways

Transactions ensure all-or-nothing changes.
COMMIT saves, ROLLBACK undoes.
ACID makes databases reliable.
Locking prevents conflicts.
Backups protect against data loss.

🚀 Preparation for the Next Module

In Module 8, we will explore stored procedures, triggers, and events. These are like mini-programs that run inside the database to automate tasks. You will learn to write reusable code and make the database do work for you automatically!

🎉 Congratulations! You have finished Module 7. You are now a guardian of data – protecting it with transactions, locks, and backups. Keep learning and stay safe!

Module Eight

Module 8 · MySQL for Data Analysis Expert

⚙️ Module Eight · MySQL for Data Analysis Expert

🤖 Automating with Stored Procedures, Triggers & Events

🌟 Module Introduction

Hello, data engineer! In the past modules, we learned to ask questions, speed up searches, and keep data safe. Now we are going to make the database do things automatically!

We will learn about stored procedures (saved programs that run on the database), triggers (actions that happen automatically when data changes), and events (scheduled tasks that run at specific times).

These tools will make your database smarter and reduce the work you have to do. Let's get started!

🎯 Learning Objectives

After this module, you will be able to:

Create and use stored procedures to save and reuse SQL code.
Understand parameters to make procedures flexible.
Use triggers to automatically run code when data is added, changed, or deleted.
Schedule events to run tasks at specific times.
Know when to use each tool.

📖 Warm‑up Story · The School's Automated System

In a big school in Ibadan, the principal is tired of manually updating grades and sending reports. Every day, she spends hours copying data.

One day, the IT teacher says, "Let's use stored procedures to save the report query. Then we can run it with one click! And we can use triggers to automatically update the class average when a new grade is entered. Finally, we can set up an event to send a report every Friday at 4pm."

Now the principal is happy because the database does all the work automatically!

📘 Lesson 1 · What is a Stored Procedure?

Definition: A stored procedure is a saved set of SQL statements that you can run like a program. It is stored in the database.

Why important: It saves time – you write the code once and run it many times.

Simple explanation: Imagine you have a recipe for your favourite meal. You write it down once, and then you can follow it anytime. A stored procedure is like a recipe for SQL.

Real‑life example: A bank has a stored procedure to calculate interest on all accounts every month.

School example: A stored procedure to generate report cards for all students.

Home example: A stored procedure to update your chore list every week.

Nigerian example: A Nigerian fintech uses a stored procedure to process daily transactions.

+---------------------------+
| Stored Procedure          |
| "get_student_report"      |
|   SELECT name, score      |
|   FROM students           |
|   WHERE class = 'JSS1';   |
+---------------------------+
        |
        V
+---------------------------+
| Run it anytime:           |
| CALL get_student_report();|
+---------------------------+

✅ Mini summary: A stored procedure is a saved SQL program that you can run repeatedly.

📘 Lesson 2 · Creating a Stored Procedure

Definition: Use CREATE PROCEDURE to define a new procedure.

Why important: This is how we save our SQL code for reuse.

Simple explanation: You write CREATE PROCEDURE name() BEGIN ... END; and put your SQL inside.

Real‑life example: CREATE PROCEDURE get_all_customers() BEGIN SELECT * FROM customers; END;

School example: CREATE PROCEDURE get_students() BEGIN SELECT * FROM students; END;

Home example: CREATE PROCEDURE get_snacks() BEGIN SELECT * FROM fridge WHERE type='snack'; END;

Nigerian example: CREATE PROCEDURE get_lagos_voters() BEGIN SELECT * FROM voters WHERE state='Lagos'; END;

DELIMITER //

CREATE PROCEDURE get_students()
BEGIN
    SELECT * FROM students;
END //

DELIMITER ;

✅ Mini summary: CREATE PROCEDURE saves a SQL query for later use.

📘 Lesson 3 · Calling a Stored Procedure

Definition: Use CALL to run a stored procedure.

Why important: This is how we execute the saved code.

Simple explanation: You just say CALL procedure_name(); and it runs.

Real‑life example: CALL get_all_customers(); shows all customers.

School example: CALL get_students(); shows all students.

Home example: CALL get_snacks(); shows all snacks.

Nigerian example: CALL get_lagos_voters(); shows Lagos voters.

CALL get_students();

✅ Mini summary: CALL runs a stored procedure.

📘 Lesson 4 · Parameters – Making Procedures Flexible

Definition: Parameters are like inputs you give to a procedure to change its behavior.

Why important: They make procedures reusable for different cases.

Simple explanation: Like a recipe where you can change the ingredients. You say "show me students in class X" where X is a parameter.

Real‑life example: CREATE PROCEDURE get_customers_by_city(IN city_name VARCHAR(50)) BEGIN SELECT * FROM customers WHERE city = city_name; END;

School example: CREATE PROCEDURE get_students_by_class(IN class_name VARCHAR(10)) BEGIN SELECT * FROM students WHERE class = class_name; END;

Home example: CREATE PROCEDURE get_items_by_type(IN type_name VARCHAR(20)) BEGIN SELECT * FROM fridge WHERE type = type_name; END;

Nigerian example: CREATE PROCEDURE get_voters_by_state(IN state_name VARCHAR(50)) BEGIN SELECT * FROM voters WHERE state = state_name; END;

CREATE PROCEDURE get_students_by_class(IN class_name VARCHAR(10))
BEGIN
    SELECT * FROM students WHERE class = class_name;
END;

CALL get_students_by_class('JSS1');

✅ Mini summary: Parameters let you pass values to a procedure for flexible queries.

📘 Lesson 5 · Dropping a Stored Procedure

Definition: Use DROP PROCEDURE to delete a procedure.

Why important: If you no longer need a procedure, you can remove it.

Simple explanation: Like throwing away a recipe you don't use anymore.

Real‑life example: DROP PROCEDURE get_all_customers;

School example: DROP PROCEDURE get_students;

Home example: DROP PROCEDURE get_snacks;

Nigerian example: DROP PROCEDURE get_lagos_voters;

DROP PROCEDURE get_students_by_class;

✅ Mini summary: DROP PROCEDURE removes a stored procedure.

📘 Lesson 6 · What is a Trigger?

Definition: A trigger is a set of SQL statements that automatically run when a certain event happens on a table (INSERT, UPDATE, or DELETE).

Why important: Triggers let you enforce rules and automate actions without writing extra code.

Simple explanation: Imagine a spring-loaded trap – when someone opens a door (event), the trap triggers (action).

Real‑life example: When a product is sold (INSERT into sales), a trigger reduces the stock in the products table.

School example: When a new student is added, a trigger creates a record in the attendance table.

Home example: When you eat a snack (UPDATE quantity), a trigger alerts you to buy more.

Nigerian example: When a voter registers, a trigger sends a confirmation SMS.

+---------------------------+      +---------------------------+
| Event: INSERT on sales    | ---> | Trigger: UPDATE stock     |
| (a sale happens)          |      | (reduce product quantity) |
+---------------------------+      +---------------------------+

✅ Mini summary: A trigger runs automatically when data is added, changed, or deleted.

📘 Lesson 7 · Creating a Trigger

Definition: Use CREATE TRIGGER to define a trigger. You specify the event (INSERT, UPDATE, DELETE), the timing (BEFORE or AFTER), and the table.

Why important: It's how we set up automatic actions.

Simple explanation: You say "Before inserting a sale, check if stock is available."

Real‑life example: CREATE TRIGGER reduce_stock AFTER INSERT ON sales FOR EACH ROW UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;

School example: CREATE TRIGGER update_attendance AFTER INSERT ON students FOR EACH ROW INSERT INTO attendance (student_id) VALUES (NEW.id);

Home example: CREATE TRIGGER alert_low_stock AFTER UPDATE ON fridge FOR EACH ROW IF NEW.quantity < 5 THEN INSERT INTO alerts (message) VALUES ('Low stock!'); END IF;

Nigerian example: CREATE TRIGGER log_voter_registration AFTER INSERT ON voters FOR EACH ROW INSERT INTO logs (action) VALUES ('Voter registered');

DELIMITER //

CREATE TRIGGER reduce_stock
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    UPDATE products SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;
END //

DELIMITER ;

✅ Mini summary: CREATE TRIGGER sets up automatic actions on tables.

📘 Lesson 8 · BEFORE vs AFTER Triggers

Definition: BEFORE triggers run before the event, AFTER triggers run after.

Why important: The timing determines what you can do – BEFORE can change the data, AFTER cannot.

Simple explanation: BEFORE is like checking if you have money before buying; AFTER is like updating your balance after buying.

Real‑life example: BEFORE INSERT – check if a customer exists. AFTER INSERT – send a welcome email.

School example: BEFORE UPDATE – check if grade is valid. AFTER UPDATE – update the class average.

Home example: BEFORE DELETE – backup the row. AFTER DELETE – log the action.

Nigerian example: BEFORE INSERT – validate BVN. AFTER INSERT – send SMS.

+---------------------------+      +---------------------------+
| BEFORE Trigger            |      | AFTER Trigger             |
| (runs before event)       |      | (runs after event)        |
| Can modify data           |      | Cannot modify data        |
+---------------------------+      +---------------------------+

✅ Mini summary: BEFORE triggers run before the change, AFTER triggers run after.

📘 Lesson 9 · Dropping a Trigger

Definition: Use DROP TRIGGER to remove a trigger.

Why important: If you no longer need a trigger, you can drop it.

Simple explanation: Like removing a sensor that no longer needs to trigger.

Real‑life example: DROP TRIGGER reduce_stock;

School example: DROP TRIGGER update_attendance;

Home example: DROP TRIGGER alert_low_stock;

Nigerian example: DROP TRIGGER log_voter_registration;

DROP TRIGGER reduce_stock;

✅ Mini summary: DROP TRIGGER removes a trigger.

📘 Lesson 10 · What is an Event?

Definition: An event is a task that runs at a scheduled time, like a cron job in MySQL.

Why important: It automates regular maintenance and reporting.

Simple explanation: Like setting an alarm to wake you up every morning – the database runs a task at a specific time.

Real‑life example: A daily event to back up the database at 2am.

School example: An event every Friday to generate report cards.

Home example: An event every Sunday to clean up old files.

Nigerian example: An event to send daily transaction summaries to the bank manager.

+---------------------------+
| Event Scheduler           |
| Runs at scheduled times   |
| e.g., every day at 2am    |
+---------------------------+
        |
        V
+---------------------------+
| Task: Backup database     |
| Generate report           |
| Send email                |
+---------------------------+

✅ Mini summary: An event schedules a task to run at a specific time.

📘 Lesson 11 · Creating an Event

Definition: Use CREATE EVENT to define a scheduled task.

Why important: It allows automation of repetitive tasks.

Simple explanation: You say "Run this SQL every day at 2am".

Real‑life example: CREATE EVENT daily_backup ON SCHEDULE EVERY 1 DAY STARTS '2025-01-01 02:00:00' DO ...

School example: CREATE EVENT weekly_report ON SCHEDULE EVERY 1 WEEK STARTS '2025-01-06 16:00:00' DO CALL generate_report();

Home example: CREATE EVENT cleanup ON SCHEDULE EVERY 1 WEEK DO DELETE FROM logs WHERE date < NOW() - INTERVAL 7 DAY;

Nigerian example: CREATE EVENT daily_transaction_summary ON SCHEDULE EVERY 1 DAY STARTS '2025-01-01 23:59:00' DO INSERT INTO summaries SELECT ...;

CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2025-01-01 00:00:00'
DO
  DELETE FROM temp_table WHERE created_at < NOW() - INTERVAL 1 DAY;

✅ Mini summary: CREATE EVENT schedules a task to run at a set time.

📘 Lesson 12 · Enabling and Disabling Events

Definition: You can enable or disable events with ALTER EVENT.

Why important: It gives you control over when events run.

Simple explanation: You can pause an event without deleting it.

Real‑life example: ALTER EVENT daily_backup DISABLE; to stop backups temporarily.

School example: ALTER EVENT weekly_report ENABLE; to start it again.

Home example: ALTER EVENT cleanup DISABLE; to stop cleanup.

Nigerian example: ALTER EVENT daily_summary ENABLE; to enable it.

ALTER EVENT daily_cleanup DISABLE;
ALTER EVENT daily_cleanup ENABLE;

✅ Mini summary: ALTER EVENT enables or disables scheduled events.

📘 Lesson 13 · Dropping an Event

Definition: Use DROP EVENT to remove an event.

Why important: If you no longer need a scheduled task, you can delete it.

Simple explanation: Like deleting an alarm on your phone.

Real‑life example: DROP EVENT daily_backup;

School example: DROP EVENT weekly_report;

Home example: DROP EVENT cleanup;

Nigerian example: DROP EVENT daily_summary;

DROP EVENT daily_cleanup;

✅ Mini summary: DROP EVENT removes a scheduled event.

📘 Lesson 14 · Combining Procedures, Triggers, and Events

Definition: We can use all three together to build a fully automated system.

Why important: Real-world applications use them in combination.

Simple explanation: Use procedures for reusable logic, triggers for instant reactions, and events for scheduled tasks.

Real‑life example: An e‑commerce system: trigger updates stock when an order is placed, procedure generates monthly sales report, event sends the report every month.

School example: Trigger logs grade changes, procedure calculates class average, event emails the principal weekly.

Home example: Trigger updates chore status, procedure generates weekly summary, event sends reminder.

Nigerian example: Trigger logs transactions, procedure computes daily balance, event sends report to the bank manager.

+---------------------------+      +---------------------------+
| Trigger: log changes      |      | Procedure: generate report|
| (on INSERT/UPDATE/DELETE) |      | (reusable SQL logic)      |
+---------------------------+      +---------------------------+
                |                              |
                V                              V
+---------------------------+      +---------------------------+
| Event: runs at scheduled  |----->| Calls procedure, sends    |
| time (e.g., daily)        |      | report via email          |
+---------------------------+      +---------------------------+

✅ Mini summary: Triggers, procedures, and events work together for full automation.

📘 Lesson 15 · Best Practices for Automation

Definition: Best practices help you use these tools effectively and safely.

Why important: They prevent errors and keep the system running smoothly.

Simple explanation: Like following safety rules when using tools.

Best practices:

Keep procedures simple and focused.
Test triggers in a development environment first.
Schedule events during off-peak hours.
Log all automated actions.

Real‑life example: A bank tests new triggers on a test server before production.

School example: The IT teacher tests procedures on sample data.

Home example: You test a new event on a small dataset.

Nigerian example: A fintech company runs events at midnight to avoid affecting users.

+---------------------------+      +---------------------------+
| Development               |      | Production                |
| (test first)              | ---> | (deploy after testing)    |
+---------------------------+      +---------------------------+

✅ Mini summary: Best practices ensure safe and effective automation.

📚 Key Vocabulary

Stored Procedure: A saved SQL program that you can run repeatedly.
Parameter: A value you pass to a procedure to make it flexible.
Trigger: SQL that runs automatically when data is added, changed, or deleted.
Event: A scheduled task that runs at a specific time.
BEFORE Trigger: Runs before the event and can modify data.
AFTER Trigger: Runs after the event and cannot modify data.

🧠 Important Concepts

Stored procedures save SQL code for reuse.
Parameters make procedures flexible.
Triggers automatically run on events.
Events schedule tasks for automation.
Combine all three for powerful automation.

🪜 Step‑by‑Step Explanations

How to create and use a stored procedure:

Write CREATE PROCEDURE name() BEGIN ... END;
Put your SQL inside the BEGIN and END.
Use CALL name(); to run it.

How to create a trigger:

Write CREATE TRIGGER name BEFORE/AFTER INSERT/UPDATE/DELETE ON table FOR EACH ROW BEGIN ... END;
Inside BEGIN/END, write the SQL to execute.
The trigger will run automatically when the event occurs.

How to create an event:

Write CREATE EVENT name ON SCHEDULE EVERY interval DO ...;
Specify the schedule and the SQL task.
Enable the event scheduler if needed.

🌍 Real‑life Examples

Amazon uses procedures to process orders, triggers to update inventory, and events to send shipping updates.
Banks use procedures for monthly interest calculations.
Telecom companies use triggers to manage prepaid balances.

🇳🇬 Nigerian Examples

Flutterwave uses procedures to process payments.
MTN uses triggers to update data balances.
JAMB uses events to generate exam schedules.

🎈 Fun Examples for Children

Create a procedure to show your favourite toys.
Create a trigger that says "Yay!" when you add a new game.
Create an event that reminds you to do homework every day.

🏠 Everyday Examples

When you add a new contact, a trigger backs up the old list.
Your phone runs an event to clean up old messages every week.
Your game uses a procedure to save your progress.

👩‍🏫 Teacher Notes

Use the recipe analogy for procedures. Demonstrate triggers with real-time examples (like a doorbell). Use a countdown timer to explain events. Encourage students to think of automation in their daily lives. Use group activities to design a small automated system.

👨‍👩‍👧‍👦 Parent Tips

Relate procedures to daily routines (like making breakfast). Explain triggers as "if this, then that" rules. Discuss events as reminders or alarms. Help your child think of ways to automate tasks at home.

💡 Interesting Facts

Stored procedures were introduced in SQL in the 1980s.
Triggers can be used to enforce complex business rules.
Events are also called scheduled jobs.

🧐 Did You Know?

Did you know that you can call a stored procedure from within another procedure?
Did you know that triggers can be nested?

🔔 Remember This

Procedures save SQL code for reuse.
Triggers run automatically on data changes.
Events schedule tasks at specific times.
Use parameters to make procedures flexible.

❌ Common Mistakes

Forgetting the DELIMITER: In MySQL, you must change the delimiter when creating procedures or triggers.
Using wrong trigger timing: BEFORE vs AFTER – choose the right one.
Not checking for existing objects: Use DROP IF EXISTS.
Making triggers too complex: Keep them simple to avoid performance issues.

✅ Best Practices

Use meaningful names for procedures, triggers, and events.
Test in a development environment first.
Keep procedures and triggers short.
Log actions for debugging.
Schedule events during off-peak hours.

📊 ASCII Diagrams & Illustrations

Procedure flow:

+---------------------------+
| CREATE PROCEDURE          |
|   get_students()          |
| BEGIN                     |
|   SELECT * FROM students; |
| END                       |
+---------------------------+
        |
        V
+---------------------------+
| CALL get_students();      |
| (runs the SQL)           |
+---------------------------+

Trigger flow:

+---------------------------+      +---------------------------+
| Event: INSERT on sales    | ---> | Trigger: reduce_stock     |
| (a sale happens)          |      | (runs automatically)      |
+---------------------------+      +---------------------------+
        |
        V
+---------------------------+
| Update products table     |
| (stock is reduced)        |
+---------------------------+

Event flow:

+---------------------------+
| Event Scheduler           |
| Runs every day at 2am     |
+---------------------------+
        |
        V
+---------------------------+
| Task: Backup database     |
| (automatically)           |
+---------------------------+

📋 Comparison Table: Procedures vs Triggers vs Events

Feature	Procedure	Trigger	Event
When runs	When called	On data change	On schedule
Can accept parameters	Yes	No	No
Can be reused	Yes	No (automatic)	Yes (scheduled)
Used for	Reusable logic	Automatic actions	Scheduled tasks

📝 End‑of‑Module Summary

In this module, we explored the powerful world of automation in MySQL. Stored procedures let us save and reuse SQL code, making our work faster and more consistent. Triggers automatically run when data changes, allowing us to enforce rules and perform actions without manual intervention. Events schedule tasks to run at specific times, perfect for regular maintenance and reporting.

We learned how to create, call, and drop procedures, how to set up triggers with BEFORE and AFTER timing, and how to schedule events. We also saw how these tools can work together to build a fully automated system.

With these skills, you are now able to make your database work smart, not hard!

❓ Frequently Asked Questions

What is a stored procedure? – A saved SQL program.
How do I run a procedure? – Use CALL procedure_name();
What is a parameter? – A value passed to a procedure to make it flexible.
What is a trigger? – SQL that runs automatically on data changes.
What is the difference between BEFORE and AFTER? – BEFORE runs before the event, AFTER runs after.
What is an event? – A scheduled task.
How do I create an event? – Use CREATE EVENT.
Can triggers call procedures? – Yes.
Can events call procedures? – Yes.
Why use automation? – To save time and avoid errors.

📝 Review Questions

What is a stored procedure?
How do you call a stored procedure?
What is a parameter?
What is a trigger?
What are the three events that can fire a trigger?
What is the difference between BEFORE and AFTER?
What is an event?
How do you schedule an event?
How do you drop a procedure?
How do you drop a trigger?
How do you drop an event?
Why would you use a stored procedure?
Give an example of a trigger at school.
Give an example of an event at home.
How do procedures, triggers, and events work together?

✏️ Fill‑in‑the‑Blank

CREATE ________ my_proc() BEGIN SELECT * FROM students; END;
CALL ________(); (to run a procedure)
CREATE TRIGGER reduce_stock ________ INSERT ON sales ...
DROP ________ my_trigger;
CREATE ________ daily_backup ON SCHEDULE EVERY 1 DAY DO ...

✅ True or False

Stored procedures can take parameters. (True)
Triggers can be called manually. (False)
Events run on a schedule. (True)
AFTER triggers can modify the data. (False)
BEFORE triggers run after the event. (False)

🔢 Multiple Choice Questions

Which command creates a stored procedure?
A) CREATE PROCEDURE B) CREATE TRIGGER C) CREATE EVENT D) CREATE VIEW
Which command runs a stored procedure?
A) RUN B) EXECUTE C) CALL D) START
Which keyword passes a value to a procedure?
A) OUT B) IN C) PARAM D) VALUE
Which event can fire a trigger?
A) SELECT B) INSERT C) JOIN D) VIEW
Which trigger runs before the event?
A) AFTER B) BEFORE C) INSTEAD OF D) ON
Which statement schedules a task?
A) CREATE PROCEDURE B) CREATE TRIGGER C) CREATE EVENT D) CREATE INDEX
Which command removes a procedure?
A) DROP PROCEDURE B) DELETE PROCEDURE C) REMOVE PROCEDURE D) DROP TRIGGER
Which command removes a trigger?
A) DROP TRIGGER B) DELETE TRIGGER C) REMOVE TRIGGER D) DROP EVENT
Which command removes an event?
A) DROP EVENT B) DELETE EVENT C) REMOVE EVENT D) DROP PROCEDURE
Which timing allows modification of data?
A) BEFORE B) AFTER C) Both D) Neither
Which of these can be used with an event?
A) SELECT B) INSERT C) UPDATE D) All of the above
What does CALL do?
A) Runs a procedure B) Creates a trigger C) Schedules an event D) Drops a table
Which is NOT a trigger event?
A) INSERT B) UPDATE C) DELETE D) SELECT
Which is used to enable/disable an event?
A) ALTER EVENT B) CHANGE EVENT C) MODIFY EVENT D) UPDATE EVENT
Why use automation?
A) To save time B) To reduce errors C) To improve consistency D) All of the above

Answers: 1-A, 2-C, 3-B, 4-B, 5-B, 6-C, 7-A, 8-A, 9-A, 10-A, 11-D, 12-A, 13-D, 14-A, 15-D

🔗 Matching Exercises

Match the term to its description:

Term	Description
Procedure	Saved SQL program
Trigger	Automatic on data change
Event	Scheduled task
Parameter	Input to procedure
BEFORE	Runs before event

📝 Short Answer Questions

Write a stored procedure to get all students from JSS1.
Write a trigger that updates the class average when a new grade is added.
Write an event that runs every day at 10pm to clean up old logs.
Explain the difference between a procedure and a trigger.
Why would you use an event instead of a trigger?

🎭 Scenario‑based Exercises

You have a library database. Create a procedure to count books by category. Create a trigger to log when a book is borrowed. Create an event to send a reminder for overdue books.
You have a school database. Create a procedure to generate a report card. Create a trigger to update the grade average. Create an event to email the principal weekly.
You have a supermarket. Create a procedure to reorder stock. Create a trigger to reduce stock when a sale is made. Create an event to run the reorder procedure every Monday.

👥 Group Activity

In groups, design a small automated system for a school. Divide roles: one group writes procedures, one writes triggers, one writes events. Combine them to show a complete automated system. Present to the class.

🧑 Individual Activity

Create a stored procedure to add a new student. Create a trigger that logs the addition. Create an event that runs every night to backup the student table.

💬 Classroom Discussion Questions

Why is automation important in databases?
What could go wrong if a trigger is poorly written?
How do events make life easier for a database administrator?

🛠️ Mini Project

Build a small e‑commerce system. Tables: products, orders, order_items. Create procedures: place_order, get_total_sales. Create triggers: update_stock_after_order, log_order. Create event: generate_daily_sales_report.

💻 Practical Assignment

Using MySQL, create a "library" database. Create a procedure to add a book. Create a trigger to log when a book is borrowed. Create an event to run a report every week. Test all components.

⚡ Challenge Exercise

Write a stored procedure that transfers money between two accounts. Include validation (sufficient balance, accounts exist). Create a trigger that logs all transfers. Create an event that runs daily to compute interest and add it to accounts.

📌 Quiz Answers

Multiple choice answers are given above. Fill-in-the-blank: 1. PROCEDURE, 2. my_proc, 3. AFTER, 4. TRIGGER, 5. EVENT. True/False: 1T, 2F, 3T, 4F, 5F.

🔑 Key Takeaways

Stored procedures save and reuse SQL code.
Triggers automate actions on data changes.
Events schedule tasks at specific times.
Combine them for full automation.
Always test before deploying to production.

🚀 Preparation for the Next Module

In Module 9, we will dive into performance tuning and query optimization. We will learn how to make queries even faster using EXPLAIN, query caching, and optimization techniques. Get ready to become a performance expert!

🎉 Congratulations! You have finished Module 8. You are now a master of automation – making your database work for you. Keep learning and automating!

Module Nine

Module 9: MySQL for Data Analysis Expert – Complete HTML Course

Module 9: MySQL for Data Analysis Expert

Welcome, young data explorer! In this module, you will become a data analysis expert using MySQL. We will learn how to ask questions to our database, get answers, and make smart decisions – just like a detective solving a mystery! We will use simple words and lots of examples. Ready? Let's go!

Learning Objectives

Understand what data analysis means.
Learn how to use MySQL to answer questions.
Write simple queries to find patterns.
Use GROUP BY, ORDER BY, and WHERE like a pro.
Create reports from data.
Think like a data analyst!

Warm-up Story: The Mystery of the Missing Cookies 🍪

Imagine you have a big jar of cookies. Every day, you and your friends take some cookies. But one day, the jar is empty! Who took the most cookies? You have a list of names and how many cookies each friend took each day. You need to analyze the data to find the cookie champion! With MySQL, you can sort the numbers, group them, and find the answer. This is what data analysis is about – turning numbers into stories!

Main Lessons

Lesson 1: What is Data Analysis?

Definition: Data analysis is looking at information (data) to understand it better.

Why it's important: It helps us make good choices, like which game to play or what to eat for lunch.

Simple explanation: Imagine you have a box of crayons. You count how many red, blue, and green you have. That's analysis!

Real-life example: A teacher counts how many students like maths, science, or art.

School example: Your class votes for the best field trip. You count the votes.

Home example: You count how many apples, bananas, and oranges are in the fruit basket.

Nigerian example: A market seller counts how many yams, tomatoes, and peppers she sold.

Illustration:

  Data (numbers)  →  Analyze (look closely)  →  Answer (what we learn)

Mini summary: Data analysis is looking at data to find answers.

Lesson 2: The MySQL Toolbox

Definition: MySQL is a program that stores data and lets us ask questions.

Why it's important: It's like a giant digital filing cabinet.

Simple explanation: Think of MySQL as a magic notebook where you write everything, and then you can ask, "Show me all the red things!"

Real-life example: A library uses a computer to find books.

School example: Your school uses a database to store student names.

Home example: A phone contact list is a tiny database.

Nigerian example: A bank in Lagos uses MySQL to track customers' money.

Illustration:

  MySQL = Database + Query (question)

Mini summary: MySQL is our tool for storing and asking about data.

Lesson 3: SELECT – The Magic Word

Definition: SELECT is the command to get data from MySQL.

Why it's important: It's like saying "I want to see..."

Simple explanation: If you want to see all names, you write SELECT * FROM friends;

Real-life example: You ask your mom, "Can I see the list of groceries?"

School example: Teacher says, "Show me all students in class 5."

Home example: You check your toy box for all cars.

Nigerian example: A shop owner asks, "Show me all the shoes I have."

Illustration:

  SELECT * FROM toys;   -->  shows every toy in the table

Mini summary: SELECT is the key to seeing your data.

Lesson 4: WHERE – The Filter

Definition: WHERE helps us pick only the data we want.

Why it's important: It's like a sieve that keeps only red M&Ms.

Simple explanation: SELECT * FROM fruits WHERE color = 'yellow'; shows only yellow fruits.

Real-life example: You filter your WhatsApp contacts to show only friends.

School example: List only students who scored above 80%.

Home example: Show only the toys that are red.

Nigerian example: A seller sees only customers who bought groundnuts.

Illustration:

  Data → WHERE (condition) → Filtered data

Mini summary: WHERE filters data like a net.

Lesson 5: ORDER BY – Sorting

Definition: ORDER BY arranges data in order (like A–Z or 1–10).

Why it's important: Makes it easier to find the biggest or smallest.

Simple explanation: SELECT name FROM students ORDER BY score DESC; puts highest score first.

Real-life example: Sorting your books by height.

School example: Ranking students by test scores.

Home example: Arranging your Pokémon cards by power.

Nigerian example: A farmer sorts his yams by size.

Illustration:

  Before: [5, 2, 8]   ORDER BY ASC → [2, 5, 8]

Mini summary: ORDER BY sorts your data nicely.

Lesson 6: GROUP BY – Grouping Friends

Definition: GROUP BY puts data into groups.

Why it's important: We can count, sum, or average each group.

Simple explanation: If you have a list of fruits, GROUP BY color gives groups: red, yellow, green.

Real-life example: In a class, group by age to see how many 8-year-olds, 9-year-olds.

School example: Group students by their favourite subject.

Home example: Group your toys by type (cars, dolls, balls).

Nigerian example: Group customers by state they live in.

Illustration:

  Data: (apple, red), (banana, yellow), (berry, red)
  GROUP BY color → red:2, yellow:1

Mini summary: GROUP BY makes piles of similar things.

Lesson 7: COUNT – How Many?

Definition: COUNT tells the number of rows.

Why it's important: We often need to know "how many".

Simple explanation: SELECT COUNT(*) FROM toys; tells total toys.

Real-life example: Counting how many books you read.

School example: Counting students present today.

Home example: Counting your siblings.

Nigerian example: Counting bags of rice in a store.

Illustration:

  COUNT(*) = 5   (if there are 5 rows)

Mini summary: COUNT answers "how many?"

Lesson 8: SUM – Adding Up

Definition: SUM adds numbers in a column.

Why it's important: To find totals, like total money.

Simple explanation: SELECT SUM(score) FROM exams; gives total scores.

Real-life example: Adding up your pocket money.

School example: Total marks for all students in a test.

Home example: Sum of all your toy prices.

Nigerian example: Total sales of mangoes for a week.

Illustration:

  scores: [10, 20, 30] → SUM = 60

Mini summary: SUM adds numbers.

Lesson 9: AVG – The Average

Definition: AVG calculates the average (sum divided by count).

Why it's important: Helps us see the typical value.

Simple explanation: SELECT AVG(age) FROM students; gives average age.

Real-life example: Average marks in a test.

School example: Average height of class.

Home example: Average time you spend on homework.

Nigerian example: Average price of yams in the market.

Illustration:

  [10, 20, 30] → AVG = 20

Mini summary: AVG shows the middle or typical value.

Lesson 10: MIN and MAX – Smallest and Biggest

Definition: MIN finds the smallest, MAX finds the largest.

Why it's important: To know extremes – shortest and tallest.

Simple explanation: SELECT MAX(score) FROM exams; shows highest score.

Real-life example: The youngest and oldest in family.

School example: Lowest and highest marks.

Home example: Smallest and largest shoe size.

Nigerian example: Cheapest and most expensive phone in a shop.

Illustration:

  [5, 12, 3, 9] → MIN=3, MAX=12

Mini summary: MIN and MAX give extremes.

Lesson 11: Putting It All Together – Reports

Definition: A report is a summary of findings.

Why it's important: We share our analysis.

Simple explanation: You make a chart or list to show your friends.

Real-life example: A weather report shows temperatures.

School example: Class performance report.

Home example: Chore chart.

Nigerian example: A trader's weekly sales report.

Illustration:

  Query → MySQL → Report (table with totals, averages)

Mini summary: A report is a clear summary of data.

Lesson 12: Nigerian Example – Market Data

Let's say you have a table of goods sold at a market in Abuja. Columns: item, quantity, price. We can use MySQL to find the total sales, most popular item, and average price. This helps the seller know what to stock.

  SELECT item, SUM(quantity) AS total_sold FROM market GROUP BY item;

Key Vocabulary

Data: Information like numbers, names, or pictures.
Database: A place where data is stored.
Query: A question you ask the database.
Table: A set of data in rows and columns (like a spreadsheet).
Row: One record (e.g., one person).
Column: One type of information (e.g., age).
Filter: To pick only some data.
Sort: To arrange in order.
Group: To put similar things together.
Aggregate: A function like COUNT, SUM, AVG.

Important Concepts

1. Primary Key: A unique ID for each row (like your student number).

2. Foreign Key: A link to another table.

3. Joins: Combining two tables (we'll learn later).

Step-by-Step Explanations

Step 1: Open MySQL.

Step 2: Choose your database: USE mydb;

Step 3: Write a SELECT query.

Step 4: Add WHERE, ORDER BY, GROUP BY.

Step 5: Run the query and look at the results.

Real-life Examples

A shop owner uses MySQL to know which product sells most.
A school uses MySQL to track attendance.
You can use MySQL to keep a list of your friends' birthdays.

Nigerian Examples

A bank in Lagos uses MySQL to analyze customer deposits.
A farmer in Kano uses it to track crop yields.
A supermarket in Ibadan uses it to find best-selling items.

Fun Examples

Track your Pokémon collection: count each type.
Count how many times you eat pizza vs. ice cream.
List your toys sorted by size.

Everyday Examples

Checking the weather forecast – that's data analysis!
Comparing prices before buying.
Keeping a scoreboard in a game.

Teacher Notes

Encourage students to think of questions they want to ask about data. Use simple, familiar datasets (class list, favorite foods). Emphasize that analysis is about curiosity.

Parent Tips

Ask your child to count items around the house and make simple tables. Show them how you use data (e.g., budget). Make it a game!

Interesting Facts

MySQL is used by big websites like Facebook and YouTube.
The first database was created in the 1960s.
Data analysis can help save endangered animals!

Did You Know?

MySQL is named after My, the daughter of one of the founders.

Remember This

SELECT = see data
WHERE = filter
ORDER BY = sort
GROUP BY = group
COUNT, SUM, AVG, MIN, MAX = summaries

Common Mistakes

Forgetting semicolon (;) at the end of a query.
Spelling column names wrong.
Using GROUP BY without an aggregate function.
Confusing WHERE and HAVING (we'll learn HAVING later).

Best Practices

Use uppercase for SQL keywords (SELECT, WHERE) to make it clear.
Write one query at a time.
Test with a small dataset first.
Comment your queries with --.

Illustrations

Flowchart of Data Analysis

  Ask Question
       |
       V
  Collect Data
       |
       V
  Clean Data
       |
       V
  Analyze (MySQL)
       |
       V
  Get Answer
       |
       V
  Share Report

Table Example: Toy Inventory

Toy	Color	Quantity
Ball	Red	5
Doll	Pink	3
Car	Blue	7

Comparison Table: Aggregate Functions

Function	What it does
COUNT	Number of rows
SUM	Adds numbers
AVG	Average
MIN	Smallest
MAX	Largest

End-of-Module Summary

You learned that data analysis is about asking questions and finding answers using MySQL. You know how to SELECT, FILTER with WHERE, SORT with ORDER BY, GROUP with GROUP BY, and use COUNT, SUM, AVG, MIN, MAX. You can now look at data like a detective and find patterns. Great work!

Frequently Asked Questions

What is MySQL? – A database program.
What is a query? – A question to the database.
What does SELECT do? – Shows data.
What is WHERE? – Filters data.
What is GROUP BY? – Groups data.
What is COUNT? – Counts rows.
What is SUM? – Adds numbers.
What is AVG? – Finds average.
What is MIN? – Finds smallest.
What is MAX? – Finds largest.

Review Questions

What does data analysis mean?
Name the command to see all data in a table.
How do you filter data?
How do you sort data?
What is GROUP BY used for?
Which function counts rows?
Which function adds numbers?
Which function finds the average?
Which function finds the smallest?
Which function finds the largest?
What is a primary key?
What is a table?
What is a row?
What is a column?
Give an example of a report.

Fill-in-the-Blank

SELECT * FROM _______ ;
_______ filters data.
ORDER BY _______ sorts in descending order.
GROUP BY is used with _______ functions.
_______ gives the total of numbers.

True or False

MySQL is a game. (False)
SELECT shows data. (True)
WHERE sorts data. (False)
COUNT adds numbers. (False)
AVG finds the average. (True)

Multiple Choice

Which command shows data? A) SHOW B) SELECT C) DISPLAY (Answer: B)
Which filters data? A) WHERE B) ORDER BY C) GROUP BY (Answer: A)
Which sorts? A) WHERE B) ORDER BY C) GROUP BY (Answer: B)
Which groups? A) WHERE B) ORDER BY C) GROUP BY (Answer: C)
Which counts rows? A) COUNT B) SUM C) AVG (Answer: A)
Which adds? A) COUNT B) SUM C) AVG (Answer: B)
Which averages? A) COUNT B) SUM C) AVG (Answer: C)
Which finds smallest? A) MIN B) MAX C) AVG (Answer: A)
Which finds largest? A) MIN B) MAX C) AVG (Answer: B)
What is a table? A) A chair B) Data in rows/columns C) A query (Answer: B)
What is a row? A) A column B) A record C) A function (Answer: B)
What is a column? A) A field B) A row C) A table (Answer: A)
Which is not an aggregate? A) SUM B) ORDER BY C) AVG (Answer: B)
What does * mean in SELECT? A) all columns B) one column C) none (Answer: A)
Which is correct? A) SELECT * FROM students; B) SELECT FROM students; (Answer: A)

Matching

Match the left with the right:

Term	Definition
SELECT	Shows data
WHERE	Filters
ORDER BY	Sorts
GROUP BY	Groups
COUNT	Counts rows

Short Answer

Write a query to see all columns from a table called 'students'.
How do you filter to see only students with age > 10?
What does ORDER BY do?

Scenario-based

Scenario: You have a table 'sales' with columns: id, item, quantity, price. Write a query to find the total quantity sold for each item.

Answer: SELECT item, SUM(quantity) FROM sales GROUP BY item;

Group Activity

In groups of 3, create a small table of your favourite snacks (name, type, rating). Use MySQL to find the average rating and the most popular type.

Individual Activity

Create a table of your monthly allowance (month, amount). Write a query to find the total, average, and max allowance.

Classroom Discussion

Why is data analysis important?
Can you think of a time you analyzed data without a computer?
How can MySQL help a business?

Mini Project

Build a simple 'Library' database with tables: Books (id, title, author) and Borrowers (id, name, book_id). Write queries to count books by author and list borrowers.

Practical Assignment

Download a sample dataset (or use a classroom list). Write 5 different queries: SELECT all, filter, sort, group, and summarize (COUNT/SUM/AVG).

Challenge Exercise

Write a query that shows the top 3 most expensive items from a products table. (Hint: ORDER BY price DESC LIMIT 3;)

Quiz Answers (Multiple Choice)

1B, 2A, 3B, 4C, 5A, 6B, 7C, 8A, 9B, 10B, 11B, 12A, 13B, 14A, 15A

Key Takeaways

Data analysis is asking questions to data.
MySQL is a tool to store and query data.
SELECT, WHERE, ORDER BY, GROUP BY are powerful.
Aggregate functions summarize data.
Practice makes perfect!

Preparation for Module 10

In the next module, we will learn about JOINS – combining tables from different sources. It will be like connecting two puzzles! Review primary and foreign keys.

Well done, future data analyst! You're ready for more MySQL adventures.

Module Ten

Module 10: MySQL for Data Analysis Expert – Joins & Beyond

Hello, young data analyst! Welcome to Module 10. In this module, we will learn how to connect two or more tables together. This is like putting puzzle pieces side by side to see the whole picture. We will use JOINS to combine information from different tables. By the end, you will be able to answer even bigger questions using MySQL. Let's jump in!

Learning Objectives

Understand what a JOIN is and why we use it.
Learn the most common type: INNER JOIN.
Use LEFT JOIN to include all rows from one table.
Combine tables using a shared column (like a student ID).
Write queries that pull data from multiple tables.
Think about how tables are connected in real life.

Warm-up Story: The School Festival Puzzle 🧩

Imagine your school is having a big festival. You have two lists. List A has student names and their class. List B has student names and their favourite snack. But the lists are separate! You want to make one big list that shows each student's name, class, and favourite snack. To do this, you need to join the two lists by matching the student names. This is exactly what a JOIN does in MySQL. It brings information together to make a complete story.

Main Lessons

Lesson 1: What is a JOIN?

Definition: A JOIN is a way to combine data from two or more tables using a common column.

Why it's important: Data is often stored in separate tables to keep it organized. JOINs let us bring it back together.

Simple explanation: Imagine you have two boxes. Box A has names, Box B has ages. You want to know each person's age. You match the names to find the ages.

Real-life example: A school has a table for students and a table for grades. JOIN them to see each student's grades.

School example: Teacher has a list of students and a list of their project marks. JOIN to see who got what.

Home example: You have a list of family members and a list of their favourite foods. JOIN to make a family menu.

Nigerian example: A shop has a table for customers and a table for orders. JOIN to see what each customer bought.

Illustration:

  Table A (Names)   Table B (Ages)
  -------           -------
  Chidi             10
  Amina             12
  Bola              11

  JOIN → One big table: Chidi-10, Amina-12, Bola-11

Mini summary: A JOIN combines tables.

Lesson 2: The Key – Linking Columns

Definition: A key is a column that is common between tables, like a student ID.

Why it's important: The key tells MySQL how to match rows.

Simple explanation: It's like a secret code that is the same in both lists.

Real-life example: Your school ID number links your name to your grades.

School example: Student ID is used in attendance and marks tables.

Home example: Your phone number links your contacts to their addresses.

Nigerian example: BVN (Bank Verification Number) links a customer to their bank accounts.

Illustration:

  Students: id (1,2,3), name
  Grades: student_id (1,2,3), subject, score
  The 'id' and 'student_id' are the keys.

Mini summary: Keys are the matching columns.

Lesson 3: INNER JOIN – The Most Common Join

Definition: INNER JOIN returns only rows that have a match in both tables.

Why it's important: It's used when you only want data that exists in both tables.

Simple explanation: It's like a Venn diagram – only the overlapping part.

Real-life example: You want students who have both a name AND a grade.

School example: List only students who are registered AND have submitted homework.

Home example: List family members who have both a birthday AND a favourite colour.

Nigerian example: Customers who have both a phone number AND made an order.

Illustration:

  Table A       Table B
  id name       id city
  1  Chidi      1  Lagos
  2  Amina      3  Abuja
  INNER JOIN → 1-Chidi-Lagos (only common id 1)

Mini summary: INNER JOIN shows only matching rows.

Lesson 4: LEFT JOIN – Keep All from Left

Definition: LEFT JOIN returns all rows from the left table, and matching rows from the right.

Why it's important: Sometimes we want all data from the first table, even if there's no match in the second.

Simple explanation: It's like the left table is the boss – we keep all its rows.

Real-life example: You want all students, and if they have grades, show them. If not, show NULL.

School example: Show all teachers, and if they have classes, show those.

Home example: List all toys, and if they have batteries, show that.

Nigerian example: Show all markets, and if they have a stall rental, show it.

Illustration:

  Left table   Right table
  id name      id city
  1  Chidi     1  Lagos
  2  Amina     3  Abuja
  LEFT JOIN → 1-Chidi-Lagos, 2-Amina-NULL (no city for Amina)

Mini summary: LEFT JOIN keeps all rows from the left.

Lesson 5: RIGHT JOIN – Keep All from Right

Definition: RIGHT JOIN returns all rows from the right table, and matching rows from the left.

Why it's important: Sometimes we want all data from the second table.

Simple explanation: The right table is the boss – we keep all its rows.

Real-life example: You want all cities, and if there are students from them, show them.

School example: Show all clubs, and if students belong, show those.

Home example: List all rooms, and if they have furniture, show that.

Nigerian example: Show all states, and if there are shops, show them.

Illustration:

  Left table   Right table
  id name      id city
  1  Chidi     1  Lagos
  2  Amina     3  Abuja
  RIGHT JOIN → 1-Chidi-Lagos, 3-NULL-Abuja (no name for Abuja)

Mini summary: RIGHT JOIN keeps all rows from the right.

Lesson 6: FULL OUTER JOIN – All from Both (Sometimes not available)

Definition: FULL OUTER JOIN returns all rows from both tables, matching when possible.

Why it's important: It shows everything.

Simple explanation: It's like combining two sets completely.

Real-life example: You want to see all students and all subjects, even if some don't match.

School example: All teachers and all classrooms.

Home example: All family members and all pets.

Nigerian example: All markets and all products.

Illustration:

  A: 1,2   B: 2,3
  FULL JOIN → 1,2,3

Mini summary: FULL OUTER JOIN keeps every row from both.

Lesson 7: Using JOIN with WHERE – Filtering

Definition: You can add WHERE after a JOIN to filter the results.

Why it's important: You might only want some rows, like only students above 10 years old.

Simple explanation: JOIN first, then filter with WHERE.

Real-life example: JOIN students and grades, then WHERE grade > 80.

School example: JOIN teachers and classes, WHERE subject = 'Math'.

Home example: JOIN family and chores, WHERE chore = 'Wash dishes'.

Nigerian example: JOIN customers and orders, WHERE city = 'Lagos'.

Illustration:

  SELECT * FROM students
  JOIN grades ON students.id = grades.student_id
  WHERE grades.score > 90;

Mini summary: WHERE filters a JOINed result.

Lesson 8: JOIN with ORDER BY – Sorting Combined Data

Definition: You can sort the result of a JOIN using ORDER BY.

Why it's important: To see the combined data in a nice order.

Simple explanation: After joining, sort like you normally do.

Real-life example: Join students and grades, then order by score descending.

School example: Join teachers and classes, order by class name.

Home example: Join family and birthdays, order by age.

Nigerian example: Join markets and goods, order by price.

Illustration:

  SELECT * FROM students
  JOIN grades ON students.id = grades.student_id
  ORDER BY grades.score DESC;

Mini summary: ORDER BY sorts the joined table.

Lesson 9: JOIN with GROUP BY – Summaries from Combined Data

Definition: You can group the result of a JOIN.

Why it's important: To get summaries like average score per class.

Simple explanation: Join tables, then group by a column.

Real-life example: Join students and classes, then average score per class.

School example: Join teachers and classes, count classes per teacher.

Home example: Join family and expenses, sum per person.

Nigerian example: Join customers and orders, total spent per customer.

Illustration:

  SELECT class, AVG(score) FROM students
  JOIN grades ON students.id = grades.student_id
  GROUP BY class;

Mini summary: GROUP BY on a JOIN gives summaries.

Lesson 10: Joining More Than Two Tables

Definition: You can join three or more tables in one query.

Why it's important: Real databases often have many tables.

Simple explanation: Chain joins together: A JOIN B ON ... JOIN C ON ...

Real-life example: Students, grades, and subjects – to get student, subject, and score.

School example: Teachers, classes, rooms – to see which teacher is in which room.

Home example: Family, chores, schedule – to see who does what and when.

Nigerian example: Customers, orders, products – to see what each customer bought.

Illustration:

  SELECT * FROM students
  JOIN grades ON students.id = grades.student_id
  JOIN subjects ON grades.subject_id = subjects.id;

Mini summary: You can join many tables.

Lesson 11: Nigerian Example – School Management

Let's say a school in Ibadan has tables: Students (id, name), Classes (id, class_name), and Scores (student_id, class_id, score). We can JOIN to find each student's scores per class.

  SELECT s.name, c.class_name, sc.score
  FROM Students s
  JOIN Scores sc ON s.id = sc.student_id
  JOIN Classes c ON sc.class_id = c.id;

Lesson 12: Fun Example – Superhero Teams

You have a table of Superheroes (id, name) and Powers (hero_id, power). JOIN to see each hero's powers.

Key Vocabulary

JOIN: Combining tables.
INNER JOIN: Only matching rows.
LEFT JOIN: All rows from left table.
RIGHT JOIN: All rows from right table.
FULL OUTER JOIN: All rows from both.
Key: A column used to match tables.
Primary Key: Unique ID for a row.
Foreign Key: A key that links to another table.
NULL: No data (empty).

Important Concepts

1. Primary Key: A column that uniquely identifies each row (like student_id).

2. Foreign Key: A column in one table that refers to the primary key of another table.

3. Referential Integrity: Ensures that foreign keys point to existing rows.

Step-by-Step Explanations

Step 1: Identify the tables you want to join.

Step 2: Find the common column (key).

Step 3: Write the JOIN clause: JOIN table2 ON table1.key = table2.key.

Step 4: Add WHERE, GROUP BY, ORDER BY as needed.

Step 5: Run the query and check the results.

Real-life Examples

An online store joins customers and orders to see purchase history.
A hospital joins patients and appointments.
A library joins books and borrowers.

Nigerian Examples

A bank joins customers and accounts.
A transport company joins buses and routes.
A university joins students and courses.

Fun Examples

Join your friends and their favourite games.
Join your toys and their colours.
Join your class and their pet names.

Everyday Examples

Combining a phone contact list with a birthday list.
Matching a shopping list with a price list.
Linking a recipe with ingredients.

Teacher Notes

Use simple, relatable datasets like class lists. Emphasize the concept of matching keys. Show visual Venn diagrams to explain INNER, LEFT, RIGHT joins.

Parent Tips

Help your child find examples of tables at home (e.g., family members and birthdays). Ask: "How would you connect these?"

Interesting Facts

MySQL was first released in 1995.
JOINs are used by almost every application that uses databases.
The concept of joins comes from set theory in mathematics.

Did You Know?

MySQL is used by many Nigerian companies like Flutterwave and Paystack to manage their data.

Remember This

INNER JOIN = only matches.
LEFT JOIN = all left + matches.
RIGHT JOIN = all right + matches.
Always specify the join condition.

Common Mistakes

Forgetting the ON clause.
Joining on wrong columns.
Confusing LEFT and RIGHT.
Not using table aliases for readability.

Best Practices

Use table aliases (e.g., s for students).
Always specify the join condition with ON.
Test with small data first.
Use appropriate join type.

Illustrations

Join Flowchart

  Start
    |
    V
  Choose Tables
    |
    V
  Identify Key
    |
    V
  Write JOIN Query
    |
    V
  Run and Check
    |
    V
  Finish

Table Example: Students and Grades

Student ID	Name	Grade
1	Chidi	A
2	Amina	B

Comparison Table: JOIN Types

JOIN Type	Returns
INNER JOIN	Only matching rows
LEFT JOIN	All rows from left, matches from right
RIGHT JOIN	All rows from right, matches from left
FULL JOIN	All rows from both

End-of-Module Summary

In this module, you learned how to combine tables using JOINs. You know about INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. You can now answer questions that need data from multiple tables. You're becoming a true data analysis expert!

Frequently Asked Questions

What is a JOIN? – Combining tables.
What is INNER JOIN? – Only matching rows.
What is LEFT JOIN? – All rows from left.
What is RIGHT JOIN? – All rows from right.
What is a key? – A matching column.
What is a primary key? – Unique ID.
What is a foreign key? – Link to another table.
Can we join more than two tables? – Yes.
Can we filter after JOIN? – Yes, with WHERE.
Can we sort after JOIN? – Yes, with ORDER BY.

Review Questions

What is a JOIN?
What does INNER JOIN return?
What does LEFT JOIN return?
What does RIGHT JOIN return?
What is a key?
What is a primary key?
What is a foreign key?
Can you filter a JOIN with WHERE?
Can you sort a JOIN with ORDER BY?
Can you group a JOIN with GROUP BY?
How do you join three tables?
What is NULL?
What is the difference between INNER and LEFT JOIN?
Give an example of a real-life JOIN.
Why are JOINs useful?

Fill-in-the-Blank

_____ JOIN returns only matching rows.
_____ JOIN keeps all rows from the left table.
The common column used to join is called a _____ .
_____ key uniquely identifies a row.
You use _____ after JOIN to filter.

True or False

INNER JOIN returns all rows from both tables. (False)
LEFT JOIN keeps all rows from the left table. (True)
RIGHT JOIN keeps all rows from the right table. (True)
A primary key can be NULL. (False)
You can join more than two tables. (True)

Multiple Choice

Which JOIN returns only matching rows? A) LEFT B) INNER C) RIGHT (Answer: B)
Which JOIN keeps all rows from the left? A) LEFT B) INNER C) RIGHT (Answer: A)
Which JOIN keeps all rows from the right? A) LEFT B) INNER C) RIGHT (Answer: C)
What is a primary key? A) A unique ID B) A foreign key C) A join (Answer: A)
What is a foreign key? A) A unique ID B) A link to another table C) A join (Answer: B)
Can you use WHERE with a JOIN? A) Yes B) No (Answer: A)
Can you use ORDER BY with a JOIN? A) Yes B) No (Answer: A)
Can you use GROUP BY with a JOIN? A) Yes B) No (Answer: A)
How many tables can you join? A) 2 B) Many C) 1 (Answer: B)
What does NULL mean? A) Zero B) No data C) A key (Answer: B)
Which join is most common? A) LEFT B) INNER C) RIGHT (Answer: B)
What is the ON clause used for? A) Filtering B) Specifying the join condition C) Sorting (Answer: B)
Which join is also called a full outer join? A) LEFT B) RIGHT C) FULL (Answer: C)
Can you join tables without a common column? A) Yes B) No (Answer: B)
Which keyword is used for joining? A) LINK B) JOIN C) CONNECT (Answer: B)

Matching

Term	Definition
INNER JOIN	Only matching rows
LEFT JOIN	All left, matches from right
RIGHT JOIN	All right, matches from left
Primary Key	Unique ID
Foreign Key	Link to another table

Short Answer

Write an INNER JOIN query between students and grades.
What is the difference between LEFT and INNER JOIN?
Why do we need keys for joining?

Scenario-based

Scenario: You have tables: Customers (id, name) and Orders (order_id, customer_id, amount). Write a query to list customer names and their total order amount.

Answer: SELECT c.name, SUM(o.amount) FROM Customers c JOIN Orders o ON c.id = o.customer_id GROUP BY c.name;

Group Activity

In groups, create two tables: one for 'Students' and one for 'Sports'. Each student has a sport_id. Write a JOIN to list each student and their sport.

Individual Activity

Create tables: 'Books' (id, title) and 'Authors' (id, name, book_id). Write a query using INNER JOIN to show book titles and author names.

Classroom Discussion

Why is it better to split data into multiple tables?
When would you use LEFT JOIN instead of INNER JOIN?
Can you think of a situation where you need three tables?

Mini Project

Build a small 'School Library' database with tables: Books, Students, and Loans. Write JOIN queries to show which student borrowed which book.

Practical Assignment

Download a sample database with at least 3 tables. Write 5 different JOIN queries (INNER, LEFT, RIGHT, multiple tables, with WHERE/GROUP BY).

Challenge Exercise

Write a query that joins Customers, Orders, and Products to find the total amount spent by each customer on a specific product category.

Quiz Answers (Multiple Choice)

1B, 2A, 3C, 4A, 5B, 6A, 7A, 8A, 9B, 10B, 11B, 12B, 13C, 14B, 15B

Key Takeaways

JOINs combine tables using keys.
INNER JOIN shows only matches.
LEFT JOIN keeps all from left.
RIGHT JOIN keeps all from right.
You can filter, sort, and group JOIN results.
Practice is key to mastering JOINs.

Preparation for Module 11

In the next module, we will learn about advanced topics like subqueries and views. Review your JOIN skills – they will be very useful!

Excellent work! You've completed Module 10. You're now ready for more advanced data analysis.

Module Eleven

Module 11: MySQL for Data Analysis Expert – Subqueries & Views

Hello, super analyst! In this module, we will learn two powerful tools: Subqueries and Views. Subqueries are like asking a question inside another question. Views are like saving a query so you can use it again and again. These make your data analysis even smarter and faster. Let's become masters of MySQL!

Learning Objectives

Understand what a subquery is and when to use it.
Write subqueries in WHERE, FROM, and SELECT clauses.
Learn about views – saved queries.
Create views to simplify complex queries.
Use views to protect data and make reports easier.
Think like a pro data analyst.

Warm-up Story: The Treasure Hunt Map 🗺️

Imagine you are on a treasure hunt. You have a map that shows where treasure is buried. But to find the treasure, you first need to find the biggest tree. So you look for the biggest tree, then dig under it. That is like a subquery – you do one search first, then use that result for the main search. Now imagine you draw a new map that shows all treasure spots. That map is like a view – it's a saved version of your search. Let's explore!

Main Lessons

Lesson 1: What is a Subquery?

Definition: A subquery is a query inside another query.

Why it's important: It lets us use the result of one query as a condition for another.

Simple explanation: It's like asking: "Find the tallest student, then show all students taller than that."

Real-life example: Find the store that sells the most cookies, then list all cookies sold there.

School example: Find the student with the highest score, then show everyone who scored more than that.

Home example: Find the room with the most toys, then list all toys in that room.

Nigerian example: Find the market with the most yam sellers, then list all yam sellers in that market.

Illustration:

  Main Query: SELECT * FROM students WHERE score > (subquery)
  Subquery: SELECT AVG(score) FROM students

Mini summary: A subquery is a query inside another query.

Lesson 2: Subquery in WHERE – Filtering with Another Query

Definition: We put a subquery in the WHERE clause to filter based on another result.

Why it's important: It allows dynamic filtering.

Simple explanation: You find the average score first, then show students above average.

Real-life example: Show products that cost more than the average price.

School example: Show students who scored above the class average.

Home example: Show toys that are heavier than the average weight.

Nigerian example: Show shops that sell more than the average number of goods.

Illustration:

  SELECT name FROM students
  WHERE score > (SELECT AVG(score) FROM students);

Mini summary: WHERE + subquery = dynamic filter.

Lesson 3: Subquery in FROM – Using a Query as a Table

Definition: You can use a subquery in the FROM clause, treating it like a temporary table.

Why it's important: It lets you do multiple steps in one query.

Simple explanation: You make a small table with a query, then you query that table.

Real-life example: First, find all orders above 100 Naira, then count them.

School example: Find all students who passed, then average their scores.

Home example: Find all chores that take more than 10 minutes, then sum the time.

Nigerian example: Find all stores with sales above 5000, then count them.

Illustration:

  SELECT COUNT(*) FROM (SELECT * FROM orders WHERE amount > 100) AS big_orders;

Mini summary: FROM subquery = temporary table.

Lesson 4: Subquery in SELECT – Using a Value from Another Query

Definition: We can put a subquery in the SELECT part to get a single value.

Why it's important: It adds extra information to each row.

Simple explanation: Show each student's score and also show the class average next to it.

Real-life example: Show each product price and the average price of all products.

School example: Show each student's score and the class average.

Home example: Show each chore time and the average chore time.

Nigerian example: Show each shop's sales and the average sales.

Illustration:

  SELECT name, score, (SELECT AVG(score) FROM students) AS avg_score
  FROM students;

Mini summary: SELECT subquery adds a calculated column.

Lesson 5: Subquery with IN – Checking Membership

Definition: Use IN with a subquery to check if a value is in a list.

Why it's important: It's useful when you need to match multiple values.

Simple explanation: Show students who are in the list of top scores.

Real-life example: Show customers who ordered items in the 'electronics' category.

School example: Show students who are in the list of honor roll students.

Home example: Show family members whose birthdays are in the list of summer birthdays.

Nigerian example: Show markets that sell items in the 'food' category.

Illustration:

  SELECT name FROM students
  WHERE id IN (SELECT student_id FROM honors);

Mini summary: IN checks if a value is in a subquery list.

Lesson 6: Subquery with EXISTS – Testing for Existence

Definition: EXISTS checks if the subquery returns any rows.

Why it's important: It's efficient for checking existence without returning data.

Simple explanation: Show all students who have at least one grade.

Real-life example: Show all customers who have placed at least one order.

School example: Show all teachers who have at least one class.

Home example: Show all rooms that have at least one toy.

Nigerian example: Show all states that have at least one market.

Illustration:

  SELECT name FROM students s
  WHERE EXISTS (SELECT 1 FROM grades g WHERE s.id = g.student_id);

Mini summary: EXISTS checks for at least one row.

Lesson 7: What is a View?

Definition: A view is a saved query that acts like a virtual table.

Why it's important: It makes complex queries easy to reuse.

Simple explanation: You write a query once, save it as a view, then you can SELECT from it like a table.

Real-life example: A view called 'top_students' that shows students with scores above 90.

School example: A view for 'attendance_summary' that shows daily attendance counts.

Home example: A view for 'chore_list' that shows all pending chores.

Nigerian example: A view for 'market_sales' that shows total sales per market.

Illustration:

  CREATE VIEW top_students AS
  SELECT * FROM students WHERE score > 90;
  -- Now you can: SELECT * FROM top_students;

Mini summary: A view is a saved query.

Lesson 8: Creating a View – Step by Step

Definition: Use CREATE VIEW view_name AS query;

Why it's important: It saves time and reduces errors.

Simple explanation: Write CREATE VIEW, give it a name, then AS and your SELECT query.

Real-life example: CREATE VIEW high_value_orders AS SELECT * FROM orders WHERE amount > 1000;

School example: CREATE VIEW failing_students AS SELECT * FROM students WHERE score < 40;

Home example: CREATE VIEW expensive_toys AS SELECT * FROM toys WHERE price > 500;

Nigerian example: CREATE VIEW big_markets AS SELECT * FROM markets WHERE size > 1000;

Illustration:

  CREATE VIEW my_view AS
  SELECT id, name FROM students;

Mini summary: CREATE VIEW saves your query.

Lesson 9: Using a View – Just Like a Table

Definition: After creating a view, you can SELECT, JOIN, and filter it.

Why it's important: Views simplify complex queries.

Simple explanation: You can use a view in the same way as a normal table.

Real-life example: SELECT * FROM high_value_orders WHERE date > '2025-01-01';

School example: SELECT * FROM failing_students ORDER BY score;

Home example: SELECT * FROM expensive_toys WHERE color = 'red';

Nigerian example: SELECT * FROM big_markets WHERE city = 'Lagos';

Illustration:

  -- View already created
  SELECT * FROM top_students WHERE class = '5A';

Mini summary: Use a view like a table.

Lesson 10: Dropping a View – Deleting It

Definition: Use DROP VIEW view_name; to delete a view.

Why it's important: To remove views you no longer need.

Simple explanation: If you don't need the view anymore, you can drop it.

Real-life example: DROP VIEW old_sales_report;

School example: DROP VIEW old_attendance;

Home example: DROP VIEW old_chores;

Nigerian example: DROP VIEW old_market_data;

Illustration:

  DROP VIEW my_view;

Mini summary: DROP VIEW deletes a view.

Lesson 11: Advantages of Views

Definition: Views simplify, secure, and organize data.

Why it's important: They make analysis easier.

Simple explanation: Views are like shortcuts for complex queries.

Real-life example: A view for 'active_customers' hides inactive ones.

School example: A view for 'passing_students' shows only those who passed.

Home example: A view for 'weekly_chores' shows only this week's tasks.

Nigerian example: A view for 'top_selling_items' shows best sellers.

Illustration:

  Advantages:
  1. Simplicity
  2. Security (hide columns)
  3. Reusability

Mini summary: Views save time and make things safe.

Lesson 12: Nigerian Example – Bank Reports

A bank in Nigeria uses views to create daily summaries. For instance, CREATE VIEW daily_transactions AS SELECT * FROM transactions WHERE date = CURDATE(); This helps the manager see today's activities easily.

Lesson 13: Fun Example – Superhero View

CREATE VIEW powerful_heroes AS SELECT * FROM heroes WHERE power_level > 80; Then you can query: SELECT * FROM powerful_heroes WHERE team = 'Avengers';

Key Vocabulary

Subquery: A query inside another query.
View: A saved query that acts like a table.
IN: Checks if a value is in a list.
EXISTS: Checks if any rows exist.
CREATE VIEW: Command to make a view.
DROP VIEW: Command to delete a view.

Important Concepts

1. Subquery Types: In WHERE, FROM, SELECT, with IN/EXISTS.

2. View vs Table: Views do not store data, they just show data from tables.

3. Materialized Views: (Not in MySQL) store data physically.

Step-by-Step Explanations

Step 1: Write the inner query first (subquery).

Step 2: Test the subquery alone to ensure it works.

Step 3: Place the subquery in the main query.

Step 4: For views, write the SELECT query.

Step 5: Use CREATE VIEW view_name AS query.

Step 6: Query the view like a table.

Real-life Examples

E-commerce: find products above average price.
School: show students who are in the honor roll.
Bank: show accounts with balance above a threshold.

Nigerian Examples

Market: view for 'expensive goods' above 5000 Naira.
Transport: view for 'busy routes' with many passengers.
Agriculture: view for 'high yield farms'.

Fun Examples

Subquery: find the game with highest score, then list all games with lower score.
View: create view 'my_favourite_toys' and use it.

Everyday Examples

Using a view to show today's weather forecast.
Subquery to find the most popular movie.

Teacher Notes

Demonstrate subqueries step by step. Show the inner query first, then the outer. Use simple data. Explain that views are like bookmarks for queries.

Parent Tips

Encourage your child to create views for their chores or toy lists. Show them how a view can save time.

Interesting Facts

Subqueries are also called nested queries.
Views can be used to restrict access to sensitive data.
Some databases allow updating through views.

Did You Know?

MySQL views are updatable if they meet certain conditions.

Remember This

Subqueries go inside parentheses.
Views are saved queries.
Use IN for lists, EXISTS for existence.
Views can be used like tables.

Common Mistakes

Forgetting parentheses around subquery.
Subquery returns more than one row when expecting one.
Creating a view with a complex query that changes.
Not testing the subquery alone first.

Best Practices

Always test subqueries separately.
Use meaningful names for views.
Document why you created a view.
Keep views simple if possible.

Illustrations

Subquery Flowchart

  Start
    |
    V
  Write Subquery (inner)
    |
    V
  Test Subquery
    |
    V
  Write Main Query
    |
    V
  Run Main Query
    |
    V
  Finish

View Creation Flowchart

  Write Query
    |
    V
  CREATE VIEW name AS query
    |
    V
  Save View
    |
    V
  Use View like a table

Table Example: Students and Subquery Result

Name	Score	Above Average?
Chidi	90	Yes
Amina	70	No

Comparison Table: Subquery in Different Clauses

Clause	Purpose	Example
WHERE	Filter based on subquery	WHERE score > (SELECT AVG(...))
FROM	Use subquery as a table	FROM (SELECT ...) AS t
SELECT	Add a calculated column	SELECT ..., (SELECT ...) AS col
IN	Check membership	WHERE id IN (SELECT ...)
EXISTS	Check existence	WHERE EXISTS (SELECT ...)

End-of-Module Summary

In this module, you learned about subqueries – queries inside other queries – and views – saved queries. You can now use subqueries to filter, create temporary tables, and add extra columns. You can also create views to simplify complex queries and reuse them. These are powerful tools that make you a true data analysis expert!

Frequently Asked Questions

What is a subquery? – A query inside another query.
What is a view? – A saved query that acts like a table.
Where can I use a subquery? – In WHERE, FROM, SELECT, IN, EXISTS.
Can I use a view like a table? – Yes.
Do views store data? – No, they show data from tables.
How do I create a view? – CREATE VIEW name AS query.
How do I delete a view? – DROP VIEW name.
What is IN with subquery? – Checks if a value is in the subquery result.
What is EXISTS? – Checks if the subquery returns any rows.
Why use views? – To simplify and secure data access.

Review Questions

What is a subquery?
Where can a subquery be placed?
What is a view?
How do you create a view?
How do you delete a view?
What does IN do in a subquery?
What does EXISTS do?
Can a view be used in a JOIN?
Give an example of a subquery in WHERE.
Give an example of a subquery in FROM.
Give an example of a subquery in SELECT.
Why are views useful?
Do views store data permanently?
What is the syntax for CREATE VIEW?
What is the syntax for DROP VIEW?

Fill-in-the-Blank

A _______ is a query inside another query.
CREATE VIEW view_name _______ query;
_______ checks if a value is in a list from a subquery.
_______ checks if a subquery returns any rows.
A view is like a _______ table.

True or False

Subqueries can only be used in WHERE. (False)
A view stores data physically. (False)
IN can be used with a subquery. (True)
EXISTS returns data from the subquery. (False)
You can SELECT from a view. (True)

Multiple Choice

A subquery is: A) a query inside a query B) a table C) a view (Answer: A)
A view is: A) a saved query B) a table C) a database (Answer: A)
Which clause can have a subquery? A) WHERE B) FROM C) both (Answer: C)
Which command creates a view? A) MAKE VIEW B) CREATE VIEW C) NEW VIEW (Answer: B)
Which command deletes a view? A) DELETE VIEW B) DROP VIEW C) REMOVE VIEW (Answer: B)
IN with a subquery checks: A) existence B) membership C) average (Answer: B)
EXISTS checks: A) if rows exist B) if values match C) if sum is correct (Answer: A)
Can a view be updated? A) Sometimes B) Never C) Always (Answer: A)
A subquery in FROM acts like: A) a table B) a column C) a filter (Answer: A)
A subquery in SELECT returns: A) a single value B) many rows C) a table (Answer: A)
Which is NOT a place for a subquery? A) WHERE B) HAVING C) ORDER BY (Answer: C)
What does CREATE VIEW do? A) saves a query B) creates a table C) deletes data (Answer: A)
What does DROP VIEW do? A) deletes a view B) deletes a table C) deletes data (Answer: A)
Is a view a physical table? A) Yes B) No C) Sometimes (Answer: B)
Can you join a view with another table? A) Yes B) No (Answer: A)

Matching

Term	Definition
Subquery	Query inside another query
View	Saved query
IN	Membership check
EXISTS	Existence check
CREATE VIEW	Makes a view

Short Answer

Write a subquery that finds the average score from students.
Create a view named 'passing_students' that shows students with score >= 50.
What is the difference between IN and EXISTS?

Scenario-based

Scenario: You have a table 'products' with columns id, name, price. Write a query using a subquery to find products that cost more than the average price.

Answer: SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

Group Activity

In groups, create a view for a 'library' that shows books that are currently borrowed. Use a subquery to find books that are not returned.

Individual Activity

Create a view called 'my_friends' from a 'people' table where age > 10. Then write a SELECT query on the view.

Classroom Discussion

When would you use a subquery instead of a JOIN?
How can views help protect sensitive data?
What are some real-world uses of views?

Mini Project

Create a small database for a school with tables: Students, Classes, Grades. Create a view that shows each student's name, class, and average grade. Write a subquery to find students above the class average.

Practical Assignment

Write 3 subqueries (in WHERE, FROM, SELECT) and 2 views on a sample database. Test them and explain what they do.

Challenge Exercise

Write a query that uses a subquery to find the top 3 most expensive products, and then create a view for those products.

Quiz Answers (Multiple Choice)

1A, 2A, 3C, 4B, 5B, 6B, 7A, 8A, 9A, 10A, 11C, 12A, 13A, 14B, 15A

Key Takeaways

Subqueries are queries inside queries.
They can be used in WHERE, FROM, SELECT, IN, EXISTS.
Views are saved queries that act like tables.
Views simplify complex queries and enhance security.
Practice is essential to master these tools.

Preparation for Module 12

In the next module, we will dive into advanced SQL functions and performance tuning. You'll learn to make your queries even faster and more efficient. Review your subquery and view skills!

Fantastic work! You've completed Module 11. You are now an expert in subqueries and views. Keep practicing!

Module Thirteen

Module 12: MySQL for Data Analysis Expert – Advanced Functions & Performance

Welcome, data champion! In this final module, we will learn how to make our queries smarter and faster. We will explore advanced functions like string, date, and math functions. We will also learn about indexing and query optimization to make MySQL run like a cheetah! By the end, you will be a true MySQL expert. Let's go!

Learning Objectives

Use string functions to change text.
Work with dates and times.
Perform math calculations.
Understand what indexes are and why they speed up queries.
Learn simple tips to make queries faster.
Think like a performance expert.

Warm-up Story: The Race Against Time ⏱️

Imagine you are in a race. You want to run as fast as possible. To run faster, you wear special shoes and practice a lot. In MySQL, we have functions that are like special shoes – they help us do things quickly. And we have indexes that are like a shortcut on the track – they help MySQL find data faster. Today, we learn to make our MySQL queries super-fast!

Main Lessons

Lesson 1: String Functions – Playing with Text

Definition: String functions let us change, cut, or combine text.

Why it's important: We often need to clean or format names, addresses, or messages.

Simple explanation: It's like using scissors to cut paper, or glue to stick pieces together.

Real-life example: Change 'john doe' to 'John Doe' (capitalize).

School example: Combine first name and last name into full name.

Home example: Change 'chores' to 'CHORES' (uppercase).

Nigerian example: Format phone numbers to a standard pattern.

Illustration:

  UPPER('hello') → 'HELLO'
  LOWER('WORLD') → 'world'
  CONCAT('John', ' ', 'Doe') → 'John Doe'
  SUBSTRING('Hello', 2, 3) → 'ell'

Mini summary: String functions change text.

Lesson 2: Date Functions – Working with Time

Definition: Date functions help us work with dates and times.

Why it's important: We often need to find today's date, or days between two dates.

Simple explanation: It's like a calendar that can calculate for you.

Real-life example: Find orders placed in the last 7 days.

School example: Find students whose birthdays are this month.

Home example: Count days until your next holiday.

Nigerian example: Find transactions made in the last month.

Illustration:

  CURDATE() → '2026-07-04'
  NOW() → '2026-07-04 10:30:00'
  DATEDIFF('2026-07-10', '2026-07-04') → 6
  DATE_ADD(CURDATE(), INTERVAL 7 DAY) → '2026-07-11'

Mini summary: Date functions handle time.

Lesson 3: Math Functions – Number Power

Definition: Math functions do calculations like rounding, absolute value, and square root.

Why it's important: We often need to round prices or find averages.

Simple explanation: It's like a calculator built into MySQL.

Real-life example: Round prices to 2 decimal places.

School example: Find the square root of scores.

Home example: Calculate discount percentages.

Nigerian example: Round exchange rates.

Illustration:

  ROUND(4.567, 2) → 4.57
  CEIL(4.1) → 5
  FLOOR(4.9) → 4
  ABS(-5) → 5
  SQRT(16) → 4

Mini summary: Math functions do number tricks.

Lesson 4: Conditional Functions – IF and CASE

Definition: Conditional functions let us make decisions in queries.

Why it's important: We can create new columns based on conditions.

Simple explanation: It's like saying: "If it rains, take an umbrella; else, don't."

Real-life example: Label orders as 'High' if amount > 1000, else 'Low'.

School example: Pass/Fail based on score.

Home example: 'Expensive' if toy price > 500, else 'Cheap'.

Nigerian example: 'Rich' if salary > 500000, else 'Not rich'.

Illustration:

  IF(score >= 50, 'Pass', 'Fail')
  CASE 
    WHEN score >= 80 THEN 'A'
    WHEN score >= 60 THEN 'B'
    ELSE 'C'
  END

Mini summary: Conditional functions make decisions.

Lesson 5: What is an Index?

Definition: An index is like a book's index – it helps MySQL find data quickly.

Why it's important: It speeds up searches and sorting.

Simple explanation: Imagine a library without a catalog – you'd have to search every book. An index is the catalog.

Real-life example: A phone book is indexed by last name.

School example: A class roster ordered by name.

Home example: A recipe book with a list of recipes at the front.

Nigerian example: A market directory listing shops by category.

Illustration:

  Without Index: Search every row → slow
  With Index: Go directly to the row → fast

Mini summary: Indexes speed up searches.

Lesson 6: Creating an Index

Definition: Use CREATE INDEX to add an index on a column.

Why it's important: It makes SELECT queries faster.

Simple explanation: Tell MySQL: "Keep a quick list for this column."

Real-life example: CREATE INDEX idx_lastname ON customers(last_name);

School example: CREATE INDEX idx_score ON students(score);

Home example: CREATE INDEX idx_toy_name ON toys(name);

Nigerian example: CREATE INDEX idx_market_name ON markets(name);

Illustration:

  CREATE INDEX idx_name ON table(column);

Mini summary: CREATE INDEX adds a speed-booster.

Lesson 7: Dropping an Index – Removing It

Definition: Use DROP INDEX to remove an index.

Why it's important: If you don't need it anymore, you can free up space.

Simple explanation: If you finish reading a book, you can close the index.

Real-life example: DROP INDEX idx_lastname ON customers;

School example: DROP INDEX idx_score ON students;

Home example: DROP INDEX idx_toy_name ON toys;

Nigerian example: DROP INDEX idx_market_name ON markets;

Illustration:

  DROP INDEX idx_name ON table;

Mini summary: DROP INDEX removes a speed-booster.

Lesson 8: Query Optimization – Making Queries Fast

Definition: Optimization means writing queries that run quickly.

Why it's important: Fast queries save time and resources.

Simple explanation: It's like choosing the shortest path to school.

Real-life example: Use WHERE to filter early, not later.

School example: Select only needed columns, not *.

Home example: Only search for the toy you want, not all.

Nigerian example: Query only Lagos customers if that's what you need.

Illustration:

  Slow: SELECT * FROM huge_table;
  Fast: SELECT id, name FROM huge_table WHERE city = 'Lagos';

Mini summary: Optimization speeds up queries.

Lesson 9: Using EXPLAIN to See the Plan

Definition: EXPLAIN shows how MySQL executes a query.

Why it's important: It helps you see if the query uses indexes.

Simple explanation: It's like seeing the map of your route.

Real-life example: EXPLAIN SELECT * FROM students WHERE id = 5;

School example: Use EXPLAIN to check if index is used.

Home example: See how MySQL finds your toy.

Nigerian example: Check if market search uses index.

Illustration:

  EXPLAIN SELECT * FROM students WHERE id = 5;

Mini summary: EXPLAIN shows the execution plan.

Lesson 10: Avoiding Common Slow Queries

Definition: Some queries are naturally slow; we avoid them.

Why it's important: To keep our database happy and fast.

Simple explanation: Don't ask for everything if you only need a little.

Real-life example: Avoid SELECT * from a huge table.

School example: Don't use functions on indexed columns in WHERE.

Home example: Don't search for toys by color if there's no index.

Nigerian example: Don't search by name if you can search by ID.

Illustration:

  Bad: SELECT * FROM orders WHERE YEAR(date) = 2026;
  Good: SELECT * FROM orders WHERE date >= '2026-01-01' AND date < '2027-01-01';

Mini summary: Write smart queries to avoid slowness.

Lesson 11: Nigerian Example – Bank Performance

A Nigerian bank has millions of transactions. They use indexes on transaction_id and account_number. They use EXPLAIN to ensure queries are fast. They avoid SELECT * and use only needed columns.

Lesson 12: Fun Example – Game Scores

You have a game database with millions of scores. You use an index on the 'score' column to get top players quickly. You use DATE functions to find this week's top players.

Key Vocabulary

String: Text.
Function: A tool that does a job.
Index: A speed-up list.
Optimization: Making things faster.
EXPLAIN: Shows the execution plan.
Query: A question to the database.

Important Concepts

1. Index Types: PRIMARY KEY, UNIQUE, INDEX, FULLTEXT.

2. Composite Index: Index on multiple columns.

3. Query Cache: Store results for reuse (older MySQL).

Step-by-Step Explanations

Step 1: Identify slow queries using performance logs.

Step 2: Use EXPLAIN to analyze them.

Step 3: Add indexes on columns used in WHERE, JOIN, ORDER BY.

Step 4: Rewrite queries to avoid unnecessary work.

Step 5: Test and measure the improvement.

Real-life Examples

Amazon uses indexes to find products quickly.
Facebook uses indexes for user searches.
Schools use indexes to find student records by ID.

Nigerian Examples

Jumia uses indexes for product searches.
GTBank uses indexes for transaction history.
ChannelsTV uses date functions for news archives.

Fun Examples

String function: change 'robot' to 'ROBOT'.
Date function: find days until your birthday.
Math function: round your pocket money to nearest 10.

Everyday Examples

Using upper/lower case when searching for names.
Calculating age from birth date.
Calculating discount price.

Teacher Notes

Demonstrate each function with small datasets. Show the difference in speed with and without indexes using a large dataset. Explain that optimization is an ongoing process.

Parent Tips

Encourage your child to think about speed: "How can we find something faster?" Discuss real-world examples like searching a phone book.

Interesting Facts

Indexes can make queries hundreds of times faster.
MySQL uses B-tree data structures for indexes.
Too many indexes can slow down INSERT and UPDATE.

Did You Know?

MySQL has a query optimizer that automatically chooses the best way to run a query, but you can help it with good indexes and structure.

Remember This

Use indexes on columns used in WHERE, JOIN, ORDER BY.
Avoid SELECT *.
Use functions wisely.
EXPLAIN is your friend.

Common Mistakes

Creating too many indexes.
Forgetting to use indexes for JOINs.
Using functions in WHERE on indexed columns.
Not analyzing slow queries.

Best Practices

Index columns that are used frequently.
Keep indexes as small as possible.
Use EXPLAIN regularly.
Monitor query performance.

Illustrations

Indexing Flowchart

  Need Fast Search
        |
        V
  CREATE INDEX
        |
        V
  Query Uses Index
        |
        V
  Fast Results

Query Optimization Flowchart

  Write Query
        |
        V
  Use EXPLAIN
        |
        V
  Add Index/ Rewrite
        |
        V
  Test Speed
        |
        V
  Done

Table Example: Functions

Input	Function	Output
'hello'	UPPER	'HELLO'
'WORLD'	LOWER	'world'
'John', 'Doe'	CONCAT	'John Doe'

Comparison Table: With and Without Index

Scenario	Without Index	With Index
Search for ID=1000	Scans 1,000,000 rows	Scans 1 row
Time	1 second	0.001 second

End-of-Module Summary

In this module, we learned advanced functions for text, dates, math, and decision-making. We also learned about indexes – powerful tools that make queries fast. We discovered optimization techniques and how to use EXPLAIN. With these skills, you can write efficient, lightning-fast queries. You are now a complete MySQL data analysis expert!

Frequently Asked Questions

What is a string function? – It changes text.
What is a date function? – It works with dates.
What is an index? – It speeds up searches.
How do I create an index? – CREATE INDEX.
How do I remove an index? – DROP INDEX.
What is EXPLAIN? – Shows the query plan.
What is query optimization? – Making queries fast.
Why avoid SELECT *? – It takes more time.
Can indexes slow things down? – Yes, on INSERT/UPDATE.
What is a conditional function? – It makes decisions (IF, CASE).

Review Questions

What does UPPER do?
What does CONCAT do?
What does CURDATE() return?
What does DATEDIFF do?
What does ROUND do?
What does ABS do?
What is an index?
How do you create an index?
How do you drop an index?
What is EXPLAIN used for?
Why is SELECT * considered bad?
What is a conditional function?
Give an example of IF.
Give an example of CASE.
Why is optimization important?

Fill-in-the-Blank

UPPER('hello') returns ______.
CONCAT('a', 'b') returns ______.
______ returns today's date.
CREATE INDEX is used to add an ______.
EXPLAIN shows the ______ plan.

True or False

UPPER makes text lowercase. (False)
DATEDIFF calculates difference in days. (True)
Indexes always make queries faster. (False – they can slow writes)
EXPLAIN shows how a query runs. (True)
SELECT * is always the best choice. (False)

Multiple Choice

Which function makes text uppercase? A) UPPER B) LOWER C) CONCAT (Answer: A)
Which function combines text? A) UPPER B) LOWER C) CONCAT (Answer: C)
Which function returns today's date? A) NOW() B) CURDATE() C) DATE() (Answer: B)
Which function rounds numbers? A) ROUND B) FLOOR C) CEIL (Answer: A)
What is an index? A) A speed-up B) A table C) A query (Answer: A)
How to create an index? A) CREATE INDEX B) MAKE INDEX C) NEW INDEX (Answer: A)
How to drop an index? A) DELETE INDEX B) DROP INDEX C) REMOVE INDEX (Answer: B)
What does EXPLAIN do? A) Shows plan B) Runs query C) Deletes data (Answer: A)
Which is faster? A) SELECT * B) SELECT id, name (Answer: B)
IF function is: A) conditional B) string C) math (Answer: A)
CASE is: A) conditional B) string C) date (Answer: A)
Which is a date function? A) UPPER B) DATEDIFF C) ROUND (Answer: B)
Which is a math function? A) CONCAT B) SQRT C) CURDATE (Answer: B)
Which is a string function? A) ABS B) SUBSTRING C) CEIL (Answer: B)
Indexes are best used on columns in: A) WHERE B) SELECT C) VALUES (Answer: A)

Matching

Function	Purpose
UPPER	Make text uppercase
DATEDIFF	Difference in days
ROUND	Round a number
IF	Conditional check
INDEX	Speed up searches

Short Answer

Write a query to show today's date.
Write a query to change 'hello' to 'HELLO'.
How do you create an index on the 'name' column of 'students' table?

Scenario-based

Scenario: You have a 'sales' table with millions of rows. You often query by 'sale_date'. What can you do to make this faster?

Answer: Create an index on 'sale_date'. Also, avoid using functions like YEAR(sale_date) in WHERE.

Group Activity

In groups, take a large dataset (or create one). Write a slow query, use EXPLAIN, add an index, and measure the speed difference.

Individual Activity

Create a table with 10,000 rows. Write a SELECT with a WHERE condition. Add an index and observe the speed improvement.

Classroom Discussion

Why is it important to have fast queries?
What are the trade-offs of using indexes?
How can we identify slow queries?

Mini Project

Create a database for a library with tables: Books, Members, Loans. Add indexes on frequently searched columns. Write a report that uses string and date functions.

Practical Assignment

Write 5 queries using string, date, math, and conditional functions. Add indexes to optimize them. Use EXPLAIN to verify.

Challenge Exercise

Write a query that finds the top 5 customers by total spending in the last month. Optimize it for speed using indexes and proper functions.

Quiz Answers (Multiple Choice)

1A, 2C, 3B, 4A, 5A, 6A, 7B, 8A, 9B, 10A, 11A, 12B, 13B, 14B, 15A

Key Takeaways

Functions help manipulate data.
Indexes speed up queries dramatically.
Optimization is about writing efficient queries.
EXPLAIN helps us understand and improve.
Balance is key – too many indexes hurt writes.

Preparation for Module 13 (or Course Wrap-up)

Congratulations! You have completed all modules. You are now a MySQL Data Analysis Expert. Review all modules and practice. Build your own projects. The world of data is yours to explore!

🎉 Outstanding work! You've reached the end of this course. Keep analyzing, keep questioning, and keep having fun with data!

Module Fourteen

Module 14: MySQL for Data Analysis Expert – Advanced Topics & Next Steps

Hello, data champion! You have already learned so much about MySQL. But the world of data is very big. In this final bonus module, we will explore some advanced topics like stored procedures, triggers, and transactions. We will also talk about what to learn next. This module is like the final level of a video game – it gives you superpowers!

Learning Objectives

Understand what stored procedures are and why they are useful.
Learn about triggers that automatically run when data changes.
Discover transactions – a way to group changes together.
Explore other tools like Python and visualization.
Create a plan for your continued learning.
Feel confident to take on bigger challenges.

Warm-up Story: The Superpowered Database 🦸

Imagine your MySQL database is a superhero headquarters. You have heroes (tables) and gadgets (queries). But sometimes you need a superpower that can do a series of actions automatically. That's where stored procedures and triggers come in – they are like superhero sidekicks that work behind the scenes. And transactions are like a safety net – if something goes wrong, you can undo everything. Let's unlock these superpowers!

Main Lessons

Lesson 1: What is a Stored Procedure?

Definition: A stored procedure is a set of SQL statements that you save and run later.

Why it's important: It helps you reuse code and make complex tasks simple.

Simple explanation: It's like a recipe – you write it once and use it many times.

Real-life example: A bank uses a stored procedure to calculate monthly interest for all accounts.

School example: A teacher saves a procedure to generate report cards.

Home example: You save a procedure to calculate your weekly allowance.

Nigerian example: A supermarket uses a procedure to update inventory after each sale.

Illustration:

  CREATE PROCEDURE GetHighScores()
  BEGIN
    SELECT * FROM students WHERE score > 80;
  END;
  -- Call it: CALL GetHighScores();

Mini summary: Stored procedures are saved recipes for queries.

Lesson 2: Creating a Stored Procedure

Definition: Use CREATE PROCEDURE to make one.

Why it's important: It saves time and reduces errors.

Simple explanation: You write the steps inside BEGIN and END.

Real-life example: CREATE PROCEDURE AddCustomer(name, phone) ...

School example: CREATE PROCEDURE AddStudent(name, class) ...

Home example: CREATE PROCEDURE AddChore(name, time) ...

Nigerian example: CREATE PROCEDURE AddMarket(name, location) ...

Illustration:

  CREATE PROCEDURE ShowStudents()
  BEGIN
    SELECT * FROM students;
  END;

Mini summary: CREATE PROCEDURE saves your query.

Lesson 3: Calling a Stored Procedure

Definition: Use CALL to run a stored procedure.

Why it's important: It executes the saved recipe.

Simple explanation: You say "CALL" and the procedure name.

Real-life example: CALL UpdateInventory();

School example: CALL ClassReport('5A');

Home example: CALL WeeklySummary();

Nigerian example: CALL MarketSales('Abuja');

Illustration:

  CALL ShowStudents();

Mini summary: CALL runs a stored procedure.

Lesson 4: Dropping a Stored Procedure

Definition: Use DROP PROCEDURE to delete it.

Why it's important: To remove procedures you no longer need.

Simple explanation: DROP PROCEDURE procedure_name;

Real-life example: DROP PROCEDURE OldReport;

School example: DROP PROCEDURE OldGrades;

Home example: DROP PROCEDURE OldChores;

Nigerian example: DROP PROCEDURE OldMarketData;

Illustration:

  DROP PROCEDURE ShowStudents;

Mini summary: DROP PROCEDURE removes it.

Lesson 5: What is a Trigger?

Definition: A trigger is a set of SQL statements that run automatically when a table changes.

Why it's important: It helps maintain data integrity and automate actions.

Simple explanation: It's like a robot that watches a table and does something when data changes.

Real-life example: A trigger updates a log table whenever a customer is deleted.

School example: A trigger records when a student's grade is updated.

Home example: A trigger adds a timestamp whenever you add a chore.

Nigerian example: A trigger logs every transaction in a bank.

Illustration:

  CREATE TRIGGER after_insert_log
  AFTER INSERT ON students
  FOR EACH ROW
  BEGIN
    INSERT INTO logs (action) VALUES ('New student added');
  END;

Mini summary: Triggers automatically run on changes.

Lesson 6: Creating a Trigger

Definition: Use CREATE TRIGGER to make one.

Why it's important: Automates tasks.

Simple explanation: Specify when (BEFORE/AFTER) and what action (INSERT/UPDATE/DELETE).

Real-life example: CREATE TRIGGER before_delete_customer ...

School example: CREATE TRIGGER after_update_grade ...

Home example: CREATE TRIGGER before_insert_chore ...

Nigerian example: CREATE TRIGGER after_insert_sale ...

Illustration:

  CREATE TRIGGER update_timestamp
  BEFORE UPDATE ON students
  FOR EACH ROW
  SET NEW.last_updated = NOW();

Mini summary: CREATE TRIGGER sets up an automatic action.

Lesson 7: Dropping a Trigger

Definition: Use DROP TRIGGER to delete it.

Why it's important: Remove triggers you don't need.

Simple explanation: DROP TRIGGER trigger_name;

Real-life example: DROP TRIGGER after_insert_log;

School example: DROP TRIGGER after_update_grade;

Home example: DROP TRIGGER before_insert_chore;

Nigerian example: DROP TRIGGER after_insert_sale;

Illustration:

  DROP TRIGGER update_timestamp;

Mini summary: DROP TRIGGER removes it.

Lesson 8: What is a Transaction?

Definition: A transaction is a group of SQL statements that are treated as one unit.

Why it's important: It ensures that either all changes happen, or none happen.

Simple explanation: It's like a package deal – you get everything or nothing.

Real-life example: Transferring money between two accounts – both updates must succeed.

School example: Registering a student for multiple clubs – all must be recorded.

Home example: Moving toys from one box to another – both boxes must be updated.

Nigerian example: A bank transfer – debit one account, credit another.

Illustration:

  START TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  COMMIT;  -- or ROLLBACK if something goes wrong

Mini summary: Transactions group changes.

Lesson 9: COMMIT and ROLLBACK

Definition: COMMIT saves the changes; ROLLBACK undoes them.

Why it's important: Gives you control over transactions.

Simple explanation: COMMIT = "Yes, save everything"; ROLLBACK = "Oops, undo everything".

Real-life example: After a successful transfer, COMMIT.

School example: After registering a student, COMMIT.

Home example: After updating your chore list, COMMIT.

Nigerian example: After a transaction, COMMIT or ROLLBACK.

Illustration:

  START TRANSACTION;
    -- do some work
  COMMIT;  -- save
  -- or ROLLBACK; -- undo

Mini summary: COMMIT saves, ROLLBACK undoes.

Lesson 10: ACID – The Four Superpowers

Definition: ACID stands for Atomicity, Consistency, Isolation, Durability – properties of transactions.

Why it's important: Ensures data integrity.

Simple explanation: Atomicity = all or nothing; Consistency = data stays valid; Isolation = transactions don't interfere; Durability = saved changes survive.

Real-life example: Bank transfers are ACID.

School example: Registration system is ACID.

Home example: Your chore list updates are ACID.

Nigerian example: Payment systems are ACID.

Illustration:

  Atomicity  → All or nothing
  Consistency → Data stays correct
  Isolation  → Transactions don't mix
  Durability → Saved forever

Mini summary: ACID makes data reliable.

Lesson 11: Next Steps – Python, R, and Visualization

Definition: Python and R are programming languages for data analysis. Visualization tools like Tableau make charts.

Why it's important: They help you do even more with data.

Simple explanation: SQL gets the data, Python/R analyze it, and visualization shows it.

Real-life example: A data scientist uses Python to build a prediction model.

School example: You can use Python to make a grade calculator.

Home example: You can visualize your chore data in a chart.

Nigerian example: A company uses Tableau to show sales trends.

Illustration:

  SQL → Get Data
  Python/R → Analyze
  Tableau/Power BI → Visualize

Mini summary: Expand your skills with other tools.

Lesson 12: Nigerian Example – Fintech Transactions

A Nigerian fintech company uses stored procedures to process payments, triggers to log transactions, and transactions to ensure money moves safely. They use Python to analyze spending patterns.

Lesson 13: Fun Example – Game Leaderboard

You have a game with scores. You create a stored procedure to update the leaderboard. A trigger logs every new score. Transactions ensure that when a player earns points, everything updates together.

Key Vocabulary

Stored Procedure: A saved set of SQL statements.
Trigger: An automatic action on table changes.
Transaction: A group of changes that are all-or-nothing.
COMMIT: Save changes.
ROLLBACK: Undo changes.
ACID: Properties that ensure data reliability.

Important Concepts

1. Procedure vs Trigger: Procedures run when called; triggers run automatically.

2. Transaction Scope: Transactions group statements until COMMIT or ROLLBACK.

3. Data Integrity: Ensures data is accurate and consistent.

Step-by-Step Explanations

For Stored Procedure:

1. Write the SQL statements.

2. Enclose in CREATE PROCEDURE ... BEGIN ... END.

3. Call it with CALL.

For Trigger:

1. Decide when (BEFORE/AFTER) and what action (INSERT/UPDATE/DELETE).

2. Write the trigger body.

3. It runs automatically.

For Transaction:

1. START TRANSACTION.

2. Run statements.

3. If all OK, COMMIT; else ROLLBACK.

Real-life Examples

Online shopping: transactions ensure inventory updates.
Hospital: triggers log patient record changes.
Bank: stored procedures calculate interest.

Nigerian Examples

Paystack: uses transactions for payment processing.
Kuda Bank: uses stored procedures for account management.
Farmcrowdy: uses triggers for crop updates.

Fun Examples

Create a procedure to show your top 5 favourite games.
Create a trigger that logs whenever you add a new toy.
Use a transaction when trading cards with a friend.

Everyday Examples

Using a stored procedure to generate a shopping list.
A trigger that updates a total score when you add a new chore.
A transaction when you update your savings and expenses together.

Teacher Notes

Emphasize that these topics are advanced but very useful. Use simple examples and let students experiment. Encourage them to think of ways to automate tasks.

Parent Tips

Help your child identify repetitive tasks that could be automated with stored procedures or triggers. Discuss how transactions keep data safe.

Interesting Facts

Stored procedures can improve performance because they are pre-compiled.
Triggers are used in many real-world applications like CRM systems.
ACID properties were defined in the 1980s and are still used today.

Did You Know?

MySQL supports stored procedures, triggers, and transactions, making it a powerful tool for enterprise applications.

Remember This

Procedures are saved queries you call.
Triggers run automatically on changes.
Transactions are all-or-nothing.
ACID ensures data is safe.

Common Mistakes

Forgetting to change the delimiter when creating procedures.
Creating triggers that cause infinite loops.
Not using transactions for related changes.
Forgetting to COMMIT or ROLLBACK.

Best Practices

Use descriptive names for procedures and triggers.
Keep triggers simple to avoid performance issues.
Always use transactions for groups of changes.
Test procedures and triggers with small data first.

Illustrations

Transaction Flowchart

  Start Transaction
        |
        V
  Do Operation 1
        |
        V
  Do Operation 2
        |
        V
  Any Error? → Yes → ROLLBACK → End
        |
        No
        |
        V
  COMMIT → End

Trigger Flowchart

  Change on Table (INSERT/UPDATE/DELETE)
        |
        V
  Trigger Fires
        |
        V
  Run Trigger Body
        |
        V
  Continue

Table Example: Stored Procedure

Procedure Name	Purpose
GetHighScores	Show students with score > 80
UpdateInventory	Update stock after sale

Comparison Table: Procedure vs Trigger vs Transaction

Feature	Stored Procedure	Trigger	Transaction
How to run	CALL	Automatic	Manual (START)
Purpose	Save and reuse code	Automate actions	Group changes
When	Any time	On table change	During operations

End-of-Module Summary

In this module, you learned about stored procedures, triggers, and transactions – advanced tools that make MySQL even more powerful. You also explored what to learn next, including Python, R, and visualization. You are now equipped with a complete set of skills to tackle any data challenge. Well done!

Frequently Asked Questions

What is a stored procedure? – A saved query.
What is a trigger? – An automatic action.
What is a transaction? – A group of changes.
What is COMMIT? – Save changes.
What is ROLLBACK? – Undo changes.
What does ACID stand for? – Atomicity, Consistency, Isolation, Durability.
Why use stored procedures? – Reusability and performance.
Why use triggers? – Automation and data integrity.
Why use transactions? – To ensure data consistency.
What should I learn next? – Python, R, Tableau, Power BI.

Review Questions

What is a stored procedure?
How do you call a stored procedure?
What is a trigger?
When does a trigger run?
What is a transaction?
What does COMMIT do?
What does ROLLBACK do?
What does ACID stand for?
Why are transactions important?
What is the difference between a procedure and a trigger?
What tools can you learn after MySQL?
Give an example of a transaction.
Give an example of a trigger.
Give an example of a stored procedure.
Why is automation important in databases?

Fill-in-the-Blank

A stored procedure is saved using ______ PROCEDURE.
A trigger runs ______ on table changes.
______ saves a transaction.
______ undoes a transaction.
ACID ensures ______ integrity.

True or False

Stored procedures cannot be reused. (False)
Triggers run automatically. (True)
COMMIT undoes changes. (False)
Transactions are all-or-nothing. (True)
ACID is not important for databases. (False)

Multiple Choice

Which is a saved query? A) Trigger B) Procedure C) Transaction (Answer: B)
Which runs automatically? A) Procedure B) Trigger C) Transaction (Answer: B)
Which groups changes? A) Procedure B) Trigger C) Transaction (Answer: C)
What saves a transaction? A) COMMIT B) ROLLBACK C) START (Answer: A)
What undoes a transaction? A) COMMIT B) ROLLBACK C) START (Answer: B)
ACID stands for: A) Atomicity, Consistency, Isolation, Durability B) Accuracy, Clarity, Integrity, Data (Answer: A)
Procedures are called using: A) CALL B) RUN C) EXEC (Answer: A)
Triggers can run: A) Before INSERT B) After UPDATE C) Both (Answer: C)
Transactions ensure: A) All changes happen B) No changes happen C) Either (Answer: C)
A trigger is associated with: A) A table B) A database C) A server (Answer: A)
A stored procedure can: A) Take parameters B) Not take parameters C) Both (Answer: C)
Which is a visualization tool? A) Tableau B) MySQL C) Python (Answer: A)
Python is used for: A) Data analysis B) Storing data C) Both (Answer: A)
ROLLBACK is used: A) After COMMIT B) Before COMMIT C) Instead of COMMIT (Answer: B)
Transactions are important for: A) Data consistency B) Data speed C) Data size (Answer: A)

Matching

Term	Definition
Stored Procedure	Saved query
Trigger	Automatic action
Transaction	Group of changes
COMMIT	Save changes
ROLLBACK	Undo changes

Short Answer

Write a stored procedure to show all students.
What is the purpose of a transaction?
Name two tools you can use with MySQL for data analysis.

Scenario-based

Scenario: You are building a banking system. A customer transfers 5000 Naira from savings to checking. Write a transaction that ensures both accounts update correctly.

Answer: START TRANSACTION; UPDATE savings SET balance = balance - 5000 WHERE id = 1; UPDATE checking SET balance = balance + 5000 WHERE id = 2; COMMIT;

Group Activity

In groups, design a stored procedure for a library system to borrow a book. Include steps to check availability, update the book status, and log the transaction.

Individual Activity

Create a trigger that logs any change (INSERT, UPDATE, DELETE) on a table called 'products'. Use a log table to store the action and timestamp.

Classroom Discussion

How can stored procedures improve a company's workflow?
What would happen if you didn't use transactions?
How do you decide between a procedure and a trigger?

Mini Project

Build a small e-commerce database. Create stored procedures for placing an order, updating inventory, and generating invoices. Use triggers to log all changes. Use transactions for order placement.

Practical Assignment

Create a procedure that calculates the total sales for a given month. Create a trigger that updates a 'last_updated' column on any change. Use a transaction to add a new product and update inventory.

Challenge Exercise

Write a procedure that processes a batch of orders (100+). Use a transaction to ensure all orders are processed or none. Add error handling with ROLLBACK.

Quiz Answers (Multiple Choice)

1B, 2B, 3C, 4A, 5B, 6A, 7A, 8C, 9C, 10A, 11C, 12A, 13A, 14B, 15A

Key Takeaways

Stored procedures save and reuse code.
Triggers automate actions on data changes.
Transactions ensure all-or-nothing changes.
ACID properties keep data safe.
Continue learning Python, R, and visualization.

Preparation for the Next Course

This is the final module of this course. You have learned everything from basic queries to advanced topics. Now, it's time to apply your skills. Consider taking courses in Python, data science, or business intelligence. Remember, every expert was once a beginner. Keep going!

🎉 Congratulations! You have completed the MySQL for Data Analysis Expert course. You are now a true data superhero. Use your powers to answer questions, solve problems, and make the world better with data. Happy analyzing!

Module Fifteen

Module 15: MySQL for Data Analysis Expert – Grand Finale & Beyond

Hello, amazing data analyst! This is it – the final module of our journey. We have covered so much together, from basic SELECT statements to advanced stored procedures and transactions. In this grand finale, we will review everything, celebrate your progress, and look at the exciting future ahead. You are now ready to take on the world of data. Let's finish strong!

Learning Objectives

Review all the key concepts from the course.
Understand how all the pieces fit together.
Build a final comprehensive project.
Celebrate your achievements.
Plan your next steps in data analysis.
Feel confident and excited about your skills.

Warm-up Story: The Data Graduation Day 🎓

Imagine you are at a graduation ceremony. You have completed 14 modules of hard work. You have learned to ask questions, clean data, write queries, join tables, use subqueries, create views, optimize performance, and even automate tasks with procedures and triggers. Today, you receive your certificate as a MySQL Data Analysis Expert. But this is not the end – it's the beginning of your adventure. You are now ready to solve real-world problems with data. Let's celebrate and look ahead!

Main Lessons

Lesson 1: The Journey So Far – A Quick Review

Definition: Review means looking back at what we learned.

Why it's important: It helps us remember and connect ideas.

Simple explanation: It's like looking at a map of where we traveled.

Real-life example: At the end of a school year, you review all subjects.

School example: A teacher reviews the lessons before a test.

Home example: You review your toy collection to see what you have.

Nigerian example: A shop owner reviews sales from the past year.

Illustration:

  Module 1  →  Basics
  Module 2  →  SELECT & WHERE
  Module 3  →  GROUP BY & ORDER BY
  Module 4  →  JOINs
  Module 5  →  Subqueries & Views
  Module 6  →  Functions & Indexes
  Module 7  →  Projects & Career
  Module 8  →  Stored Procedures & Triggers
  Module 9  →  Transactions & ACID
  Module 10 →  Advanced & Next Steps

Mini summary: We have learned a lot!

Lesson 2: The Data Analysis Pipeline – Revisited

Definition: The pipeline is the step-by-step process of analysis.

Why it's important: It guides us from question to answer.

Simple explanation: It's like a recipe for making a meal.

Real-life example: A chef follows a recipe to cook a dish.

School example: You follow steps to solve a math problem.

Home example: You follow steps to clean your room.

Nigerian example: A farmer follows steps to plant and harvest.

Illustration:

  Ask Question → Collect Data → Clean Data → Analyze → Report → Act

Mini summary: The pipeline is our guide.

Lesson 3: Why Data Analysis Matters

Definition: Data analysis helps us make better decisions.

Why it's important: It solves problems and improves lives.

Simple explanation: It turns numbers into useful information.

Real-life example: Data analysis helps doctors choose the best treatment.

School example: Data analysis helps teachers know which subjects need more attention.

Home example: Data analysis helps you decide how to spend your pocket money.

Nigerian example: Data analysis helps the government plan for schools and hospitals.

Illustration:

  Data → Analysis → Insights → Better Decisions

Mini summary: Data analysis makes the world better.

Lesson 4: Your Superpower – SQL

Definition: SQL is your superpower to talk to databases.

Why it's important: It lets you get answers from data.

Simple explanation: It's like a magic wand for data.

Real-life example: A detective uses clues to solve a case.

School example: You use a calculator for math.

Home example: You use a remote to change TV channels.

Nigerian example: A market trader uses a scale to weigh goods.

Illustration:

  SQL = Ask Questions → Get Answers

Mini summary: SQL is your data superpower.

Lesson 5: Putting It All Together – A Final Project

Definition: A final project combines everything you learned.

Why it's important: It shows you are a true expert.

Simple explanation: It's like a final exam but more fun.

Real-life example: A company asks you to analyze their sales data.

School example: A teacher asks you to create a report on class performance.

Home example: You analyze your family's monthly expenses.

Nigerian example: You analyze market data for a local cooperative.

Illustration:

  Choose Topic → Collect Data → Clean → Analyze → Report → Present

Mini summary: The final project shows your skills.

Lesson 6: Celebrating Your Achievements

Definition: Celebrating means recognizing your hard work.

Why it's important: It boosts confidence and motivation.

Simple explanation: It's like giving yourself a high-five.

Real-life example: After finishing a big project, you celebrate with friends.

School example: After exams, you have a party.

Home example: After cleaning your room, you reward yourself.

Nigerian example: After a successful harvest, farmers celebrate.

Illustration:

  Hard Work → Achievements → Celebration 🎉

Mini summary: Celebrate your success!

Lesson 7: What's Next? – Your Data Journey Continues

Definition: Your data journey is the path you will take after this course.

Why it's important: It helps you plan for the future.

Simple explanation: It's like looking at a map for your next adventure.

Real-life example: A data analyst learns Python to do more advanced analysis.

School example: You learn new subjects each year.

Home example: You learn new skills like cooking or coding.

Nigerian example: A data professional takes online courses to stay updated.

Illustration:

  MySQL → Python → Machine Learning → AI → Data Science

Mini summary: The learning never stops.

Lesson 8: Building a Portfolio – Your Showcase

Definition: A portfolio is a collection of your best projects.

Why it's important: It shows employers what you can do.

Simple explanation: It's like a photo album of your work.

Real-life example: An artist shows their portfolio to get a job.

School example: You have a folder of your best homework.

Home example: You have a collection of your drawings.

Nigerian example: A web developer has a portfolio of websites.

Illustration:

  Project 1: Sales Analysis
  Project 2: Customer Insights
  Project 3: Market Trends

Mini summary: A portfolio showcases your talent.

Lesson 9: Networking and Community

Definition: Networking means connecting with other people in your field.

Why it's important: You can learn from others and find opportunities.

Simple explanation: It's like making friends who share your interests.

Real-life example: Joining a data science club.

School example: Being in a study group.

Home example: Playing with friends who like the same games.

Nigerian example: Joining a local tech meetup.

Illustration:

  Connect → Share → Learn → Grow

Mini summary: Community makes you stronger.

Lesson 10: Giving Back – Teaching Others

Definition: Teaching others means sharing what you know.

Why it's important: It reinforces your own learning and helps others.

Simple explanation: It's like showing a friend how to play a game.

Real-life example: A senior data analyst mentors a junior.

School example: You help a classmate with homework.

Home example: You teach a sibling how to do a chore.

Nigerian example: A tech expert volunteers to teach in a community centre.

Illustration:

  Learn → Teach → Learn More

Mini summary: Teaching helps everyone.

Lesson 11: Nigerian Example – Using Data for Development

Nigeria has many challenges, but data can help. For example, data analysis can help improve agriculture, healthcare, and education. A data analyst in Nigeria can use MySQL to track disease outbreaks, monitor school enrollment, or optimize supply chains.

Lesson 12: Fun Example – The Data Superhero Game

You are a data superhero with a mission to save the city. You use MySQL to analyze crime data, find patterns, and help the police. Your skills are the superpower that makes the city safer.

Key Vocabulary

Review: Looking back at what you learned.
Pipeline: The step-by-step process.
Portfolio: A collection of your projects.
Networking: Connecting with others.
Mentor: An experienced guide.
Community: A group of people with shared interests.

Important Concepts

1. Lifelong Learning: Keep learning new things every day.

2. Community: Join groups, forums, and meetups.

3. Impact: Use your skills to make a positive difference.

Step-by-Step Explanations

Step 1: Review all the modules.

Step 2: Choose a final project topic.

Step 3: Collect and clean data.

Step 4: Analyze with MySQL.

Step 5: Create a report and presentation.

Step 6: Share your project with others.

Step 7: Plan your next steps.

Real-life Examples

A data analyst at a hospital uses data to reduce patient wait times.
A data analyst at a school uses data to improve student performance.
A data analyst at a bank uses data to prevent fraud.

Nigerian Examples

A data analyst at the Nigerian Communications Commission (NCC) monitors network quality.
A data analyst at the National Bureau of Statistics (NBS) analyzes economic data.
A data analyst at a startup like Flutterwave uses data to improve payment systems.

Fun Examples

Analyze your video game stats to see which level is hardest.
Track your school grades and find your best subject.
Create a survey for your friends and analyze the results.

Everyday Examples

Analyzing weather data to plan a trip.
Tracking your daily steps to stay healthy.
Comparing prices to get the best deal.

Teacher Notes

This module is a celebration. Encourage students to reflect on their journey. Help them plan a final project. Discuss the future of data analysis and how they can continue learning.

Parent Tips

Celebrate your child's achievement. Help them think about how they can use their skills in real life. Encourage them to keep learning and exploring.

Interesting Facts

Data analysis is used in almost every industry – sports, entertainment, healthcare, and more.
Some data analysts work on space missions to analyze data from Mars.
The field of data science is growing very fast, with many job opportunities.

Did You Know?

Data analysts have helped find new planets, cure diseases, and even predict earthquakes!

Remember This

You are a data analysis expert.
You have skills that are in high demand.
Keep learning and practicing.
Share your knowledge with others.
Use data for good.

Common Mistakes

Forgetting to celebrate your achievements.
Stopping learning after the course.
Not sharing your work with others.
Underestimating the power of community.
Being afraid to take on big projects.

Best Practices

Set clear goals for your future.
Build a portfolio of your best work.
Network with other data professionals.
Contribute to open-source projects.
Stay curious and ask questions.

Illustrations

Your Data Journey

  Start → Learn → Practice → Master → Apply → Teach → Repeat

Final Project Checklist

  □ Choose Topic
  □ Collect Data
  □ Clean Data
  □ Analyze with MySQL
  □ Create Report
  □ Practice Presentation
  □ Share with Community

Table Example: Course Overview

Module	Main Topic
1	Introduction to MySQL
2	SELECT and WHERE
3	GROUP BY and ORDER BY
4	JOINs
5	Subqueries and Views
6	Functions and Indexes
7	Projects and Career
8	Stored Procedures
9	Triggers
10	Transactions and ACID
11	Advanced Topics
12	Optimization
13	Real-World Projects
14	Next Steps
15	Grand Finale

Comparison Table: Beginner vs Expert

Beginner	Expert
Knows basic SELECT	Uses subqueries and joins
Writes simple queries	Creates stored procedures
Works with one table	Optimizes complex queries
Follows examples	Builds own projects

End-of-Module Summary

This module was a celebration of your journey. You reviewed all the key concepts, from the data analysis pipeline to advanced topics like stored procedures and transactions. You reflected on your achievements and planned your next steps. You are now a certified MySQL Data Analysis Expert with the skills to solve real-world problems. Congratulations!

Frequently Asked Questions

What is the most important thing I learned? – How to ask questions and get answers from data.
What should I do next? – Practice, build projects, and learn Python.
How can I get a job as a data analyst? – Build a portfolio, network, and apply for internships.
Is this course enough to become a data analyst? – It's a great start. Continue learning and practicing.
What is the best way to practice? – Work on real-world projects.
How can I stay updated? – Read blogs, join forums, and take advanced courses.
What if I forget something? – Review the modules and practice.
Can I teach others? – Yes! Teaching is a great way to learn.
What is the future of data analysis? – It's growing rapidly with AI and machine learning.
Will I need to learn other tools? – Yes, Python, R, and visualization tools are useful.

Review Questions

What is the data analysis pipeline?
Why is data cleaning important?
What does SQL stand for?
What is a JOIN?
What is a subquery?
What is a view?
What is an index?
What is a stored procedure?
What is a trigger?
What is a transaction?
What does ACID stand for?
Why is a portfolio important?
What is networking?
What are some next steps after this course?
How can you use data to help others?

Fill-in-the-Blank

The data analysis pipeline is a ______-by-step process.
SQL stands for ______ Query Language.
A ______ is a collection of your projects.
______ means connecting with other people in your field.
______ is the key to staying relevant in data analysis.

True or False

Data analysis is only for experts. (False)
SQL is a programming language. (True)
A portfolio is not important. (False)
Networking can help you find a job. (True)
You should stop learning after this course. (False)

Multiple Choice

What is the first step in the data analysis pipeline? A) Clean B) Ask Question C) Report (Answer: B)
SQL is used to: A) Bake cakes B) Talk to databases C) Draw pictures (Answer: B)
A portfolio is: A) A collection of projects B) A type of data C) A query (Answer: A)
Networking means: A) Connecting with others B) Building a network C) Both (Answer: C)
Which is a good next step? A) Learn Python B) Stop learning C) Forget everything (Answer: A)
Data analysis helps: A) Make decisions B) Make trouble C) Make noise (Answer: A)
A trigger runs: A) Automatically B) On command C) Never (Answer: A)
A transaction ensures: A) All-or-nothing B) Nothing happens C) Only some changes (Answer: A)
ACID stands for: A) Atomicity, Consistency, Isolation, Durability B) Accuracy, Clarity, Integrity, Data (Answer: A)
Why is cleaning data important? A) It removes errors B) It makes data bigger C) It makes data harder (Answer: A)
What is a view? A) A saved query B) A table C) A database (Answer: A)
What is an index? A) A speed-up B) A slowdown C) A type of data (Answer: A)
What is a stored procedure? A) A saved set of statements B) A table C) A query (Answer: A)
What does COMMIT do? A) Saves changes B) Undoes changes C) Starts a transaction (Answer: A)
What does ROLLBACK do? A) Saves changes B) Undoes changes C) Starts a transaction (Answer: B)

Matching

Term	Definition
SQL	Language for databases
View	Saved query
Index	Speed-up
Transaction	All-or-nothing
Portfolio	Collection of projects

Short Answer

What is the data analysis pipeline?
Why is it important to have a portfolio?
Name one thing you will do after this course.

Scenario-based

Scenario: You are hired by a Nigerian company to analyze their sales data. They want to know which products are selling best and which regions are most profitable. Write a plan for your project.

Answer: 1. Collect sales data. 2. Clean data. 3. Use GROUP BY to find best-selling products. 4. Use JOIN to link products to regions. 5. Create a report with recommendations.

Group Activity

In groups, create a plan for a data analysis project that could help your local community. Present your plan to the class.

Individual Activity

Write a one-page reflection on your journey through this course. What was the most interesting thing you learned? What will you do next?

Classroom Discussion

How has this course changed the way you think about data?
What is one problem you would like to solve with data?
How can data analysis help Nigeria?

Mini Project

Choose a dataset of your choice (e.g., from Kaggle). Perform a complete analysis: clean, explore, write queries, and create a report. Present your findings to the class.

Practical Assignment

Write a comprehensive report on a dataset of your choice. Include: data description, cleaning steps, queries used, results, and recommendations.

Challenge Exercise

Take a large dataset (10,000+ rows). Optimize your queries using indexes and EXPLAIN. Measure the performance improvement and write a report.

Quiz Answers (Multiple Choice)

1B, 2B, 3A, 4C, 5A, 6A, 7A, 8A, 9A, 10A, 11A, 12A, 13A, 14A, 15B

Key Takeaways

You have completed the MySQL for Data Analysis Expert course.
You know how to ask questions and get answers from data.
You have skills in SQL, data cleaning, analysis, and reporting.
You have a portfolio of projects to showcase.
You are ready for the next step in your data journey.

Preparation for the Next Course

This is the final module of this course. We have covered everything from basics to advanced topics. Now, it's time to explore new horizons. Consider learning Python for data analysis, data visualization with Tableau, or machine learning. The world of data is vast and exciting. Congratulations on becoming a MySQL Data Analysis Expert!

🎉🎓 Thank you for joining this course. You have worked hard and achieved a lot. Remember, data is everywhere, and you now have the power to understand it. Use your skills wisely, keep learning, and always stay curious. Good luck on your data adventure!

🏆 Get Certified

🔒

Earn this certificate

Every lesson is already free to read. Sign up, pass the exam, and unlock Practice Tools plus a verified certificate with your name on it — ₦4,000/month.

🎓 Sign Up & Unlock for ₦4,000/month

🛠️ Practice Tools

Hands-on simulators & labs - subscription required.

→

🎯 Internship Tasks

Real-world tasks to build your portfolio - try them free for 7 days, no card required.

→