Part4. SQL Function
Introduction
SQL Aggregate Functions
SQL Scalar Functions
SQL Function :
SQL has many built-in-functions for performing processing on string or numeric data.
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Some useful aggregate functions:
1. SQL AVG() : The SQL AVG aggregate function selects the average value for certain table column.
Syntax:
SELECT AVG(colm-name) FROM tble-name
For example:
SELECT AVG(Salary) FROM TEACHER;
2. SQL SUM() : The SQL SUM aggregate function allows selecting the total for a numeric column
Syntax:
SELECT SUM(colm-name) FROM tble-name;
For example;
SELECT SUM(Salary) FROM TEACHER;
3. SQL COUNT() : The SQL COUNT aggregate function is used to count the number of rows in a database table.
Syntax:
SELECT COUNT(colm-name) FROM tble-name;
For Example:
SELECT COUNT(TEACHER_ID) FROM TEACHER;
4. SQL FIRST() : Returns the first value
Syntax:
SELECT FIRST(colm-name) FROM tble-name;
Or
SELECT TOP 1 colm-name FROM tble-name
ORDER BY colm-name ASC;
For example:
SELECT TOP 1 Teacher_Name FROM TEACHER
ORDER BY Teacher_Name ASC;
5. SQL LAST() : Returns the last value
Syntax:
SELECT LAST(colm-name) FROM tble-name;
Or
SELECT TOP 1 colm-name FROM tble_name
ORDER BY colm-name DESC;
For example:
SELECT TOP 1 Teacher_Name FROM TEACHER
ORDER BY Teacher_Name DESC;
6. SQL MAX() : The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
Syntax:
SELECT MAX(column-name) FROM table-name;
For example:
SELECT MAX(Salary) FROM TEACHER;
7. SQL MIN() : The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
Syntax:
SELECT MIN(colm-name) FROM tble-name;
For example:
SELECT MIN(Salary) FROM TEACHER;
SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
Some useful scalar functions:
1. SQL UCASE() : Converts a field to upper case
Syntax;
SELECT UCASE(colmn-name) FROM tble-name;
Or
SELECT UPPER (colmn-name) FROM tble-name;
For example:
SELECT UPPER(Teacher_Name) FROM TEACHER;
2. SQL LCASE() : Converts a field to lower case
Syntax:
SELECT LCASE(colm-name) FROM tble-name;
Or
SELECT LOWER(colm-name) FROM tble-name;
For example:
SELECT LOWER(Teacher_Name) FROM TEACHER;
3. SQL ROUND() : Rounds a numeric field to the number of decimals specified
Syntax:
SELECT ROUND(colm-name, decimals) FROM tble-name;
For example:
SELECT ROUND(Salary,0) FROM TEACHER;
4. SQL NOW() : Returns the current system date and time
Syntax:
SELECT NOW() FROM tble-name;
Or
SELECT getdate() FROM tble-name;
For example:
SELECT Salary, GETDATE() PerDate FROM TEACHER;
5. SQL FORMAT() : Formats how a field is to be displayed
Syntax:
SELECT FORMAT(colm-name, format) FROM tble-name;
For example:
6. SQL MID() : Extract characters from a text field
Syntax:
SELECT MID(colm-name, start[,length]) AS some-name FROM tble-name;
Or
SELECT SUBSTRING(colm_name, start, length) AS some_name FROM tble_name;
For example:
SELECT SUBSTRING(Teacher_Name,1,4) Name FROM TEACHER;
7. SQL LEN() : Returns the length of a text field
Syntax:
SELECT LEN(colm-name) FROM tble-name;
For example:
SELECT LEN(Salary) FROM TEACHER;
Very affordable projects!! And that to submit before deadlines. Thanks for helping me in my database project and raising my grades. I have been able to secure good marks in my internal assessment only because of you. Read More
Follow Us