SQL for Beginners: Build a Mini School Database

SQL for Beginners: Building a Mini School Database from Scratch

Learning SQL demands practical application. Reading theory alone often falls short. This guide walks through a hands-on project: building a mini database for a fictional school, Greenwood Academy, using PostgreSQL. We will progress from an empty schema to a populated database containing students, subjects, and exam results, then practice querying this data to answer meaningful questions. Key SQL concepts covered include creating tables (DDL), manipulating data (DML - INSERT, UPDATE, DELETE), filtering with WHERE, using range, membership, and pattern-matching operators, counting rows with COUNT, and categorizing data with CASE WHEN.

Setting the Scene: Greenwood Academy's Needs

Greenwood Academy requires a database to manage its core entities: students, subjects, and their exam performance. This translates into three primary tables: Students, Subjects, and ExamResults. Each table will hold specific information about these entities.

Designing the Schema: Creating Tables (DDL)

We begin by defining the structure of our database using Data Definition Language (DDL). This involves creating the tables and specifying the data types for each column. Primary keys will uniquely identify each record within a table, while foreign keys will establish relationships between tables.

The Students Table

This table will store information about each student. We'll include a unique student ID, their first and last names, and their date of birth. The student_id will be our primary key.

CREATE TABLE Students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE
);

Here, SERIAL PRIMARY KEY automatically generates a unique integer for student_id. VARCHAR(50) defines a string field up to 50 characters, and NOT NULL ensures these fields must have a value. DATE stores the date of birth.

The Subjects Table

This table will list all the subjects offered at Greenwood Academy. Each subject will have a unique ID and a name.

CREATE TABLE Subjects (
    subject_id SERIAL PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL UNIQUE
);

Similar to the Students table, subject_id is the primary key. subject_name is a string up to 100 characters and must be unique; no two subjects can have the same name.

The ExamResults Table

This table will link students to subjects and record their exam scores. It requires foreign keys referencing both the Students and Subjects tables to establish these relationships. The exam_id will be the primary key.

CREATE TABLE ExamResults (
    exam_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES Students(student_id),
    subject_id INT REFERENCES Subjects(subject_id),
    score INT CHECK (score >= 0 AND score <= 100)
);

student_id and subject_id are integers that must correspond to existing entries in their respective tables, enforced by the REFERENCES clause. The score is an integer, and the CHECK constraint ensures scores are between 0 and 100 inclusive.

Populating the Database: Inserting Data (DML)

With our tables defined, we can now add data using Data Manipulation Language (DML) statements, specifically INSERT.

Adding Students

Let's add a few fictional students:

INSERT INTO Students (first_name, last_name, date_of_birth)
VALUES 
('Alice', 'Smith', '2005-03-15'),
('Bob', 'Johnson', '2006-07-22'),
('Charlie', 'Williams', '2005-11-01');

Adding Subjects

Now, let's add some subjects:

INSERT INTO Subjects (subject_name)
VALUES 
('Mathematics'),
('Science'),
('History'),
('English');

Recording Exam Results

Finally, we record exam results. We need to know the student_id and subject_id for each entry. Assuming Alice is student_id 1, Bob is 2, and Charlie is 3, and subjects are Mathematics (1), Science (2), History (3), English (4):

INSERT INTO ExamResults (student_id, subject_id, score)
VALUES 
(1, 1, 85), -- Alice, Mathematics
(1, 2, 92), -- Alice, Science
(2, 1, 78), -- Bob, Mathematics
(2, 3, 88), -- Bob, History
(3, 1, 95), -- Charlie, Mathematics
(3, 4, 70); -- Charlie, English

Querying the Data: Answering Questions

Now for the powerful part: retrieving insights from our data. We'll use SELECT statements with various clauses.

Retrieving All Data

To see all students:

SELECT * FROM Students;

To see all exam results:

SELECT * FROM ExamResults;

Filtering with WHERE

Let's find all exam results for Alice (student_id 1):

SELECT * FROM ExamResults WHERE student_id = 1;

We can also filter by subject. Find scores for Mathematics (subject_id 1):

SELECT * FROM ExamResults WHERE subject_id = 1;

Range, Membership, and Pattern Matching

Find students born after January 1st, 2006:

SELECT first_name, last_name FROM Students WHERE date_of_birth > '2006-01-01';

Find students who are either Alice or Bob:

SELECT first_name, last_name FROM Students WHERE first_name IN ('Alice', 'Bob');

Find subjects whose names start with 'M':

SELECT subject_name FROM Subjects WHERE subject_name LIKE 'M%';

Counting Rows with COUNT

How many students are in the database?

SELECT COUNT(*) FROM Students;

How many exams were taken in Mathematics?

SELECT COUNT(*) FROM ExamResults WHERE subject_id = 1;

Categorizing Data with CASE WHEN

We can assign grades based on scores. Let's create a view of exam results with a 'Grade' column:

SELECT 
    student_id,
    subject_id,
    score,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM ExamResults;

This query uses CASE WHEN to evaluate the score and assign a letter grade. This is a fundamental technique for data transformation and reporting within SQL.

Beyond the Basics: Next Steps

This project covers the foundational elements of SQL database creation and querying. To expand your skills, consider implementing more complex relationships (e.g., teachers, courses), performing data aggregation (e.g., average scores per subject using GROUP BY and AVG), and exploring advanced joins to combine data from multiple tables seamlessly. The key is continuous practice and building progressively more complex projects.