Create MySQL Maximum Value Function

Introduction

In this project, you will learn how to create a custom function in MySQL that returns the larger of two given numbers. This project will guide you through the process of accessing MySQL, importing data, and developing a function to perform a maximum value judgment.

👀 Preview

MariaDB [edusys]> SELECT getMax(1, 3);
+--------------+
| getMax(1, 3) |
+--------------+
|            3 |
+--------------+
1 row in set (0.001 sec)

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without a password
• How to import data from an SQL script into MySQL
• How to create a custom function in MySQL that takes two integers as input and returns the larger value

🏆 Achievements

After completing this project, you will be able to:

• Understand how to work with MySQL and create custom functions
• Develop a function to perform a maximum value judgment
• Apply your MySQL knowledge to solve practical problems

Skills Graph

Access MySQL and Import Data

In this step, you will learn how to access MySQL using the sudo command without any password, and import the data from the given edusys.sql script into MySQL.

1. Start your MySQL database:

sudo service mysql start

2. Access MySQL:

sudo mysql

3. Import the data from the edusys.sql script:

SOURCE /home/labex/project/edusys.sql;

Create the getMax Function

In this step, you will learn how to create a custom function getMax that takes two integers as input and returns the larger value.

1. Open the getMax.sql file.
2. Add the following script to create the getMax function:

-- Set the delimiter to // to enable using semicolon (;) within the function body
DELIMITER //

-- Create a custom function named getMax that takes two integers as input and returns the maximum value
CREATE FUNCTION getMax(a INT, b INT)
RETURNS INT
BEGIN
  -- Declare a variable max_val to store the maximum value
  DECLARE max_val INT;

  -- Compare the input values a and b
  IF a > b THEN
    SET max_val = a;
  ELSE
    SET max_val = b;
  END IF;

  -- Return the maximum value
  RETURN max_val;
END //

-- Reset the delimiter back to the default semicolon (;)
DELIMITER ;

Test the getMax Function

In this step, you will learn how to test the getMax function by running an example query.

1. Run the SQL script in the MySQL prompt:

SOURCE /home/labex/project/getMax.sql

2. Run the following query to test the getMax function:

SELECT getMax(1, 3);

The output should be:

+--------------+
| getMax(1, 3) |
+--------------+
|            3 |
+--------------+
1 row in set (0.001 sec)

Congratulations! You have successfully created a custom function getMax that returns the larger of two given numbers.

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.