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.
