SQL DML statements – SQL Tutorial

SQL is a powerful language used for managing and manipulating data in relational database systems. SQL consists of several types of statements, and among them, Data Manipulation Language (DML) statements are essential for interacting with data stored in a database. DML statements are used to query, insert, update, and delete data within database tables.

DML Applications

Data Analysis: DML statements enable data analysts to retrieve and manipulate data for various analytical purposes, such as identifying trends, generating reports, and supporting decision-making processes.

Database Maintenance: DML statements are crucial for maintaining and updating database content, ensuring the accuracy and consistency of data within the database structure.

Application Development: DML statements form the bedrock of database-driven applications, facilitating data retrieval, manipulation, and storage within the applications’ logic.

Web Applications: DML statements power web applications that interact with databases, allowing users to view, modify, or add data through their web interfaces.

Key DML Statements

Let’s take a closer look at the common SQL DML statements:

SELECT

The SELECT statement is used to retrieve data from one or more tables in a database. It allows you to specify which columns you want to retrieve, apply filtering conditions, and even join multiple tables together to get the desired result set. Here’s a basic example:

SELECT first_name, last_name 
FROM employees 
WHERE department = 'Sales';

INSERT

The INSERT statement is used to add new records (rows) into a table. You provide the values to be inserted into each column, and you can either specify the column names or omit them if you’re inserting values for all columns. Here’s an example:

INSERT INTO customers (customer_id, first_name, last_name, email) 
VALUES (101, 'John', 'Doe', '[email protected]');

UPDATE

The UPDATE statement is used to modify existing records in a table. You specify the table to update, the columns to change, and the new values for those columns. You can also include a WHERE clause to filter which records should be updated. Here’s an example:

UPDATE products 
SET price = 19.99 
WHERE category = 'Electronics';

DELETE

The DELETE statement is used to remove one or more records from a table. Like with the UPDATE statement, you can use a WHERE clause to specify which records should be deleted. Be cautious when using DELETE, as it can permanently remove data from a table. Here’s an example:

DELETE FROM orders 
WHERE order_date < '2023-01-01';

MERGE

The MERGE statement, also known as an upsert operation, combines the functionality of INSERT, UPDATE, and DELETE. It's used to synchronize data between two tables based on a specified condition. If a matching record exists, it updates it; otherwise, it inserts a new record. This is particularly useful for managing data synchronization in data warehousing and ETL (Extract, Transform, Load) processes.

MERGE INTO target_table AS T
USING source_table AS S
ON T.id = S.id
WHEN MATCHED THEN
    UPDATE SET T.value = S.value
WHEN NOT MATCHED THEN
    INSERT (id, value) VALUES (S.id, S.value);

These are the fundamental SQL DML statements used for querying and manipulating data within a relational database. They allow you to interact with data, retrieve information, insert new records, update existing records, and delete unwanted data, making SQL a versatile language for managing database systems.