Mastering SQL DDL, DML, and Data Querying by Building a School Database.

Introduction

Databases are the backbone of almost every modern application, from school management systems and banking platforms to e-commerce websites and healthcare systems. While learning SQL, one of the best ways to understand how databases work is by building one from scratch.

As part of my SQL learning journey, I completed a project for Greenwood Academy, a fictional secondary school in Nairobi. The objective was to design the school's database, populate it with data, and write SQL queries to retrieve useful information.

This project gave me practical experience with database design, data manipulation, and querying using PostgreSQL.

Project Objective

The objective of the project was to build a relational database capable of storing student, subject, and examination information while practicing the core SQL concepts used in real-world database management.

The project involved:

• Creating a database schema
• Creating multiple related tables
• Modifying table structures
• Inserting records
• Updating existing data
• Deleting unwanted records
• Querying data using different SQL operators
• Using aggregate functions
• Applying conditional logic with CASE WHEN

Designing the Database Structure (DDL)

Populating the Database (DML)
After creating the tables, the next step was inserting data.

Sample records representing students, school subjects, and examination results were inserted into the database.

I also learned how to maintain data by performing common database operations such as:

• Updating incorrect records
• Correcting student information
• Removing cancelled examination results These tasks strengthened my understanding of SQL's Data Manipulation Language (DML).

Retrieving Information with SQL Queries
Once the database was populated, I explored different ways of retrieving information.

Some of the queries included:

Finding all students in Form 4
Displaying only female students
Retrieving subjects from the Sciences department
Filtering examination results above a certain mark
Finding students from specific cities
Searching names using the LIKE operator

I also combined multiple conditions using:

• AND
• OR
• BETWEEN
• IN
• NOT IN These operators make SQL extremely powerful for filtering and analyzing data.

Using Aggregate Function
Another important concept introduced during the project was aggregate functions.

I used COUNT() to answer questions such as:

How many students are currently in Form 3?
How many examination results scored 70 marks or higher?

Aggregate functions help summarize large amounts of data and are commonly used in reports and dashboards.

Applying Conditional Logic with CASE WHEN

One of the most interesting parts of the assignment was using the CASE WHEN statement.

Instead of displaying only numerical marks, SQL was used to classify student performance into meaningful categories:

1. Distinction
2. Merit
3. Pass
4. Fail

I also categorized students as either:

1. Senior
2. Junior

based on their class level.

This demonstrated how SQL can transform raw data into information that is much easier to understand.

Skills I Practiced

This project strengthened my understanding of several important SQL concepts, including:

1. Database Design
2. PostgreSQL
3. SQL DDL
4. SQL DML
5. Table Relationships
6. Data Filtering
7. Search Operators
8. Aggregate Functions
9. CASE WHEN Statements
10. Data Management
11. Query Writing

More importantly, I gained confidence in solving practical database problems instead of only studying SQL syntax.

Challenges I Faced
Like many beginners, I encountered several challenges while working on the project.

Some of these included:

Remembering the correct SQL syntax.
Knowing when to use different filtering operators.
Understanding how ALTER TABLE modifies existing structures.
Writing efficient queries that returned the correct results.

Although these challenges slowed me down initially, solving them helped reinforce my understanding of SQL.

Key Lessons Learned

This project taught me that building a database involves much more than storing data.

A well designed database should make it easy to:

• Organize information efficiently.
• Maintain data accuracy.
• Retrieve information quickly.
• Support reporting and decision-making.

I also learned that SQL is not just a programming language it is a tool that transforms raw data into meaningful information for businesses and organizations.

Conclusion

Building the Greenwood Academy database was an excellent exercise that reinforced the fundamentals of SQL and relational databases.

From designing tables and managing records to querying data and applying conditional logic, every stage of the project reflected tasks that database professionals perform in real world environments.

This experience has strengthened my confidence in SQL, and I look forward to applying these skills to more advanced projects involving database optimization, joins, stored procedures, and business intelligence tools.

If you're also learning SQL, I would love to hear about the projects you're working on and the concepts you're exploring.

Happy coding!