How Education Data Connects Across Tables

Overview

Education data is stored across multiple tables, but these tables are connected through shared keys. Understanding these connections allows you to combine data and analyze the full academic and administrative system.

The students table is central. Each student is linked to a program using program_id, which connects to the programs table. Programs are then linked to departments through department_id, showing how academic structures are organized.

Courses are stored in the courses table and are also connected to departments. Each course is offered through the sections table, where course_id links courses to specific term offerings.

The sections table connects to the terms table using term_id, allowing analysis across semesters. It also connects to instructors through teaching assignments using instructor_id.

Student activity is captured in the enrollments table, which links student_id to section_id, showing which students are taking which courses.

Administrative records extend this connection. The grades table links to enrollments to capture academic performance, the attendance table tracks participation, and the tuition_payments table connects financial records to students and terms.

Together, these connections form a complete structure:Student → Program → Department → Course → Section → Term → Enrollment → Performance → Administration

Understanding these relationships allows you to combine tables using SQL joins and answer more advanced education and administrative questions.

---

The Basic Pattern

SELECT column_name, COUNT(*) AS total
FROM table_name
GROUP BY column_name;
  

---

Examples

Example 1 — Count Students by Program

Example 2 — Count Courses by Department

Example 3 — Count Enrollments by Section

---

Practice Tasks (Your Turn!)

Task 1

Count the number of instructors by employment type.

Task 2

Count the number of sections by campus.

Task 3

Count the number of grades by assessment type.