Built-in Functions in MySQL
STEP 1: Create Database
CREATE DATABASE CollegeDB;
USE CollegeDB;
STEP 2: Create Table
CREATE TABLE Students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
course VARCHAR(50),
marks DECIMAL(5,2),
fees DECIMAL(10,2),
admission_date DATE,
city VARCHAR(50)
);
STEP 3: Insert Records
INSERT INTO Students VALUES
(1, 'Aman', 'BCA', 78.56, 45000.50, '2023-07-10', 'Delhi'),
(2, 'Riya', 'BBA', 88.75, 50000.00, '2022-06-15', 'Mumbai'),
(3, 'Karan', 'BCA', 67.40, 47000.75, '2023-01-20', 'Jaipur'),
(4, 'Sneha', 'BCom', 91.20, 52000.00, '2021-08-05', 'Delhi'),
(5, 'Arjun', 'BBA', 72.30, 49000.00, '2022-09-12', 'Pune');
NUMERIC FUNCTIONS
ROUND()
SELECT name, ROUND(marks) AS rounded_marks FROM Students;
👉 Rounds marks to nearest integer.
CEIL()
SELECT name, CEIL(marks) AS ceil_marks FROM Students;
👉 Rounds upward.
FLOOR()
SELECT name, FLOOR(marks) AS floor_marks FROM Students;
👉 Rounds downward.
ABS()
SELECT ABS(-25) AS absolute_value;
👉 Converts negative to positive.
POWER()
SELECT POWER(2,3) AS power_value;
👉 2³ = 8
SQRT()
SELECT SQRT(64) AS square_root;
👉 √64 = 8
MOD()
SELECT MOD(10,3) AS remainder;
👉 Gives remainder.
AVG(), SUM(), MIN(), MAX()
SELECT
AVG(marks) AS average_marks,
SUM(fees) AS total_fees,
MIN(marks) AS minimum_marks,
MAX(marks) AS maximum_marks
FROM Students;
DATE FUNCTIONS
CURDATE()
SELECT CURDATE();
👉 Shows current date.
NOW()
SELECT NOW();
👉 Shows current date and time.
DATEDIFF()
SELECT name, DATEDIFF(CURDATE(), admission_date) AS days_in_college
FROM Students;
👉 Difference between two dates.
DATE_ADD()
SELECT name, DATE_ADD(admission_date, INTERVAL 1 YEAR) AS next_year
FROM Students;
DATE_SUB()
SELECT name, DATE_SUB(admission_date, INTERVAL 6 MONTH) AS six_months_before
FROM Students;
YEAR(), MONTH(), DAY()
SELECT
name,
YEAR(admission_date) AS year,
MONTH(admission_date) AS month,
DAY(admission_date) AS day
FROM Students;
DAYNAME()
SELECT name, DAYNAME(admission_date) AS admission_day
FROM Students;
STRING FUNCTIONS
UPPER()
SELECT UPPER(name) AS upper_name FROM Students;
LOWER()
SELECT LOWER(city) AS lower_city FROM Students;
LENGTH()
SELECT name, LENGTH(name) AS name_length FROM Students;
CONCAT()
SELECT CONCAT(name, ' - ', course) AS student_info
FROM Students;
SUBSTRING()
SELECT name, SUBSTRING(name,1,3) AS short_name
FROM Students;
REPLACE()
SELECT REPLACE(city,'Delhi','New Delhi') AS updated_city
FROM Students;
TRIM()
SELECT TRIM(' MySQL ') AS trimmed_text;
INSTR()
SELECT name, INSTR(name,'a') AS position
FROM Students;
NUMERIC FUNCTION PRACTICE
Basic Level
1. Display marks rounded to 1 decimal place.
2. Show marks rounded upward using CEIL().
3. Show marks rounded downward using FLOOR().
4. Find square root of fees.
5. Find power of marks (marks²).
Medium Level
6. Find average marks of all students.
7. Display total fees collected from students.
8. Show highest and lowest marks.
9. Display remainder when fees is divided by 1000.
10. Show students whose rounded marks are greater than 80.
DATE FUNCTION PRACTICE
Basic Level
Display current date and time.
Show admission year of each student.
Show month of admission.
Display day name of admission.
Medium Level
Show how many days each student has been in college.
Display admission date after adding 6 months.
Display admission date before subtracting 3 months.
Show students admitted in year 2022.
STRING FUNCTION PRACTICE
Basic Level
Display names in uppercase.
Display city names in lowercase.
Find length of each student's name.
Combine name and city in one column using CONCAT().
Medium Level
Show first 3 characters of each course.
Replace ‘Delhi’ with ‘New Delhi’ in city column (without updating table).
Display position of letter 'a' in each name.
Remove extra spaces from a string.
Combined Practice (Mixed Functions)
Show student name in uppercase and admission year together.
Display number of years each student has completed in college.
Show student whose name length is greater than 4.
Display marks rounded and also show if marks > average marks.
Display formatted fees using FORMAT() function.