DML (Data Manipulation Language)

What is DML?

DML (Data Manipulation Language) is a subset of the SQL (Structured Query Language) language that is used to manage the data within the tables of a database.

• Allows: inserting, querying, updating, and deleting data.
• Daily use: social networks, online stores, banks, among others, use DML commands constantly.
• Essential: without DML, databases would be empty structures.

---

What is SQL?

SQL (Structured Query Language) is a programming language designed to store, process, and retrieve information in relational databases.

• Stores data in tables (rows and columns).
• Allows performing operations such as:
  • Inserting data
  • Querying records
  • Updating information
  • Deleting records
• It also serves to optimize and maintain database performance.

---

Main DML Commands

Below, we will see the main commands with examples using a products table.

Example table: products

Column	Type	Description
id	SERIAL	Unique identifier (auto-incremental)
name	VARCHAR(100)	Product name
price	DECIMAL	Product price
stock	INT	Available units

---

1. INSERT - Add new records

Syntax:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Example:

INSERT INTO products (name, price, stock) VALUES ('T-shirt', 25.99, 100);
INSERT INTO products (name, price, stock) VALUES ('Pants', 45.00, 50);
INSERT INTO products (name, price, stock) VALUES ('Shoes', 70.50, 30);

---

2. SELECT - Query data

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;
-- SELECT * selects all columns

Examples:

-- All products
SELECT * FROM products;

-- Only name and price
SELECT name, price FROM products;

-- Products with price greater than 50
SELECT * FROM products WHERE price > 50;

-- Products with stock less than 60
SELECT name, stock FROM products WHERE stock < 60;

---

3. UPDATE - Update existing data

Syntax:

UPDATE table_name SET column1 = value1 WHERE condition;

Important!: Always use WHERE to avoid modifying all rows.

Examples:

-- Change price of the T-shirt
UPDATE products SET price = 29.99 WHERE name = 'T-shirt';

-- Increase stock of shoes by 5
UPDATE products SET stock = stock + 5 WHERE name = 'Shoes';

---

4. DELETE - Delete records

Syntax:

DELETE FROM table_name WHERE condition;

Caution!: If you omit WHERE, all records will be deleted.

Examples:

-- Delete product by id
DELETE FROM products WHERE id = 1;

-- Delete products without stock
DELETE FROM products WHERE stock = 0;

---

Testing DML with Docker and PostgreSQL

Requirements

• Docker installed
• Linux Terminal (Ubuntu)
• PostgreSQL Client (psql)

---

Step 1: Create PostgreSQL container

docker run --name my-postgres-db \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -d postgres

Arguments explained:

• --name: container name
• -e POSTGRES_PASSWORD: password for the postgres user
• -p: exposes local port 5432
• -d: runs in background

---

Step 2: Connect to PostgreSQL from the terminal

1. Install PostgreSQL client if you don't have it:

sudo apt update
sudo apt install postgresql-client -y

2. Connect to the container:

psql -h localhost -p 5432 -U postgres

It will ask for the password: mysecretpassword.

---

Step 3: Create table and test DML

Create the table (DDL):

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL
);

---

Insert products (INSERT):

INSERT INTO products (name, price, stock) VALUES ('T-shirt', 25.99, 100);
INSERT INTO products (name, price, stock) VALUES ('Pants', 45.00, 50);
INSERT INTO products (name, price, stock) VALUES ('Shoes', 70.50, 30);

---

Query products (SELECT):

-- All
SELECT * FROM products;

-- Price greater than 40
SELECT name, price FROM products WHERE price > 40;

---

Update data (UPDATE):

-- Change T-shirt price
UPDATE products SET price = 29.99 WHERE name = 'T-shirt';

-- Verify change
SELECT * FROM products WHERE name = 'T-shirt';

---

Delete product (DELETE):

-- Get ID first
SELECT id FROM products WHERE name = 'Pants';

-- Suppose it is 2
DELETE FROM products WHERE id = 2;

-- Verify
SELECT * FROM products;

---

Step 4: Exit and clean up

Exit psql:

\q

Stop and remove the container:

docker stop my-postgres-db
docker rm my-postgres-db

---