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.