LabEx
Log InJoin For Free

Average Salaries Per Department

SQLSQLBeginner
Practice Now

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/DataManipulationandQueryingGroup(["Data Manipulation and Querying"]) mysql(("MySQL")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["Database Functions and Data Types"]) sql(("SQL")) -.-> sql/BasicSQLCommandsGroup(["Basic SQL Commands"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") sql/BasicSQLCommandsGroup -.-> sql/select("SELECT statements") sql/BasicSQLCommandsGroup -.-> sql/insert("INSERT INTO statements") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("External Code Execution") sql/BasicSQLCommandsGroup -.-> sql/create_table("CREATE TABLE statements") sql/DataManipulationandQueryingGroup -.-> sql/order_by("ORDER BY clause") sql/DataManipulationandQueryingGroup -.-> sql/group_by("GROUP BY clause") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/database("DB Function - Info Retrieval") subgraph Lab Skills mysql/use_database -.-> lab-301284{{"Average Salaries Per Department "}} sql/select -.-> lab-301284{{"Average Salaries Per Department "}} sql/insert -.-> lab-301284{{"Average Salaries Per Department "}} mysql/select -.-> lab-301284{{"Average Salaries Per Department "}} mysql/source -.-> lab-301284{{"Average Salaries Per Department "}} sql/create_table -.-> lab-301284{{"Average Salaries Per Department "}} sql/order_by -.-> lab-301284{{"Average Salaries Per Department "}} sql/group_by -.-> lab-301284{{"Average Salaries Per Department "}} mysql/database -.-> 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.

  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.

โœจ Check Solution and Practice

Summary

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