Mastering the Basics: A Deep Dive into SQL Fundamentals

#sql #beginners #database

This week, I have worked on a project of creating a Database Administrator for Nairobi Academy. My task was to build a school database from scratch, progressing from initial schema design to data population and complex querying.

Understanding the Language: DDL vs. DML

To manage a database effectively, one must distinguish between the blueprint and the content.

DDL (Data Definition Language): This is used to define the structure or schema of the database. It deals with the containers. Examples: CREATE, ALTER, DROP.
DML (Data Manipulation Language): This is used to manage the data within those structures. It deals with the content.Examples: INSERT, UPDATE, DELETE.

The Key Difference: DDL changes the shape of the database (e.g., adding a new table), while DML changes the information stored inside it (e.g., adding a new student).

Building and Managing the Academy Database.

In this assignment, I utilized four core SQL commands to manage the school's records:

CREATE: I used this DDL command to establish the nairobi_academy schema and its three primary tables: students, subjects, and exam_results.

INSERT: This DML command allowed me to populate the tables with 10 unique student profiles, subject details, and their corresponding exam marks.

UPDATE: I used this to maintain data accuracy. For instance, when Esther Akinyi moved to Nairobi, I updated her city record specifically using her student_id.

DELETE: When an exam result was cancelled, I used this to remove specific rows from the exam_results table to ensure the database remained clean and relevant.

Precision Filtering with the WHERE Clause

The WHERE clause is the most powerful tool for extracting specific insights from data.

I applied several operators:

Equality (=): Used to find all students in a specific class, such as 'Form 4'.
Comparison (>): Used to identify high-achieving students with marks greater than or equal to 70.
BETWEEN: Excellent for ranges. I used this to find exams held within a specific four-day window in March 2024.
IN: This allowed me to filter students living in multiple cities (Nairobi, Mombasa, or Kisumu) without writing multiple OR statements.
LIKE: I used pattern matching (e.g., 'A%') to find students whose names started with specific letters or subjects containing the word 'Studies'.

Transforming Data with CASE WHEN

Using the CASE WHEN statement, I transformed raw numeric marks into meaningful labels. For example, I created a performance column that categorized marks into 'Distinction', 'Merit', 'Pass', or 'Fail'. I also used it to categorize students as 'Senior' or 'Junior' based on their form level, which makes the data much more readable for school management.