Building and Querying a Database: A Practical SQL Guide

As a data analyst, mastering database design and querying is essential. In this article, I will walk you through how I built and queried a database schema for Greenwood Academy using PostgreSQL.

The article covers everything from defining or changing the structure of database objects using the Data Definition Language (DDL), modifying data within tables using the Data Manipulation Language (DML), and fetching and retrieving data from the database using the Data Query Language (DQL). The article also explores filtering the results, utilizing specialized operators, and writing conditional logic with CASE WHEN.

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 on 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!