SQL Functions: What They Are and How to Use Them (Basic 1 - 8)

SQL functions are predefined programs that perform specific tasks on data. They can be used to perform a wide variety of tasks, such as:

• Mathematical operations (e.g., SUM(), AVG(), COUNT())
• String manipulation (e.g., UPPER(), LOWER(), CONCAT())
• Date and time manipulation (e.g., ADD_MONTHS(), TRUNC(), TO_CHAR())
• Conversion functions (e.g., TO_NUMBER(), TO_DATE())

SQL functions can be used in a variety of SQL statements, including SELECT, INSERT, UPDATE, and DELETE statements.

Types of SQL functions

There are two main types of SQL functions:

• Scalar functions: Scalar functions return a single value.
• Aggregate functions: Aggregate functions operate on a group of rows and return a single value that summarizes the group.

Examples of scalar functions:

• UPPER(): Converts a string to uppercase.
• LOWER(): Converts a string to lowercase.
• CONCAT(): Concatenates two or more strings together.
• ROUND(): Rounds a number to a specified number of decimal places.
• TO_CHAR(): Converts a date or number to a string.

Examples of aggregate functions:

• SUM(): Returns the sum of all values in a column.
• AVG(): Returns the average of all values in a column.
• COUNT(): Returns the number of rows in a table or group of rows.
• MIN(): Returns the smallest value in a column.
• MAX(): Returns the largest value in a column.

How to use SQL functions

To use a SQL function, simply specify the function name followed by the parentheses enclosing the arguments to the function. For example, to calculate the sum of all values in the salary column, you would use the following SQL function:

SQL

SELECT SUM(salary) FROM employees;

To convert a date to a string, you would use the following SQL function:

SQL

SELECT TO_CHAR(hire_date, 'dd-mm-yyyy') FROM employees;

SQL functions can also be used in combination with each other. For example, the following SQL function calculates the average salary for each department:

SQL

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Conclusion

SQL functions are a powerful tool for manipulating and analyzing data. By understanding the different types of SQL functions and how to use them, you can write more efficient and effective SQL queries.