Database Management

1. Database Fundamentals & SQL Basics (MySQL & PostgreSQL)

A relational database stores data in tables (like spreadsheets) with rows (records) and columns (fields). SQL (Structured Query Language) is the universal language for interacting with databases. Popular database systems include MySQL (open-source, widely used in web apps) and PostgreSQL (advanced features, great for complex queries).

• A table has a fixed schema: each column has a name and a data type (INT, VARCHAR, DATE).
• SELECT * FROM students retrieves all columns and rows from the students table.
• Use WHERE to filter: SELECT * FROM students WHERE city = 'Juba';
• Always end SQL statements with a semicolon (;) — the standard in both MySQL and PostgreSQL.

💡 Practical Skill: Install MySQL Workbench (GUI for MySQL) or pgAdmin (GUI for PostgreSQL). Practice in the terminal with mysql> or psql>. Both are free and run on Windows, macOS, and Linux.

-- Create a new database
CREATE DATABASE rescue_academy;

-- Use the database
USE rescue_academy;  -- MySQL
-- \c rescue_academy  -- PostgreSQL alternative

-- Create a students table
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- SERIAL in PostgreSQL
    name VARCHAR(100) NOT NULL,
    age INT,
    city VARCHAR(50),
    program VARCHAR(100)
);

-- Insert sample data
INSERT INTO students (name, age, city, program)
VALUES 
    ('Grace', 22, 'Juba', 'Database Management'),
    ('John', 24, 'Malakal', 'Web Development'),
    ('Amina', 21, 'Wau', 'Data Science');

-- Query: show all students
SELECT * FROM students;

2. Querying & Filtering Data

The SELECT statement is the most common SQL command. You can filter rows with WHERE, sort with ORDER BY, and limit results with LIMIT (MySQL/PostgreSQL) or TOP (SQL Server). Filtering is essential for generating reports and finding specific records in large datasets.

• SELECT name, program FROM students WHERE age > 21; — filters students older than 21.
• ORDER BY age DESC — sorts from oldest to youngest.
• LIMIT 3 — returns only the first 3 rows.
• Use LIKE for pattern matching: WHERE city LIKE 'J%' finds cities starting with "J".

💡 Practical Skill: Use MySQL Workbench or pgAdmin to visually build queries. Export results to CSV for reports. Track your schema changes with Git by saving SQL scripts as .sql files.

-- Find all students from Juba
SELECT name, program, age
FROM students
WHERE city = 'Juba';

-- Sort students by age (youngest first)
SELECT name, age, city
FROM students
ORDER BY age ASC;

-- Find the top 2 oldest students
SELECT name, age, program
FROM students
ORDER BY age DESC
LIMIT 2;

-- Pattern search: students whose name starts with 'G'
SELECT * FROM students
WHERE name LIKE 'G%';
-- Output: Grace, 22, Juba, Database Management

3. Joins & Table Relationships

Databases use relationships to connect tables via foreign keys. A JOIN combines rows from two or more tables based on a related column. The most common type is INNER JOIN, which returns only matching rows. This is how you link students to their enrolled courses, orders to customers, or projects to teams.

• A primary key (e.g., student_id) uniquely identifies each row in a table.
• A foreign key (e.g., student_id in the enrollments table) references the primary key of another table.
• INNER JOIN returns only rows with matching keys in both tables.
• LEFT JOIN returns all rows from the left table, even if no match exists on the right.

💡 Practical Skill: Design your database on paper first — draw Entity-Relationship Diagrams (ERDs) using tools like draw.io or MySQL Workbench. A clear schema prevents data duplication and keeps your queries fast.

-- Create a related enrollments table
CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course VARCHAR(100),
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

-- Insert enrollment data
INSERT INTO enrollments (student_id, course, enrollment_date)
VALUES 
    (1, 'Database Management', '2026-01-15'),
    (1, 'SQL Advanced', '2026-02-01'),
    (2, 'Web Development', '2026-01-20'),
    (3, 'Data Science', '2026-01-25');

-- INNER JOIN: show student names with their courses
SELECT s.name, e.course, e.enrollment_date
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
ORDER BY s.name;
-- Output: Grace (Database Management, 2026-01-15)
-- Output: Grace (SQL Advanced, 2026-02-01)
-- Output: John (Web Development, 2026-01-20)
-- Output: Amina (Data Science, 2026-01-25)

-- LEFT JOIN: show ALL students even if not enrolled
SELECT s.name, e.course
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id;

4. Aggregation & Reporting

Aggregate functions perform calculations across multiple rows and return a single value. COUNT(), SUM(), AVG(), MIN(), and MAX() are the building blocks of business reports. Combined with GROUP BY, you can summarize data by category — like counting students per program or calculating average age per city.

• COUNT(*) counts all rows in a group.
• AVG(age) calculates the average age.
• GROUP BY program groups results by each unique program name.
• Use HAVING (not WHERE) to filter after aggregation.

💡 Practical Skill: Build reports that NGOs and businesses need — e.g., "Number of beneficiaries per region" or "Total monthly expenses." Use MySQL Workbench to export query results to Excel for further analysis.

-- Count total students
SELECT COUNT(*) AS total_students FROM students;

-- Average age of all students
SELECT AVG(age) AS average_age FROM students;

-- Count students per program
SELECT program, COUNT(*) AS student_count
FROM students
GROUP BY program;

-- Average age per city (only cities with at least 1 student)
SELECT city, AVG(age) AS avg_age, COUNT(*) AS count
FROM students
GROUP BY city
HAVING COUNT(*) >= 1
ORDER BY avg_age DESC;

-- Find the oldest student overall
SELECT name, age FROM students
WHERE age = (SELECT MAX(age) FROM students);

5. Indexing, Performance & Database Security

As your database grows, queries can become slow. Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Think of an index like a book's table of contents. Database security involves user permissions, backups, and protecting against SQL injection — one of the most common web vulnerabilities.

• Create an index: CREATE INDEX idx_city ON students(city); — speeds up queries filtering by city.
• Never concatenate user input directly into SQL — use parameterized queries (prepared statements).
• Backup your database regularly: mysqldump -u root rescue_academy > backup.sql
• Grant least-privilege permissions: GRANT SELECT ON rescue_academy.* TO 'readonly_user';

💡 Practical Skill: Schedule automated backups with cron (Linux) or Task Scheduler (Windows). Use MySQL Workbench or pgAdmin to manage users and permissions visually. Always test backups by restoring to a staging server.

-- Create an index to speed up city-based searches
CREATE INDEX idx_students_city ON students(city);

-- Show index usage (MySQL)
EXPLAIN SELECT * FROM students WHERE city = 'Juba';
-- PostgreSQL: EXPLAIN ANALYZE SELECT ...

-- Create a read-only user for reporting
-- MySQL:
CREATE USER 'reporter'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT ON rescue_academy.* TO 'reporter'@'localhost';

-- PostgreSQL:
-- CREATE USER reporter WITH PASSWORD 'secure_password';
-- GRANT CONNECT ON DATABASE rescue_academy TO reporter;
-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;

-- Parameterized query example (pseudo-code in Python/Java)
-- Python: cursor.execute("SELECT * FROM students WHERE city = %s", (user_input,))
-- Java:  PreparedStatement stmt = conn.prepareStatement("SELECT * FROM students WHERE city = ?");

-- Backup command (run in terminal, not inside SQL prompt)
-- mysqldump -u root -p rescue_academy > rescue_academy_backup.sql
-- pg_dump -U postgres rescue_academy > rescue_academy_backup.sql

Quick Quiz — Database Management Basics