General Functions in SQL
General Functions in SQL
General functions in SQL are built-in functions that perform common operations on data, such as manipulating strings, numbers, dates, or aggregating values. These functions help you transform, calculate, or retrieve information efficiently within queries.
Common Categories of General Functions:
1. Aggregate Functions
Operate on multiple rows and return a single value.
| Function | Description | Example |
|---|---|---|
COUNT() | Counts rows or non-null values | SELECT COUNT(*) FROM users; |
SUM() | Sum of numeric values | SELECT SUM(sales) FROM orders; |
AVG() | Average of numeric values | SELECT AVG(price) FROM products; |
MIN() | Minimum value | SELECT MIN(age) FROM employees; |
MAX() | Maximum value | SELECT MAX(score) FROM tests; |
2. String Functions
Manipulate text strings.
| Function | Description | Example |
|---|---|---|
CONCAT() | Concatenate strings | SELECT CONCAT(first_name, ' ', last_name) FROM users; |
LENGTH() | Returns length of string | SELECT LENGTH(name) FROM products; |
UPPER() | Convert to uppercase | SELECT UPPER(city) FROM customers; |
LOWER() | Convert to lowercase | SELECT LOWER(email) FROM users; |
SUBSTRING() | Extract substring | SELECT SUBSTRING(name, 1, 3) FROM products; |
TRIM() | Remove spaces from both ends | SELECT TRIM(' hello '); |
3. Numeric Functions
Perform operations on numbers.
| Function | Description | Example |
|---|---|---|
ROUND() | Round number to specified decimal places | SELECT ROUND(price, 2) FROM items; |
FLOOR() | Round down to nearest integer | SELECT FLOOR(4.7); |
CEIL() or CEILING() | Round up to nearest integer | SELECT CEIL(4.1); |
ABS() | Absolute value | SELECT ABS(-10); |
POWER() | Raise number to power | SELECT POWER(2, 3); |
MOD() | Modulus (remainder) | SELECT MOD(10, 3); |
4. Date and Time Functions
Manipulate dates and times.
| Function | Description | Example |
|---|---|---|
NOW() | Current date and time | SELECT NOW(); |
CURDATE() | Current date | SELECT CURDATE(); |
DATEADD() / DATE_ADD() | Add interval to date | SELECT DATE_ADD(NOW(), INTERVAL 5 DAY); |
DATEDIFF() | Difference between two dates | SELECT DATEDIFF('2025-06-10', '2025-06-01'); |
YEAR(), MONTH(), DAY() | Extract part of a date | SELECT YEAR(NOW()); |
EXTRACT() | Extract part of date/time | SELECT EXTRACT(MONTH FROM NOW()); |
5. Conditional Functions
| Function | Description | Example |
|---|---|---|
COALESCE() | Returns first non-NULL value | SELECT COALESCE(NULL, NULL, 5, 10); returns 5 |
IF() | Conditional logic (MySQL) | SELECT IF(score > 50, 'Pass', 'Fail'); |
CASE | SQL standard conditional expression | sql SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM results; |
Summary Table
| Category | Examples |
|---|---|
| Aggregate | COUNT(), SUM(), AVG(), MIN(), MAX() |
| String | CONCAT(), LENGTH(), UPPER(), LOWER(), SUBSTRING(), TRIM() |
| Numeric | ROUND(), FLOOR(), CEIL(), ABS(), POWER(), MOD() |
| Date & Time | NOW(), CURDATE(), DATE_ADD(), DATEDIFF(), YEAR(), EXTRACT() |
| Conditional | COALESCE(), IF(), CASE |
If you want, I can provide examples or detailed explanations for any specific category or function!
