Structured Query Language (SQL): DDL, DML, Filtering, and Aggregation

SS2 Term 1 • Week 7

About 45 minutes

In the previous lessons, you designed relational databases on paper and normalized tables to Third Normal Form.

Now, how do software programs, web apps, and data analysts actually communicate with database management systems like PostgreSQL, MySQL, and SQLite?

The universal standard language of relational databases is Structured Query Language (SQL). Developed by IBM in the 1970s and standardized by ANSI and ISO, SQL is a declarative language: you specify what data you want, and the database query optimizer figures out how to retrieve it efficiently.

In this lesson, we master the two main branches of SQL: Data Definition Language (DDL) and Data Manipulation Language (DML).

Learning Outcomes

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

Distinguish between DDL (CREATE, ALTER, DROP) and DML (SELECT, INSERT, UPDATE, DELETE).
Define database structures using data types, PRIMARY KEY, and NOT NULL constraints.
Filter and sort records using WHERE, AND/OR/NOT, LIKE, and ORDER BY.
Aggregate data using COUNT(), SUM(), AVG(), MIN(), MAX(), and GROUP BY.

Key Vocabulary

Term	Technical Definition
SQL	Structured Query Language; standard declarative programming language for managing relational databases.
DDL	Data Definition Language; SQL subset used to create, modify, or destroy database structures (tables, indices).
DML	Data Manipulation Language; SQL subset used to retrieve, insert, update, and remove stored data records.
Wildcard (`%`)	A symbol in SQL pattern matching representing zero, one, or multiple arbitrary characters.
Aggregate Function	A SQL function that calculates a single summary scalar value from multiple rows (e.g., `SUM()`, `AVG()`).
GROUP BY	A SQL clause that groups rows with identical values into summary rows, typically used with aggregate functions.

DDL vs DML: Understanding the Two Halves of SQL

SQL is divided into distinct operational sub-languages:

┌─────────────────────────────────────────────────────────────────────────┐
│                      STRUCTURED QUERY LANGUAGE (SQL)                    │
├────────────────────────────────────┬────────────────────────────────────┤
│   DDL (Data Definition Language)   │  DML (Data Manipulation Language)  │
│   Builds & alters the containers   │   Manages the data inside tables   │
├────────────────────────────────────┼────────────────────────────────────┤
│ - CREATE TABLE                     │ - INSERT INTO                      │
│ - ALTER TABLE                      │ - SELECT ... FROM                  │
│ - DROP TABLE                       │ - UPDATE ... SET                   │
│ - CREATE INDEX                     │ - DELETE FROM                      │
└────────────────────────────────────┴────────────────────────────────────┘

Writing DDL: Creating Tables with Precision

When defining a table, each column requires an explicit Data Type and optional Integrity Constraints:

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name TEXT NOT NULL,
    age INTEGER CHECK (age >= 10 AND age <= 22),
    state_of_origin TEXT NOT NULL,
    exam_score REAL DEFAULT 0.0,
    registration_date TEXT NOT NULL
);

Constraints:

PRIMARY KEY: Guarantees uniqueness and non-null values.
NOT NULL: Prevents missing/empty fields.
CHECK: Enforces business validation rules before saving data.
DEFAULT: Supplies a fallback value when none is provided.

Writing DML: Querying, Filtering, and Aggregating Data

The cornerstone of SQL is the SELECT query:

SELECT column1, column2 FROM table_name WHERE condition ORDER BY column1 ASC;

1. Filtering with Operators:

Comparison: =, != (or <>), >, <, >=, <=
Range: WHERE exam_score BETWEEN 70 AND 100;
List inclusion: WHERE state_of_origin IN ('Lagos', 'Ogun', 'Oyo');
Text pattern matching: WHERE full_name LIKE 'Chi%'; (% matches any characters).

2. Aggregations and Grouping:

Aggregate functions compute metrics across multiple rows: - COUNT(*): Total number of rows matching condition. - AVG(exam_score): Arithmetic mean score. - SUM(fee_paid): Total sum. - MAX(exam_score), MIN(exam_score): Range extremes.

-- Calculate average score and student count per state
SELECT 
    state_of_origin,
    COUNT(*) AS total_students,
    AVG(exam_score) AS average_score
FROM students
WHERE exam_score > 0
GROUP BY state_of_origin
HAVING COUNT(*) >= 2
ORDER BY average_score DESC;

Practical Activity: Building a Nigerian Student Scorecard in SQL

Let us write an executable Python script using SQLite to create a student records database, insert records from various Nigerian states, and execute advanced analytical queries.

Step 1: Create `sql_academy_lab.py`

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

# 1. DDL: Create Tables
cursor.execute("""
CREATE TABLE results (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    gender TEXT NOT NULL,
    subject TEXT NOT NULL,
    score INTEGER NOT NULL,
    state TEXT NOT NULL
);
""")

# 2. DML: Insert Student Score Records
sample_data = [
    (1, 'Amina Bello', 'F', 'Computer Studies', 88, 'Kano'),
    (2, 'Chinedu Okafor', 'M', 'Computer Studies', 94, 'Enugu'),
    (3, 'Favour Adebayo', 'F', 'Computer Studies', 72, 'Oyo'),
    (4, 'Ibrahim Musa', 'M', 'Computer Studies', 65, 'Kaduna'),
    (5, 'Zainab Umar', 'F', 'Computer Studies', 91, 'Kano'),
    (6, 'Emeka Nwosu', 'M', 'Computer Studies', 83, 'Enugu'),
    (7, 'Blessing John', 'F', 'Computer Studies', 79, 'Plateau'),
    (8, 'Tunde Bakare', 'M', 'Computer Studies', 85, 'Lagos'),
    (9, 'Halima Sani', 'F', 'Computer Studies', 69, 'Kano'),
    (10, 'Kelechi Eze', 'M', 'Computer Studies', 95, 'Enugu')
]

cursor.executemany("INSERT INTO results VALUES (?, ?, ?, ?, ?, ?);", sample_data)
conn.commit()

# Query 1: Find all students who scored 85 or above (Distinction)
print("=== DISTINCTION STUDENTS (Score >= 85) ===")
cursor.execute("SELECT name, score, state FROM results WHERE score >= 85 ORDER BY score DESC;")
for row in cursor.fetchall():
    print(f"Name: {row[0]:<16} | Score: {row[1]} | State: {row[2]}")

# Query 2: Aggregate Analysis by State using GROUP BY
print("\n=== STATE PERFORMANCE REPORT (GROUP BY) ===")
query_state = """
SELECT 
    state,
    COUNT(*) AS student_count,
    ROUND(AVG(score), 1) AS mean_score,
    MAX(score) AS top_score
FROM results
GROUP BY state
ORDER BY mean_score DESC;
"""
cursor.execute(query_state)
print(f"{'State':<12}{'Students':<12}{'Average':<12}{'Top Score'}")
print("-" * 46)
for row in cursor.fetchall():
    print(f"{row[0]:<12}{row[1]:<12}{row[2]:<12}{row[3]}")

conn.close()

Step 2: Run the Script

python sql_academy_lab.py

Expected Results and Model Output

When you run sql_academy_lab.py, your terminal will display:

=== DISTINCTION STUDENTS (Score >= 85) ===
Name: Kelechi Eze      | Score: 95 | State: Enugu
Name: Chinedu Okafor   | Score: 94 | State: Enugu
Name: Zainab Umar      | Score: 91 | State: Kano
Name: Amina Bello      | Score: 88 | State: Kano
Name: Tunde Bakare     | Score: 85 | State: Lagos

=== STATE PERFORMANCE REPORT (GROUP BY) ===
State       Students    Average     Top Score
----------------------------------------------
Enugu       3           90.7        95
Lagos       1           85.0        85
Kano        3           82.7        91
Plateau     1           79.0        79
Oyo         1           72.0        72
Kaduna      1           65.0        65

The database query optimizer handled the sorting and aggregation calculations instantaneously!

Common Mistakes to Avoid

Omitting the WHERE Clause in UPDATE or DELETE: Writing DELETE FROM results; without a WHERE clause deletes every single record in the table permanently! Always specify WHERE id = ?.
Filtering Aggregated Columns with WHERE Instead of HAVING: The WHERE clause filters individual rows before aggregation occurs. To filter based on the result of an aggregate function (e.g., COUNT(*) > 2), you must use the HAVING clause.
Using = for NULL Comparisons: In SQL, you cannot check WHERE score = NULL. Because NULL represents the absence of a value, comparisons must use WHERE score IS NULL or WHERE score IS NOT NULL.

Notebook Summary

DDL (CREATE, ALTER, DROP) defines the schema; DML (SELECT, INSERT, UPDATE, DELETE) manages data.
The SELECT query retrieves columns; WHERE filters rows using Boolean logic and pattern wildcards (%).
ORDER BY sorts records in ascending (ASC) or descending (DESC) order.
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) compute statistics, grouped across categories using GROUP BY.

Optional Extension Challenge

Write a SQL query that calculates the average score for male students versus female students from the results table. Display gender, student_count, and average_score rounded to 2 decimal places.