Format Query for Teacher Salary

Introduction

In this project, you will learn how to query and format the salaries of teaching staff in a university's database. You will start by learning how to start the MySQL service, import the database, write the SQL query to retrieve the teacher salaries, and format the output.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to start the MySQL service
• How to import a database into MySQL
• How to write an SQL query to retrieve data from a table
• How to format the output of an SQL query

🏆 Achievements

After completing this project, you will be able to:

• Understand how to manage a MySQL database
• Write SQL queries to retrieve and format data
• Apply formatting techniques to improve the readability of query results

Start the MySQL Service

In this step, you will learn how to start the MySQL service. Follow the steps below to complete this step:

1. Open a terminal on your system.
2. Run the following command to start the MySQL service:

sudo service mysql start

This will start the MySQL service on your system.

Import the Database

In this step, you will learn how to import the edusys.sql database into MySQL. Follow the steps below to complete this step:

1. Open the MySQL command-line client by running the following command:

mysql -uroot

2. Once you are in the MySQL command-line, run the following command to import the edusys.sql script:

SOURCE ~/project/edusys.sql

This will import the data from the edusys.sql script into the MySQL database.

Write the Query

In this step, you will learn how to write the query to retrieve the teacher salaries from the instructor table and format the output.

1. Create a new file named teacherSalary.sql in the ~/project directory.
2. In the teacherSalary.sql file, add the following SQL query:

SELECT name, FORMAT(salary, 0) AS salary
FROM instructor
ORDER BY salary DESC;

This query will retrieve the name and salary columns from the instructor table, format the salary column to display without decimal places, and sort the results in descending order of salary.

Run the Query

In this step, you will learn how to run the teacherSalary.sql script and view the formatted output.

1. Open the MySQL command-line client by running the following command:

mysql -uroot

2. Once you are in the MySQL command-line, run the following command to execute the teacherSalary.sql script:

SOURCE ~/project/teacherSalary.sql;

This will execute the query in the teacherSalary.sql script and display the formatted output in the MySQL command-line.

The output should look similar to the following:

MariaDB [edusys]> SOURCE ~/project/teacherSalary.sql;
+------------+--------+
| name       | salary |
+------------+--------+
| Einstein   | 95,000 |
| Brandt     | 92,000 |
| Wu         | 90,000 |
| Gold       | 87,000 |
| Kim        | 80,000 |
| Singh      | 80,000 |
| Katz       | 75,000 |
| Crick      | 72,000 |
| Srinivasan | 65,000 |
| Califieri  | 62,000 |
| El Said    | 60,000 |
| Mozart     | 40,000 |
+------------+--------+
12 rows in set (0.000 sec)

Congratulations! You have successfully completed the project of querying and formatting the teacher salaries in the university's database.

Summary

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