Building and Querying a Database: A Practical SQL Guide

Building the Database (DDL)

Data Definition Language (DDL) is used to define the database structure. For this task, I created a dedicated schema called greenwood_academy to keep everything organized. Inside the schema, I created three tables: students, subjects, and exam_results.

Before creating the tables it is important to make sure that they are created in the intended schema. The SET search_path TO [schema_name] is necessary for this. A better and safer practice is to always initialize your table names with the schema name, for example to create a table students in the schema greenwood_academy you can use the command CREATE TABLE greenwood_academy.students. This will ensure that the table is created in the correct schema.

-- Creating a schema greenwood_academy and making sure SQL is using it.
CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

-- Creating the students table.
CREATE TABLE greenwood_academy.students (
    student_id INT 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)
);

Table Modifications

In practice, database structural requirements change from time to time. In this case, I altered the existing table structures by adding a phone_number column to the students table, renamed a column in the subjects table for better clarity (i.e. credits to credit_hours), and then dropped the phone_number column when it was no longer needed:

-- Adding a column 'phone_number' to the students table.
ALTER TABLE greenwood_academy.students ADD COLUMN phone_number VARCHAR(20);

-- Renaming column 'credits' to 'credit_hours'
ALTER TABLE greenwood_academy.subjects RENAME COLUMN credits TO credit_hours;

-- Removing the phone_number column in the students table.
ALTER TABLE greenwood_academy.students DROP COLUMN phone_number;

Filling in the Tables & Modifying the Database (DML)

With the structures ready, Data Manipulation Language (DML) was used to modify the data within the tables. Data for 10 students, 10 subjects, and their respective exam records was inserted into the three tables.

-- ===========================================================
-- Filling the Database (DML: INSERT, UPDATE, DELETE)
-- ===========================================================

-- Example of inserting student data
INSERT INTO greenwood_academy.students (student_id, first_name, last_name, gender, date_of_birth, class, city)
VALUES
    (1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
    (2, 'Brian', 'Mutua', 'M', '2007-07-25', 'Form 4', 'Mombasa'),
    (3, 'Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
    (4, 'David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi'),
    (5, 'Esther', 'Akinyi', 'F', '2009-06-30', 'Form 2', 'Nakuru');
-- Note: Truncated for readability, full file includes 10 records per table

After inserting the data into the tables, it is important to confirm that it is entered correctly before proceeding.

-- SELECT query to confirm all 10 rows exist in each of the three tables
SELECT * FROM greenwood_academy.students;
SELECT * FROM greenwood_academy.subjects;
SELECT * FROM greenwood_academy.exam_results;

Data Maintenance

Databases are dynamic and get updates and deletions time and again. In this scenario, I updated a student's city, corrected a typing error in an exam mark, and deleted a specific exam result row entirely:

-- UPDATE statement to change Esther Akinyi's city to Nairobi
UPDATE greenwood_academy.students SET city = 'Nairobi' WHERE student_id = 5;

-- UPDATE to fix the marks for result_id 5 to 59
UPDATE greenwood_academy.exam_results SET marks = 59 WHERE result_id = 5;

-- DELETE statement to remove result_id 9 from the exam_results table
DELETE FROM greenwood_academy.exam_results WHERE result_id = 9;

Quick Reminder: Forgetting to specify the condition for the update or deletion in the WHERE clause in each of the queries above is catastrophic, as the command will affect the entire table.

Querying Data (Filtering with WHERE)

Filtering data effectively reduces server load and isolates relevant insights. Below are a couple of specific queries using basic WHERE, AND, and OR logical operators to look through the records:

• Find all students in Form 3 AND from Nairobi:

SELECT student_id, first_name, last_name
FROM greenwood_academy.students
WHERE class = 'Form 3' AND city = 'Nairobi'
ORDER BY student_id;

• Find all students who are in Form 2 OR Form 4:

SELECT student_id, first_name, last_name
FROM greenwood_academy.students
WHERE class = 'Form 2' OR class = 'Form 4'
ORDER BY student_id;

Range, Membership & Search Operators

To handle more versatile matching constraints, built-in SQL operators like BETWEEN, IN, and LIKE are usually used.

Range Filtering (BETWEEN)

Useful for narrowing down continuous datasets like dates or numeric boundaries:

-- Finding all exams that took place between 15th March 2024 and 18th March 2024.
SELECT result_id, student_id, subject_id, exam_date
FROM greenwood_academy.exam_results
WHERE exam_date BETWEEN '2024-03-15' AND '2024-03-18'
ORDER BY exam_date;

Set Membership (IN / NOT IN)

Replaces repetitive multi-line OR statements:

-- Finding all students who live in Nairobi, Mombasa, or Kisumu using IN
SELECT first_name, last_name
FROM greenwood_academy.students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu')
ORDER BY city;

-- Finding all students who are NOT in Form 2 or Form 3 using NOT IN.
SELECT student_id, first_name, last_name
FROM greenwood_academy.students
WHERE class NOT IN ('Form 2', 'Form 3')
ORDER BY student_id;

Pattern Matching (LIKE with Wildcards)

The % wildcard allows flexible string evaluation to search for prefixes or embedded substrings:

-- Finding all students whose first name starts with the letter 'A' or 'E' using LIKE.
SELECT student_id, first_name
FROM greenwood_academy.students
WHERE first_name LIKE 'A%' OR first_name LIKE 'E%'
ORDER BY student_id;

-- Finding all subjects whose subject name contains the word 'Studies'.
SELECT subject_name
FROM greenwood_academy.subjects
WHERE subject_name LIKE '%Studies%'
ORDER BY subject_name;

Aggregating Data with COUNT

Aggregation helps make sense of the larger scale of the data. Using the COUNT() function, we can calculate quick statistical summaries, such as performance thresholds:

-- Students currently in Form 3
SELECT count(*) AS form_3_students
FROM greenwood_academy.students
WHERE class = 'Form 3';

-- Exam results having a mark of 70 or above
SELECT count(*) AS scores_70_and_above
FROM greenwood_academy.exam_results
WHERE marks >= 70;

Conditional Logic with CASE WHEN

The CASE WHEN expression acts as an if-else statement directly inside the SQL query. This is useful for dynamically creating computed category labels without changing the underlying stored data.

Generating Performance Descriptions

-- Exam results with a grade description
SELECT result_id, marks,
    CASE
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS perfomance
FROM greenwood_academy.exam_results
ORDER BY marks;

Grouping Students by Seniority Level

-- Using CASE WHEN to label each student's level
SELECT student_id, first_name, last_name,
    CASE
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        ELSE 'Junior'
    END AS student_level
FROM greenwood_academy.students
ORDER BY student_level;

Conclusion

This exercise simulates a realistic scenario for setting up and querying databases. It covers schema design, table modifications, and relevant classification using CASE WHEN. The task gives foundational knowledge to manipulating enterprise-level data. The entire source code is structured cleanly into structured query files and tracked on my GitHub repository. Thanks for reading! If you have any questions or alternate methods for querying this data, feel free to drop a comment below! Happy querying!