Average Salaries Per Department

Introduction

In this project, you will learn how to calculate the average salary for each department in a database and display the results in descending order using SQL queries.

👀 Preview

MariaDB [edusys]> SOURCE ~/project/singleTableQuery.sql
+------------+--------------+
| dept_name  | avg_salary   |
+------------+--------------+
| Physics    | 91000.000000 |
| Finance    | 85000.000000 |
| Elec. Eng. | 80000.000000 |
| Comp. Sci. | 77333.333333 |
| Biology    | 72000.000000 |
| History    | 61000.000000 |
| Music      | 40000.000000 |
+------------+--------------+
7 rows in set (0.001 sec)

🎯 Tasks

In this project, you will learn:

• How to start the MySQL server and import a database
• How to write an SQL query to group data by department and calculate the average salary
• How to order the results in descending order based on the average salary

🏆 Achievements

After completing this project, you will be able to:

• Understand how to use SQL's SELECT, FROM, WHERE, GROUP BY, and ORDER BY clauses to perform data analysis
• Implement a SQL query to calculate and display the average salaries per department in descending order
• Apply your SQL knowledge to solve real-world data analysis problems

Start MySQL and Import the Database

In this step, you will learn how to start the MySQL server and import the edusys.sql database into MySQL.

1. Open a terminal on your system.

2. Start the MySQL server:

   sudo /etc/init.d/mysql start
   

3. Log into the MySQL terminal:

   mysql -uroot
   

4. Import the edusys.sql database:

   SOURCE ~/project/edusys.sql
   

Write the SQL Query

In this step, you will write the SQL query to calculate the average salary for each department and display the results in descending order.

1. Open a text editor and create a new file named singleTableQuery.sql in the ~/project directory.

2. Add the following SQL query to the file:

   SELECT dept_name, AVG(salary) AS avg_salary
   FROM instructor
   GROUP BY dept_name
   ORDER BY avg_salary DESC;
   

   This query will:

   • Select the dept_name and the average salary (AVG(salary)) as avg_salary.
   • Group the results by dept_name.
   • Order the results by avg_salary in descending order.

3. Save the singleTableQuery.sql file.

Run the SQL Query

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

1. In the MySQL terminal, run the following command to execute the singleTableQuery.sql script:

   SOURCE ~/project/singleTableQuery.sql
   

2. The query results will be displayed in the terminal, showing the average salary for each department in descending order.

   +------------+--------------+
   | dept_name  | avg_salary   |
   +------------+--------------+
   | Physics    | 91000.000000 |
   | Finance    | 85000.000000 |
   | Elec. Eng. | 80000.000000 |
   | Comp. Sci. | 77333.333333 |
   | Biology    | 72000.000000 |
   | History    | 61000.000000 |
   | Music      | 40000.000000 |
   +------------+--------------+
   7 rows in set (0.001 sec)
   

Congratulations! You have successfully completed the project by writing and running the SQL query to display the average salaries per department in descending order.

Summary

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