Databases and SQL for Web Applications

🎯Learning Objectives

A web application needs a data model that preserves meaning across requests. This lesson connects relational tables, keys, constraints and SQL to the backend boundaries developed in Levels 22–25. You will design a small course-enrollment schema and explain how joins and grouped reports treat missing relationships.

The interactive lab models a fixed reporting query over local tables. It is JavaScript, not a browser SQL engine or a deployed database. The SQL examples use SQLite-compatible syntax; other databases may require different types, identity syntax, parameters and pagination.

🗃️Relational Data Behind a Web Application

A relational database organizes rows into tables with named columns and rules. A learner is an entity; an enrollment describes a learner’s relationship to a course. A row should represent one clear fact rather than an unrelated mixture of profile, course and progress values.

The browser should interact with an authorized backend API. The backend validates input, performs database operations and returns an accepted result model. Do not expose database credentials or rely on a hidden frontend control to protect a record.

The schema and queries in this lesson are teaching examples. The patch does not create a production database, replace placement-platform tables or modify tracer backends.

🧩Entities and Many-to-Many Relationships

One learner can enroll in several courses and one course can have several learners. A junction table represents that many-to-many relationship: each enrollment references one learner and one course.

Keeping enrollments separate lets a learner have no enrollment and a course have no learners. A comma-separated course list inside a learner row makes referential checks, filtering and updates harder than explicit relationship rows.

The normal fixture has learners Asha, Ravi and Mina; courses HTML, JSP <Views> and SQL & Data. Asha completed HTML with grade 88 and is active in JSP with no grade. Ravi completed HTML with grade 72. Mina and SQL have no enrollment.

An absent enrollment is not an invalid foreign key. It means no relationship row exists. An enrollment pointing to a nonexistent learner or course is a different integrity failure.

🔑Primary Keys, Foreign Keys and Pair Uniqueness

A primary key identifies a row. A foreign key requires a referenced relationship to satisfy the database’s rules. A composite enrollment key prevents the same learner/course pair being inserted twice in this model.

Names and titles are display values rather than stable relationship identifiers. Two learners can have the same display name, and a title can change without changing the enrollment references.

Choose delete behavior deliberately. The illustrative schema does not use cascading deletion; with foreign-key enforcement active, a referenced parent cannot simply disappear while leaving its enrollment behind.

CREATE TABLE learners (
    learner_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);
CREATE TABLE enrollments (
    learner_id INTEGER NOT NULL REFERENCES learners(learner_id),
    course_id INTEGER NOT NULL REFERENCES courses(course_id),
    status TEXT NOT NULL CHECK (status IN ('active', 'completed')),
    grade INTEGER CHECK (grade BETWEEN 0 AND 100),
    PRIMARY KEY (learner_id, course_id)
);

SQLite foreign-key enforcement must be enabled for the connection, outside an active transaction, and verified. The validation script uses PRAGMA foreign_keys = ON. These manually supplied integer IDs avoid implying one portable identity-generation syntax for every database.

🧱Types, Nullability and Constraints

NOT NULL requires a value; CHECK constrains acceptable values; a key constrains identity or pair uniqueness. Each rule addresses a different property. A CHECK on grade alone permits NULL in this SQLite example, so a missing grade is allowed deliberately.

NULL represents missing or unknown data rather than zero or an empty string. Active enrollment with no grade must not be automatically interpreted as grade zero. The application still needs bounded input rules and permission checks.

SQLite has its own type-affinity behavior. The SQL schema is not a promise that every supplied value has been validated as a bounded JavaScript/Java integer. The lab’s application fixture validator explicitly enforces integer grades or null and exact enum strings.

A constraint violation is not a reason to silently drop data or report success. Translate it into an appropriate application outcome while retaining useful private diagnostics.

🧹Normalization and Update Anomalies

Separating a course title from each enrollment avoids editing every enrollment row when the title changes. Keeping learner identity in its own table likewise avoids inconsistent copies of one person’s profile.

Normalization is about dependencies and meaningful facts, not simply splitting every column into a separate table. An enrollment’s status and grade describe its learner/course pair, so they belong with that relationship in this example.

Deliberate denormalization can help some workloads, but it introduces synchronization responsibilities. Start with a clear source of truth before adding cached counts or repeated labels.

Avoid repeated profile/course data in every enrollment:
learner_name, learner_email, course_title, status, grade

Prefer related facts:
learners(learner_id, name)
courses(course_id, title)
enrollments(learner_id, course_id, status, grade)

The lab derives enrollment counts from relationship rows instead of trusting a client-supplied count. It has no stored aggregate cache to synchronize.

📋SELECT, WHERE and Deterministic Ordering

SELECT chooses result columns. WHERE filters rows by conditions. ORDER BY states the intended result order; without it, a particular row order is not guaranteed. LIMIT restricts a sorted result in this dialect.

Read explicit columns and aliases for a stable application contract. Bind values such as a status or minimum count rather than concatenating them into SQL. Dynamic join/count choices in the lab come from fixed allowlists.

Pagination syntax varies across database products. Even a stable order needs a suitable tie breaker, and offset-based pagination can shift under concurrent changes. The lab orders unique course IDs and limits the finished groups.

SELECT course_id, title
FROM courses
ORDER BY course_id
LIMIT ?;

-- Bind a validated limit; do not concatenate arbitrary SQL.

Filtering and limiting happen at different stages. Taking the first course before grouping or HAVING can discard the course that should have met the report threshold.

✏️INSERT, UPDATE and DELETE with Deliberate Scope

INSERT creates a row, UPDATE changes matching rows and DELETE removes matching rows. Define a precise predicate before changing stored data; an omitted WHERE can affect many rows. Check the intended affected-row count and transaction outcome.

A new enrollment must reference existing parents and satisfy pair uniqueness. Updating its status should identify the learner/course pair. Deleting a parent requires a deliberate relationship policy rather than accidental orphan rows.

The following statements are examples for a controlled test database. The course lab is read-only and does not execute these mutations or modify a server.

INSERT INTO enrollments (learner_id, course_id, status, grade)
VALUES (?, ?, ?, ?);

UPDATE enrollments
SET status = ?, grade = ?
WHERE learner_id = ? AND course_id = ?;

DELETE FROM enrollments
WHERE learner_id = ? AND course_id = ?;

Validate and authorize the operation before accessing data, then check database constraints too. Prepared values do not grant permission to modify the referenced learner.

🔗INNER JOIN and LEFT JOIN

An inner join returns matching row combinations. A left join additionally preserves a left-side row when there is no matching right-side row, filling the right-side columns with NULL.

Starting from courses lets the report include SQL even though nobody enrolled, when the left join is used. An inner join has no matching enrollment for SQL, so it contributes no report group.

Matching one course to two enrollments produces two joined rows. Do not mistake those repeated course columns for duplicate course records or add DISTINCT to conceal a legitimate one-to-many join.

SELECT c.course_id, c.title, e.learner_id, e.status
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.course_id
ORDER BY c.course_id, e.learner_id;

The lab displays those intermediate rows so the subsequent counts and filters are visible. It models only this fixed equijoin, not arbitrary SQL syntax.

🎯ON Filters and WHERE Filters Differ

For a left join, placing a right-table condition in ON limits which relationships match while preserving each left course. Putting that condition in WHERE filters after null-extension and can remove courses with no qualifying enrollment.

For completed status in ON, HTML has two matches while JSP and SQL retain null-extended rows. With completed in WHERE, the latter rows fail the status test and disappear. An inner join does not preserve unmatched courses either way for this fixed predicate.

The lab’s all-status option omits the status predicate altogether, so switching its ON/WHERE selector has no effect in that case.

-- Preserve courses with zero completed enrollments:
LEFT JOIN enrollments e
  ON e.course_id = c.course_id AND e.status = ?

-- Filter joined rows; unmatched courses do not pass:
LEFT JOIN enrollments e ON e.course_id = c.course_id
WHERE e.status = ?

These snippets illustrate different report requirements. Choose the placement that answers the intended question instead of assuming the two forms are interchangeable.

❔NULL, COUNT and AVG

COUNT(*) counts joined rows, including a null-extended placeholder from a left join. COUNT(e.learner_id) counts non-null learner values and therefore reports zero enrollments for an unmatched course.

AVG ignores null grades. HTML’s grades 88 and 72 average to 80. JSP’s ungraded enrollment and SQL’s missing enrollment produce NULL averages, not zero. Use an explicit missing-value display rather than treating no grade as a failing grade.

COUNT and AVG answer different questions. One count can report active enrollment even when no graded value exists. In the lab, the alternative COUNT(*) mode is labelled as joined-row count to make its difference visible.

SELECT c.course_id, c.title,
       COUNT(e.learner_id) AS enrollment_count,
       AVG(e.grade) AS average_grade
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
GROUP BY c.course_id, c.title
ORDER BY c.course_id;

To test for missing values, use IS NULL or IS NOT NULL rather than = NULL. SQL comparisons with NULL do not behave like ordinary equality between known values.

📊GROUP BY, HAVING and Report Stages

GROUP BY collects rows for aggregate calculation. WHERE filters input rows, whereas HAVING filters groups after aggregation. Apply the minimum count to the appropriate count expression and only then sort and limit the retained groups.

Group both course ID and title in the examples. Some databases permit particular non-grouped expressions under special rules; avoid relying on an arbitrary title from a group when designing a portable report.

The lab’s count choice is used consistently in SELECT and HAVING. Choosing joined-row count can make an unmatched left course appear to meet a minimum of one, which is wrong if the intended threshold is actual enrollments.

GROUP BY c.course_id, c.title
HAVING COUNT(e.learner_id) >= ?
ORDER BY c.course_id
LIMIT ?;

This is a teaching order for reasoning about results, not a claim about the engine’s physical execution plan. The optimizer can choose a different internal strategy.

⚡Indexes and Query Plans

An index can help locate rows or satisfy some ordering and relationship access patterns. It also uses space and adds work to mutations. An index is not a guarantee that every query becomes faster.

The enrollment key starts with learner_id. A separate index on course_id can support course-oriented lookup in the illustrative schema; inspect the actual workload and plan before adding many indexes.

EXPLAIN QUERY PLAN can show a SQLite plan. Its output is diagnostic and can vary across versions and data sizes. A local fixture with three rows does not establish production performance.

CREATE INDEX enrollments_by_course
ON enrollments(course_id);

EXPLAIN QUERY PLAN
SELECT learner_id, status
FROM enrollments
WHERE course_id = 101;

Check realistic cardinality, selectivity and database statistics. The browser lab does not model an optimizer or benchmark index performance.

🔄Transactions, Migrations and Backups

Related mutations can require one transaction so constraints and application invariants are maintained together. Level 25 demonstrated the difference between a paired operation and separate auto-committed statements. Real isolation and concurrency need database-specific checks.

Version schema changes as migrations and test them against the existing data. Adding NOT NULL or a relationship constraint can fail if older rows do not satisfy it. Do not assume a successful empty-database migration proves an upgrade is safe.

Backups need a tested restore procedure. A frontend deployment ZIP is not a database backup. Choose migration, backup and recovery methods appropriate to the actual engine and environment.

The schema validation script creates a temporary local database only. It neither connects to nor migrates CodeBhavya’s production data.

🛡️Application Boundaries and Verification

Database constraints protect data shape and relationships; backend authorization protects permitted access. Neither can be replaced by hiding a button or trusting a submitted learner ID. Use bound values, deliberate outputs and stable public errors.

Test joins with zero, one and multiple matching rows, status filters in both positions, null grades and duplicate/orphan mutations. Compare actual query results with independently expected rows, not only a visually attractive report.

The lesson’s fixed SQL queries are checked in a local SQLite database. Its frontend lab remains a separate JavaScript model; those checks do not verify a production connection, JDBC driver, server permissions or concurrent database behavior.

Next, Level 27 develops Web APIs and REST: the application contract between the frontend and backend data operations.

🧪Premium Interactive — Joins, Filters and Course Reports

Run a fixed read-only course report over local learner/course/enrollment fixtures. This is JavaScript, not a SQL interpreter or a database connection. The SQL panel is an explanatory prepared query built from allowlisted choices.

Compare inner versus left join, status filters in ON versus WHERE, actual enrollment count versus joined-row count, minimum group count and final limit. With all statuses, filter placement makes no difference. The minimum applies to the selected count expression. Results order by course ID before limiting.

Normal fixture: HTML has two completed grades 88/72; JSP <Views> has one active enrollment with NULL grade; SQL & Data has no enrollment. Average grade ignores NULL. Empty fixtures return a completed zero-row result; an orphan fixture fails the model contract before reporting.

No accepted query yet.

No joined rows yet.

No trace yet.

No accepted report.

No response yet.

🛠️Debugging — Ask Which Rows Survive

If an empty course disappears, inspect both join type and a right-table WHERE predicate. If it reports one learner despite no enrollment, inspect COUNT(*). If its average becomes zero, check whether the application incorrectly replaced NULL grades.

If duplicate enrollments exist, verify the composite key and whether the database constraints were actually enabled. If an orphan insert succeeds in SQLite, verify foreign_keys on the current connection outside a transaction.

The lab validates relationships before computing the report. Its stable errors clear old rows; a failed fixture does not leave a previous report looking current.

💬Interview Questions — Flip to Explain

❓MCQ Practice — Explain SQL Report Decisions

💻Extra Practice — Predict the Exact Report

📌Quick Revision

📖Glossary — Flip to Learn

🏆Final Challenge — Design an Enrollment Report

Specify learners, courses and enrollments with appropriate keys, references, nullability and status/grade constraints. Explain why a missing enrollment differs from an orphan reference and why grade NULL differs from zero.

Build a report that preserves courses with no qualifying enrollment, counts actual matches and displays a missing average deliberately. Place the status predicate in ON, apply a group threshold with HAVING, then order and limit the retained groups.

Test duplicate pairs, orphan inserts, invalid grade/status, empty tables, null grades and ON/WHERE differences. Verify constraints on the actual database connection and separate those results from authorization, concurrency and deployment checks.

Success criterion: predict the exact course rows and counts before executing the query. Next: Level 27 — Web APIs and REST.