Database Joins for Personnel Data

Introduction

In this project, you will learn how to use join queries to retrieve pertinent data from the emp table, dept table, and salgrade table in the personnel database.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to access the MySQL database and import the personnel data
• How to use an equi-join (inner join) to retrieve the names, hire dates, and corresponding department names from the employee table
• How to use a non-equi join to retrieve the names, salaries, and corresponding salary grade information for all employees from the employee table
• How to use a self-join to retrieve the superior information of the employee named "SMITH"
• How to use a left outer join to retrieve all department information and their corresponding employee information

🏆 Achievements

After completing this project, you will be able to:

• Understand the different types of join queries and when to use them
• Retrieve and manipulate data from multiple tables in a database using join queries
• Apply advanced SQL concepts to solve complex data retrieval problems

Access MySQL and Import the Data

In this step, you will learn how to access the MySQL database and import the personnel data.

1. Open a terminal and run the following command to start the MySQL service:

sudo service mysql start

2. Access the MySQL command-line interface using the sudo command without any password:

sudo mysql

3. Import the personnel data from the /home/labex/project/personnel.sql file into the MySQL database:

SOURCE /home/labex/project/personnel.sql;

Now you have successfully accessed the MySQL database and imported the personnel data. You can proceed to the next step.

Retrieve Employee Names, Hire Dates, and Department Names

In this step, you will learn how to use an equi-join (inner join) to retrieve the names, hire dates, and corresponding department names from the employee table.

1. Open the join_query.sql file in a text editor and add the following code:

SELECT e.ename AS `Name`, e.hiredate AS HireDate, d.dname AS DepartmentName
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

This query performs an inner join between the emp and dept tables, matching the deptno column from the emp table with the deptno column from the dept table. The resulting output will include the employee name, hire date, and department name.

2. Save the join_query.sql file.

Retrieve Employee Names, Salaries, and Salary Grades

In this step, you will learn how to use a non-equi join to retrieve the names, salaries, and corresponding salary grade information for all employees from the employee table.

1. Open the join_query.sql file and add the following code below the previous query:

SELECT e.ename AS `Name`, e.sal AS Salary,
       CASE s.grade
           WHEN 1 THEN 'A'
           WHEN 2 THEN 'B'
           WHEN 3 THEN 'C'
           WHEN 4 THEN 'D'
           WHEN 5 THEN 'E'
           ELSE 'Unknown'
       END AS Grade
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

This query performs a non-equi join between the emp and salgrade tables, matching the employee's salary (e.sal) with the salary grade range (s.losal and s.hisal). The CASE statement is used to convert the numeric salary grade to a corresponding letter grade.

2. Save the join_query.sql file.

Retrieve the Superior Information of the Employee Named 'SMITH'

In this step, you will learn how to use a self-join to retrieve the superior information of the employee named "SMITH".

1. Open the join_query.sql file and add the following code below the previous queries:

SELECT e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno
FROM emp e1
JOIN emp e2 ON e1.empno = e2.mgr
WHERE e2.ename = 'SMITH';

This query performs a self-join on the emp table, matching the empno column of one employee (e1) with the mgr column of another employee (e2). The WHERE clause filters the results to only include the superior information of the employee named "SMITH".

2. Save the join_query.sql file.

Retrieve All Department Information and Their Corresponding Employee Information

In this step, you will learn how to use a left outer join to retrieve all department information and their corresponding employee information. If a department does not have any employees, the employee information will be filled with NULL.

1. Open the join_query.sql file and add the following code below the previous queries:

SELECT e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno,
       d.deptno AS deptno_dept, d.dname, d.loc
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno;

This query performs a left outer join between the dept and emp tables, matching the deptno column from both tables. The resulting output will include all department information, along with the corresponding employee information. If a department does not have any employees, the employee information will be filled with NULL.

2. Save the join_query.sql file.

Now you have completed all the tasks in the project. You can run the join_query.sql script in the MySQL command-line interface to see the results of the queries:

MariaDB [personnel]> SOURCE /home/labex/project/join_query.sql;

Summary

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