SQL FUNCTIONS

SQL Functions

Commonly Used Functions

1. Window Functions examples include, (Row_number, Rank). This function is used to retrieve single records out of a repeating table . Row_number: This gives each row a unique number in sequential order within a group.

For example, Number transactions by customer . Hence for each customer, number their transactions starting from 1.

Rank: This ranks each row based on its value, leaving gaps when two or more rows have the same rank.

This ranks all successful transactions by amount. The biggest transaction gets rank 1. If two transactions have the same amount, they get the same rank, and the next rank skips a number.

1. STRING FUNCTIONS (CONCAT, SUBSTRING, TRIM, REPLACE, UPPER/LOWER). This is a basic text manipulation for formatting and cleaning the data.

*Concat *: This is used to join two or more strings together into a single string.

Substring : This is used to extract a portion of a string based on position and length.

The results looks like;

Trim: This is used to remove unwanted characters which is mostly spaces from the beginning and or end of a string. eg ' John Mwangi ' to 'John Mwangi'

Replace: This is used to substitute all occurrences of a specified substring within a string with another substring.

Upper/Lower/initcap
Upper converts all characters to UPPERCASE.
Lower converts all characters to lowercase.
Initcap converts first letter of each word to uppercase. Example;

Results;

1. Aggregate Functions (COUNT, SUM, AVG, MIN, MAX): Fundamental for summarising data.

Count, returns the number of rows that match a specified condition.

Sum, returns the total sum of a numeric column.

Avg, returns the average which is the mean value of a numeric column.

Min, returns the minimum or the smallest value from a column.

Max, returns the maximum or largest value from a column.

1. GROUP_CONCAT is used for combining values per group from multiple rows to a single string.

Conclusion
The advantages on these functions are such that it is efficient for SQL to perform operations on entire sets inside the database engine.
It also handles large data, as SQL is designed for big tables and can process millions of rows reliably.