The SQL Functions with Details

5 years ago
Zaid Bin Khalid
2,651 Views
4

In this session, you will learn SQL functions details with the help of Table.

SQL Function.

In SQL, it has many built-in functions for doing the operation on data SQL. They categorize them into two main categories and different seven functions sub-category. In Aggregate functions, they operate on records and produce a summary, with GROUP BY, whereas non-aggregate functions can work on each file without depending upon anyone.

SQL Category.

There are two main SQL Function categories, as mention below:

Aggregate Function.
Scalar Function.

SQL Aggregate Functions.

SQL aggregate function performs a calculation on a set of rows of value and returns results based on groups of rows value. This function produces a single amount for the entire Table or group in the SQL database.

Useful Aggregate functions.

Some useful aggregate functions summarized as mention below:

SQL AVG function
SQL COUNT function
SQL FIRST function
SQL LAST function
SQL MAX function
SQL MIN function
SQL SUM function

Function	Description
`AVG()`	Returns the average of values
`SUM()`	Returns the sum of values
`COUNT()`	Returns the number of rows in a result set
`MAX()`	Returns the maximum value
`MIN()`	Returns the minimum value

SQL Scalar Functions

The SQL Scalar functions depend on user input. These Functions also returns a single value in the SQL database table.

Useful Scalar functions

Some useful Scalar functions are summarizes as mention below:

SQL UCASE Function
SQL LCASE Function
SQL MID Function
SQL LEN Function
SQL ROUND Function
SQL NOW Function
SQL FORMAT Function

SQL String Functions

This SQL Function Perform operation on attach input value and return a string or numerical value. The mention below Table summarizes some useful string function as shown:

Function	Description
`CONCAT()`	Returns a string by concatenating two or more string values.
`CONCAT_WS()`	Returns a string by concatenating two or more string values with a separator.
`FORMAT()`	Returns a value formatted with the specified format.
`LOWER()`	Converts a string to lowercase.
`UPPER()`	Converts a string to uppercase.
`TRIM()`	Remove leading and trailing spaces from a string.
`REVERSE()`	Returns the reverse order of a string value.
`SUBSTRING()`	Returns a substring from string.

SQL Date Functions

This SQL function has used to operate on date values in the SQL database server.

MySQL Date Functions

The following mention table shows some most useful built-in date functions in MYSQL.

Function	Description
`NOW()`	Returns the current date and time.
`CURDATE()`	Returns the current date.
`CURTIME()`	Returns the current time
`DATE()`	Extract the date part of a date or datetime expression.
`DAY()`	Returns the day of the month (0-31).
`DAYNAME()`	Returns the name of the weekday.
`MONTH()`	Returns the month from the date passed (1-12).
`MONTHNAME()`	Returns the name of the month.
`YEAR()`	Returns the year.
`DATE_FORMAT()`	Displays date and time value in other formats.
`EXTRACT()`	Extract part of a date.
`DATE_ADD()`	Adds a specified time value (or interval) to a date value.
`DATE_SUB()`	Subtracts a specified time value (or interval) from a date value.
`DATEDIFF()`	Returns the number of days between two dates

SQL Server Date Functions

The following mention table shows some most useful built-in date functions in SQL SERVER.

Function	Description
`GETDATE()`	Returns the current date and time.
`DATEPART()`	Returns the specified datepart of the specified date e.g. `DATEPART(year,'2016-10-25')` return 2016.
`DAY()`	Returns the day of the month (0-31).
`MONTH()`	Returns the month from the specified date (0-12).
`YEAR()`	Returns the year from the specified date.
`DATEADD()`	Adds or subtracts a specified time interval from a date.
`DATEDIFF()`	Returns the date or time between two specified dates.
`CONVERT()`	Displays date and time value in other formats.