Download SQL Functions: A Comprehensive Guide to Aggregate and Scalar Functions in SQL and more Assignments Database Programming in PDF only on Docsity!
What are SQL Functions?
SQL provides many built-in functions to perform operations on data. These functions are useful while performing mathematical calculations, string concatenations, sub-strings etc. SQL functions are divided into two categories,
- Aggregate Functions
- Scalar Functions
Aggregate Functions
These functions return a single value after performing calculations on a group of values. Following are some of the frequently used Aggregrate functions. AVG() Function Average returns average value after calculating it from values in a numeric column. Its general syntax is, SELECT AVG(column_name) FROM table_name Using AVG() function
Consider the following Emp table eid name age salary 401 Anu 22 9000 402 Shane 29 8000 403 Rohan 34 6000 404 Scott 44 10000 405 Tiger 35 8000 SQL query to find average salary will be, SELECT avg(salary) from Emp; Result of the above query will be, avg(salary) 8200
405 Tiger 35 8000 SQL query to count employees, satisfying specified condition is, SELECT COUNT(name) FROM Emp WHERE salary = 8000 ; Result of the above query will be, count(name) 2 Example of COUNT(distinct) Consider the following Emp table eid name age salary 401 Anu 22 9000 402 Shane 29 8000
403 Rohan 34 6000 404 Scott 44 10000 405 Tiger 35 8000 SQL query is, SELECT COUNT(DISTINCT salary) FROM emp; Result of the above query will be, count(distinct salary) 4 FIRST() Function First function returns first value of a selected column Syntax for FIRST function is,
first(salary) 9000 LAST() Function LAST function returns the return last value of the selected column. Syntax of LAST function is, SELECT LAST(column_name) FROM table-name; Using LAST() function Consider the following Emp table eid name age salary 401 Anu 22 9000 402 Shane 29 8000
403 Rohan 34 6000 404 Scott 44 10000 405 Tiger 35 8000 SQL query will be, SELECT LAST(salary) FROM emp; Result of the above query will be, last(salary) 8000 MAX() Function MAX function returns maximum value from selected column of the table. Syntax of MAX function is, SELECT MAX(column_name) from table-name;
MIN() Function MIN function returns minimum value from a selected column of the table. Syntax for MIN function is, SELECT MIN(column_name) from table-name; Using MIN() function Consider the following Emp table, eid name age salary 401 Anu 22 9000 402 Shane 29 8000 403 Rohan 34 6000
404 Scott 44 10000 405 Tiger 35 8000 SQL query to find minimum salary is, SELECT MIN(salary) FROM emp; MIN(salary) 6000 Result will be, SUM() Function SUM function returns total sum of a selected columns numeric values. Syntax for SUM is, SELECT SUM(column_name) from table-name; Using SUM() function
Scalar Functions
Scalar functions return a single value from an input value. Following are some frequently used Scalar Functions in SQL. UCASE() Function UCASE function is used to convert value of string column to Uppercase characters. Syntax of UCASE, SELECT UCASE(column_name) from table-name; Using UCASE() function Consider the following Emp table eid name age salary 401 anu 22 9000 402 shane 29 8000
403 rohan 34 6000 404 scott 44 10000 405 Tiger 35 8000 SQL query for using UCASE is, SELECT UCASE(name) FROM emp; Result is, UCASE(name) ANU SHANE ROHAN SCOTT TIGER
405 Tiger 35 8000 SQL query for converting string value to Lower case is, SELECT LCASE(name) FROM emp; Result will be, LCASE(name) anu shane rohan scott tiger MID() Function
MID function is used to extract substrings from column values of string type in a table. Syntax for MID function is, SELECT MID(column_name, start, length) from table- name; Using MID() function Consider the following Emp table eid name age salary 401 anu 22 9000 402 shane 29 8000 403 rohan 34 6000 404 scott 44 10000 405 Tiger 35 8000 SQL query will be,
Syntax of Round function is, SELECT ROUND(column_name, decimals) from table-name; Using ROUND() function Consider the following Emp table eid name age salary 401 anu 22 9000. 402 shane 29 8000. 403 rohan 34 6000. 404 scott 44 10000 405 Tiger 35 8000. SQL query is, SELECT ROUND(salary) from emp;
Result will be, ROUND(salary) 9001 8001 6000 10000 8000
