Salary Range for Teachers by Department.

Introduction

In this project, you will learn how to retrieve the maximum and minimum salary for each department in a MySQL database using built-in functions and SQL queries.

👀 Preview

MySQL [edusys]> SOURCE ~/project/functionQuery.sql;
+------------+------------+------------+
| dept_name  | max_salary | min_salary |
+------------+------------+------------+
| Biology    |   72000.00 |   72000.00 |
| Comp. Sci. |   92000.00 |   65000.00 |
| Elec. Eng. |   80000.00 |   80000.00 |
| Finance    |   90000.00 |   80000.00 |
| History    |   62000.00 |   60000.00 |
| Music      |   40000.00 |   40000.00 |
| Physics    |   95000.00 |   87000.00 |
+------------+------------+------------+
7 rows in set (0.001 sec)

🎯 Tasks

In this project, you will learn:

• How to start the MySQL server and import data into the database
• How to write a SQL query to retrieve the maximum and minimum salary for each department
• How to execute the SQL query script in the MySQL command line client

🏆 Achievements

After completing this project, you will be able to:

• Understand how to use MySQL built-in functions like MAX() and MIN() to retrieve data
• Write SQL queries to group and aggregate data based on specific criteria
• Execute SQL scripts in the MySQL command line client
• Apply your knowledge to solve real-world data analysis problems

Skills Graph

Start MySQL and Import the Data

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

1. Start the MySQL server:

   sudo /etc/init.d/mysql start

2. Log into the MySQL terminal:

   mysql -uroot

3. Import the data from the edusys.sql script:

   SOURCE ~/project/edusys.sql

Write the Query Script

In this step, you will learn how to write the query script to retrieve the maximum and minimum salary for each department in the instructor table.

1. Create a new file named functionQuery.sql in the ~/project directory.

2. Open the functionQuery.sql file in a text editor and add the following SQL query:

   SELECT
       dept_name AS 'dept_name',
       MAX(salary) AS 'max_salary',
       MIN(salary) AS 'min_salary'
   FROM
       instructor
   GROUP BY
       dept_name;

   This query uses the MAX() and MIN() functions to retrieve the maximum and minimum salary for each department, and the GROUP BY clause to group the results by department.

3. Save the functionQuery.sql file.

Execute the Query Script

In this step, you will learn how to execute the functionQuery.sql script in the MySQL command line client.

1. In the MySQL terminal, execute the functionQuery.sql script:

   SOURCE ~/project/functionQuery.sql

2. The query results will be displayed in the MySQL terminal, with the following columns:

   • dept_name: the name of the department
   • max_salary: the maximum salary for the department
   • min_salary: the minimum salary for the department

The output should look similar to the following:

+------------+------------+------------+
| dept_name  | max_salary | min_salary |
+------------+------------+------------+
| Biology    |   72000.00 |   72000.00 |
| Comp. Sci. |   92000.00 |   65000.00 |
| Elec. Eng. |   80000.00 |   80000.00 |
| Finance    |   90000.00 |   80000.00 |
| History    |   62000.00 |   60000.00 |
| Music      |   40000.00 |   40000.00 |
| Physics    |   95000.00 |   87000.00 |
+------------+------------+------------+
7 rows in set (0.001 sec)

This completes the project. You have successfully written a query script to retrieve the maximum and minimum salary for each department in the instructor table.

Summary

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