SQL | Functions (Aggregate and Scalar Functions)

In SQL, functions perform an action or a computation on values. These functions can be broadly categorized into two main types: Aggregate Functions and Scalar Functions.

1. Aggregate Functions:

Aggregate functions perform a calculation on a set of values and return a single value. They are often used in conjunction with the GROUP BY clause.

Common Aggregate Functions:

COUNT(): Counts the number of rows in a set. If a column name is passed as an argument, it counts the number of non-NULL values in that column.
```
SELECT COUNT(column_name) FROM table_name;
```
SUM(): Adds up the values in a numeric column.
```
SELECT SUM(column_name) FROM table_name;
```
AVG(): Calculates the average value of a numeric column.
```
SELECT AVG(column_name) FROM table_name;
```
MIN(): Returns the smallest value in a column.
```
SELECT MIN(column_name) FROM table_name;
```
MAX(): Returns the largest value in a column.
```
SELECT MAX(column_name) FROM table_name;
```

2. Scalar Functions:

Scalar functions operate on a single value and return a single value. Unlike aggregate functions, they don't work on a set of rows but rather on a single value.

Common Scalar Functions:

UCASE() or UPPER(): Converts a string to upper case.
```
SELECT UPPER(column_name) FROM table_name;
```
LCASE() or LOWER(): Converts a string to lower case.
```
SELECT LOWER(column_name) FROM table_name;
```

LENGTH(): Returns the length of a string.

SELECT LENGTH(column_name) FROM table_name;

SUBSTRING() or SUBSTR(): Extracts a portion of a string.

SELECT SUBSTRING(column_name, start_position, length) FROM table_name;

ROUND(): Rounds a numeric value to the number of decimals specified.
```
SELECT ROUND(column_name, number_of_decimals) FROM table_name;
```
NOW(): Returns the current date and time.
```
SELECT NOW();
```

DATEPART(), DAY(), MONTH(), YEAR(): Extracts parts of a date.

SELECT DATEPART(year, column_name) FROM table_name; -- Syntax might vary based on RDBMS.

It's important to note that the availability and exact syntax of these functions can vary depending on the specific relational database management system (RDBMS) you're using. Always refer to the documentation of the RDBMS you are working with for precise details and additional functions.

UPPER() Function:
- Description: Converts a string to uppercase.
- Example:
```
SELECT UPPER('hello') AS result;
```
LOWER() Function:
- Description: Converts a string to lowercase.
- Example:
```
SELECT LOWER('Hello') AS result;
```
LENGTH() or LEN() Function:
- Description: Returns the length of a string.
- Example:
```
SELECT LENGTH('apple') AS result;
```
CONCAT() or || Operator:
- Description: Concatenates two or more strings.
- Example:
```
SELECT CONCAT('John', ' ', 'Doe') AS full_name;
```
SUBSTRING() Function:
- Description: Extracts a substring from a string.
- Example:
```
SELECT SUBSTRING('abcdef', 2, 3) AS result;
```
TRIM() Function:
- Description: Removes leading and trailing spaces from a string.
- Example:
```
SELECT TRIM('   Hello   ') AS result;
```
REPLACE() Function:
- Description: Replaces occurrences of a substring with another substring.
- Example:
```
SELECT REPLACE('Hello, world!', 'world', 'John') AS result;
```
CHAR_LENGTH() Function:
- Description: Returns the number of characters in a string.
- Example:
```
SELECT CHAR_LENGTH('apple') AS result;
```
LEFT() Function:
- Description: Returns the left part of a string.
- Example:
```
SELECT LEFT('apple', 3) AS result;
```
RIGHT() Function:
- Description: Returns the right part of a string.
- Example:
```
SELECT RIGHT('apple', 3) AS result;
```

Examples of Aggregate Functions in SQL:

SUM() Function:
- Description: Calculates the sum of values in a numeric column.
- Example:
```
SELECT SUM(salary) AS total_salary
FROM employees;
```
AVG() Function:
- Description: Calculates the average of values in a numeric column.
- Example:
```
SELECT AVG(salary) AS avg_salary
FROM employees;
```
COUNT() Function:
- Description: Counts the number of rows or non-null values in a column.
- Example:
```
SELECT COUNT(*) AS total_employees
FROM employees;
```
MIN() Function:
- Description: Finds the minimum value in a column.
- Example:
```
SELECT MIN(salary) AS min_salary
FROM employees;
```
MAX() Function:
- Description: Finds the maximum value in a column.
- Example:
```
SELECT MAX(salary) AS max_salary
FROM employees;
```

SQL Built-in Aggregate Functions:

GROUP_CONCAT() Function:
- Description: Concatenates values from multiple rows into a single string, grouped by a specified column.
- Example:
```
SELECT department_id, GROUP_CONCAT(employee_name) AS employee_list
FROM employees
GROUP BY department_id;
```

How to Use Scalar Functions in SQL Queries:

Using Scalar Functions in SQL Queries:
- Description: Scalar functions can be applied to columns in SELECT statements.
- Example:
```
SELECT UPPER(employee_name) AS upper_name
FROM employees;
```

SQL Aggregate Functions with GROUP BY:

SQL Aggregate Functions with GROUP BY:
- Description: Aggregate functions can be used with GROUP BY to perform calculations on grouped data.
- Example:
```
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
```

Scalar Functions vs. Aggregate Functions in SQL:

Scalar Functions vs. Aggregate Functions in SQL:
- Scalar Functions: Operate on a single value and are applied to each row individually.
- Aggregate Functions: Operate on a group of values and return a single result for the group.
- Example (Scalar):
```
SELECT UPPER(employee_name) AS upper_name
FROM employees;
```
- Example (Aggregate):
```
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
```

Common Examples of SQL Scalar Functions:

Common Examples of SQL Scalar Functions:

Example 1 (CONCATENATION):

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Example 2 (UPPERCASE):

SELECT UPPER(product_name) AS upper_name
FROM products;

Overview of Aggregate and Scalar Functions in SQL:

Overview of Aggregate and Scalar Functions in SQL:
- Aggregate Functions: Perform calculations on a set of values and return a single result.
- Scalar Functions: Operate on individual values, transforming or manipulating them.
- Example (Aggregate):
```
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
```
- Example (Scalar):
```
SELECT LEFT(employee_name, 3) AS initials
FROM employees;
```