Creating Stored Procedures for Course Data Retrieval

Introduction

In this project, you will learn how to create a stored procedure in MySQL to retrieve courses with credits greater than 3. This project will help you understand the basics of working with stored procedures in a database management system.

👀 Preview

MariaDB [edusys]> CALL getCourses();
+-----------+----------------------------+------------+---------+
| course_id | title                      | dept_name  | credits |
+-----------+----------------------------+------------+---------+
| BIO-101   | Intro. to Biology          | Biology    |       4 |
| BIO-301   | Genetics                   | Biology    |       4 |
| CS-101    | Intro. to Computer Science | Comp. Sci. |       4 |
| CS-190    | Game Design                | Comp. Sci. |       4 |
| PHY-101   | Physical Principles        | Physics    |       4 |
+-----------+----------------------------+------------+---------+
5 rows in set (0.000 sec)

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without a password
• How to import data into the MySQL database
• How to create a stored procedure to retrieve specific data from a table
• How to test the stored procedure to ensure it is working as expected

🏆 Achievements

After completing this project, you will be able to:

• Understand the purpose and benefits of using stored procedures in a database
• Create your own stored procedures to perform specific data retrieval tasks
• Troubleshoot and test stored procedures to ensure they are functioning correctly
• Apply the knowledge gained in this project to build more complex database-driven applications

Access MySQL and Import the Database

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

1. Start the MySQL service:

sudo service mysql start

2. Access MySQL using the sudo command:

sudo mysql

3. Import the data from /home/labex/project/edusys.sql into MySQL and switch to the edusys database:

MariaDB [None]> SOURCE /home/labex/project/edusys.sql;

Create the getCourses Stored Procedure

In this step, you will create a stored procedure called getCourses that retrieves courses from the course table where the credits field value is greater than 3.

1. Open the getCourses.sql file.
2. Add the following code to the getCourses.sql file:

-- Creating the getCourses stored procedure
DELIMITER //

CREATE PROCEDURE getCourses()
BEGIN
-- Retrieve courses from the course table where the credits field is greater than 3
SELECT course_id, title, dept_name, credits
FROM course
WHERE credits > 3;
END //

DELIMITER ;

3. Save and exit the file.

Test the getCourses Stored Procedure

In this step, you will test the getCourses stored procedure to ensure it is working as expected.

1. Run the getCourses.sql script in MySQL:

MariaDB [edusys]> SOURCE /home/labex/project/getCourses.sql;

2. Call the getCourses stored procedure:

MariaDB [edusys]> CALL getCourses();
+-----------+----------------------------+------------+---------+
| course_id | title                      | dept_name  | credits |
+-----------+----------------------------+------------+---------+
| BIO-101   | Intro. to Biology          | Biology    |       4 |
| BIO-301   | Genetics                   | Biology    |       4 |
| CS-101    | Intro. to Computer Science | Comp. Sci. |       4 |
| CS-190    | Game Design                | Comp. Sci. |       4 |
| PHY-101   | Physical Principles        | Physics    |       4 |
+-----------+----------------------------+------------+---------+
5 rows in set (0.000 sec)

The output shows that the getCourses stored procedure is working as expected, retrieving all the courses with credits greater than 3.

Summary

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