SQL Functions — String, Numeric, Date, Aggregate

The teacher wants both a consistent display name and a count of students, but those tasks operate at different levels.

A SQL function computes a value from inputs or a collection of rows. Scalar functions work on a row’s values; aggregate functions summarize a group.

UPPER(name) transforms a string in each selected row. AVG(marks) summarizes multiple marks into one value per group or whole input. Date, string and numeric function names and conversions differ by engine; the result depends on arguments, NULL handling and type rules.

Related concepts: Group and Summarize and Missing Values and NULL and Choose Effective Indexes.

SQL · portable concepts; preserved examples identify their engine

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');

SQL · portable concepts; preserved examples identify their engine

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;

SQL · portable concepts; preserved examples identify their engine

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;

SQL · portable concepts; preserved examples identify their engine

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;

SQL · portable concepts; preserved examples identify their engine

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

• 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.

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

COUNT(*) includes rows whose marks are NULL, while COUNT(marks) does not. Date formatting and rounding are not universal across dialects. Applying a function to an indexed column in a filter can affect available index access paths.

Count the students and compute the average mark in one summary row.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT COUNT(*) AS students, AVG(marks) AS average_marks FROM practice_students;

The count uses all three rows. The average is (85 + 70 + 85) / 3; NULL marks would affect AVG and COUNT(marks) differently.

A SQL function computes a value from inputs or a collection of rows. Scalar functions work on a row’s values; aggregate functions summarize a group.

The count uses all three rows. The average is (85 + 70 + 85) / 3; NULL marks would affect AVG and COUNT(marks) differently.

Continue your understanding with Group and Summarize and Missing Values and NULL and Choose Effective Indexes.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.