DML (Data Manipulation Language)

Data Manipulation Language (DML) statements are used to retrieve, insert, update, and delete data in your database. These are the most frequently used SQL statements.

Core DML Statements

INSERT Statement

The INSERT statement is used to add new records to a table.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

You can also insert multiple rows at once:

INSERT INTO table_name (column1, column2)
VALUES
    (value1_a, value2_a),
    (value1_b, value2_b),
    (value1_c, value2_c);

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

Syntax:

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

Example:

UPDATE Employees
SET Salary = Salary * 1.10, Department = 'Sales'
WHERE EmployeeID = 101;

DELETE Statement

The DELETE statement is used to remove existing records from a table.

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Orders
WHERE OrderDate < '2023-01-01';

TRUNCATE TABLE table_name;

SELECT Statement

The SELECT statement is used to query the database and retrieve data from one or more tables.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING group_condition
ORDER BY column_name(s) [ASC|DESC];

Common Clauses:

• SELECT: Specifies the columns to retrieve. Use * to select all columns.
• FROM: Specifies the table(s) to retrieve data from.
• WHERE: Filters records based on a specified condition.
• GROUP BY: Groups rows that have the same values in specified columns into summary rows.
• HAVING: Filters groups based on a specified condition (used with GROUP BY).
• ORDER BY: Sorts the result set by one or more columns.

Example:

SELECT CustomerName, City
FROM Customers
WHERE Country = 'USA'
ORDER BY City;

Advanced DML Concepts

Joins

Joins are used to combine rows from two or more tables based on a related column between them.

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table.
• FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table.

Example (INNER JOIN):

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

Subqueries

A subquery (or inner query or nested query) is a query within another SQL query.

Example:

SELECT ProductName
FROM Products
WHERE ProductID IN (
    SELECT ProductID
    FROM OrderDetails
    WHERE Quantity > 50
);

Data Modification Language (DML) Best Practices

• Always use a WHERE clause with UPDATE and DELETE unless you intend to affect all rows.
• Test your DML statements on a development or staging environment before executing them on production.
• Use transactions to ensure data integrity for complex operations.
• Consider performance implications for large tables.