Data in Multiple Tables

Overview

Up to this point, SQL has been used against one table at a time. In this part, we look into how to work with multiple tables and the relationships between them. We first study why data is distributed across tables at all, then practice the SQL patterns for reconstructing useful views from those related tables, and finally turn those patterns into visible application features in the study tracker project.

Two small notes about the shape of this part are useful to make up front.

First, relational algebra is introduced only after SQL joins, subqueries, and set operations have already been practiced. That is a deliberate choice. The algebra chapter provides a formal way to read queries you already understand, in a form that makes their structure and transformations easier to see.

Second, this part does not have a single dedicated project checkpoint chapter. Instead, the project work is spread across the two applied chapters at the end of the part, one for a one-to-many feature and one for a many-to-many feature. Together they play the same role that a single project checkpoint played in the earlier parts.

The structure of this part is as follows:

  • Data Distribution and Redundancy explains why one big table often becomes fragile, introduces the multi-table schema that the rest of the part uses, and notes how that schema relates to the one we’ve used in the previous parts.
  • Tables and Relationships shows how to read one-to-many and many-to-many relationships before writing joins.
  • Inner Joins introduces the basic pattern for combining related rows when matches are required.
  • Left Joins shows how to keep rows that have no match on one side.
  • Complex Joins extends the same ideas to longer join paths, self-joins, and debugging suspicious results.
  • Subqueries and Set Operations introduces other ways to express multi-table questions.
  • A Relational-Algebra Reading of Queries revisits familiar SQL through a small set of relational-algebra operators that make query structure and query transformation easier to discuss.
  • Relational Queries in Applications shows how a one-to-many relationship becomes a visible feature in the project application.
  • Tags for Cards in Applications extends the project to a many-to-many relationship by attaching existing tags to cards.

The chapters from Chapter 2 to Chapter 7 use an extended teaching schema with tables such as courses, course_instances, exercises, users, enrollments, and submissions. This is a separate dataset used for SQL examples and exercises; it is not part of the study tracker project. The project itself continues to grow in Chapters 9 and 10, where the schema adds cards and a small linking table on top of the existing decks and tags.

A useful rhythm for this part is:

  1. read the regular chapters in order,
  2. complete the chapter-level quizzes and SQL exercises as you go,
  3. then complete the two applied chapters (Chapter 9 and Chapter 10), which together play the role of the project checkpoint for this part,
  4. and finish with the recap quiz and feedback form.

Finally, at the end of the part, there is a recap and feedback chapter that briefly summarizes the part and asks for feedback.

Data Distribution and Redundancy →