Learning SQL by Building a School Management Database

Introduction

Data is only useful if you can store it properly and ask the right questions of it. For my week 2 SQL assignment, i was tasked with designing and building a small relational database from scratch, Greenwood Academy, that has student, subjects, teachers and exams records.
The goal of this assignment was going through the full lifecycle of working with a database from defining its structure, populating it with data, querying it to answer real questions and using SQL's targeted tools to turn raw rows into actual insights.

1. Data Definition Language - Building the Database

DDL is the main subset of SQL that is used to create, modify and delete the structure of database objects like schemas, tables and indexes.
CREATE - defines a new schema, database or table.
ALTER- changes the structure of an existing table like adding a column, renaming a column or changing the data type.
DROP - permanently deletes a table or column and its content from the system.

Create a Schema

Schema is a logical container within a database that organizes tables, data types and functions.

Create schema greenwood_academy;
set search_path to greenwood_academy;

I configured the search_path to instruct the database to know where to look for the tables, views and functions.

Create a Table

create table greenwood_academy.students(
   student_id int SERIAL primary key,
   first_name varchar(50) not null,
   last_name varchar(50) not null,
   gender varchar(1),
   date_of_birth date,
   class varchar(10),
   city varchar(50)
);

create table greenwood_academy.subjects(
   subject_id int SERIAL primary key,
   subject_name varchar(100) not null unique,
   department varchar(50),
   teacher_name varchar(50),
   credits int
);

create table greenwood_academy.exam_results(
   result_id int SERIAL primary key,
   student_id int not null references greenwood_academy.students(student_id),
   subject_id int not null references greenwood_academy.subjects(subject_id),
   marks int not null,
   exam_date date,
   grade varchar(2)
);

I used SERIAL to automatically generate unique row identifiers for the table instead of doing it manually. Postgresql has constraints which are rules used on tables and rows that limit the invalid data entered.

Types of Constraints:

• not null - prevents a column from having null values.

• unique - guarantees all values are distinct.

• Primary Key - marks a column as the unique identifier for each row in a table.

• Foreign Key - links a column in one table to the primary key of another.

Alter table

alter table greenwood_academy.students add column phone_number varchar(20);

alter table greenwood_academy.subjects rename column credits to credit_hours;

alter table greenwood_academy.students drop phone_number;

2. Data Manipulation Language - Inserting the database

DML is a subset of SQL that has commands that are used to manage and modify data stored in existing tables.
INSERT - adds rows to the table.
UPDATE - modifies existing rows based on specified conditions.
DELETE - removes existing rows on the table.

Populating the table

insert into greenwood_academy.students(first_name,last_name,gender,date_of_birth,class,city)
values 
(1,'Amina','Wanjiku','F','2008-03-12','Form 3','Nairobi'),
(2,'Brian','Ochieng','M','2007-07-25','Form 4','Mombasa'),

------Quick Check-----------------------
select * from greenwood_academy.students;
select * from greenwood_academy.subjects;
select * from greenwood_academy.exam_results;

After inserting values into the table, use select *-means all from the specific table to check if the inserted values are there.

Output on DBeaver

Modifying the table

update greenwood_academy.exam_results
set marks = 59
where result_id = 5;

delete from greenwood_academy.exam_results
where result_id = 9;

3. Data Query Language - Querying The Data

DQL is used to retrieve and read data from a relational database. It returns a temporary table with the results. DQL has one primary command SELECT that is paired by other commands to retrieve data.
SELECT - chooses which column to return.
FROM - specifies which table the data comes from.
WHERE - filters rows based on specified condition.
ORDER BY- sorts the result set, ascending is default or DESC descending.
GROUP BY - collapses rows into summary groups, always paired with aggregate functions like count() or avg().
HAVING - filters groups created by group by.
As - alias is a temporary name assigned to a table or column to make the code easier to read.

select marks
from greenwood_academy.exam_results 
where marks >= 70
order by marks;

select subject_id,
       count(*) as total_results,
       AVG(marks) as average_mark
from greenwood_academy.exam_results
group by subject_id
order by subject_id;

4. Range, Membership, Operator

This section covered operators that make filtering more precise and more readable that long chains of OR conditions.
BETWEEN- filters values within a range.
IN- checks whether a column's value matches any item in the list
NOT IN - checks column's values that do not match any item in the list.
LIKE - pattern-matches text using % as a wildcard for any number of characters and _for exactly one character.
ILIKE -it works same as LIKE, but is case-insensitive.

• Like 'A%' - finds any value that begins with "A".

• Like '%A' - finds any value that ends with "A".

• Like '%A%' - finds any value that contains "A" in any position.
  

where exam_date between '2024-03-15' and '2024-03-18';
where city in ('Nairobi','Mombasa','Kisumu');
where class not in ('Form 2','Form 3');
where first_name like 'A%' or first_name like 'E%';
where subject_name like '%Studies%';

5. Count

Count() is an aggregate function that is used to total up rows.

select count(*) as form3_students
from greenwood_academy.students 
where class = 'Form 3';

6. Case When

The Case statement is a way of adding conditional logic directly inside a query. It is similar to if/else statements in most programming languages.

• CASE...WHEN...THEN...ELSE...END-It evaluates conditions in order, top to bottom and returns the result tied to the first condition that matches. ELSE is the fallback if nothing matches.

select marks,
case
    when marks >=80 then 'Distinction'
    when marks >=60 then 'Merit'
    when marks >=40 then 'Pass'
    else 'Fail'
end as Performance
from greenwood_academy.exam_results
order by marks;

Case Output

Conclusion

Working through Greenwood academy's database from create table statement to case when gave me a much clearer picture of how a relational database works end to end.
You can find the full set of SQL files for this project, including the complete Greenwood Academy schema and all six sections, in my github Repository