Multiple rows of data can be calculated using SQL's aggregate functions, which yield a single result.

Common Aggregate Functions

    COUNT(): Returns the number of rows.
    SUM(): Returns the total sum of a numeric column.
    AVG(): Returns the average value of a numeric column.
    MIN(): Returns the minimum value.
    MAX(): Returns the maximum value.

Example Usage of Aggregate Functions

1. Using COUNT()
SELECT COUNT(*) FROM Employees;

2. Using SUM()
SELECT SUM(Salary) FROM Employees;

3. Using AVG()
SELECT AVG(Salary) FROM Employees;

4. Using MIN() and MAX()
SELECT MIN(Salary) FROM Employees;

SELECT MAX(Salary) FROM Employees;

Output
    MIN(Salary): 50,000 (Lowest salary)
    MAX(Salary): 200,000 (Highest salary)

Using GROUP BY with Aggregate Functions
GROUP BY is often used with aggregate functions to group results by one or more columns.
SELECT
 Department, AVG(Salary)
FROM Employees
GROUP BY Department;

Output

Using HAVING with Aggregate Functions

HAVING is used to filter results after aggregation.

SELECT
 Department, COUNT(*)
FROM Employees
GROUP BY Department
 HAVING COUNT(*) > 10;

Output

Advanced Use of Aggregate Functions
Aggregate functions in SQL can be used in advanced ways to solve complex data analysis problems efficiently.

1. Using Aggregate Functions with CASE
SELECT Department,
       SUM(   CASE
                  WHEN Gender = 'Male' THEN
                      1
                  ELSE
                      0
              END
          ) AS Male_Count,
       SUM(   CASE
                  WHEN Gender = 'Female' THEN
                      1
                  ELSE
                      0
              END
          ) AS Female_Count
FROM Employees
GROUP BY Department;

Output

2. Using Aggregate Functions with DISTINCT
SELECT COUNT(DISTINCT Department) AS Total_Departments FROM Employees;

Output. 5 (Total distinct departments)

3. Using Aggregate Functions with PARTITION BY
PARTITION BY allows applying aggregate functions without collapsing rows.
SELECT EmployeeID,
       Name,
       Department,
       Salary,
       AVG(Salary) OVER (PARTITION BY Department) AS Avg_Department_Salary
FROM Employees;

Output

4. Using Aggregate Functions with HAVING for Filtering
SELECT Department,
   COUNT(*) AS Employee_Count
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;

Output. Departments have more than 10 employees.

Advantages of Advanced Aggregate Functions

• Allows detailed data analysis with conditions.
• Enhances reporting and business intelligence capabilities.
• Reduces query complexity using built-in SQL functions.
• Helps in summarizing data.
• Improves query efficiency by reducing result set size.
• Facilitates data analysis and reporting.

These advanced aggregate functions help in efficient query design and deeper data insights.

HostForLIFEASP.NET SQL Server 2022 Hosting

Today, I'd want to revisit one of the most typical interview questions and replies, which I see frequently referenced in subsequent interviews.

Transaction
A transaction is a collection of SQL statements that operate as a single unit. In layman's terms, a transaction is a unit in which a series of tasks is completed in order to finish the entire activity. To better appreciate this, consider a bank transaction.

SQL Server ACID Properties
SQL Server's ACID properties assure data integrity during a transaction. A transaction is a series of SQL (insert, update, and delete) statements that are handled as a single unit and are executed using the completed or Not principle. To be successful, a transaction must adhere to the ACID Properties. Atomicity, Consistency, Isolation, and Durability are abbreviated as ACID.

Atomicity
Every transaction adheres to atomicity. It indicates that if a transaction is initiated, it must be completed or rolled back. To illustrate, if a person transfers money from account "A" to account "B," the money should be credited to account B after the transaction is completed. If a failure occurs, the modification should be rolled back after debiting the money from account "A." In other words, each transaction will either succeed or fail.

Consistency
Consistency says that after the completion of a transaction, changes made during the transaction should be consistent. Let’s understand this fact by referring to the above example, if account “A” has been debited by 200 RS, then after the completion of the transaction, account “B” should be credited by 200 RS. It means changes should be consistent. In simple words, Consistency means a guarantee that a transaction never leaves your database in a half-finished state.

Isolation
Isolation states that every transaction should be isolated from each other. There should not be any interference between the two transactions. In simple words, Any other operation cannot affect my operation.

Durability
Durability means that once the transaction is completed, all the changes should be permanent. It means that in case of any system failure, changes should not be lost. In simple words, When the Transaction is complete, the changes are saved.Now we can understand this with the help of a Figure. This is very easy for us. This Figure is shown below.

FAQs
Q. What is the difference between the Local and the Distributed SQL Server transactions?
The Local transaction is an SQL Server transaction that processes data from the local database server

The Distributed transaction is an SQL Server transaction that processes data from more than one database server

Q. Some transactions may not complete their execution successfully. Explain these transactions.
These transactions are known as,

Aborted.

Whenever a transaction finishes its final statement, it ultimately enters into this state:

Partially committed.
Q. What is the SQL Server Transaction Log LSN?
Each Transaction log record that is written to the SQL Server transaction log file can be identified by its Log Sequence Number (LSN).