SQL Functions and Expressions: Aggregate (COUNT, SUM, AVG), Scalar (UPPER, LENGTH, NOW), Operators & Comprehensive Examples

Table of Contents

1. Functions and Expressions in SQL

Q: What are functions and expressions in SQL?

Q: Why are functions and expressions important?

2. Aggregate Functions: COUNT, SUM, AVG

Q: What are aggregate functions in SQL?

Aggregate functions process a set of rows and return a single summarized value. They are commonly used with GROUP BY or on entire tables.

Q: How do you use COUNT, SUM, and AVG?

Syntax:

SELECT COUNT(column_name) FROM table_name;
SELECT SUM(column_name) FROM table_name;
SELECT AVG(column_name) FROM table_name;

Q: Can you give an example of aggregate functions?

-- Create sample table
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Department VARCHAR(50), Salary DECIMAL(10, 2), Email VARCHAR(100)
); 
-- Insert sample data
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, Email)
VALUES (1, 'John', 'Doe', 'IT', 60000.00, '[email protected]'), (2, 'Jane', 'sahil', 'HR', 55000.00, NULL), (3, 'kristal', 'Johnson', 'IT', 65000.00, '[email protected]'), (4, 'Ram', 'Williams', 'HR', 55000.00, NULL), (5, 'hari', 'Brown', 'Marketing', NULL, '[email protected]'); 
-- Aggregate functions
-- COUNT: Total employees and employees with emails
SELECT COUNT(*) AS TotalEmployees, COUNT(Email) AS EmployeesWithEmail
FROM Employees; 
-- SUM: Total salary for IT department
SELECT SUM(Salary) AS TotalITSalary
FROM Employees
WHERE Department = 'IT'; 
-- AVG: Average salary across all employees
SELECT AVG(Salary) AS AverageSalary
FROM Employees;

Output (COUNT):

TotalEmployees | EmployeesWithEmail
--------------|-------------------
5 | 3

Output (SUM):

TotalITSalary
-------------
125000.00

Output (AVG):

AverageSalary
-------------
58750.00

Note:AVG ignores NULLs (e.g., hari’s NULL salary), so it’s calculated as (60000 + 55000 + 65000 + 55000) / 4.

3. Scalar Functions: UPPER, LENGTH, NOW

Q: What are scalar functions in SQL?

Scalar functions take a single input value (per row) and return a single output value. They are used for data transformation, such as formatting strings, calculating lengths, or retrieving system data.

Q: How do you use UPPER, LENGTH, and NOW?

Syntax:

SELECT UPPER(column_name) FROM table_name;
SELECT LENGTH(column_name) FROM table_name;
SELECT NOW();

Variations:

Q: Can you give an example of scalar functions?

-- Scalar functions
-- UPPER: Convert names to uppercase
SELECT UPPER(FirstName) AS UpperFirstName, LastName
FROM Employees; 
-- LENGTH: Get length of email addresses
SELECT FirstName, Email, LENGTH(Email) AS EmailLength
FROM Employees
WHERE Email IS NOT NULL; 
-- NOW: Get current timestamp
SELECT NOW() AS CurrentTime;

Output (UPPER):

UpperFirstName | LastName
---------------|----------
JOHN | Doe
JANE | sahil
kristal | Johnson
Ram | Williams
hari | Brown

Output (LENGTH):

FirstName | Email | EmailLength
----------|---------------------------|------------
John | [email protected] | 20
kristal | [email protected] | 24
hari | [email protected] | 22

Output (NOW):

CurrentTime
---------------------
2025-09-07 04:47:00

Note:NOW() output depends on the system’s timezone and RDBMS (e.g., MySQL/PostgreSQL use NOW(), SQL Server uses GETDATE()).

4. Using Arithmetic and String Operators

Q: What are arithmetic and string operators in SQL?

Q: How do you use arithmetic and string operators in SQL?

Q: Can you give an example of arithmetic and string operators?

-- Arithmetic operators: Calculate 10% salary increase
SELECT FirstName, LastName, Salary, Salary * 1.1 AS IncreasedSalary
FROM Employees
WHERE Salary IS NOT NULL; 
-- String operators: Concatenate names (using CONCAT for portability)
SELECT CONCAT(FirstName, ' ', LastName) AS FullName, Department
FROM Employees; 
-- Combined with scalar functions
SELECT UPPER(CONCAT(FirstName, ' ', LastName)) AS UpperFullName, Salary + 5000 AS BonusSalary
FROM Employees
WHERE Salary IS NOT NULL;

Output (Arithmetic):

FirstName | LastName | Salary | IncreasedSalary
----------|-----------|----------|----------------
John | Doe | 60000.00 | 66000.00
Jane | sahil | 55000.00 | 60500.00
kristal | Johnson | 65000.00 | 71500.00
Ram | Williams | 55000.00 | 60500.00

Output (String):

FullName | Department
-----------------|------------
John Doe | IT
Jane sahil | HR
kristal Johnson | IT
Ram Williams | HR
hari Brown | Marketing

Output (Combined):

UpperFullName | BonusSalary
-----------------|------------
JOHN DOE | 65000.00
JANE sahil | 60000.00
kristal JOHNSON | 70000.00
Ram WILLIAMS | 60000.00

5. Comprehensive Example Combining All Concepts

Q: Can you provide a comprehensive example using aggregate functions, scalar functions, arithmetic/string operators, and prior filtering concepts?

-- Comprehensive query combining aggregate, scalar, operators, and filtering
SELECT Department, COUNT(*) AS TotalEmployees, COUNT(Email) AS EmployeesWithEmail, SUM(Salary) AS TotalSalary, AVG(Salary) AS AvgSalary, UPPER(CONCAT(FirstName, ' ', LastName)) AS UpperFullName, LENGTH(Email) AS EmailLength, Salary * 1.1 AS IncreasedSalary, NOW() AS QueryTime
FROM Employees
WHERE (Department IN ('IT', 'HR') AND Salary IS NOT NULL) OR FirstName LIKE 'C%'
GROUP BY Department, FirstName, LastName, Email, Salary
ORDER BY AvgSalary DESC
LIMIT 3; -- MySQL/PostgreSQL/SQLite (use TOP 3 for SQL Server)

Description:

6. Common Mistakes and Best Practices

Q: What are common mistakes when using functions and expressions in SQL?

Aggregate Functions:

Scalar Functions:

Arithmetic/String Operators:

Q: What are best practices for using functions and expressions in SQL?

Aggregate Functions:

Scalar Functions:

Arithmetic/String Operators:

General: