Data Manipulation and Aggregation Explained

1. INSERT Statement

The INSERT statement is used to add new records into a table. It can insert one or more rows at a time.

Example:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (1, 'John', 'Doe', 'HR');
    

This command adds a new employee with the ID 1, named John Doe, to the HR department.

2. UPDATE Statement

The UPDATE statement is used to modify existing records in a table. It can update one or more columns for selected rows.

Example:

UPDATE Employees
SET Department = 'Sales'
WHERE EmployeeID = 1;
    

This command changes the department of the employee with ID 1 to Sales.

3. DELETE Statement

The DELETE statement is used to remove existing records from a table. It can delete one or more rows based on a condition.

Example:

DELETE FROM Employees
WHERE EmployeeID = 1;
    

This command removes the employee with ID 1 from the Employees table.

4. SELECT Statement

The SELECT statement is used to retrieve data from a database. It can retrieve specific columns or all columns from one or more tables.

Example:

SELECT FirstName, LastName
FROM Employees
WHERE Department = 'Sales';
    

This query retrieves the first and last names of all employees in the Sales department.

5. Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX.

Example:

SELECT COUNT(*) AS TotalEmployees,
       SUM(Salary) AS TotalSalary,
       AVG(Salary) AS AverageSalary,
       MIN(Salary) AS MinimumSalary,
       MAX(Salary) AS MaximumSalary
FROM Employees;
    

This query calculates the total number of employees, total salary, average salary, minimum salary, and maximum salary from the Employees table.

6. GROUP BY Clause

The GROUP BY clause is used to group rows that have the same values into summary rows, often used with aggregate functions to perform calculations for each group.

Example:

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department;
    

This query groups employees by their department and counts the number of employees in each department.