SQL functions are built into the SQL language and they are used to manipulate data in various ways. In this unit, we will explore two categories of SQL functions: numeric and string functions.
Numeric functions in SQL are used to perform operations on numeric data. They can be used in the SELECT, WHERE, and ORDER BY clauses of the SQL statement. Here are two commonly used numeric functions:
The AVG() function returns the average value of a numeric column. It adds up all the values in the column, and then divides by the number of values.
Here's an example of how to use the AVG() function:
SELECT AVG(salary) AS AverageSalary FROM employees;
In this example, the AVG() function calculates the average salary of all employees.
The SUM() function returns the total sum of a numeric column. It adds up all the values in the column.
Here's an example of how to use the SUM() function:
SELECT SUM(salary) AS TotalSalary FROM employees;
In this example, the SUM() function calculates the total salary of all employees.
String functions in SQL are used to manipulate string data. They can be used in the SELECT, WHERE, and ORDER BY clauses of the SQL statement. Here are two commonly used string functions:
The UPPER() function converts all letters in the specified string to uppercase.
Here's an example of how to use the UPPER() function:
SELECT UPPER(first_name) AS UpperCaseFirstName FROM employees;
In this example, the UPPER() function converts the first name of all employees to uppercase.
The CONCAT() function concatenates two or more strings into one string.
Here's an example of how to use the CONCAT() function:
SELECT CONCAT(first_name, ' ', last_name) AS FullName FROM employees;
In this example, the CONCAT() function concatenates the first name and the last name of all employees to create a full name.
In conclusion, SQL functions are powerful tools that can help you manipulate and analyze data in your SQL statements. By understanding how to use these functions, you can write more efficient and effective SQL queries.