With the schemas given above, please provide SQL code to answer the following qu

Question

With the schemas given above, please provide SQL code to answer the following questions.

Q1: List the name of the employee whose salary is above his department’s average salary, which is higher than the maximum salary of another department.

Q2: List the name of the employee and his department name where the employee’s salary is below the average pay of his department that have more than five workers.

Q3: List the name of the employee and his department name where the employee receives the second highest pay of the department whose average salary is higher than the average salary of another department.

EMPLOYEES Name EMP ID FIRST NAME LAST NAME JOB MGR HIRE DATE SALARY COMM PCT DEPT ID Null? Typ NOT NULL NUMBER(4) VARCHAR2(10) VARCHAR2(10) VARCHAR2(9 NUMBER(4) DATE NOT NULL INUMBER NUMBER(7,2 NUMBER(2) DEPARTMENTS Name DEPT ID DEPT NAME DEPT LOC ID Null? Type 11? NUMBER(2) VARCHAR2(20) NUMBER

Explanation / Answer

Q1:
SELECT FIRST_NAME, DEPT_ID, salary
FROM employees e
WHERE salary > (select avg(salary) from employees e2 where e2.dept_id = e.dept_id);

Q3:
SELECT * FROM employees WHERE emp_id IN (SELECT employee_id FROM employees  
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)));

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.