Comprehensive MySQL Functions Practice

Introduction

In this project, you will learn how to use standard MySQL functions to retrieve information from the emp table in the personnel database. You will practice accessing MySQL, importing data, and writing SQL scripts to perform various data manipulation tasks.

👀 Preview

MariaDB [personnel]> SOURCE /home/labex/project/BasicFunctions.sql;
+--------+--------+-------------+
| Name   | Salary | Days on Job |
+--------+--------+-------------+
| HTIMS  |  26.67 |       14990 |
| NELLA  |  53.33 |       14925 |
| DRAW   |  41.67 |       14923 |
| SENOJ  |  99.17 |       14884 |
| NITRAM |  41.67 |       14705 |
| EKALB  |  95.00 |       14855 |
| KRALC  |  81.67 |       14816 |
| TTOCS  | 100.00 |       12621 |
| GNIK   | 166.67 |       14655 |
| RENRUT |  50.00 |       14725 |
| SMADA  |  36.67 |       12621 |
| SEMAJ  |  31.67 |       14639 |
| DROF   | 100.00 |       14639 |
| RELLIM |  43.33 |       14588 |
+--------+--------+-------------+
14 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 from a SQL file into the MySQL database
• How to write a SQL script to retrieve the name, daily salary, and days on the job for each employee in the emp table
• How to use MySQL functions like REVERSE(), ROUND(), and DATEDIFF() to manipulate the data

🏆 Achievements

After completing this project, you will be able to:

• Efficiently access and manage a MySQL database using the command line
• Utilize standard MySQL functions to perform complex data retrieval and manipulation tasks
• Develop SQL scripts to automate data processing and reporting
• Demonstrate your understanding of MySQL fundamentals and their practical applications

Skills Graph

Access MySQL and Import the Data

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

1. Start the MySQL service:

sudo service mysql start

2. Access MySQL using the sudo command:

sudo mysql

3. Import the data from the /home/labex/project/personnel.sql file into the personnel database:

MariaDB [(none)]> SOURCE /home/labex/project/personnel.sql;

Now you have successfully accessed MySQL and imported the data into the personnel database.

Write the SQL Script

In this step, you will write the SQL script to retrieve the name, daily salary, and days on the job for each employee in the emp table.

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

SELECT REVERSE(ename) AS `Name`,
       ROUND(sal/30, 2) AS `Salary`,
       DATEDIFF('2022-01-01', hiredate) AS `Days on Job`
FROM emp;

This SQL code will:

• Retrieve the name, daily salary, and days on the job for each employee in the emp table.
• Display the name in reverse order.
• Calculate the daily salary by assuming 30 days per month and rounding to two decimal places.
• Calculate the days on the job by finding the difference between the hire date and January 1, 2022.

3. Save the BasicFunctions.sql file.

Run the SQL Script

In this step, you will run the SQL script you created in the previous step.

1. In the MySQL prompt, run the following command to execute the BasicFunctions.sql script:

MariaDB [personnel]> SOURCE /home/labex/project/BasicFunctions.sql;

This will execute the SQL script and display the results in the MySQL prompt.

The output should look similar to the following:

+--------+--------+-------------+
| Name   | Salary | Days on Job |
+--------+--------+-------------+
| HTIMS  |  26.67 |       14990 |
| NELLA  |  53.33 |       14925 |
| DRAW   |  41.67 |       14923 |
| SENOJ  |  99.17 |       14884 |
| NITRAM |  41.67 |       14705 |
| EKALB  |  95.00 |       14855 |
| KRALC  |  81.67 |       14816 |
| TTOCS  | 100.00 |       12621 |
| GNIK   | 166.67 |       14655 |
| RENRUT |  50.00 |       14725 |
| SMADA  |  36.67 |       12621 |
| SEMAJ  |  31.67 |       14639 |
| DROF   | 100.00 |       14639 |
| RELLIM |  43.33 |       14588 |
+--------+--------+-------------+
14 rows in set (0.000 sec)

Congratulations! You have successfully completed the project by writing and running a SQL script to retrieve information from the emp table in the personnel database.

Summary

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