Problem Statement

Consider the tables dept , emp , compensation and emp_dept of an HR database. The employer wants to know the second-highest salary paid to an employee in the month of January 2021 . Write a query to get emp_no , first_name , last_name , gender , dept_no , dept_name and the salary credited in January 2021, for every employee who was paid that second-highest amount. “Second-highest” means the second-largest distinct amount credited in January 2021. If several employees were paid that amount, all of them must be listed, ordered by emp_no ascending. Schema emp emp_no INT(11) dob DATE first_name VARCHAR(14) last_name VARCHAR(16) gender VARCHAR(8) joining_date DATE dept dept_no CHAR(4) dept_name VARCHAR(40) emp_dept emp_no INT(11) dept_no CHAR(4) from_date DATE to_date DATE ( NULL means this is the employee's current department) compensation emp_no INT(11) credited_date DATE amount INT(11) Input Format All dates are given as DD/MM/YYYY . Fields within a row are separated by a pip