Employee Hierarchy
HardYou have been hired by company XYZ as a Data Analyst. The management has provided you with the employee data and salary information listed below. You have been asked to draw a few key conclusions from the data.
Table 1: tbl_Employee
Table 2: tbl_EmployeeSalary
A new employee named Paul Desmond whose emp_id is 2 has joined the organization as a senior software engineer. The management wants to look at his reporting hierarchy up to level 3 and the salary difference between him and the reporting manager at that level. Print the full names of all reporting managers, as well as the salary differences as mentioned below. The salary amount shown in the table tbl EmployeeSalary is in the country's local currency. To keep things simple, all salaries should be converted to US dollars before calculating and presenting the results.
Note: To convert Irish currency to dollars, multiply the salary by 1.09 whereas for Indian currency, multiply by 0.012.
Desired Output:
Our solution will involve several JOIN statements and UNION ALL to combine data across tables and subqueries. For readability, we have also used multiple common table expressions (CTE) to organize our query before the final SELECT at the end to produce the result table.
Here's our complete solution to the problem:
Related questions
Employee Earnings.Related courses
