String Functions
Basic String Manipulation
-- Display first 3 characters of country name
SELECT LEFT("INDIA", 3);
-- Display last 4 characters
SELECT RIGHT("Computer Science", 4);
-- Extract middle portion of string
SELECT MID("Informatics", 3, 4);
SELECT SUBSTR("Practices", 3);
-- Get length of string
SELECT LENGTH("Informatics Practices");
Case conversion
-- Convert to uppercase
SELECT UPPER("informatics practices");
-- Convert to lowercase
SELECT LOWER("COMPUTER SCIENCE");
-- Remove leading/trailing spaces
SELECT TRIM(" Hello World ");
String search function
-- Find position of substring
SELECT INSTR("WELCOME WORLD", "COME");
-- Check if string contains pattern
SELECT LOCATE("tech", "Computer Technology");
Mathematical Functions
Basic math operation
-- Power function
SELECT POW(2, 3);
SELECT POWER(5, 2);
-- Square root
SELECT SQRT(25);
-- Absolute value
SELECT ABS(-15);
-- Modulus (remainder)
SELECT MOD(100, 9);
Rounding Function
-- Round to specific decimal places
SELECT ROUND(123.2345, 2);
SELECT ROUND(342.9234, -1);
-- Ceiling and floor
SELECT CEIL(4.3);
SELECT FLOOR(4.9);
-- Truncate decimal places
SELECT TRUNCATE(123.456, 1);
Date and Time function
Current date and time
-- Get current date
SELECT CURDATE();
-- Get current time
SELECT CURTIME();
-- Get current date and time
SELECT NOW();
Date Extraction Function
-- Extract year, month, day
SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), DAY("1979/11/26");
-- Get month and day names
SELECT MONTHNAME("1979/11/26"), DAYNAME("1979/11/26");
-- Extract time components
SELECT HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
Aggregate Function
Basic Aggregate queries
-- Assuming a table STUDENT with columns: StudentID, Name, Marks, Grade
SELECT COUNT(*) FROM STUDENT;
SELECT MAX(Marks) FROM STUDENT;
SELECT MIN(Marks) FROM STUDENT;
SELECT AVG(Marks) FROM STUDENT;
SELECT SUM(Marks) FROM STUDENT;
Advanced aggregate queries
-- Count non-null values
SELECT COUNT(Grade) FROM STUDENT WHERE Grade IS NOT NULL;
-- Group by with aggregate
SELECT Grade, COUNT(*) FROM STUDENT GROUP BY Grade;
SELECT Grade, AVG(Marks) FROM STUDENT GROUP BY Grade HAVING AVG(Marks) >
75;
Practical Exercise Questions
Question 1: Product Table Operations
-- Create table
CREATE TABLE PRODUCT (
PCode VARCHAR(10) PRIMARY KEY,
PName VARCHAR(50) NOT NULL,
UPrice DECIMAL(10,2),
Quantity INT
);
-- Add discount column
ALTER TABLE PRODUCT ADD DISCOUNT DECIMAL(10,2);
-- Calculate 10% discount for items > 100
UPDATE PRODUCT SET DISCOUNT =
CASE
WHEN UPrice > 100 THEN UPrice * 0.10
ELSE 0
END;
Question 2: Employee Commission Calculation
-- Add commission column
ALTER TABLE SALE ADD Commission NUMERIC(7,2);
-- Calculate 12% commission
UPDATE SALE SET Commission = 12/100 * SalePrice;
-- Display records where commission > 73000
SELECT * FROM SALE WHERE Commission > 73000;
Question 3: Mixed Function Queries
-- Display current month name
SELECT MONTHNAME(CURDATE());
-- Remove spaces and convert to upper case
SELECT UPPER(TRIM(" panorama "));
-- Extract 7 characters from 7th position
SELECT SUBSTR("INDIA SHINING", 7, 7);
-- Round salary to nearest 100
SELECT EmpName, ROUND(Salary, -2) FROM EMPLOYEE;
-- Display day name of birth date
SELECT Name, DAYNAME(DOB) FROM STUDENT;
Sample Database Queries for Practice
-- Assuming STUDENT table: StudentNo, Name, Subject, Marks, Grade
-- Count students by grade
SELECT Grade, COUNT(*) as StudentCount
FROM STUDENT
GROUP BY Grade;
-- Average marks by subject
SELECT Subject, ROUND(AVG(Marks), 2) as AvgMarks
FROM STUDENT
GROUP BY Subject;
-- Students with marks above average
SELECT Name, Marks
FROM STUDENT
WHERE Marks > (SELECT AVG(Marks) FROM STUDENT);
Student Performance Analysis
-- Assuming STUDENT table: StudentNo, Name, Subject, Marks, Grade
-- Count students by grade
SELECT Grade, COUNT(*) as StudentCount
FROM STUDENT
GROUP BY Grade;
-- Average marks by subject
SELECT Subject, ROUND(AVG(Marks), 2) as AvgMarks
FROM STUDENT
GROUP BY Subject;
-- Students with marks above average
SELECT Name, Marks
FROM STUDENT
WHERE Marks > (SELECT AVG(Marks) FROM STUDENT);
Text Processing Samples
-- Extract first word from full name
SELECT LEFT(Name, INSTR(Name, ' ') - 1) as FirstName FROM STUDENT;
-- Display names starting with 'A'
SELECT Name FROM STUDENT WHERE Name LIKE 'A%';
-- Count characters in longest name
SELECT MAX(LENGTH(Name)) FROM STUDENT;
Function Categories:
● Single Row Functions: Work on one row at a time (String, Math, Date
functions)
● Aggregate Functions: Work on multiple rows to return single result
Common Use Cases:
● Data validation and formatting
● Mathematical calculations for business logic
● Date manipulations for reporting
● Statistical analysis using aggregate functions