General Function Comprehensive

Introduction

In this project, you will learn how to access MySQL, import data, and query employee information using built-in MySQL functions.

👀 Preview

MariaDB [personnel]> SOURCE /home/labex/project/answer.sql;
+------------------------------------------+---------------+----------------+
| Name                                     | Annual Income | Current User   |
+------------------------------------------+---------------+----------------+
| bc70be7f38046e64dd779f276ce4a20f99153f26 | 800.000       | root@localhost |
| 6e1cae96112fe249dc0fb4f9a004c9dc04315608 | 1,900.000     | root@localhost |
| 7c7f32425fa076caeed971f5549c82ffa88fd0eb | 1,750.000     | root@localhost |
| a1787353201302d65c744486c1e940709fee0eff | 2,975.000     | root@localhost |
| f3b9b87a2f377c006e935f32dd25ea55ebf7f551 | 2,650.000     | root@localhost |
| 7cde55b3a6e9cf610db200b6b9cc9b5adca1657f | 2,850.000     | root@localhost |
| 4f90e103facadd0787d7a6828f096f6e66595a47 | 2,450.000     | root@localhost |
| 7634fdc80aa4027cfd5e966abc1b6b4b4ea19fbe | 3,000.000     | root@localhost |
| 50b8a339f82ab9ce6c55bf8ea10dad8513e9d142 | 5,000.000     | root@localhost |
| e043e3d3ebddafaa5ed97f7dcc4d236286f8ad4a | 1,500.000     | root@localhost |
| 2d2eb27e1db8836e44cd4e94f58b89728331e8de | 1,100.000     | root@localhost |
| 6afc44af3cbdbb6718e8da8715a1956b895dc5d2 | 950.000       | root@localhost |
| 7e5b07d8bbb5e0a55b5d428476d4691b3ee97b4a | 3,000.000     | root@localhost |
| 25d5ae3b0e0dac511ba94e337b88e0887538b31f | 1,300.000     | root@localhost |
+------------------------------------------+---------------+----------------+
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 query the names and annual income of employees from the emp table
• How to encrypt employee names using the SHA function
• How to format the annual income with 3 decimal places
• How to display the current database account information

🏆 Achievements

After completing this project, you will be able to:

• Confidently access and manage a MySQL database using the command line
• Perform data import and basic data querying operations
• Apply built-in MySQL functions to manipulate and format data
• Understand the importance of data security and encryption
• Demonstrate your ability to work with MySQL databases and SQL queries

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

1. Open a terminal and start the MySQL service using the following command:

sudo service mysql start

2. Access MySQL using the sudo command:

sudo mysql

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

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

Query Employee Names and Annual Income

In this step, you will learn how to query the names and annual income of all employees in the emp table, and display the database account information for the current operation.

1. Add the following code to the answer.sql file.

SELECT SHA(emp.ename) AS 'Name',
       FORMAT(IFNULL(emp.sal, 0) + IFNULL(emp.comm, 0), 3) AS 'Annual Income',
       CURRENT_USER() AS 'Current User'
FROM emp;

Here's what the code does:

• SHA(emp.ename): Encrypts the employee names using the SHA function.
• IFNULL(emp.sal, 0) + IFNULL(emp.comm, 0): Calculates the annual income by adding the salary (sal) and bonus (comm) columns, and replaces any NULL values with 0.
• FORMAT(... , 3): Formats the annual income with 3 decimal places.
• CURRENT_USER(): Displays the current database account information.

2. Execute the answer.sql file.

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

The query will return the following output:

+------------------------------------------+---------------+----------------+
| Name                                     | Annual Income | Current User   |
+------------------------------------------+---------------+----------------+
| bc70be7f38046e64dd779f276ce4a20f99153f26 | 800.000       | root@localhost |
| 6e1cae96112fe249dc0fb4f9a004c9dc04315608 | 1,900.000     | root@localhost |
| 7c7f32425fa076caeed971f5549c82ffa88fd0eb | 1,750.000     | root@localhost |
| a1787353201302d65c744486c1e940709fee0eff | 2,975.000     | root@localhost |
| f3b9b87a2f377c006e935f32dd25ea55ebf7f551 | 2,650.000     | root@localhost |
| 7cde55b3a6e9cf610db200b6b9cc9b5adca1657f | 2,850.000     | root@localhost |
| 4f90e103facadd0787d7a6828f096f6e66595a47 | 2,450.000     | root@localhost |
| 7634fdc80aa4027cfd5e966abc1b6b4b4ea19fbe | 3,000.000     | root@localhost |
| 50b8a339f82ab9ce6c55bf8ea10dad8513e9d142 | 5,000.000     | root@localhost |
| e043e3d3ebddafaa5ed97f7dcc4d236286f8ad4a | 1,500.000     | root@localhost |
| 2d2eb27e1db8836e44cd4e94f58b89728331e8de | 1,100.000     | root@localhost |
| 6afc44af3cbdbb6718e8da8715a1956b895dc5d2 | 950.000       | root@localhost |
| 7e5b07d8bbb5e0a55b5d428476d4691b3ee97b4a | 3,000.000     | root@localhost |
| 25d5ae3b0e0dac511ba94e337b88e0887538b31f | 1,300.000     | root@localhost |
+------------------------------------------+---------------+----------------+
14 rows in set (0.000 sec)

The query has been saved in the file /home/labex/project/answer.sql.

Summary

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