INTRODUCTION TO SQL (DML & DDL

Introduction

Every day, businesses and applications generate large amounts of data. Think about an online shop that needs to keep track of its customers, products, orders, and payments. This information needs to be stored in an organized way so that it can easily be accessed, updated, and analyzed. One common way of doing this is by using a relational database.

A relational database organizes data into tables, which consist of rows and columns. For example, a company may have a customers table containing customer information and an orders table containing details about purchases made by those customers.

To communicate with these databases, we use SQL (Structured Query Language).

SQL is a language used to interact with relational databases. It allows us to perform tasks such as creating tables, adding new records, retrieving information, updating existing data, and deleting data that is no longer required.

For example, imagine we have a table called customers. If we wanted to see all the customers stored in that table, we could write:

SELECT *
FROM customers;

This simple SQL statement asks the database to return all the records from the customers table.

However, not all SQL commands perform the same type of task. Some commands are used to define and change the structure of a database, while others are used to work with the data stored inside those structures.

SQL commands are therefore commonly grouped into categories according to their purpose. Two important categories that every SQL beginner should understand are:

• DDL — Data Definition Language, which is used to define and manage database structures.
• DML — Data Manipulation Language, which is used to add, modify, and remove data stored within those structures.

In this article, we will explore DDL and DML using practical examples and learn how they work together when building and managing a relational database.

Data Definition Language (DDL)

Before we can store customer details, sales transactions, products, or any other information in a relational database, we first need to create a structure that will hold that data.

This is where Data Definition Language (DDL) comes in.

DDL (Data Definition Language) is a category of SQL commands used to create, define, modify, and remove database objects and structures.

Database objects can include:

• Databases
• Tables
• Schemas
• Views
• Indexes
• Sequences

For a beginner, the easiest place to understand DDL is by working with tables.

Imagine we are developing a database for a car dealership. Before we can add information about the cars available for sale, we need to create a table that defines what information should be stored.

For example, we might want to store:

• Car ID
• Make
• Model
• Year of manufacture
• Price
• Status

DDL allows us to create this structure before inserting the actual car records.

The main DDL commands we will explore are:

• CREATE — creates a new database object.
• ALTER — changes the structure of an existing database object.
• TRUNCATE — removes all records from a table while retaining its structure.
• DROP — removes a database object completely.

---

1. CREATE — Creating Database Objects

The CREATE command is used to create new database objects.

One of its most common uses is creating a table.

The general syntax is:

```sql id="8o7rta"
CREATE TABLE table_name (
column_name data_type constraints
);

Let's create a table called `cars`:



```sql id="o5ryy6"
CREATE TABLE cars (
    car_id SERIAL PRIMARY KEY,
    make VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year_of_manufacture INT,
    price DECIMAL(12,2),
    status VARCHAR(30)
);

At this point, we have created the structure of the table, but we have not added any car records.

We can think of it like creating an empty spreadsheet:

car_id	make	model	year_of_manufacture	price	status

The columns exist, but there is no data yet.

Understanding the CREATE statement

Let's break down some important parts.

```sql id="jj9e0h"
car_id SERIAL PRIMARY KEY

`car_id` is the name of the column.

`SERIAL` is a PostgreSQL feature commonly used to generate sequential integer values automatically.

`PRIMARY KEY` means that the column uniquely identifies each record in the table.

Next:



```sql id="o7i5fc"
make VARCHAR(50) NOT NULL

VARCHAR(50) allows text values with a maximum length of 50 characters.

NOT NULL means that this column must contain a value.

And:

```sql id="5c9yte"
price DECIMAL(12,2)

allows us to store decimal numbers, making it suitable for values such as prices.

Choosing appropriate **data types and constraints** is an important part of database design because they determine what kind of information can be stored in each column.

---

### 2. ALTER — Modifying an Existing Table

Database requirements can change over time.

Suppose we created our `cars` table but later realized that we also need to record the colour of each vehicle.

We do not necessarily need to delete and recreate the entire table.

Instead, we can use `ALTER TABLE`.



```sql id="8nmzoh"
ALTER TABLE cars
ADD COLUMN colour VARCHAR(30);

Our table structure now becomes:

car_id	make	model	year_of_manufacture	price	status	colour

ALTER TABLE can be used for several structural changes.

For example, we can rename a column:

```sql id="r62ec9"
ALTER TABLE cars
RENAME COLUMN colour TO car_colour;

We can also remove a column:



```sql id="81vzi4"
ALTER TABLE cars
DROP COLUMN car_colour;

Therefore, ALTER is useful when the structure of an existing database object needs to change.

---

3. TRUNCATE — Removing All Records

Suppose our cars table contains hundreds of records and we want to remove all the rows while keeping the table itself.

We can use:

```sql id="m0xcr7"
TRUNCATE TABLE cars;

After executing the command, the table still exists:

| car_id | make | model | year_of_manufacture | price | status |
| ------ | ---- | ----- | ------------------- | ----- | ------ |
|        |      |       |                     |       |        |

The **structure remains**, but the records have been removed.

This distinction is important because `TRUNCATE` does not mean the same thing as `DROP`.

---

### 4. DROP — Removing a Database Object

The `DROP` command removes a database object completely.

For example:



```sql id="adwsm2"
DROP TABLE cars;

This removes the cars table itself.

After executing the statement, we can no longer query the table because its definition has been removed from the database.

A simple way to remember the difference is:

TRUNCATE

```text id="tv5mdv"
Table
├── Structure ✓
└── Records ✗

**DROP**



```text id="ezcn2w"
Table
├── Structure  ✗
└── Records    ✗

Because commands such as DROP and TRUNCATE can remove large amounts of data or entire database objects, they should be used carefully, particularly in production environments.

---

Putting DDL Together

Let's look at the lifecycle of our table.

First, we create it:

```sql id="dl1qv5"
CREATE TABLE cars (
car_id SERIAL PRIMARY KEY,
make VARCHAR(50),
model VARCHAR(50),
price DECIMAL(12,2)
);

Later, our requirements change, so we **alter** it:



```sql id="w1m2lg"
ALTER TABLE cars
ADD COLUMN status VARCHAR(30);

If we want to remove all its records but keep the structure, we can use:

```sql id="a3ic11"
TRUNCATE TABLE cars;

And if the table itself is no longer required:



```sql id="n4nbbw"
DROP TABLE cars;

The four commands can therefore be summarized as:

Command	Purpose	Table Structure	Data
CREATE	Creates a new object	Created	Empty initially
ALTER	Changes an existing object's structure	Modified	Usually retained
TRUNCATE	Removes all table records	Retained	Removed
DROP	Removes the database object	Removed	Removed

DDL therefore provides the structure or foundation of our database.

Once that structure exists, we can begin adding and working with actual records. This brings us to the next category of SQL commands: Data Manipulation Language (DML).

Data Manipulation Language (DML)

Once a database and its tables have been created, the next step is to work with the data stored inside those tables. This is where Data Manipulation Language (DML) comes in.

DML (Data Manipulation Language) refers to SQL commands used to add, modify, and remove data stored in database tables.

Think of a database table like a spreadsheet. The table structure defines the columns available, while DML allows us to work with the individual records stored in the rows.

For example, suppose we have the following customers table:

customer_id	customer_name	county	email
1	John Kamau	Nairobi	[e-mail]
2	Mary Wanjiku	Kiambu	[e-mail]
3	Brian Kiptoo	Nakuru	[e-mail]

The main DML commands we will explore are:

• INSERT — adds new records to a table.
• UPDATE — modifies existing records.
• DELETE — removes records from a table.

We will also look at SELECT, which retrieves data from a database. SELECT is sometimes taught alongside DML, although some SQL classifications place it in a separate category called DQL (Data Query Language).

1. INSERT — Adding Data

The INSERT statement is used when we want to add new records to a table.

The basic syntax is:

```sql id="5um3sm"
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Suppose a new customer named Alice joins our business. We could add her to the `customers` table using:



```sql id="98hspj"
INSERT INTO customers (customer_name, county, email)
VALUES ('Alice Njeri', 'Nairobi', '[e-mail]');

Breaking this statement down:

• INSERT INTO tells SQL that we want to add a new record.
• customers identifies the table receiving the data.
• The columns in parentheses specify where the values should be stored.
• VALUES contains the actual information being inserted.

After executing the statement, our table might contain:

customer_id	customer_name	county	email
1	John Kamau	Nairobi	[e-mail]
2	Mary Wanjiku	Kiambu	[e-mail]
3	Brian Kiptoo	Nakuru	[e-mail]
4	Alice Njeri	Nairobi	[e-mail]

We can also insert several records in a single statement:

```sql id="i4n3rx"
INSERT INTO customers (customer_name, county, email)
VALUES
('Peter Otieno', 'Kisumu', '[e-mail]'),
('Faith Chebet', 'Kericho', '[e-mail]'),
('David Mwangi', 'Nyeri', '[e-mail]');

This is known as inserting **multiple rows**.

---

### 2. UPDATE — Modifying Existing Data

Data stored in a database does not always remain the same. A customer might change their email address, move to another county, or update other personal details.

The `UPDATE` statement allows us to modify existing records.

Its basic syntax is:



```sql id="17l8gf"
UPDATE table_name
SET column_name = new_value
WHERE condition;

Suppose Alice moves from Nairobi to Nakuru. We could update her record using:

```sql id="e21cbr"
UPDATE customers
SET county = 'Nakuru'
WHERE customer_id = 4;

The statement can be understood as:



```text id="8pn4ow"
UPDATE customers
        ↓
Which table?

SET county = 'Nakuru'
        ↓
What should change?

WHERE customer_id = 4
        ↓
Which record should change?

The WHERE clause is extremely important because it determines which rows are affected.

Consider the following statement:

```sql id="r1v61y"
UPDATE customers
SET county = 'Nakuru';

Because there is no `WHERE` condition, SQL will attempt to change the county to `Nakuru` for **every row in the table**.

Therefore, before executing an `UPDATE`, always check whether your `WHERE` condition identifies the intended records.

You can even check the affected records first:



```sql id="4ppfl4"
SELECT *
FROM customers
WHERE customer_id = 4;

Once you are satisfied that the correct record has been identified, you can execute the UPDATE.

---

3. DELETE — Removing Data

The DELETE statement removes existing records from a table.

Its basic syntax is:

```sql id="rfdjhu"
DELETE FROM table_name
WHERE condition;

For example, suppose we want to remove the customer whose ID is `4`:



```sql id="97gexx"
DELETE FROM customers
WHERE customer_id = 4;

The WHERE condition tells the database exactly which record should be removed.

As with UPDATE, you must be careful when using DELETE.

Consider:

```sql id="b1f44m"
DELETE FROM customers;

Without a `WHERE` clause, this statement targets **all rows in the table**.

The table itself still exists, but its records will be removed.

This is different from:



```sql id="c68daw"
DROP TABLE customers;

DROP TABLE removes the table itself, including its structure, whereas DELETE removes records from an existing table.

---

4. SELECT — Retrieving Data

Although SELECT does not change the data stored in a table, it is one of the SQL commands you will use most frequently.

SELECT is used to retrieve information from a database.

To retrieve every column from the customers table:

```sql id="syf0pz"
SELECT *
FROM customers;

The `*` means **all columns**.

If we only need customer names and counties, we can specify those columns:



```sql id="xy7fyq"
SELECT customer_name, county
FROM customers;

We can also use WHERE to retrieve only records matching a particular condition:

```sql id="rd3jgg"
SELECT customer_name, county
FROM customers
WHERE county = 'Nairobi';

This asks the database to return customers whose county is Nairobi.

`SELECT` becomes much more powerful when combined with SQL features such as:

* `WHERE` for filtering
* `ORDER BY` for sorting
* `GROUP BY` for grouping
* Aggregate functions such as `SUM()`, `COUNT()`, `AVG()`, `MIN()`, and `MAX()`
* `JOIN` for retrieving related information from multiple tables

These concepts can be explored separately as you progress beyond basic DML.

---

## Bringing the DML Commands Together

We can think about the commands in terms of the lifecycle of a record.

A record is first **created**:



```sql id="j18qnd"
INSERT INTO customers (customer_name, county, email)
VALUES ('Alice Njeri', 'Nairobi', '[e-mail]');

It can later be read:

```sql id="os2xvw"
SELECT *
FROM customers
WHERE customer_name = 'Alice Njeri';

If something changes, it can be **updated**:



```sql id="c39tn2"
UPDATE customers
SET county = 'Nakuru'
WHERE customer_name = 'Alice Njeri';

Finally, if the record is no longer required, it can be deleted:

```sql id="vz7ij9"
DELETE FROM customers
WHERE customer_name = 'Alice Njeri';

These four operations are often described using the acronym **CRUD**:

| CRUD Operation | SQL Command | Purpose                |
| -------------- | ----------- | ---------------------- |
| Create         | `INSERT`    | Add new data           |
| Read           | `SELECT`    | Retrieve existing data |
| Update         | `UPDATE`    | Modify existing data   |
| Delete         | `DELETE`    | Remove existing data   |

CRUD operations form the foundation of how many applications interact with relational databases.

For example, when a customer creates an account on an online platform, the application may use an `INSERT` operation behind the scenes. When the customer views their profile, the application retrieves their information. Updating their address modifies the existing record, while deleting an account may involve removing or deactivating the associated data.

Understanding DML therefore goes beyond learning SQL syntax. It helps us understand how applications create, retrieve, modify, and manage the data that organizations use every day.

##Conclusion

Understanding DDL (Data Definition Language) and DML (Data Manipulation Language) is an important foundation for anyone learning SQL and relational databases.

DDL focuses on the structure of the database. Commands such as CREATE, ALTER, TRUNCATE, and DROP allow us to create and manage database objects such as tables.

DML, on the other hand, focuses on the data stored within those structures. Commands such as INSERT, UPDATE, and DELETE allow us to add, modify, and remove records, while SELECT is commonly used to retrieve and explore the stored data.