Week 5
| Site: | Newgate University Minna - Elearning Platform |
| Course: | Data Management |
| Book: | Week 5 |
| Printed by: | Guest user |
| Date: | Sunday, 14 June 2026, 4:02 AM |
Description
Teaching and Learning Methods to be Employed
1. Interactive lecture with live SQL demonstrations in MySQL
2. Hands-on SQL lab session — students write and execute queries
3. Pair programming: SQL query challenges
4. Real dataset exercise using Nigerian banking/demographic sample data
Learning Outcomes / Objectives
By the end of this week, students should be able to:
1. Write SQL DDL statements to create and alter tables.
2. Write SQL DML statements to insert, update, and delete data.
3. Write SQL SELECT queries with WHERE, ORDER BY, and LIMIT clauses.
4. Apply SQL aggregate functions (COUNT, SUM, AVG, MIN, MAX) with GROUP BY and HAVING.
5. Write SQL JOIN queries to combine data from multiple tables.
6. Write basic subqueries.
5.1 SQL Overview
SQL (Structured Query Language) is the standard language for communicating with relational database management systems. It was developed at IBM in the 1970s based on Codd's relational model. SQL is declarative, you specify WHAT data you want, not HOW to retrieve it.
5.2 Data Definition Language (DDL)
5.2.1 CREATE TABLE
Syntax and example — creating a CUSTOMER table for a Nigerian microfinance bank:
CREATE TABLE CUSTOMER (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
BVN VARCHAR(11) UNIQUE NOT NULL,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
PhoneNumber VARCHAR(15) NOT NULL,
LGA VARCHAR(100),
State VARCHAR(50) DEFAULT 'Niger State',
AccountType ENUM('Savings','Current','Fixed') DEFAULT 'Savings',
DateOpened DATE NOT NULL,
Balance DECIMAL(15,2) DEFAULT 0.00,
IsActive BOOLEAN DEFAULT TRUE
);
5.2.2 ALTER TABLE
ALTER TABLE CUSTOMER ADD COLUMN Email VARCHAR(100);
ALTER TABLE CUSTOMER MODIFY COLUMN PhoneNumber VARCHAR(14) NOT NULL;
ALTER TABLE CUSTOMER DROP COLUMN IsActive;
5.3 Data Manipulation Language (DML)
5.3.1 INSERT
INSERT INTO CUSTOMER (BVN, FirstName, LastName, PhoneNumber, LGA, State, AccountType, DateOpened, Balance)
VALUES ('12345678901', 'Abdulrahman', 'Musa', '08012345678', 'Chanchaga', 'Niger State', 'Savings', '2024-01-15', 50000.00);
5.3.2 UPDATE
-- Give all Savings account customers in Minna a 5% balance top-up
UPDATE CUSTOMER
SET Balance = Balance * 1.05
WHERE AccountType = 'Savings' AND LGA = 'Chanchaga';
5.3.3 DELETE
-- Delete inactive customers who have zero balance
DELETE FROM CUSTOMER
WHERE Balance = 0.00 AND IsActive = FALSE;
5.4 Data Query Language — SELECT
5.4.1 Basic SELECT
-- Retrieve all customers
SELECT * FROM CUSTOMER;
-- Retrieve specific columns
SELECT FirstName, LastName, Balance, State FROM CUSTOMER;
-- With WHERE clause — customers with balance over ₦100,000
SELECT FirstName, LastName, Balance FROM CUSTOMER
WHERE Balance > 100000 AND AccountType = 'Savings';
-- ORDER BY — Sort by balance descending
SELECT FirstName, LastName, Balance FROM CUSTOMER
ORDER BY Balance DESC LIMIT 10;
5.4.2 Aggregate Functions
SELECT COUNT(*) AS TotalCustomers FROM CUSTOMER;
SELECT SUM(Balance) AS TotalDeposits FROM CUSTOMER WHERE AccountType = 'Savings';
SELECT AVG(Balance) AS AverageBalance FROM CUSTOMER;
SELECT MAX(Balance) AS HighestBalance, MIN(Balance) AS LowestBalance FROM CUSTOMER;
5.4.3 GROUP BY and HAVING
-- Count customers and total balance per State
SELECT State, COUNT(*) AS NumCustomers, SUM(Balance) AS TotalBalance
FROM CUSTOMER
GROUP BY State
HAVING SUM(Balance) > 1000000
ORDER BY TotalBalance DESC;
|
GROUP BY vs WHERE: WHERE filters ROWS before grouping. HAVING filters GROUPS after grouping. You cannot use aggregate functions in a WHERE clause — use HAVING instead. E.g., to find states with more than 100 customers, use HAVING COUNT(*) > 100, not WHERE COUNT(*) > 100. |
5.4.4 SQL JOINs
JOINs combine rows from two or more tables based on a related column. Consider these tables for a university:
|
JOIN Type |
Description and Syntax |
|
INNER JOIN |
Returns rows that have matching values in BOTH tables. Most common join. SELECT s.MatricNo, s.FirstName, d.DeptName FROM STUDENT s INNER JOIN DEPARTMENT d ON s.DeptCode = d.DeptCode; |
|
LEFT (OUTER) JOIN |
Returns ALL rows from the LEFT table + matching rows from right. Non-matching right rows are NULL. SELECT s.FirstName, e.Grade FROM STUDENT s LEFT JOIN ENROLMENT e ON s.MatricNo = e.MatricNo; |
|
RIGHT (OUTER) JOIN |
Returns ALL rows from the RIGHT table + matching rows from left. Non-matching left rows are NULL. |
|
FULL OUTER JOIN |
Returns ALL rows from BOTH tables. NULLs where no match. (Not directly in MySQL — simulate with UNION of LEFT and RIGHT joins.) |
|
CROSS JOIN |
Cartesian product — every row in left joined with every row in right. Use cautiously. |
|
SELF JOIN |
Joining a table with itself. Useful for hierarchical data (e.g., employee reports to manager who is also an employee). |
MULTI-TABLE JOIN EXAMPLE — Show student names with their enrolled course names and grades:
SELECT s.FirstName, s.LastName, c.CourseName, e.Grade
FROM STUDENT s
INNER JOIN ENROLMENT e ON s.MatricNo = e.MatricNo
INNER JOIN COURSE c ON e.CourseCode = c.CourseCode
WHERE e.Semester = 'First' AND e.Grade IS NOT NULL
ORDER BY s.LastName, c.CourseName;
5.4.5 Subqueries
A subquery is a SELECT statement nested inside another SQL statement.
-- Find all customers with above-average balance
SELECT FirstName, LastName, Balance FROM CUSTOMER
WHERE Balance > (SELECT AVG(Balance) FROM CUSTOMER);
-- Find departments that have no students (using NOT IN)
SELECT DeptName FROM DEPARTMENT
WHERE DeptCode NOT IN (SELECT DISTINCT DeptCode FROM STUDENT);
Reading List / References
1. Ramakrishnan, R. & Gehrke, J. (2003). Database Management Systems, Chapters 4 & 5: SQL. McGraw-Hill.
2. MySQL 8.0 Reference Manual. Chapter 13: SQL Statements. dev.mysql.com/doc
3. Silberschatz et al. (2020). Database System Concepts, Chapters 3 & 4: SQL. McGraw-Hill.
Activities
|
Self-Assessment Quiz: 1. What is the difference between WHERE and HAVING in SQL? 2. Write an SQL query to find the total deposits and number of accounts for each account type in the CUSTOMER table. 3. What is an INNER JOIN? Write a query joining STUDENT and DEPARTMENT to show each student's name and department name. 4. Write a subquery to find all lecturers whose salary is above the average salary in their department. |