Learn SQL: Calculate Average Salaries by 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

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"]) sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"]) sql(("`SQL`")) -.-> sql/DataManipulationandQueryingGroup(["`Data Manipulation and Querying`"]) sql(("`SQL`")) -.-> sql/AdvancedDataOperationsGroup(["`Advanced Data Operations`"]) sql(("`SQL`")) -.-> sql/DatabaseManagementandOptimizationGroup(["`Database Management and Optimization`"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") sql/BasicSQLCommandsGroup -.-> sql/select("`SELECT statements`") sql/DataManipulationandQueryingGroup -.-> sql/in("`IN clause`") sql/DataManipulationandQueryingGroup -.-> sql/order_by("`ORDER BY clause`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("`Data Retrieval`") sql/DataManipulationandQueryingGroup -.-> sql/group_by("`GROUP BY clause`") sql/AdvancedDataOperationsGroup -.-> sql/numeric_functions("`Numeric functions`") sql/DatabaseManagementandOptimizationGroup -.-> sql/using_indexes("`Using Indexes`") subgraph Lab Skills mysql/source -.-> lab-301284{{"`Average Salaries Per Department `"}} sql/select -.-> lab-301284{{"`Average Salaries Per Department `"}} sql/in -.-> lab-301284{{"`Average Salaries Per Department `"}} sql/order_by -.-> lab-301284{{"`Average Salaries Per Department `"}} mysql/select -.-> lab-301284{{"`Average Salaries Per Department `"}} sql/group_by -.-> lab-301284{{"`Average Salaries Per Department `"}} sql/numeric_functions -.-> lab-301284{{"`Average Salaries Per Department `"}} sql/using_indexes -.-> lab-301284{{"`Average Salaries Per Department `"}} end

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.

Open a terminal on your system.
Start the MySQL server:
```
sudo /etc/init.d/mysql start
```
Log into the MySQL terminal:
```
mysql -uroot
```
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.

Open a text editor and create a new file named singleTableQuery.sql in the ~/project directory.
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.
Save the singleTableQuery.sql file.

Run the SQL Query

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

In the MySQL terminal, run the following command to execute the singleTableQuery.sql script:
```
SOURCE ~/project/singleTableQuery.sql
```

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.

Average Salaries Per Department