WEEK1 MYSQL

https://s3.fr-par.scw.cloud/
files.powerlearnprojectafrica.org/lms/production/videos/
Lesson%201.mp4?X-Amz-Algorithm=AWS4-HMAC-
SHA256&X-Amz-
Credential=SCW1GD83Z2RS0PZEWJNW
%2F20250801%2Ffr-par%2Fs3%2Faws4_request&X-Amz-
Date=20250801T192259Z&X-Amz-Expires=518400&X-
Amz-SignedHeaders=host&X-Amz-
Signature=9debfd894f3da3c02a0a3c45a2ba579438bf8b542ed
20f780bfd69f1e9d849e7
Data: The Building Blocks of Modern Apps
https://youtu.be/yFSEf6TOzDQ
Welcome to the exciting world of data! 🌟 In this lesson, we'll explore the fascinating realm of
information that powers our digital experiences.

Key Topics🏆:
 Understanding Data: What is data?
 Types of Data: Structured, semi-structured, and unstructured data.
 Data in Databases: How data is organized and stored in databases.
🧠 Understanding Data: What's the Big Deal? 🤔

Data is like the secret sauce 🥫 of modern applications. It's the collection of information that can
be processed and analyzed electronically. Think of it as the ingredients that make your favorite
app work its magic! ✨

Information 💡 is data that has been processed, organized, and interpreted to give it meaning and
context.

Example:

Data: A website collects the ages of all visitors.

Information: The website analyzes the data and discovers that the majority of visitors are
between the ages of 15 and 20. 🤯 This processed data now provides valuable insight into the
website's target audience. 🎯
Types of Data: A Colorful Variety 🌈

Let's dive into the different flavors of data:

Structured Data 📏
This is the neat freak of the data world! It's highly organized and follows a strict format. Imagine
a perfectly arranged bookshelf 📚 where you can find any book in seconds!

Semi-structured Data 🧩
The cool cousin of structured data! It has some organization but likes to keep things flexible.
Think of it as a messy desk where you can still find what you need (most of the time) 😅

Unstructured Data 🎨
The free spirit of data types! It's like a Jackson Pollock painting – no rigid structure, but full of
valuable information. Social media posts, images, and videos fall into this category 📸

Now you're ready to dive into the world of data with confidence and style😎 Remember,
understanding data is the key to unlocking the potential of modern applications. So go forth and
conquer the data universe 🚀🌠

Basic Databases Concept: Where the Magic Happens ✨

https://youtu.be/X7v0O8yiUuY

A Database is a collection of related data organized in a way that can be easily accessed,
managed, and updated.

Databases are like the super-organized fridges of the digital world 🍱 They keep all the data fresh
and easy to access!

Tables

A table is a collection of data elements organized rows and columns. Think of these as the
different compartments in your fridge. Each one has a specific purpose, like storing user info or
transaction records.

Row 📊

A single entry in a table is called a tuple or row. Imagine a table at a restaurant. Each row on the
table is like a plate of food. That plate holds all the information about one specific dish, like its
name, price, and ingredients.

Columns 📊
Each column on the table is like a category of information about all the dishes

Data Types
Just as you wouldn't store ice cream in the vegetable drawer, data types ensure the right
information goes in the right place. Numbers for counting, text for names – everything has its
perfect spot! 🧊🥕

Primary Keys 🔑: Unique identifiers for each row. Think of it as a VIP pass that ensures each
entry in the database is unique.

Foreign Keys 🔗: Links between tables that connect related data, like a bridge connecting two
islands.

Now you're ready to dive into the world of data with confidence and style! 😎 Remember,
understanding data is the key to unlocking the potential of modern applications. So go forth and
conquer the data universe! 🚀🌠
More Resources:
Database Basics

🎲 Types of Data Models: Meet the Data Family

Data Models describe how we organize and connect data.

1. Hierarchical Model 🌳: Organizes data in a tree-like structure with one parent per child.
o Use Case: Organizational charts, file directories.
2. Network Model 🌐: More flexible than the hierarchical model; each item can have
multiple connections.
o Use Case: Telecommunications and complex networks.
3. Relational Model 💞: Stores data in tables that can be linked by relationships.
o Use Case: Most business applications, like customer management systems.
4. Object-Oriented Model : Stores data as objects, useful for complex structures like
game characters.
o Use Case: Video games and simulations.

Databases: The Digital Filing Cabinets of the Web 🌐

Welcome to the world of databases! 🎉 Now that we've got the basics of data down, let's dive into
how websites keep all that information organized and accessible. It's like magic but with more
zeros and ones!
🗺️
Key Topics: Your Database Adventure Map

 🧠 Grasp the core principles of databases

 🚀 Explore the role of rows and data types
 🤝 Comprehend relationships between tables
 Discover the role of Database Management Systems (DBMS)
 Introduce SQL's role in database interaction

More Resources:
Data Models

SQL: The Universal Database Language 🌍

https://youtu.be/et1n7-UxI2M

SQL is like the cool translator 😎 between your app and the database. It helps your application:

 🐱
Retrieve Specific Data: "Find me all the cat videos!" 🐱
 📊 Organize Data for Display: "Show the most popular ice cream flavours!" 🍦
 ✏️Update Data as Needed: "Change my username to 'CodeNinja'!" 🥷
And there you have it! You're now ready to rock the database world! 🎸 Remember, databases are
the unsung heroes keeping our favorite websites and apps running smoothly. So next time you're
scrolling through your feed, give a little nod to the hardworking databases behind the
scenes! 👏🏼🚀

Building Your First Data Table: Let's Get Hands-On!

Welcome, data architects! Now that you've mastered the basics, it's time to roll up your
sleeves and create your very first table. Get ready to turn your project ideas into a data reality! 🌟

🎯 Lesson Objectives: Your Data Adventure Checklist ✅

 🧠 Conceptualize and Design a Data Table

 🔍 Identify Crucial Data Points
 Assign Appropriate Data Types

📐 Designing Your Data Table: Let's Build! 🚧

Imagine you're creating a magical filing cabinet for your project. Each drawer is a table, each
folder a row, and each sheet of paper a piece of data. Let's make it happen! 🎩✨

1. Conceptualize Your Needs 💭

Put on your thinking cap and ask yourself:

 What information do I want to track?

 🔮 What details will be useful in the future?
 🚀 Any cool features I might want to add later?
2. Identify Essential Data Points 📊

Time to pick your data superheroes! Here are some common contenders:

 💰 Amount/Value: The numerical star of the show

 Date/Time: When did the magic happen?
 Category: What kind of data are we dealing with?
 📝 Description: A little extra info never hurts!

3. Assign Appropriate Data Types 🎭

Let's dress up our data in the right costumes:

 🔢 Amount/Value: Numbers only, please! (integer or decimal)

 📅 Date/Time: Time to shine! (date or timestamp)
 🆎 Category: Text me! (text data type)
 📚 Description: Text, but not too much! (text with length limit)
MORE Resources:
What is SQL

Step 1: Download MySQL Installer

 Open your web browser and navigate to the MySQL website

at https://dev.mysql.com/downloads/installer/.

 Scroll down to the “MySQL Installer” section, make sure “Microsoft Windows“ is
selected under Select Operating System and click on the “Download” button for the
MySQL Installer MSI (both the 2.4M and 331.3M version will work)

On the next page, you’ll be prompted to log in or create a MySQL account. You can choose “No
thanks, just start my download.”
Step 2: Install and Configure MySQL

You can also choose “Custom” and only install MySQL Server and MySQL Workbench.
· In the next window, just click “Execute.”
· In the “Account and Roles” window, choose a new password for your root account. Be sure
to choose a strong password and enter the password under MySQL Root Password and Repeat
Password then click “Next.”
· Click “Finish” and MySQL Workbench will launch.
Step 3: Using MySQL Workbench

In the bottom half of the window under “MySQL Connections” you should see one tile for your
local instance connection, Local instance MySQL80.
· Go ahead and double-click it.

Navigator

The Navigator is typically located on the left-hand side of the MySQL Workbench GUI. It serves
as a central hub for database management and navigation. It houses some administrative features
and a handy tree-like structure of all your databases under the tab “Schemas.”
JULY CLASS RECORDING

https://drive.google.com/drive/folders/1nrn47R18WwWQzR98HApLm7JcH-L-oYn4?
usp=sharing

MORE RESOURCE

https://youtu.be/zsjvFFKOm3c

https://youtu.be/WnP6jDvupiY

https://youtu.be/yFSEf6TOzDQ
WEEKLY CODE CHALLENGE

Creating a Sales Database 🛒

🏬 Write an SQL query to create a database named 'SalesDB'. 💰

Creating and Dropping a School Database 🏫 💥

Write an SQL query to create a database named 'SchoolDB'. Then, imagine it was a test run
and use the 'DROP' command to delete it.

ANSWER

Absolutely! Here's how you can handle both tasks in SQL:

🛒 Creating a Sales Database

sql
CopyEdit
CREATE DATABASE SalesDB;

🏫 Creating and Dropping a School Database

sql
CopyEdit
-- Create the database
CREATE DATABASE SchoolDB;

-- Drop (delete) the database

DROP DATABASE SchoolDB;

💡 Note:

 Ensure you have the necessary permissions to create or drop databases.

 Be very cautious with DROP DATABASE — it permanently deletes the database and all its
data. 🛑

Let me know if you want to add tables or data inside these databases!

You said:
YES PLEASE
ChatGPT said:

Great! Let’s build on that. I’ll show you how to:

1. ✅ Create the SalesDB and add a basic Customers and Orders table.
2. ✅ Create the SchoolDB, add a Students and Courses table.
3. (We’ll not drop them now since we’re adding content—but I’ll include the DROP syntax
in case you want it.)

🛒 1. SalesDB – Create Tables and Insert Sample Data

sql
CopyEdit
-- Create the database
CREATE DATABASE SalesDB;
USE SalesDB;

-- Create a Customers table

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
CustomerName VARCHAR(100),
Email VARCHAR(100),
City VARCHAR(50)
);

-- Create an Orders table

CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
OrderDate DATE,
Amount DECIMAL(10, 2),
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insert sample data

INSERT INTO Customers (CustomerName, Email, City)
VALUES
('Alice Johnson', 'alice@example.com', 'New York'),
('Bob Smith', 'bob@example.com', 'Chicago');

INSERT INTO Orders (OrderDate, Amount, CustomerID)

VALUES
('2025-08-01', 199.99, 1),
('2025-08-02', 89.50, 2);

🏫 2. SchoolDB – Create Tables and Insert Sample Data

sql
CopyEdit
-- Create the database
CREATE DATABASE SchoolDB;
USE SchoolDB;

-- Create a Students table

CREATE TABLE Students (
StudentID INT PRIMARY KEY AUTO_INCREMENT,
FullName VARCHAR(100),
GradeLevel INT,
BirthDate DATE
);

-- Create a Courses table

CREATE TABLE Courses (
CourseID INT PRIMARY KEY AUTO_INCREMENT,
CourseName VARCHAR(100),
Credits INT
);

-- Insert sample data

INSERT INTO Students (FullName, GradeLevel, BirthDate)
VALUES
('Fatima Yusuf', 10, '2008-05-12'),
('Daniel Tesfaye', 11, '2007-03-30');

INSERT INTO Courses (CourseName, Credits)

VALUES
('Mathematics', 4),
('Biology', 3);

💥 Optional: Dropping the Databases

sql
CopyEdit
-- To delete SalesDB (if needed)
DROP DATABASE SalesDB;

-- To delete SchoolDB (if needed)

DROP DATABASE SchoolDB;
////////////////////////////////

🏫 Enhance SchoolDB: Student Enrollments in Courses

We’ll add a Enrollments table to link students to courses.

sql
CopyEdit
-- Use the SchoolDB
USE SchoolDB;
-- Create Enrollments table (Many-to-Many relationship)
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

-- Insert sample enrollments

INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate)
VALUES
(1, 1, '2025-08-01'), -- Fatima -> Mathematics
(2, 2, '2025-08-01'); -- Daniel -> Biology

🛒 Enhance SalesDB: Add Products and OrderDetails

Let’s assume each order has one or more products. We’ll add Products and OrderDetails tables.

sql
CopyEdit
-- Use the SalesDB
USE SalesDB;

-- Create a Products table

CREATE TABLE Products (
ProductID INT PRIMARY KEY AUTO_INCREMENT,
ProductName VARCHAR(100),
Price DECIMAL(10, 2)
);

-- Create OrderDetails to link Orders and Products (Many-to-Many)

CREATE TABLE OrderDetails (
OrderDetailID INT PRIMARY KEY AUTO_INCREMENT,
OrderID INT,
ProductID INT,
Quantity INT,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insert sample products

INSERT INTO Products (ProductName, Price)
VALUES
('Laptop', 1000.00),
('Headphones', 150.00),
('Keyboard', 75.00);

-- Insert sample order details

INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
VALUES
(1, 1, 1), -- Order 1: 1 Laptop
(2, 2, 2); -- Order 2: 2 Headphones
These enhancements allow:

 Students to enroll in multiple courses 📚

 Orders to contain multiple products

Let me know if you'd like to:

 Generate reports (e.g., total sales, student lists)?

 Add user authentication or logins?
 Export this as .sql file or visualize it in a diagram?

You said:

✅ Finalized Features Recap

🛒 SalesDB includes:

 Customers — Customer info

 Orders — Linked to customers
 Products — Item catalog
 OrderDetails — Order-product-quantity link

🏫 SchoolDB includes:

 Students — Basic student data

 Courses — Subject catalog
 Enrollments — Student-course relationship

📊 REPORTING QUERIES (Advanced SQL Insights)

🛒 Sales Reports

1. Total Sales per Customer:

sql
CopyEdit
SELECT c.CustomerName, SUM(od.Quantity * p.Price) AS TotalSpent
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
GROUP BY c.CustomerName;

2. Most Sold Products:

sql
CopyEdit
SELECT p.ProductName, SUM(od.Quantity) AS TotalUnitsSold
FROM OrderDetails od
JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName
ORDER BY TotalUnitsSold DESC;

🏫 School Reports

1. Student Course List:

sql
CopyEdit
SELECT s.FullName, c.CourseName, e.EnrollmentDate
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
ORDER BY s.FullName;

2. Course Enrollment Count:

sql
CopyEdit
SELECT c.CourseName, COUNT(e.StudentID) AS TotalEnrolled
FROM Courses c
LEFT JOIN Enrollments e ON c.CourseID = e.CourseID
GROUP BY c.CourseName;

🔐 Optional: Add User Logins

User Table Example (for SchoolDB):

sql
CopyEdit
CREATE TABLE Users (
UserID INT PRIMARY KEY AUTO_INCREMENT,
Username VARCHAR(50) UNIQUE,
PasswordHash VARCHAR(255),
Role ENUM('admin', 'teacher', 'student') NOT NULL
);

You could later link a UserID to each student or teacher profile.

 📁 Export or Diagram?
I can now:

1. 📦 Generate the full .sql file for both SalesDB and SchoolDB (for you to run in MySQL,
PostgreSQL, etc.)
2. 🧭 Create a database diagram (ERD) showing table relationships
3. 🗂 Bundle everything into a zip with setup and insert scripts

👉 Which one(s) do you want? Just say:

 "Send me the SQL file"

 "Create a database diagram"
 "Give me both"

WEEK1 QUIZ

Skip to content
You said:
What is a database? A collection of related tables A collection of programming code A type of
spreadsheet A document with text and images
The correct answer is:

✅ A collection of related tables

Explanation:

A database is an organized collection of data, typically stored and accessed electronically. In

relational databases (like MySQL, PostgreSQL, or SQL Server), the data is organized
into tables, and these tables can be related to one another through keys (primary and foreign
keys).

The other options are incorrect:

 ❌ A collection of programming code — That's a codebase, not a database.

 ❌ A type of spreadsheet — While spreadsheets may look like simple tables, they are not
databases.
 ❌ A document with text and images — That describes a word processor document, not a
database.
You said:
Which of the following is not a type of database model? Relational NoSQL Object-Oriented
Binary

The correct answer is:

 ✅ Binary

Explanation:

Here’s a breakdown of the options:

 Relational ✅ – A common database model where data is organized into tables with rows and
columns (e.g., MySQL, PostgreSQL).
 NoSQL ✅ – A category of database models that includes document, key-value, column-family,
and graph models (e.g., MongoDB, Redis).
 Object-Oriented ✅ – A model where data is represented as objects, as in object-oriented
programming (e.g., db4o, ObjectDB).
 Binary ❌ – Not a recognized database model. "Binary" refers to a data format, not a database
model.

So, Binary is not a type of database model.

You said:
Which database management system (DBMS) is known for its open-source nature and use of
SQL? Microsoft Access Oracle Database MongoDB PostgreSQL

The correct answer is:

✅ PostgreSQL

Explanation:
 ✅ PostgreSQL – An open-source and powerful relational database management
system (RDBMS) that uses SQL (Structured Query Language). It's widely used for web apps,
data analytics, and enterprise systems.
 Microsoft Access – A desktop DBMS from Microsoft. It uses SQL but is not open-source.
 Oracle Database – A powerful and widely used RDBMS, but it is proprietary (not open-
source).
 MongoDB – An open-source NoSQL database, but it does not use SQL.

Conclusion:
PostgreSQL is the open-source DBMS known for using SQL.

You said:
What is the primary key in a relational database used for? To establish relationships between
tables To ensure that data is stored in binary format To uniquely identify records in a table To
create backup copies of the database

The correct answer is:

✅ To uniquely identify records in a table

 Explanation:
 Primary Key: A primary key is a column (or a combination of columns) in a table
that uniquely identifies each row in that table. No two rows can have the same primary key
value, and it cannot be null.

Why the other options are incorrect:

 ❌ To establish relationships between tables – That’s the job of foreign keys, not primary keys
(although foreign keys often reference a table’s primary key).
 ❌ To ensure that data is stored in binary format – Irrelevant to the purpose of a primary key.
 ❌ To create backup copies of the database – That’s done through backup utilities, not
primary keys.

So:
✔️Primary key = Uniquely identifies records in a table.

You said:
In a relational database, what is a foreign key? A key used for encrypting data A key used to
unlock database tables A key that points to a primary key in another table A key used to sort data
alphabetically

The correct answer is:

✅ A key that points to a primary key in another table

Explanation:

A foreign key is a column (or set of columns) in one table that references the primary key in
another table. It is used to establish and enforce a link (relationship) between the data in two
tables.

Why the others are incorrect:

 ❌ A key used for encrypting data – That would be an encryption key, not a foreign key.
 ❌ A key used to unlock database tables – Databases don’t use keys to "unlock" tables in this
sense.
 ❌ A key used to sort data alphabetically – Sorting is done using ORDER BY, not with keys.

Summary:
✔️A foreign key helps maintain referential integrity between related tables.

Week 1 Assignment
Description
This week, we'll be diving into the exciting world of SQL and databases.

We'll explore what SQL is used for, how it benefits web applications, and the
building blocks of databases: tables, columns, and data types.

-- 📝 Assignment: Introduction to Databases

-- Student: Edris Abdella (replace with your full name if needed)

-- 🎯 Question 1: Write an SQL query to create a new database called salesDB.

CREATE DATABASE salesDB;

-- 🎯 Question 2: Write an SQL query to drop (delete) the database called demo.

DROP DATABASE demo;