Scribd
Upload
8 views5 pages

DBMS Exp-8

The document describes various MySQL functions, categorizing them into Aggregate Functions and Scalar Functions. It provides examples of each type, including functions like SUM, MIN, MAX, and string, numeric, date/time, and control flow functions. The document illustrates how to perform operations such as counting employees, formatting dates, and classifying salaries using SQL queries.

Uploaded by

rakeshraj5186
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views5 pages

DBMS Exp-8

The document describes various MySQL functions, categorizing them into Aggregate Functions and Scalar Functions. It provides examples of each type, including functions like SUM, MIN, MAX, and string, numeric, date/time, and control flow functions. The document illustrates how to perform operations such as counting employees, formatting dates, and classifying salaries using SQL queries.

Uploaded by

rakeshraj5186
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

Program – 08

AIM: To Describe Various MySQL Functions.

• SQL Functions are built-in programs that are used to perform different operations on
the database.

There are two types of functions in SQL:

• Aggregate Functions

• Scalar Functions

o Initial Table

Fig (01): Initial Table

➢ Aggregate Functions
1. SUM

SELECT COUNT(E_ID) AS EmployeeCount FROM Employee;

Fig (02): SUM ()

2. MIN

SELECT COUNT(E_ID) AS EmployeeCount FROM Employee;

Fig (03): MIN()

3. MAX

SELECT MAX(Salary) AS MaxSalary FROM Employee;


Fig (04): MAX()

4. GROUP_CONCAT

SELECT GROUP_CONCAT(Name) AS EmployeeNames FROM Employee;

Fig (05): GROUP_CONCAT()

➢ Scalar Functions Operations


1. String Functions
❖ Converts all the Names to UPPERCASE

SELECT UPPER(Name) AS UpperCaseName FROM Employee;

Fig (06): UPPER()

❖ Converts all the names to LOWERCASE

SELECT LOWER(Name) AS LowerCaseName FROM Employee;

Fig (07): LOWER()


❖ Concatenate employee Name and Designation

SELECT CONCAT(Name, ' - ', Designation) AS FullDetails FROM Employee;

Fig (08): CONCAT()

2. Numeric Functions
❖ Round the salary to 2 decimal places

SELECT ROUND(Salary, 2) AS RoundedSalary FROM Employee;

Fig (09): ROUND()

❖ Get the floor value of salaries

SELECT FLOOR(Salary) AS FloorSalary FROM Employee;

Fig (10): FLOOR()

3. Date and Time Functions


❖ Get the current date and time

SELECT NOW() AS CurrentDateTime;


Fig (11): NOW()

❖ Format the date of joining (DOJ) in dd-mm-yyyy format

SELECT DATE_FORMAT(DOJ, '%d-%m-%Y') AS FormattedDOJ FROM


Employee;

Fig (12): FORMAT()

❖ Calculate the difference in days between today and the joining date

SELECT Name, DATEDIFF(NOW(), DOJ) AS DaysSinceJoining FROM


Employee;

Fig (13): DATEDIFF()

4. Control Flow Functions


❖ Use the IF function to label employees with salaries above 70,000 as
"High"

SELECT Name, Salary, IF(Salary > 70000, 'High', 'Low') AS SalaryLevel


FROM Employee;
Fig (14): IF ()

❖ Use the CASE function to classify salaries into categories

SELECT

Name,

Salary,

CASE

WHEN Salary > 80000 THEN 'High'

WHEN Salary > 60000 THEN 'Medium'

ELSE 'Low'

END AS SalaryCategory

FROM Employee;

Fig (15): CASE()

You might also like