Step-by-Step SQL Queries: Date Functions, String Functions, and Math Functions

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

  1. Create a database collegeDB and use it.
  2. Create a table Students with student details like id, name, dob, marks, and city.
  3. Insert sample records into the table.
  4. 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() and MONTH() 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() and MIN() 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.

FYBSc IT DBMS Practical – Queries using Date, String and Math Functions in MySQL
🔗