- 4 years ago
- Zaid Bin Khalid
- 1981 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. |
- 4 years ago
- Zaid Bin Khalid
- 1981 Views
-
4