Mastering DML Commands in SQL - A Practical Guide

In the dynamic world of database management, Data Manipulation Language (DML) commands serve as the backbone for interacting with and modifying data within tables. Let’s explore into the usage of essential DML commands with examples to provide a comprehensive understanding.

1. INSERT

The `INSERT` statement is crucial for adding new records to a database table. It offers flexibility, allowing you to specify both column names and values or simply provide values in the order in which columns were defined.

-- Syntax with column names
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

-- Syntax without column names
INSERT INTO table_name VALUES (value1, value2, value3, ...);

Examples:

-- Both ways are correct
INSERT INTO CUSTOMERS (InsuranceID, Name, DOB, NIN, Location, email_id) VALUES ('123', 'Mango','2000-01-01','56789','LO','Mango@xyz.com');
INSERT INTO CUSTOMERS VALUES ('123', 'Mango','2000-01-01','56789','LO','Mango@xyz.com');

2. SELECT

The `SELECT` statement is fundamental for retrieving data from database tables. It allows you to specify the columns you want to retrieve and apply conditions for filtering.

-- Syntax
SELECT column1, column2, ...
FROM table_name
[WHERE condition];

Example:

-- Retrieve all columns from the CUSTOMERS table
SELECT * FROM CUSTOMERS;

3. UPDATE

The `UPDATE` statement facilitates the modification of existing values in a database table. It allows you to set new values for specified columns based on a given condition.

-- Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

-- Update the email_id for a specific InsuranceID in the CUSTOMERS table
UPDATE CUSTOMERS SET email_id = 'mango.lo@xyz.com' WHERE InsuranceID='123';

4. DELETE

The `DELETE` statement is used to remove existing records from a database table based on specified conditions.

-- Syntax
DELETE FROM table_name WHERE condition;

Example:

-- Delete a specific record from the CUSTOMERS table
DELETE FROM CUSTOMERS WHERE InsuranceID='123';

Understanding and mastering these DML commands is essential for effective data management in SQL. Whether you’re adding new records, retrieving information, updating existing values, or deleting records, these commands form the foundation of manipulating data within a relational database system.

By applying these commands judiciously, you can maintain the integrity and accuracy of your database.

You may also like: