SQL is a standard language that is used to communicate with relational databases. In simple terms, it is the way in which you speak to a database to get data, write new data, alter existing data, delete data, grant access, and determine the structure of your database.
SQL statements can be classified into groups of what they do:
| Category | Purpose |
|---|---|
| DQL – Data Query Language | Retrieve data |
| DML – Data Manipulation Language | Modify data |
| DDL – Data Definition Language | Define database structure |
| DCL – Data Control Language | Control access |
| TCL – Transaction Control Language | Manage transactions |
This week, I focused on two of these: DDL and DML.
What’s the Difference Between DDL and DML?
DDL is about structure where, it defines and sets up the database itself. Think of it as building a house before moving the furniture in.
DML is about data; It is what you use once the structure exists to add, change, or remove information inside it.
| DDL | DML | |
|---|---|---|
| What it does | Defines structure | Modifies data |
| Commands | Create, Drop, Alter, Rename, Truncate | Insert, Update, Delete |
| Affects | Tables and Schemas | Rows/Records |
How I Used DDL and DML in My Assignment
CREATE: Building the Table (DDL)
The first thing I did was create a table to hold the data. CREATE sets up the table and defines what columns it will have and what type of data each column accepts.
For example, after having created my schema, I set the path to the name of my current schema and then proceeded to create the table using command ‘Create.’
INSERT: Adding Data (DML)
Once the table existed, I populated it with records using INSERT.
UPDATE: Editing Existing Records (DML)
UPDATE lets you change data that is already in the table without deleting and re-entering everything.
For example, changing the City to Nairobi having made an entry of City as Mombasa;
DELETE: Removing Records (DML)
DELETE removes specific rows from the table. Used carefully, it's a clean way to get rid of records you no longer need.
Filtering with WHERE
The WHERE clause lets you target specific rows instead of affecting or retrieving everything. Here are some common operators I used:
| Operator | What it does | Example |
|---|---|---|
= |
Exact match | WHERE city = 'Nairobi' |
> |
Greater than | WHERE marks > 70 |
BETWEEN |
Within a range | WHERE marks BETWEEN 50 AND 80 |
IN |
Matches a list | WHERE status IN ('Active', 'Pending') |
LIKE |
Partial match | WHERE name LIKE 'A%' |
For example, take a look of the following;
CASE WHEN: Transforming Data on the Fly
CASE WHEN works like an IF statement. It lets you create a new column based on conditions, without changing the original data.
For example, you can label students as "Senior" or “Junior” based on their class right inside your query.
My Reflection
The section which was of most interest to me was CASE WHEN. It was as though SQL had ceased to be simply about data storage, and began to become about processing that data. You are actually making choices within a query that seems strong considering something so simplistic.
I was challenged by getting WHERE conditions correct which were particularly the combination of multiple operators. Even a minor syntax error, a lost quote, an incorrect operator and the entire query fails. However, that is what made it satisfying as well when it did finally work.
SQL is beginning to disintegrate into a programming language and more of a logic of the data.
Top comments (0)