SQL Functions

1. What are SQL Functions?

SQL functions take input values, process them, and return a result. They are commonly used in SELECT statements.

• Return a single value
• Used for calculations and formatting
• Improve query efficiency

2. Types of SQL Functions

• Numeric Functions
• String Functions
• Date & Time Functions

3. Numeric Functions

ABS(-10)     → 10
ROUND(12.6)  → 13
MOD(10,3)    → 1
POWER(2,3)   → 8

Example:

SELECT ABS(-25), ROUND(45.7);

4. String Functions

UPPER('sql')        → SQL
LOWER('DBMS')       → dbms
LENGTH('Hello')     → 5
SUBSTRING('SQL',1,2)→ SQ

Example:

SELECT UPPER(Name), LENGTH(Name)
FROM student;

5. CONCAT Function

The CONCAT function joins two or more strings.

SELECT CONCAT(FirstName, ' ', LastName)
FROM student;

6. Date & Time Functions

CURDATE()     → Current date
NOW()         → Current date & time
YEAR(date)   → Extract year
MONTH(date)  → Extract month
DAY(date)    → Extract day

Example:

SELECT CURDATE(), NOW();

7. Using Functions with WHERE Clause

SELECT * FROM employee
WHERE YEAR(JoinDate) = 2023;

8. Summary of SQL Functions

Function Type     Examples
---------------- --------------------------------
Numeric           ABS, ROUND, MOD, POWER
String            UPPER, LOWER, LENGTH, CONCAT
Date & Time       CURDATE, NOW, YEAR, MONTH

Practice Questions

1. What are SQL functions?
2. Explain numeric functions with examples.
3. What is CONCAT function?
4. Write SQL date functions.
5. How are functions used with WHERE clause?

Practice Task