MySQL Data Analysis

Introduction

In this project, you will learn how to use MySQL's built-in functions to retrieve relevant information from the employee table (emp) in the personnel database. You will practice writing SQL queries to access and manipulate data in a relational database.

👀 Preview

MariaDB [personnel]> SOURCE /home/labex/project/multiple_line_function.sql;
+---------------+------------+------------+-------------+
| department_id | max_salary | min_salary | avg_salary  |
+---------------+------------+------------+-------------+
|            20 |    3000.00 |     800.00 | 2175.000000 |
|            10 |    5000.00 |    1300.00 | 2916.666667 |
+---------------+------------+------------+-------------+
2 rows 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 a SQL file into a MySQL database
• How to write a SQL query to retrieve the department number, maximum salary, minimum salary, and average salary for departments with an average salary of 2000 or more, and sort the results in descending order by department number

🏆 Achievements

After completing this project, you will be able to:

• Understand how to use MySQL's built-in functions to perform complex data analysis
• Write SQL queries to retrieve and manipulate data from a relational database
• Apply your knowledge of SQL to solve real-world data management problems

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.

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

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

Write the SQL Query

In this step, you will learn how to write the SQL query to retrieve the relevant information from the emp table in the personnel database.

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

SELECT deptno AS 'department_id', MAX(sal) AS 'max_salary', MIN(sal) AS 'min_salary', AVG(sal) AS 'avg_salary'
FROM emp
GROUP BY deptno
HAVING AVG(sal) >= 2000
ORDER BY deptno DESC;

This query will retrieve the department number, maximum salary, minimum salary, and average salary for departments with an average salary of 2000 or more, and sort the results in descending order by department number.

Run the SQL Query

In this step, you will learn how to run the SQL query that you have written in the previous step.

1. Open the MySQL prompt:

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

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

The output should look similar to the following:

+---------------+------------+------------+-------------+
| department_id | max_salary | min_salary | avg_salary  |
+---------------+------------+------------+-------------+
|            20 |    3000.00 |     800.00 | 2175.000000 |
|            10 |    5000.00 |    1300.00 | 2916.666667 |
+---------------+------------+------------+-------------+
2 rows in set (0.001 sec)

Congratulations! You have successfully completed the project.

Summary

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