Mastering the SQL Analytical Toolkit

As a budding data professional, I recently completed an intensive SQL Week 2 Assignment that pushed me from basic queries into more powerful territory. I worked with two real-world databases — nairobi_academy (school management) and city_hospital (healthcare appointments).

In this article, I’ll walk you through the key concepts I practiced, share practical queries, and explain why these skills matter in real jobs.

Why This Matters

Modern data roles don’t just require SELECT * FROM table. You need to clean data, combine multiple tables, analyze trends over time, and create meaningful rankings. This assignment covered exactly those real-world skills:

• String & Number Functions → Data cleaning & formatting
• Date & Time Functions → Temporal analysis
• JOINS → Connecting related data
• Window Functions → Advanced analytics without losing row-level detail
• SET Operators → Combining results from multiple queries

Part 1: String Functions – Making Data Look Professional

String functions help clean and present data nicely.

Example Query (Uppercase + Lowercase + CONCAT):

SELECT 
    UPPER(first_name || ' ' || last_name) AS upper_name,
    LOWER(city) AS lower_city,
    CONCAT(first_name, ' ', last_name, ' is in Form ', form, 
           ' and comes from ', city) AS student_summary
FROM students;

Key functions practiced:
UPPER(), LOWER()
LENGTH()
LEFT() / SUBSTRING()
CONCAT()

Part 2: Number Functions – Doing the Math

SELECT 
    marks,
    ROUND(marks, 1) AS rounded_one_decimal,
    CEIL(marks) AS rounded_up,
    FLOOR(marks) AS rounded_down,
    ROUND(marks * 1.10) AS boosted_mark
FROM exam_results;

I also calculated summary statistics using COUNT, AVG, MIN, MAX, and SUM in a single query, very useful for reports.

Part 3: Date & Time Functions (PostgreSQL)

    first_name,
    date_of_birth,
    EXTRACT(YEAR FROM date_of_birth) AS birth_year,
    EXTRACT(MONTH FROM date_of_birth) AS birth_month,
    AGE(date_of_birth) AS age_in_years,
    TO_CHAR(exam_date, 'Day, DDth Month YYYY') AS formatted_date
FROM students;

Part 4: JOINS – The Heart of Relational Databases

This was the most exciting part.
INNER JOIN (Only matching records)
LEFT JOIN (All patients, even those without appointments)
RIGHT JOIN (All doctors)
Three-table JOIN for appointments + patient + doctor + prescription
Finding patients who never had an appointment:

SELECT 
    p.full_name,
    p.city
FROM patients p
LEFT JOIN appointments a ON p.patient_id = a.patient_id
WHERE a.appointment_id IS NULL;

Part 5: Window Functions – The Game Changer

This section took my SQL to the next level.
Window functions let you perform calculations across rows while keeping every detail.
Examples I wrote:
ROW_NUMBER() – Unique ranking
RANK() vs DENSE_RANK()
NTILE(3) – Dividing students into performance bands
AVG() OVER(PARTITION BY student_id) – Student average alongside each result
LAG() – Comparing current exam with previous exam

SELECT 
    result_id,
    student_id,
    marks,
    AVG(marks) OVER(PARTITION BY student_id) AS student_avg,
    LAG(marks) OVER(PARTITION BY student_id ORDER BY exam_date) AS previous_marks,
    marks - LAG(marks) OVER(PARTITION BY student_id ORDER BY exam_date) AS improvement
FROM exam_results;

Part 6: SET Operators (UNION, INTERSECT, etc.)

Learned how to combine results from different tables/databases cleanly.
-- Cities that exist in both school and hospital databases

SELECT city FROM students
INTERSECT
SELECT city FROM patients;

Key Takeaways & Advice

Working through these exercises helped reinforce several important SQL skills:

1. Cleaning and formatting text data
2. Performing numerical analysis
3. Working with dates and time
4. Combining tables with joins
5. Performing advanced analysis with window functions
6. Merging results using set operators

These skills form the foundation of real-world SQL work in data analysis, backend development, and data engineering.

Final Thoughts

SQL is more than just a query language,it’s a tool for discovering insights from data.

By practicing with realistic datasets like school records and hospital systems, we begin to see how SQL powers real applications.

Whether you're analyzing exam results or managing hospital appointments, mastering SQL opens the door to data-driven decision making.