SQL Functions

SQL functions are built-in operations that allow you to manipulate, calculate, and summarize data. Functions can be categorized intoAggregate Functions (working on multiple rows) andScalar Functions (working on single values).

📌 Aggregate Functions

These functions operate on a set of values and return a single summary value:

• COUNT(column) – Counts the number of rows.
• SUM(column) – Calculates the total sum of a numeric column.
• AVG(column) – Calculates the average value of a column.
• MIN(column) – Returns the minimum value.
• MAX(column) – Returns the maximum value.

📌 Scalar Functions

These functions operate on single values and return a single value:

• UPPER(string) – Converts text to uppercase.
• LOWER(string) – Converts text to lowercase.
• LENGTH(string) – Returns the length of a string.
• ROUND(number, decimals) – Rounds a numeric value.
• NOW() – Returns the current date and time.

📊 Example Table: users

id | name    | age | city
---+---------+-----+---------
1  | Alice   | 22  | New York
2  | Bob     | 30  | London
3  | Charlie | 18  | Sydney
4  | Diana   | 27  | Toronto
5  | Ethan   | 35  | Paris

🔍 Example 1: Aggregate Functions

Get total users and average age:

SELECT COUNT(*) AS total_users, AVG(age) AS average_age
FROM users;

🔍 Example 2: Scalar Functions

Format and manipulate data:

SELECT UPPER(name) AS name_upper, LENGTH(name) AS name_length 
FROM users;

🔍 Example 3: Date and Time Functions

Get current timestamp and calculate date differences:

SELECT NOW() AS current_datetime;

⚡ Best Practices

• Use aggregate functions with GROUP BY to summarize data per group.
• Use scalar functions for formatting and transforming individual column values.
• Be mindful of performance when using functions on large datasets, especially in WHERE clauses.
• Combine functions to create more powerful and flexible queries.

📝 Summary

SQL functions are essential tools for data manipulation, summarization, and formatting. Aggregate functions allow calculations over multiple rows, while scalar functions operate on individual values. Mastering SQL functions is key to writing efficient, readable, and powerful queries.

🚀 Next Steps

Next, we will explore SQL Stored Procedures, which allow grouping SQL statements into reusable programmatic routines.