SQL Functions

SQL Functions

SQL functions are built-in operations that can be applied to data in a database to perform calculations, manipulate strings, or perform other data transformations. These functions are essential for data analysis and processing.

Types of SQL Functions

SQL functions can be categorized into several types:

  • Aggregate Functions: Perform a calculation on a set of values and return a single value. Examples include COUNT, SUM, AVG, MIN, and MAX.
  • Scalar Functions: Perform operations on individual values and return a single value. Examples include UCASE, LCASE, LENGTH, ROUND, and NOW.
  • Date Functions: Perform operations on date and time values. Examples include GETDATE, DATEADD, DATEDIFF, FORMAT, and YEAR.
  • String Functions: Perform operations on string values. Examples include CONCAT, SUBSTRING, REPLACE, LTRIM, and RTRIM.
  • Numeric Functions: Perform operations on numeric values. Examples include ABS, CEILING, FLOOR, POWER, and RAND.

Example SQL Functions

Let's look at some examples of SQL functions using the employees table:

1. COUNT Function

SELECT COUNT(*) AS num_employees
FROM employees;

This query uses the COUNT function to count the number of rows in the employees table.

2. SUM Function

SELECT SUM(salary) AS total_salary
FROM employees;

This query uses the SUM function to calculate the total salary of all employees.

3. AVG Function

SELECT AVG(salary) AS avg_salary
FROM employees;

This query uses the AVG function to calculate the average salary of all employees.

4. DATEADD Function

SELECT DATEADD(year, 1, hire_date) AS next_year_hire_date
FROM employees;

This query uses the DATEADD function to add one year to the hire_date of each employee.

5. CONCAT Function

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

This query uses the CONCAT function to concatenate the first_name and last_name of each employee into a single string.

Conclusion

SQL functions are powerful tools for performing various operations on data within a database. Understanding how to use different types of SQL functions and their syntax is essential for effective data analysis and processing in a relational database.