SQL data modification orders with examples: quick and easy guide

introduction

SQL is not only about to inquire about the data – it also includes strong orders to edit data within the tables. these Data processing language (DML) Orders, such as insert, update, and delete, enable you to add, modify or remove rows in a database. In this article, we will explore these orders with practical examples.

Sample schedule

Let’s start with a sample table to show examples:

1. Enter: add rows to a table

A listing order is used to add new rows to a table.

a task: Add a new employee, Frank, a test in the quality guarantee department with a salary of $ 60,000.

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

a result: The table now includes the new employee:

2. Update: modifying the current rows

The update order allows you to amend the data in the ranks based on specific conditions.

a taskGive all developers in the IT department an increase in salary by 10 %.

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

a resultAlice, Charlie and Eve’s salary:

3. Delete: remove rows from a table

It removes the deletion of rows from a table dependent on a condition.

a taskRemove all employees in the Quality Assurance Department.

DELETE FROM Employees
WHERE Department = 'QA';

a resultFrank was removed from the table:

4. Upsert: combine inclusion and update

The merger is used to include new rows or update the existing rows based on a matching condition. This is also known as “UPSERT”.

a task: In the event of an employee with Effreneid = 5, update his position to the “main 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);

a resultEve’s position has been updated to a “major developer”:

5. Detament: Cleansing all rows quickly

Tracate removes all rows from a table, but unlike deletion, it does not record the individual row deletions, which makes it faster.

a task: Wipe all the classes from the employee schedule.

TRUNCATE TABLE Employees;

a result: The table is now empty, but the structure is still intact.

6. Driving: Remove the entire table

Drop is permanently deleting a schedule and data.

a task: Remove the employee’s schedule from the database.

DROP TABLE Employees;

a result: The employee schedule is no longer present.

summary

SQL provides a wide range of orders to modify data and table structures. Here is a quick summary:

Order Using the case

1. Insert: Add new rows to a table.
2. Update: Data modification in the current rows.
3. Delete: remove specific rows from a table.
4. Inclusion: combining Upsert.
5. Dutting: I quickly wipe all rows in the table.
6. Driving, remove the entire table structure and data.

These orders allow you to keep your database updated, clean and well organized. Do these examples on your database to gain confidence in the data modification with SQL!

Thank you for spending time to explore data related visions with me. I can share you. If you find this information useful, I invite you to follow up or contact me on LinkedIn. Happy exploring!