SQL Functions
String, Numeric, Date, Aggregate

Introduction

Functions in SQL act like tools — each one designed for a specific job. Some format text, others calculate averages or extract dates. Together, they make your queries smarter and your reports richer. In this tutorial, we’ll explore string, numeric, date, and aggregate functions using examples from a school database.

Sample Table – students

CREATE TABLE students (
  roll_no INT,
  name VARCHAR(50),
  dob DATE,
  marks INT,
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '2008-06-12', 85, 'Delhi'),
(2, 'Diya Iyer', '2009-11-23', 92, 'Chennai'),
(3, 'Sneha Patil', '2007-05-17', 78, 'Pune'),
(4, 'Mehul Agarwal', '2008-01-03', 90, 'Delhi');

String Functions

1. CONCAT

Combine name and city:

SELECT CONCAT(name, ' - ', city) AS student_info
FROM students;

student_info
-------------------------
Aarav Sharma - Delhi
Diya Iyer - Chennai
...

2. UPPER / LOWER

SELECT UPPER(name) AS upper_name, LOWER(city) AS lower_city
FROM students;

3. LENGTH

SELECT name, LENGTH(name) AS name_length
FROM students;

Numeric Functions

1. ROUND

SELECT name, ROUND(marks / 3.0, 1) AS average_per_subject
FROM students;

2. CEIL / FLOOR

SELECT name, CEIL(marks/3.0) AS ceiling_value, FLOOR(marks/3.0) AS floor_value
FROM students;

3. MOD

Find remainder when marks are divided by 7:

SELECT name, MOD(marks, 7) AS remainder
FROM students;

Date Functions

1. CURRENT_DATE / NOW

SELECT name, CURRENT_DATE AS today, NOW() AS current_datetime
FROM students;

2. YEAR / MONTH / DAY

SELECT name, YEAR(dob) AS birth_year, MONTH(dob) AS birth_month
FROM students;

3. DATEDIFF

Find how many days since birth:

SELECT name, DATEDIFF(CURRENT_DATE, dob) AS age_in_days
FROM students;

Aggregate Functions

These work on a group of rows and return one result.

1. COUNT

SELECT COUNT(*) AS total_students
FROM students;

2. SUM / AVG

SELECT SUM(marks) AS total_marks, AVG(marks) AS avg_marks
FROM students;

3. MIN / MAX

SELECT MIN(marks) AS lowest, MAX(marks) AS highest
FROM students;

4. GROUP BY + Aggregate

SELECT city, AVG(marks) AS avg_city_marks
FROM students
GROUP BY city;

city      | avg_city_marks
----------+----------------
Delhi     | 87.5
Chennai   | 92.0
Pune      | 78.0

Best Practices

• Use string functions for formatting outputs in reports.
• Apply numeric functions in calculations, fees, and grading logic.
• Use date functions in age calculations, term durations, or attendance summaries.
• Aggregate functions are essential in dashboards and analytics.

Real-World Use Case

In a school dashboard, you might use:

• CONCAT to display "Student Name – Class"
• AVG to show average marks per subject or class
• DATEDIFF to show how long since admission or birth
• COUNT to calculate how many students live in each city

Summary

SQL functions give you power and flexibility to analyze, format, and calculate data within queries. Whether you’re handling names, marks, dates, or groups — string, numeric, date, and aggregate functions help you turn raw data into actionable insight.

What’s Next?

Next, we’ll explore SQL CASE expressions to apply conditional logic directly within your SELECT statements.