T-SQL Data Manipulation Language (DML)

Data Manipulation Language (DML) statements are used to manage data within schema objects. DML statements include INSERT, UPDATE, DELETE, and SELECT.

1. INSERT Statement

The INSERT statement is used to add new rows of data to a table.

Basic INSERT syntax:

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

INSERTing with specific columns:

INSERT INTO Employees (FirstName, LastName, HireDate)
VALUES ('John', 'Doe', '2023-10-26');

INSERTing all columns (values must be in table order):

INSERT INTO Departments
VALUES ('HR', 'Human Resources', 'New York');

2. UPDATE Statement

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

Basic UPDATE syntax:

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

Updating a specific record:

UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 101;

Updating multiple records:

UPDATE Products
SET Price = Price * 1.10 -- Increase price by 10%
WHERE CategoryID = 3;

3. DELETE Statement

The DELETE statement is used to remove rows from a table.

Basic DELETE syntax:

DELETE FROM table_name
WHERE condition;

Deleting a specific record:

DELETE FROM Customers
WHERE CustomerID = 50;

Deleting all records (use with extreme caution!):

DELETE FROM Orders;

Alternative to DELETE for removing all rows (faster and uses fewer resources):

TRUNCATE TABLE table_name;

4. SELECT Statement

The SELECT statement is used to retrieve data from one or more tables.

Basic SELECT syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Selecting all columns from a table:

SELECT *
FROM Products;

Selecting specific columns with a WHERE clause:

SELECT ProductName, Price
FROM Products
WHERE CategoryID = 2
ORDER BY Price DESC;

Using joins to combine data from multiple tables:

SELECT o.OrderID, c.CustomerName
FROM Orders AS o
JOIN Customers AS c ON o.CustomerID = c.CustomerID;

Common DML Clauses

WHERE: Filters records based on a specified condition.
ORDER BY: Sorts the result set in ascending or descending order.
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).
JOIN: Combines rows from two or more tables based on a related column.