MODULE 4 – SQL FUNCTIONS, AGGREGATES & GROUPING (WEEK 4)
Topics Covered:
1. String Functions
2. Numeric Functions
3. Date and Time Functions
4. Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)
5. GROUP BY and HAVING Clauses
1) String Functions
String functions are used to manipulate and process text values stored in SQL columns.
Common String Functions
Function Purpose Example
UPPER() Converts text to uppercase SELECT UPPER(name) FROM
student;
LOWER() Converts text to lowercase SELECT LOWER(name) FROM
student;
LENGTH() Returns length of string SELECT LENGTH(name) FROM
student;
SUBSTRING() Extracts part of a string SELECT SUBSTRING(name,1,3)
FROM student;
CONCAT() Joins two or more strings SELECT CONCAT(first_name,'
',last_name) FROM student;
TRIM() Removes extra spaces SELECT TRIM(name) FROM
student;
Examples
SELECT UPPER(name) FROM student;
SELECT LOWER(name) FROM student;
SELECT LENGTH(name) FROM student;
SELECT SUBSTRING(name,1,3) FROM student;
SELECT CONCAT(name, ' - ', course) FROM student;
2) Numeric Functions
Numeric functions are used to perform calculations or transformations on numeric data.
Common Numeric Functions
Function Purpose Example
ROUND() Rounds a number SELECT ROUND(85.678,2);
CEIL()/CEILING() Rounds to next highest integer SELECT CEIL(85.2);
FLOOR() Rounds to previous integer SELECT FLOOR(85.9);
ABS() Returns absolute value SELECT ABS(-25);
MOD() Returns remainder SELECT MOD(10,3);
Examples
SELECT ROUND(85.678,2);
SELECT CEIL(45.2);
SELECT FLOOR(45.9);
SELECT ABS(-30);
SELECT MOD(17,5);
3) Date and Time Functions
Date and time functions are used to work with dates, current system time, and date calculations.
Common Date/Time Functions
Function Purpose Example
CURDATE() Returns current date SELECT CURDATE();
NOW() Returns current date and time SELECT NOW();
YEAR(date) Extracts year from a date SELECT YEAR(join_date) FROM
employee;
MONTH(date) Extracts month SELECT MONTH(join_date) FROM
employee;
DATEDIFF(d1,d2) Difference between two dates SELECT DATEDIFF('2026-07-
10','2026-07-01');
Examples
SELECT CURDATE();
SELECT NOW();
SELECT YEAR(join_date) FROM employee;
SELECT MONTH(join_date) FROM employee;
SELECT DATEDIFF('2026-07-10','2026-07-01');
4) Aggregate Functions
Aggregate functions perform calculations on a set of rows and return a single value.
Important Aggregate Functions
Function Meaning Example
COUNT() Counts number of rows SELECT COUNT(*) FROM student;
SUM() Returns total sum SELECT SUM(marks) FROM
student;
AVG() Returns average value SELECT AVG(marks) FROM student;
MIN() Returns smallest value SELECT MIN(marks) FROM student;
MAX() Returns largest value SELECT MAX(marks) FROM student;
Examples
SELECT COUNT(*) FROM student;
SELECT SUM(marks) FROM student;
SELECT AVG(marks) FROM student;
SELECT MIN(marks) FROM student;
SELECT MAX(marks) FROM student;
Aggregate Flow Diagram
Student Table Contains Many Rows
Choose Numeric Column (Example: marks)
Apply Aggregate Function
SQL Processes All Matching Rows
Single Summary Value is Produced
Result Displayed to User
5) GROUP BY and HAVING Clauses
GROUP BY is used to group rows having the same values in a column. HAVING is used to filter grouped results,
especially when aggregate functions are involved.
Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
Example
SELECT course, COUNT(*) AS total_students
FROM student
GROUP BY course;
SELECT course, AVG(marks) AS avg_marks
FROM student
GROUP BY course
HAVING AVG(marks) > 70;
GROUP BY vs HAVING
GROUP BY HAVING
Used to create groups of rows Used to filter grouped results
Works before final result is shown Works after grouping is done
Often used with aggregate functions Usually used with aggregate conditions
Grouping Diagram
Student Table → Group by Course → Calculate COUNT / AVG / SUM → Apply HAVING → Show Final Grouped
Result
6) Full Practice Example
CREATE TABLE student (
student_id INT PRIMARY KEY,
name VARCHAR(50),
marks INT,
course VARCHAR(30),
join_date DATE
);
INSERT INTO student VALUES (101, 'Asha', 85, 'BSc', '2026-07-01');
INSERT INTO student VALUES (102, 'Ravi', 72, 'BBA', '2026-07-02');
INSERT INTO student VALUES (103, 'Suresh', 48, 'BCom', '2026-07-03');
INSERT INTO student VALUES (104, 'Akash', 91, 'BSc', '2026-07-04');
INSERT INTO student VALUES (105, 'Divya', 67, 'BBA', '2026-07-05');
SELECT UPPER(name) FROM student;
SELECT LENGTH(name) FROM student;
SELECT ROUND(85.678,2);
SELECT CURDATE();
SELECT COUNT(*) FROM student;
SELECT AVG(marks) FROM student;
SELECT course, COUNT(*) AS total_students
FROM student
GROUP BY course;
SELECT course, AVG(marks) AS avg_marks
FROM student
GROUP BY course
HAVING AVG(marks) > 70;
7) Table Snapshot Example
student_id name marks course join_date
101 Asha 85 BSc 2026-07-01
102 Ravi 72 BBA 2026-07-02
103 Suresh 48 BCom 2026-07-03
104 Akash 91 BSc 2026-07-04
105 Divya 67 BBA 2026-07-05
8) Important Viva / Interview Questions
What are string functions in SQL?
What is the use of CONCAT() and SUBSTRING()?
What are numeric functions?
What is the difference between CEIL() and FLOOR()?
What is the use of CURDATE() and NOW()?
What are aggregate functions in SQL?
Difference between WHERE and HAVING?
What is GROUP BY used for?
9) Student Assignment
Create a student table with student_id, name, marks, course, join_date.
Insert 5 records into the student table.
Display student names in uppercase.
Find the length of each student name.
Find the total, average, minimum, and maximum marks.
Display current date and time using SQL functions.
Group students by course and count how many students are in each course.
Display only those courses whose average marks are greater than 70.