Question

Quis -2 EMPLOYEES EMPLOYEE ID NUMBER(6.0 FIRST NAME VARCHAR NTE) CONSTRAINT EMPLASTNAME NN NOT NULL ENA LAST NAME VARCHAI CONSTRAINT EMPEMAIL NN NOT NULL ENABLE "EMAIL VARCHAR CHARZI29 BYTEL "PHONE NUMBE CONSTRAINT EMP HIRE DATE NN NOT NULL ENABLE "HERE DAT Aaue BYTE) CONS TRAINT EMPJOB NN NOT NULL ENABLE 3o8 MBER3 BYTE) SA OMMISSION PCT NUMBER2.23 "MANAGER 1D NUMBER(6.0), "DEPARTMENT ID NUMBER(4,0) 1. Return the names of employees who have the 2nd highest salary Sεutct 2. For every employee, return the difference between the employee's salary and the average salary of the working in same department as employee.

Answer 1

Please find the response below:

A. To fetch the employees names having the second highest salary: It uses a nested query.The first part of the query iterates through the maximum of all salaries excluding the maximum salary which is calculated by the second part of the query.

SELECT FIRST_NAME,LAST_NAME, MAX(SALARY) AS salary
  FROM EMPLOYEES
 WHERE salary < (SELECT MAX(SALARY)
                 FROM EMPLOYEES);

B. To return the difference between employee salary and average salary of the employees working in the same department as employee. The first part of the query stores the employee details and salary difference between employee's salary and average salary of employees working in the same department as object x. The second part of the query calculates the average salary of employees working in the department = x.DEPARTMENT_ID and stores it in object y.

SELECT x.EMPLOYEE_ID,
           x.FIRST_NAME, 
           x.LAST_NAME, 
           x.DEPARTMENT_ID, 
          (x.SALARY- y.SALARY) as sal_diff
FROM EMPLOYEES AS x
INNER JOIN (
     SELECT DEPARTMENT_ID, avg(SALARY) as SALARY from EMPLOYEES group by    
             DEPARTMENT_ID) as y
    ON x.DEPARTMENT_ID= y.DEPARTMENT_ID