LEARNING SQL

So, I Had to Run a School This Weekend (A SQL Story)
Here's the thing. I am not a database administrator. I'm just a student with a laptop, a slightly questionable internet connection, and a growing obsession with making tables do exactly what I want. But this weekend? Greenwood Academy in Nairobi gave me the keys to their digital kingdom. My job was to build their entire school database from scratch and start querying it.

No pressure, right?

Here is how it went down. The wins, the face-palm moments, and why CASE WHEN is officially my new favorite thing.

Step 1: The Setup (DDL - Building the Sandbox)
Before I could do anything cool, I had to actually build the container for all this data. That meant creating a schema called greenwood_academy and setting up three tables: students, subjects, and exam_results.

Writing out all those CREATE TABLE statements was pretty straightforward. I just followed the specs: student_id as the primary key, VARCHAR for names, INT for marks.

But then, plot twist. Just after I finished, the school realized they forgot phone numbers. Classic. So I had to whip out the ALTER TABLE command to add a phone_number column. Then they changed their minds again. "Actually, scrap that. Drop it." So I did. Dropping a column felt a bit risky, like deleting a file off your desktop, but hey, it worked. I also renamed credits to credit_hours, which just felt like good housekeeping.

Takeaway: Building a database isn't a one-and-done deal. You have to be flexible because schools (or bosses) will always change their minds.

Step 2: Inserting the Data (DML - The Real Work)
This is where I got a bit jittery. Copy-pasting 10 students, 10 subjects, and 10 exam results into a terminal feels like you're typing out a nuclear launch code. One missing comma and everything blows up.

I got to meet the students, though. Amina Wanjiku, Brian Ochieng, Esther Akinyi. I started rooting for them. But honestly? I messed up. Twice.

Esther's City: I inserted her with a city of "Nakuru". The spec said she moved to Nairobi. So I had to run an UPDATE on student_id = 5 to fix her address.

The Marks Blunder: For result_id 5, I typed "49" instead of "59". Ouch. One UPDATE later, I saved that student from a low grade. It felt weirdly satisfying to correct my own sloppy paperwork.

Oh, and I had to DELETE result_id 9 entirely because the exam got cancelled. Felt a bit harsh, but rules are rules.

Step 3: The Fun Part - Asking Questions (WHERE & Operators)
Once the data was solid, I finally got to do what SQL is actually for: querying.

The WHERE clause became my best friend.

"Show me all the Form 4 students." Done.

"Who's in Form 3 and from Nairobi?" Got them.

"Find the Science subjects." Easy peasy.

I hit Q21 and Q22, and things got technical. Using BETWEEN for marks (50 to 80) and dates (15th to 18th March) felt like I was slicing data with a laser beam. Then Q23 asked me to find students in Nairobi, Mombasa, or Kisumu. I used IN for the first time, and it just felt so clean compared to writing city = 'Nairobi' OR city = 'Mombasa' OR city = 'Kisumu'.

But the search operators (LIKE) threw me a curveball. Q25 asked for names starting with 'A' or 'E'. I nearly forgot the % wildcard. And looking for subjects containing "Studies"? LIKE '%Studies%' nailed it.

Step 4: The Grand Finale - COUNT and CASE WHEN
Q27 and Q28 were simple counting exercises. How many Form 3s? (3). How many marks above 70? (6). Easy math.

But then came Q29 and Q30. The CASE WHEN questions.

Honestly, this is where I fell in love with SQL a little bit.

For Q29, I had to label exam results as "Distinction", "Merit", "Pass", or "Fail". I wrote a CASE statement that looked at the marks column and spat out a text label. Here's the magical part. I didn't have to create a new column in the database. I just used the CASE logic inside my SELECT query, and the performance label appeared like magic in my result grid. No ALTER TABLE, no UPDATE. Just pure logic on the fly.

For Q30, I did the same thing for the students. If they were in Form 3 or Form 4, they were 'Senior'. If they were in Form 2 or Form 1, they were 'Junior'. It felt like giving out honorary titles.

Final Thoughts
If you're just starting with SQL, don't overthink it. It's basically just telling the computer:

"Hey, look at this table. If X is true, call it Y. If it's false, call it Z. And only show me the rows where the city is Nairobi."

Honestly, making mistakes (like putting Esther in the wrong city) helped me learn more than getting it right on the first try.

Tomorrow, I'm zipping this all up, pushing it to GitHub (look for my sql-week2-assignment repo), and submitting the link. If you're reading this and struggling with your own CASE WHEN, keep going. That "Aha!" moment is just one query away.