SQL Sample Database

In the world of software development, a sample database is more than just a set of tables; it is a controlled environment where you can safely experiment with queries, test application logic, and learn the nuances of relational data without the risk of breaking production systems. By using a pre-populated schema, you can focus on mastering SQL syntax and understanding how different entities relate to one another.

Example: Sample Database - School

To demonstrate how a relational database works, we will use a School management system. This model is ideal for learning because it illustrates the most common types of relationships: one-to-many (one teacher can teach many classes) and many-to-many (many students can enroll in many courses).

The School database consists of four core tables: Students, Courses, Teachers, and Enrollments. Each table serves a specific purpose, ensuring that data is organized and "normalized" to prevent redundancy.

Table Structures

The following SQL statements define our schema. We use specific data types like INT for identifiers, VARCHAR for text, and DATE for time-based entries to ensure data integrity.

1. Students Table

The Students table stores personal information. The student_id acts as the Primary Key, ensuring every student has a unique identifier.

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    email VARCHAR(100)
);

2. Courses Table

The Courses table defines the curriculum. We use the TEXT type for the description to allow for longer strings than a standard VARCHAR.

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    course_description TEXT
);

3. Teachers Table

This table tracks faculty members and their specific areas of expertise.

CREATE TABLE Teachers (
    teacher_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    subject VARCHAR(100)
);

4. Enrollments Table

This is a junction table. It connects students, courses, and teachers. Notice the FOREIGN KEY constraints; these ensure that you cannot enroll a student who doesn't exist in the Students table.

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    teacher_id INT,
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id),
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id)
);

Sample Data

Once the structure (schema) is built, we need to populate it with data. The INSERT INTO command adds rows to our tables. Order matters here: you must create the Students and Courses before you can create an Enrollment that references them.

Inserting Sample Data

-- Inserting data into Students table
INSERT INTO Students (student_id, first_name, last_name, date_of_birth, email)
VALUES 
(1, 'John', 'Doe', '2000-05-15', '[email protected]'),
(2, 'Jane', 'Smith', '1999-09-23', '[email protected]');

-- Inserting data into Courses table
INSERT INTO Courses (course_id, course_name, course_description)
VALUES
(101, 'Math 101', 'Introduction to Mathematics'),
(102, 'Science 101', 'Introduction to Science');

-- Inserting data into Teachers table
INSERT INTO Teachers (teacher_id, first_name, last_name, subject)
VALUES
(1, 'Michael', 'Johnson', 'Math'),
(2, 'Sarah', 'Davis', 'Science');

-- Inserting data into Enrollments table
INSERT INTO Enrollments (enrollment_id, student_id, course_id, teacher_id, enrollment_date)
VALUES
(1, 1, 101, 1, '2022-09-01'),
(2, 2, 102, 2, '2022-09-01');

Sample Queries

With data in place, we can now perform queries to extract meaningful information. This is where SQL shows its power in handling complex relationships.

1. Select All Students

The simplest way to view your data. Use this during debugging to verify that your INSERT statements worked correctly.

SELECT * FROM Students;

2. Get All Enrollments with Student and Course Details

This query uses JOIN to combine data from three different tables into a single readable list.

SELECT 
    e.enrollment_id, 
    s.first_name AS student_first_name, 
    s.last_name AS student_last_name, 
    c.course_name 
FROM Enrollments e
JOIN Students s ON e.student_id = s.student_id
JOIN Courses c ON e.course_id = c.course_id;

3. Get Teachers and Their Courses

By joining the Teachers and Enrollments tables, we can identify which instructors are currently assigned to specific courses.

SELECT 
    t.first_name AS teacher_first_name,
    t.last_name AS teacher_last_name,
    c.course_name
FROM Teachers t
JOIN Enrollments e ON t.teacher_id = e.teacher_id
JOIN Courses c ON e.course_id = c.course_id;

4. Count the Number of Students Enrolled in Each Course

This uses the COUNT aggregate function and GROUP BY. We use a LEFT JOIN to ensure that even courses with zero students appear in our results.

SELECT 
    c.course_name, 
    COUNT(e.student_id) AS num_students
FROM Courses c
LEFT JOIN Enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name;

5. Find Students Enrolled in a Specific Course

The WHERE clause allows us to filter the results. This is useful for building "Class Roster" features in an application.

SELECT 
    s.first_name, 
    s.last_name 
FROM Students s
JOIN Enrollments e ON s.student_id = e.student_id
JOIN Courses c ON e.course_id = c.course_id
WHERE c.course_name = 'Math 101';

Summary

The School sample database provides a practical foundation for understanding how data flows through a relational system. By practicing with these tables, you've learned how to define schemas, enforce data integrity via foreign keys, and perform complex joins to retrieve actionable insights. These skills are fundamental for any developer working with back-end systems or data analysis.