DML in SQL: Definition, Types, and Basic Commands

Definition and Types of DML

Data Manipulation Language (DML) is a query method in SQL that can be used after the database structure is created using DDL (Data Definition Language). Simply put, DML is a collection of query commands that function to manipulate data within a database.

The main commands in DML include:

• INSERT to add data.
• UPDATE to change or replace data.
• DELETE to delete data.

DML itself is divided into two main types, namely:

1. Procedural DML

In this type, the commands used to manipulate data must be accompanied by clear instructions on how the data in the database file is accessed.

Procedural DML is generally used in high-level programming languages such as C and C++.

2. Non-Procedural DML

Unlike procedural DML, in non-procedural DML data can be manipulated directly without needing to include instructions on how to access the data.

Non-procedural DML is usually used in DBMS (Database Management System) such as Paradox, FoxPro, and SQL.

---

Usage of INSERT, UPDATE, and DELETE Commands in SQL

The following is a more detailed explanation of the three basic DML commands along with examples of their usage:

1. INSERT Command

INSERT is a command used to add or insert new data rows into a table in the database.

Syntax

INSERT INTO table_name (column_data) VALUES (data_value);

Example Usage

INSERT INTO Jurusan (KodeJur, NamaJur) 
VALUES ('12', 'Teknik Informatika');

Explanation

The above command is used to add new data to the Jurusan table. The KodeJur column will be filled with the value 12, while the NamaJur column will be filled with Teknik Informatika.

---

2. UPDATE Command

UPDATE is a command used to change, update, or replace existing data in the database.

Syntax

UPDATE table_name 
SET column_name = new_column_value 
WHERE condition;

Example Usage

UPDATE Mahasiswa 
SET Nama = 'Wilan' 
WHERE No = '1123';

Explanation

This command means we change the data in the Mahasiswa table, specifically by replacing the data in the Nama column with Wilan, specifically for the data row that has a No value equal to 1123.

Important Note

If the WHERE command or condition is not included, the system will change all data in that column for all rows.

---

3. DELETE Command

DELETE is a command used to delete data rows from the database.

Syntax

DELETE FROM table_name 
WHERE condition;

Example Usage

DELETE FROM Mahasiswa 
WHERE No = '1123';

Explanation

This command is used to delete data from the Mahasiswa table that has a No or NIM record of 1123.

Important Note

Data deletion using DELETE is highly dependent on the WHERE condition used. It is advisable to always double-check whether the DELETE command is defined with the correct condition to avoid accidentally deleting important data.