Week 5
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.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;