The GROUP BY clause in Microsoft SQL Server is used to group rows with similar values in one or more columns into summary rows. It is essential for aggregating and summarizing data.
Notes
Chapter 17: In-built Functions
In Microsoft SQL Server, in-built functions provide a wide range of tools for manipulating and analyzing data. This chapter explores various categories of in-built functions and their real-world use cases.
1. String Functions
String functions allow you to perform operations on text data.
Common String Functions:
● LEN(): Returns the length of a string.
Example :
Finding the length of a string
SELECT
LEN('Hello, World!')
AS
Length;
● CONCAT(): Combines two or more strings into one.
Example :
Concatenating strings
SELECT CONCAT(
FirstName, ' ', LastName)
AS
FullName
FROM
Employees;
2. Numeric Functions
Numeric functions help with mathematical operations on numeric data.
Common Numeric Functions:
● SUM(): Calculates the sum of numeric values in a column.
Example :
Calculating the total revenue
SELECT SUM
(Revenue)
AS
TotalRevenue
FROM
Sales;
Common Numeric Functions:
● AVG(): Computes the average of numeric values in a column.
Example :
Finding the average salary
SELECT AVG
(Salary)
AS
AvgSalary
FROM
Employees;
3. Date Functions
Date functions assist in working with date and time data.
Common Date Functions:
● GETDATE(): Retrieves the current date and time.
Example :
Getting the current date and time
SELECT
GETDATE()
AS
CurrentDateTime ;
● DATEDIFF(): Calculates the difference between two dates.
Example :
Finding the days between two dates
SELECT
DATEDIFF(DAY, OrderDate, ShippedDate)
AS
DaysToShip
FROM
Orders;
4. Real-world Use Cases for Built-in Functions
E-commerce System:
- Order Tracking: Use date functions to calculate the estimated delivery date based on order date and shipping time.
- User Authentication: Employ string functions to validate and manipulate user passwords securely.
Financial System:
- Tax Calculation: Utilize numeric functions to calculate taxes, percentages, and financial metrics.
- Invoice Generation: Combine string functions to create custom invoice templates with dynamic content.
5. Key Takeaways
- SQL Server provides a variety of in-built functions for string, numeric, and date manipulation.
- These functions enhance data analysis, transformation, and reporting capabilities.
- Real-world scenarios benefit from the use of in-built functions to streamline operations and calculations.
6. Exercise Questions
- Create a query that calculates the total length of product names in the "Products" table using the LEN() function.
- Write a query that calculates the average price of products in the "Electronics" category using the AVG() function.
- Find the number of days an employee has been with the company by calculating the difference between their hire date and the current date using the DATEDIFF() function.
- Concatenate the first name and last name of employees in the "HR" department, separating them with a space, using the CONCAT() function.
- Explain the advantages of using in-built functions in SQL Server for data manipulation and analysis.
- Discuss the potential challenges and considerations when dealing with date and time data in SQL Server using date functions.
In Microsoft SQL Server, User-Defined Functions (UDFs) are custom functions that allow you to encapsulate logic and perform specific tasks. This chapter explores the creation, usage, and real-world applications of UDFs.