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.
SQL functions can be categorized into several types:
COUNT
, SUM
, AVG
, MIN
, and MAX
.UCASE
, LCASE
, LENGTH
, ROUND
, and NOW
.GETDATE
, DATEADD
, DATEDIFF
, FORMAT
, and YEAR
.CONCAT
, SUBSTRING
, REPLACE
, LTRIM
, and RTRIM
.ABS
, CEILING
, FLOOR
, POWER
, and RAND
.Let's look at some examples of SQL functions using the employees
table:
SELECT COUNT(*) AS num_employees
FROM employees;
This query uses the COUNT
function to count the number of rows in the employees
table.
SELECT SUM(salary) AS total_salary
FROM employees;
This query uses the SUM
function to calculate the total salary of all employees.
SELECT AVG(salary) AS avg_salary
FROM employees;
This query uses the AVG
function to calculate the average salary of all employees.
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.
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.
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.