SQL provides various functions to perform operations on data stored in a database. Here are some commonly used SQL functions categorized based on their functionality:
- Aggregate Functions:
COUNT(): Counts the number of rows.SUM(): Calculates the sum of values.AVG(): Calculates the average of values.MIN(): Finds the minimum value.MAX(): Finds the maximum value.
- String Functions:
CONCAT(): Concatenates strings.UPPER(): Converts a string to uppercase.LOWER(): Converts a string to lowercase.SUBSTRING(): Extracts a substring from a string.LENGTH(): Returns the length of a string.TRIM(): Removes leading and trailing spaces from a string.REPLACE(): Replaces occurrences of a substring within a string.
- Date and Time Functions:
CURRENT_DATE: Returns the current date.CURRENT_TIME: Returns the current time.CURRENT_TIMESTAMP: Returns the current timestamp.DATE_ADD(): Adds a specified time interval to a date.DATE_SUB(): Subtracts a specified time interval from a date.DATEDIFF(): Calculates the difference between two dates.DATE_FORMAT(): Formats a date as a string.
- Mathematical Functions:
ABS(): Returns the absolute value of a number.ROUND(): Rounds a number to a specified number of decimal places.CEIL(): Rounds a number up to the nearest integer.FLOOR(): Rounds a number down to the nearest integer.POWER(): Raises a number to a specified power.SQRT(): Calculates the square root of a number.
- Conditional Functions:
CASE: Performs conditional logic in a query.COALESCE(): Returns the first non-null value in a list of expressions.
- Conversion Functions:
CAST(): Converts a value from one data type to another.CONVERT(): Converts a value from one data type to another (specific to certain database systems).
- Aggregate String Functions:
GROUP_CONCAT(): Concatenates strings from multiple rows into a single string.
tabular representation of some common SQL functions along with examples:
| Function | Description | Example |
|---|---|---|
COUNT() | Returns the number of rows that match a specified condition or the total number of rows in a table. | SELECT COUNT(*) FROM table_name; |
SUM() | Returns the sum of values in a numeric column. | SELECT SUM(salary) FROM employees; |
AVG() | Returns the average value of a numeric column. | SELECT AVG(age) FROM persons; |
MIN() | Returns the smallest value in a column. | SELECT MIN(price) FROM products; |
MAX() | Returns the largest value in a column. | SELECT MAX(quantity) FROM inventory; |
UPPER() | Converts a string to uppercase. | SELECT UPPER(first_name) FROM employees; |
LOWER() | Converts a string to lowercase. | SELECT LOWER(last_name) FROM employees; |
SUBSTRING() | Extracts a substring from a string. | SELECT SUBSTRING(description, 1, 10) FROM products; |
CONCAT() | Concatenates two or more strings. | SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; |
DATE_FORMAT() | Formats a date value as a string. | SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date FROM orders; |
CASE | Performs conditional logic in a query. | SELECT CASE WHEN age < 18 THEN 'Minor' WHEN age >= 18 AND age < 65 THEN 'Adult' ELSE 'Senior' END AS age_group FROM persons; |
These are just a few examples of commonly used SQL functions. SQL supports a wide range of functions for various purposes, including string manipulation, date and time operations, mathematical calculations, and more. The specific functions available may vary depending on the SQL dialect used (e.g., MySQL, PostgreSQL, SQL Server), so be sure to consult the documentation for your specific database system.
0 Comments