SQL Function Homework Help

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:

• SQL AVG() : The SQL AVG aggregate function selects the average value for certain table column.
• SQL MIN() : The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
• SQL SUM() : The SQL SUM aggregate function allows selecting the total for a numeric column.
• SQL COUNT() : The SQL COUNT aggregate function is used to count the number of rows in a database table.
• SQL FIRST() : Returns the first value
• SQL LAST() : Returns the last value
• SQL MAX() : The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.

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:

• SQL UCASE() : Synonym for UPPER()
• SQL LCASE() : LCASE() is a synonym for LOWER().
• SQL ROUND() : Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal points
• SQL NOW() : Returns the current system date and time
• SQL FORMAT() : Returns a numeric expression rounded to a number of decimal places.
• SQL MID() : MID(str, pos, len) is a synonym for SUBSTRING(str, pos, len).
• SQL LEN() : Returns the length of a text field

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;