Aggregate functions perform calculations
on a set of values and return a single value.
Fundamental Elements
They are essential for summarizing data in
Mathematical functions perform SQL queries, enabling insights and analysis.
calculations and return numeric results. Key Characteristics
MAX: Returns the maximum value in a set.
ABS: Returns the absolute value of a
number. Aggregate Functions MIN: Returns the minimum value in a set.
CEILING: Rounds a number up to the Mathematical Functions AVG: Calculates the average of a numerical
nearest integer. Key Functions dataset.
FLOOR: Rounds a number down to the SUM: Computes the total sum of a
nearest integer. Common Functions numerical dataset.
ROUND: Rounds a number to a specified COUNT: Counts the number of rows in a
number of decimal places. dataset.
POWER: Raises a number to the power of
another number.
DATEADD: Adds a specified time interval
to a date.
Conversion functions convert data from GETDATE: Returns the current date and
one type to another, ensuring time.
compatibility in operations. Purpose of Conversion Functions
CURRENT_DATE: Retrieves the current
CAST: Converts an expression from one date only.
data type to another. Practical Examples
DATEDIFF: Calculates the difference
CONVERT: Similar to CAST but allows for between two dates.
Conversion Functions
format specification.
MONTH: Extracts the month from a date.
PARSE: Converts a string representation of
a value to its corresponding type. Key Functions DAYNAME: Returns the name of the day
Date Functions from a date.
TRY_CAST: Attempts to cast an expression
to a specified data type and returns NULL
if it fails.
Important SQL DATEPART: Returns a specified part of a
Functions You
date (year, month, day).
TRY_CONVERT: Similar to TRY_CAST but
with format options.
Should Learn Key Functions YEAR: Extracts the year from a date.
DAY: Extracts the day of the month from a
date.
Window functions perform calculations
across a set of table rows that are
somehow related to the current row. Overview of Window Functions
String functions are used to manipulate
Key Characteristics and analyze string data types in SQL.
LEAD: Accesses data from the next row in
the result set without using a self-join.
SUBSTRING: Extracts a part of a string.
Window Functions
LAG: Accesses data from the previous row
in the result set. CHARINDEX: Finds the position of a
String Functions substring within a string.
Key Functions
FIRST_VALUE: Returns the first value in an
ordered set of values. LEFT: Returns a specified number of
Common Functions characters from the left side of a string.
LAST_VALUE: Returns the last value in an
ordered set of values. RIGHT: Returns a specified number of
characters from the right side of a string.
TRIM: Removes whitespace from both
Rank functions are used to assign a rank to ends of a string.
each row within a partition of a result set. Understanding Rank Functions
ROW_NUMBER: Assigns a unique
sequential integer to each row. Null handling functions are designed to
Handling Null Values deal with NULL values in SQL queries.
RANK: Assigns a rank to each row within a Rank Functions
partition, allowing duplicate ranks. ISNULL: Replaces NULL with a specified
replacement value.
Null Handling Functions
DENSE_RANK: Similar to RANK but Key Functions
without gaps in the ranking sequence. COALESCE: Returns the first non-null value
Key Functions from a list of arguments.
NTILE: Divides the result set into a
specified number of groups and assigns a NULLIF: Returns NULL if two expressions
number to each group. are equal; otherwise, it returns the first
expression.