Introduction to SQL: DDL, DML, and Querying Data

As a beginner database administrator, my first assignment was to build a school database for Nairobi Academy from scratch. At first this seemed impossible, especially since I had repeated cases of errors in my queries the whole week. But I soldiered on, ready to research and debug any cases of errors and finish the project. So here is what I learned about SQL this week and some tips;

"SQL stands for Structured Query Language". We use several languages in SQL, main ones being; DDL (Data Definition Language) & DML (Data Manipulation Language)

• DDL defines database structure using the following commands; CREATE, ALTER, and DROP.
• DML simply entails changing data inside the tables. The main commands used are INSERT, UPDATE, SELECT and DELETE

Think of it this way; DDL builds the house, DML fills it with furniture.

In my current project, the main commands I used were CREATE, INSERT, UPDATE, and DELETE and I will take you through the 'how' shortly.

I used CREATE to create a schema called nairobi_academy, then CREATE TABLE to build three tables: students, subjects, and exam_results;

I also used ALTER TABLE to modify tables after creation, that is; adding a phone_number column, renaming credits to credit_hours, and dropping phone_number when it was no longer needed. This taught me that SQL is flexible and you can always adjust a table's structure even after it has been created.

Next, I used INSERT command, where I added 10 students, 10 subjects, and 10 exam results into the database -see example below;

I then used UPDATE command and corrected data that had changed. For instance; updating Esther's city from Nakuru to Nairobi, and fixing incorrect marks from 49 to 59.

In addition, I used DELETE command to remove a cancelled exam result from the table.

NB: The most important lesson with UPDATE and DELETE is that, always use a WHERE clause, else every single row in the table gets affected and will be deleted.

Another key lesson has been how to use SQL filtering functions e.g., WHERE, SEARCH, IN, NOT IN, COUNT & CASE WHEN.

The WHERE clause filters rows based on conditions while CASE WHEN works like an if-else statement. It creates new labels based on conditions without changing the original data.

examples;

The biggest challenge this week was managing errors when running scripts multiple times. Some errors I encountered today was "relation already exists" and it kept appearing till it got frustrating. I learned that using IF NOT EXISTS and DROP SCHEMA CASCADE solves this cleanly.

I also learned the hard way that column names matter. For instance; renaming credits to credit_hours after inserting data caused errors because the INSERT still referenced the old name. Order really matters in SQL! Do not give up when creating and querying data in SQL, remain calm, research on how to debug and you've got it!