MySQL Conversion Functions Challenge

Introduction

In this project, you will learn how to use MySQL's common conversion functions to extract relevant information from the emp table in the personnel database. You will learn how to access MySQL, import data, and write a script to perform various date and time conversions on the data.

👀 Preview

MariaDB [personnel]> SOURCE /home/labex/project/ConversionFunctions.sql;
+------------+------------+
| Entry Date | New Date   |
+------------+------------+
| 80-12      | 1980-12-31 |
| 81-02      | NULL       |
| 81-02      | NULL       |
| 81-04      | 1981-04-16 |
| 81-09      | NULL       |
| 81-05      | 1981-05-15 |
| 81-06      | 1981-06-23 |
| 87-06      | 1987-06-27 |
| 81-11      | 1981-11-31 |
| 81-09      | 1981-09-22 |
| 87-06      | 1987-06-27 |
| 81-12      | 1981-12-17 |
| 81-12      | 1981-12-17 |
| 82-01      | NULL       |
+------------+------------+
14 rows in set, 4 warnings (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 perform date and time conversions using functions like DATE_FORMAT() and STR_TO_DATE()
• How to display the results of the conversion functions with appropriate aliases

🏆 Achievements

After completing this project, you will be able to:

• Understand how to use MySQL's conversion functions to manipulate and display date and time data
• Write SQL scripts to perform complex data transformations
• Apply your knowledge of MySQL to solve real-world data processing problems

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 /home/labex/project/personnel.sql into the personnel database.

1. Start the MySQL service:

sudo service mysql start

1. Access MySQL using the sudo command without any password:

sudo mysql

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

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

Write the Conversion Functions Script

In this step, you will learn how to write the ConversionFunctions.sql script to extract relevant information from the emp table in the personnel database using MySQL's common conversion functions.

1. Open the ConversionFunctions.sql file.
2. Add the following SQL query to the ConversionFunctions.sql file:

SELECT DATE_FORMAT(hiredate, '%y-%m') AS `Entry Date`,
       DATE_FORMAT(STR_TO_DATE(CONCAT(DATE_FORMAT(hiredate, '%Y%m%d') + 14), '%Y%m%d'), '%Y-%m-%d') AS `New Date`
FROM emp;

This query performs the following tasks:

• Extracts the hired date and revised date for all employees in the emp table.
• Displays the hired date in the format yy-MM.
• Adds 14 days to the hired date, converts it to a date format using the STR_TO_DATE() function, and displays it in the format yyyy-MM-dd.
• Uses aliases Entry Date and New Date to display the results.

1. Save the ConversionFunctions.sql file.

Run the Conversion Functions Script

In this step, you will learn how to run the ConversionFunctions.sql script in MySQL.

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

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

This will execute the SQL query in the ConversionFunctions.sql file and display the results.

The output should look similar to the following:

+------------+------------+
| Entry Date | New Date   |
+------------+------------+
| 80-12      | 1980-12-31 |
| 81-02      | NULL       |
| 81-02      | NULL       |
| 81-04      | 1981-04-16 |
| 81-09      | NULL       |
| 81-05      | 1981-05-15 |
| 81-06      | 1981-06-23 |
| 87-06      | 1987-06-27 |
| 81-11      | 1981-11-31 |
| 81-09      | 1981-09-22 |
| 87-06      | 1987-06-27 |
| 81-12      | 1981-12-17 |
| 81-12      | 1981-12-17 |
| 82-01      | NULL       |
+------------+------------+
14 rows in set, 4 warnings (0.000 sec)

This output displays the hired date and revised date for all employees in the emp table, with the hired date in the format yy-MM and the revised date in the format yyyy-MM-dd.

Summary

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