SQL Data Modification Commands With Examples: A Fast and Easy Guide

Introduction

SQL is not just about data -demand – it also includes powerful commands to change data within the tables. This Data Manipulation Language (DML) Commandments, such as insert, updates, and delete, enable you to add, change, or remove rows in a database. In this article, we will explore these commands with practical examples.

Sample Table: Employees

Let's start with a sample employee table to show examples:

1. Enter: Adding rows to a table

The insert command is used to add new rows to a table.

Activity: Add a new employee, Frank, who is a tester to the QA department with a salary of $ 60,000.

INSERT INTO Employees (EmployeeID, Name, Position, Salary, Department)
VALUES (6, 'Frank', 'Tester', 60000, 'QA');

Result: The table now includes the new employee:

2. Update: Changing existing rows

The update command allows you to change data to existing rows based on specific conditions.

Activity: Give all developers to the IT department a 10% salary increase.

UPDATE Employees
SET Salary = Salary * 1.10
WHERE Position = 'Developer' AND Department = 'IT';

Result: The salary for Alice, Charlie, and Eve has been updated:

3. Delete: Removing rows from a table

The delete command removes rows from a table based on a condition.

Activity: Remove all employees in the QA department.

DELETE FROM Employees
WHERE Department = 'QA';

Result: Frank was removed from the table:

4. Combine (UpSert): Insert Insert and Update

The combination statement is used to enter new rows oi -update existing rows based on a match condition. It is also known as “upsert”.

Activity: If an employee exists with an employee = 5, update their position with “Lead Developer”. Otherwise, enter a new employee.

MERGE INTO Employees AS Target
USING (SELECT 5 AS EmployeeID, 'Eve' AS Name, 'Lead Developer' AS Position, 80000 AS Salary, 'IT' AS Department) AS Source
ON Target.EmployeeID = Source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET Position = Source.Position, Salary = Source.Salary
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Name, Position, Salary, Department)
    VALUES (Source.EmployeeID, Source.Name, Source.Position, Source.Salary, Source.Department);

Result: Eve's position is updated to “Lead Developer”:

5. Truncate: Quickly clean all rows

The truncate command removes all the rows from a table, but unlike the removal, it does not log individual row removal, making it faster.

Activity: I -Clear all rows from the employee's table.

TRUNCATE TABLE Employees;

Result: The table now is empty, but the structure remains intact.

6. Drop: Removing the whole table

The drop command removes a table and its permanent data.

Activity: Remove the employee's table from the database.

DROP TABLE Employees;

Result: The employee's table no longer exists.

Summary

SQL provides a wide range of commands to change data and table structures. Here's a quick return:

Command Use a case

1. Enter: Add new rows to a table.
2. Update: Change data to existing rows.
3. Remove: Remove specific rows from a table.
4. Combine: Combine insert and update logic (upsert).
5. Truncate: All the rows are quickly clear on a table.
6. I -Drop Remove the entire table and data structure.

These commands allow you to maintain your database timely, clean, and orderly. Perform these examples in your own database to gain confidence in changing data in SQL!

Thanks for taking the time to explore the views related to the data. I appreciate your relationship. If this information is worth it, I invite you to follow me or connect me to LinkedIn. Happy exploration! 👋