DBMS Practical: Queries using Date, String and Math Functions in MySQL (FYBSc IT)
Learn how to use Date Functions, String Functions, and Math Functions in MySQL with practical examples. This is one of the most important FYBSc IT DBMS Practicals for students.
Aim
To perform queries using Date functions, String functions, and Math functions in MySQL.
Theory
MySQL provides many in-built functions to work with data effectively:
- Date Functions: Used to manipulate and extract information from date values. Example:
CURDATE()
,YEAR()
,MONTH()
,TIMESTAMPDIFF()
. - String Functions: Used to process text data. Example:
UPPER()
,LOWER()
,LENGTH()
,REPLACE()
. - Math Functions: Used to perform mathematical calculations. Example:
SQRT()
,ROUND()
,RAND()
,MAX()
,MIN()
.
Steps
- Create a database
collegeDB
and use it. - Create a table Students with student details like id, name, dob, marks, and city.
- Insert sample records into the table.
- Run queries using Date functions, String functions, and Math functions.
1. Create Database and Table
CREATE DATABASE collegeDB; USE collegeDB; CREATE TABLE Students ( student_id INT PRIMARY KEY, name VARCHAR(50), dob DATE, marks INT, city VARCHAR(30) ); INSERT INTO Students VALUES (1, 'Rahul Sharma', '2002-03-15', 78, 'Mumbai'), (2, 'Ayesha Khan', '2001-07-21', 85, 'Pune'), (3, 'Vikram Patel', '2003-01-05', 92, 'Delhi'), (4, 'Iram Shaikh', '2002-11-11', 67, 'Mumbai'), (5, 'Sahil Mehta', '2001-05-25', 74, 'Chennai');
2. Date Function Queries
-- Show today’s date SELECT CURDATE() AS Today; -- Find age of each student SELECT name, TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS Age FROM Students; -- Extract month and year from DOB SELECT name, YEAR(dob) AS Birth_Year, MONTH(dob) AS Birth_Month FROM Students; -- Show students whose birthday is in current month SELECT name, dob FROM Students WHERE MONTH(dob) = MONTH(CURDATE());
3. String Function Queries
-- Convert student names to uppercase SELECT UPPER(name) AS Name_Upper FROM Students; -- Extract first 5 characters of name SELECT LEFT(name, 5) AS Short_Name FROM Students; -- Find length of student names SELECT name, LENGTH(name) AS Name_Length FROM Students; -- Replace city 'Mumbai' with 'Bombay' SELECT REPLACE(city, 'Mumbai', 'Bombay') AS Updated_City FROM Students;
4. Math Function Queries
-- Find square root of student marks SELECT name, marks, SQRT(marks) AS SquareRoot FROM Students; -- Round off marks SELECT name, marks, ROUND(marks/7,2) AS Rounded_Marks FROM Students; -- Find highest and lowest marks SELECT MAX(marks) AS Highest, MIN(marks) AS Lowest FROM Students; -- Generate random number between 1 to 100 SELECT FLOOR(1 + (RAND() * 100)) AS Random_Number;
Explanation of Functions
- Date Functions:
CURDATE()
shows today’s date,TIMESTAMPDIFF()
calculates age,YEAR()
andMONTH()
extract date parts. - String Functions:
UPPER()
converts text to uppercase,LEFT()
extracts characters,LENGTH()
gives string length,REPLACE()
replaces text. - Math Functions:
SQRT()
finds square root,ROUND()
rounds decimals,MAX()
andMIN()
find highest/lowest values,RAND()
generates random numbers.
Conclusion
In this practical, we learned how to use Date Functions, String Functions, and Math Functions in MySQL. These functions are very useful for handling real-world database operations such as calculating age, formatting text, and performing mathematical calculations.