Advanced Relational Database Operations: Joins, Subqueries, and View Projections

SS3 Term 1 • Week 4

About 45 minutes

In Senior Secondary 2, you learned how to normalize database tables into Third Normal Form (3NF) to eliminate data redundancy. You split messy single spreadsheets into separate, clean tables: Students, Courses, and Enrollments.

However, when a school principal requests a report—such as "Print a list of all students, their class teachers, and their term exam marks"—the data is now spread across three different physical tables!

How do database engines reassemble normalized data at lightning speed without corrupting consistency?

The answer lies in Relational Algebra and Advanced SQL Joins. In this lesson, we master INNER and OUTER JOINs, construct nested Subqueries, and build secure virtual Views.

Learning Outcomes

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

Synthesize data across relations using INNER JOIN and LEFT OUTER JOIN.
Handle NULL values generated by unmatched outer join records.
Write nested Subqueries using IN, EXISTS, and scalar comparisons.
Create Virtual Views (CREATE VIEW) to simplify reporting and protect sensitive column data.

Key Vocabulary

Term	Technical Definition
INNER JOIN	A relational query operation that merges rows from two tables only when their specified join keys match.
LEFT OUTER JOIN	Merges all rows from the left table with matching rows from the right table; unmatched rows on the right receive `NULL` values.
Subquery	A query nested inside another SQL statement (e.g., inside a `WHERE` or `FROM` clause).
Database View	A saved virtual table defined by a `SELECT` query, offering simplified access and column security without duplicating physical data.
Correlated Subquery	A subquery that references attributes from the enclosing outer query, re-evaluating for every outer row.
Cartesian Product (CROSS JOIN)	A join producing all possible combinations of rows from two tables ($N \times M$ rows).

Visualizing Joins: INNER vs OUTER

Consider two tables: Students and LibraryLoans:

Table: Students                     Table: LibraryLoans
┌────────────┬───────────────┐     ┌─────────┬────────────┬───────────────┐
│ student_id │ student_name  │     │ loan_id │ student_id │ book_title    │
├────────────┼───────────────┤     ├─────────┼────────────┼───────────────┤
│ 1          │ Amina         │     │ 101     │ 1          │ Things Fall A.│
│ 2          │ Chinedu       │     │ 102     │ 1          │ Arrow of God  │
│ 3          │ Favour        │     │ 103     │ 2          │ The Concubine │
│ 4          │ Ibrahim       │     └─────────┴────────────┴───────────────┘
└────────────┴───────────────┘     (Notice: Favour and Ibrahim borrowed NO books!)

1. The INNER JOIN

Returns only rows with matches in both tables:

SELECT s.student_name, l.book_title
FROM Students s
INNER JOIN LibraryLoans l ON s.student_id = l.student_id;

Result: Returns 3 rows (Amina with 2 books, Chinedu with 1 book). Favour and Ibrahim are excluded.

2. The LEFT OUTER JOIN

Returns all students from the left table, regardless of whether they borrowed a book:

SELECT s.student_name, l.book_title
FROM Students s
LEFT JOIN LibraryLoans l ON s.student_id = l.student_id;

Result: - Amina $\rightarrow$ Things Fall Apart - Amina $\rightarrow$ Arrow of God - Chinedu $\rightarrow$ The Concubine - Favour $\rightarrow$ NULL - Ibrahim $\rightarrow$ NULL

Insight: A LEFT JOIN is ideal when you want to find students who have not borrowed books: WHERE l.loan_id IS NULL;!

Nested Subqueries: Queries Within Queries

A subquery is a query embedded within another query.

Example: Find all students whose exam score is above the school-wide average:

Instead of running two separate manual queries, combine them in one SQL statement:

SELECT full_name, exam_score
FROM results
WHERE exam_score > (
    SELECT AVG(exam_score) FROM results
);

The inner query (SELECT AVG...) executes first, returns a single scalar number (e.g., $72.4$), and the outer query filters records above that threshold!

Virtual Views: Security and Simplicity

Suppose school staff need to view student contact information, but the database table also contains confidential medical diagnosis history and parent salary records.

We do NOT grant staff direct access to the base table! Instead, we create a Virtual View:

CREATE VIEW public_student_directory AS
SELECT 
    student_id,
    full_name,
    class_arm,
    emergency_contact
FROM student_master_records;

Staff query the view just like a regular table (SELECT * FROM public_student_directory), but confidential columns are invisible and inaccessible!

Practical Activity: Multi-Table Joins and Views in Python SQLite

Let us write a comprehensive Python script that sets up three related tables, runs INNER and LEFT joins, and compiles a performance view.

Step 1: Create `advanced_sql_lab.py`

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("PRAGMA foreign_keys = ON;")

# 1. Create Schema
cursor.execute("""
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL
);
""")

cursor.execute("""
CREATE TABLE courses (
    course_code TEXT PRIMARY KEY,
    course_title TEXT NOT NULL,
    credit_units INTEGER NOT NULL
);
""")

cursor.execute("""
CREATE TABLE enrollments (
    enrollment_id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    course_code TEXT NOT NULL,
    score INTEGER NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_code) REFERENCES courses(course_code)
);
""")

# 2. Populate Records
cursor.executemany("INSERT INTO students VALUES (?, ?, ?);", [
    (1, "Amina Bello", "Science"),
    (2, "Chinedu Okafor", "Science"),
    (3, "Favour Adebayo", "Arts"),
    (4, "Musa Danladi", "Commercial") # Musa has no enrollments yet
])

cursor.executemany("INSERT INTO courses VALUES (?, ?, ?);", [
    ("COM301", "Computer Architecture", 3),
    ("MTH301", "Discrete Mathematics", 3),
    ("LIT301", "African Literature", 2)
])

cursor.executemany("INSERT INTO enrollments (student_id, course_code, score) VALUES (?, ?, ?);", [
    (1, "COM301", 88),
    (1, "MTH301", 76),
    (2, "COM301", 92),
    (3, "LIT301", 84)
])
conn.commit()

# Query 1: INNER JOIN (Only enrolled students)
print("=== 1. INNER JOIN (Active Course Enrollments) ===")
cursor.execute("""
SELECT s.name, c.course_title, e.score
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_code = c.course_code
ORDER BY s.name;
""")
for r in cursor.fetchall():
    print(f"Student: {r[0]:<16} | Course: {r[1]:<22} | Score: {r[2]}")

# Query 2: LEFT JOIN (Find students with NO enrollments)
print("\n=== 2. LEFT JOIN AUDIT (Identify Unenrolled Students) ===")
cursor.execute("""
SELECT s.name, s.department, e.course_code
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_code IS NULL;
""")
for r in cursor.fetchall():
    print(f"UNENROLLED STUDENT: {r[0]} ({r[1]} Department)")

# Query 3: Create a Virtual View
cursor.execute("""
CREATE VIEW honor_roll AS
SELECT s.name, c.course_title, e.score
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_code = c.course_code
WHERE e.score >= 85;
""")

print("\n=== 3. VIRTUAL VIEW: HONOR ROLL (Score >= 85) ===")
cursor.execute("SELECT * FROM honor_roll;")
for r in cursor.fetchall():
    print(f"HONORS: {r[0]:<16} in {r[1]} with score {r[2]}")

conn.close()

Step 2: Run the Script

python advanced_sql_lab.py

Expected Results and Model Output

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

=== 1. INNER JOIN (Active Course Enrollments) ===
Student: Amina Bello      | Course: Computer Architecture  | Score: 88
Student: Amina Bello      | Course: Discrete Mathematics   | Score: 76
Student: Chinedu Okafor   | Course: Computer Architecture  | Score: 92
Student: Favour Adebayo   | Course: African Literature     | Score: 84

=== 2. LEFT JOIN AUDIT (Identify Unenrolled Students) ===
UNENROLLED STUDENT: Musa Danladi (Commercial Department)

=== 3. VIRTUAL VIEW: HONOR ROLL (Score >= 85) ===
HONORS: Amina Bello      in Computer Architecture with score 88
HONORS: Chinedu Okafor   in Computer Architecture with score 92

Common Mistakes to Avoid

Forgetting Table Aliases in Multi-Table Joins: Without clear aliases (s, c, e), joining tables with identical column names (e.g., id) triggers "ambiguous column name" syntax errors.
Confusing INNER and LEFT JOIN: Using an INNER JOIN when generating a financial report of all customers will silently erase customers who have not made a purchase this month. Always use LEFT JOIN when reporting on parent entities.
Cartesian Explosions from Missing Join Conditions: Writing SELECT * FROM students, courses; without an ON or WHERE clause generates a Cartesian product ($4 \times 3 = 12$ rows), pairing every student with every course arbitrarily!

Notebook Summary

INNER JOIN selects rows with matching keys in both participating tables.
LEFT OUTER JOIN preserves all records from the left relation, substituting NULL for missing right-hand attributes.
Subqueries allow modular, multi-tier data filtering (e.g., finding entities above an aggregate average).
Views (CREATE VIEW) create saved virtual relations that abstract complex join logic and enforce database security.

Optional Extension Challenge

Write a SQL query utilizing GROUP BY and AVG() on the enrollments table to calculate each student's Grade Point Average (GPA), joining with the students table to display name and gpa sorted from highest to lowest.