Friday, October 28, 2016

29 Correlated Subqueries



1.  Explain the main difference between correlated and non-correlated subqueries ? 
As explained in Multiple-Row Subqueries Practice > Problem 3:
Correlated subquery is executed multiple times once for each intermediate result row from outer query And for each row of the intermediate results inner correlated subquery is executed, if the inner correlated subquery is satisfied, that row becomes part of final results from the whole query. A correlated subquery will get a candidate row from an outer query, execute the inner query using candidate row value, and use values from the inner query to qualify or disqualify the candidate row.
Non-correlated subquery: result/s of inner query are calculated first and reused throughout the execution of outer query.

2.  Write a query that lists the highest earners for each department. Include the last_name, department_id, and the salary for each employee.
WITH highs AS ( SELECT NVL(department_id,-1), MAX(salary) FROM employees GROUP BY NVL(department_id,-1))
SELECT last_name, department_id, salary
FROM employees
WHERE (NVL(department_id,-1), salary) IN (SELECT * FROM highs );

or

SELECT oe.last_name, oe.department_id, oe.salary
FROM employees oe
WHERE oe.salary = (SELECT MAX(ie.salary) FROM employees ie WHERE NVL(ie.department_id,-1) = NVL(oe.department_id,-1));

LAST_NAME
DEPARTMENT_ID
SALARY
King
90
24000
Whalen
10
4400
Higgins
110
12000
Abel
80
11000
Grant

7000
Mourgos
50
5800
Hunold
60
9000
Hartstein
20
13000

3.  Examine the following select statement and finish it so that it will return the last_name, department_id, and salary of employees who have at least one person reporting to them. So we are effectively looking for managers only. In the partially written SELECT statement, the WHERE clause will work as it is. It is simply testing for the existence of a row in the subquery.

SELECT (enter columns here)
FROM (enter table name here) outer
WHERE 'x' IN (SELECT 'x'
FROM (enter table name here) inner
WHERE inner(enter column name here) = inner(enter column name here)

Finish off the statement by sorting the rows on the department_id column.

SELECT outer.last_name, outer.department_id, outer.salary
FROM employees outer
WHERE outer.employee_id  IN (SELECT DISTINCT inner.manager_id
FROM employees  inner
WHERE inner.manager_id = outer.employee_id)
ORDER BY outer.department_id;


OR

SELECT outer.last_name, outer.department_id, outer.salary
FROM employees outer
WHERE outer.employee_id IN (SELECT DISTINCT inner.manager_id
FROM employees  inner
WHERE inner.manager_id IS NOT NULL)
ORDER BY outer.department_id;
4.  Using a WITH clause, write a SELECT statement to list the job_title of those jobs whose maximum salary is more than half the maximum salary of the entire company. Name your subquery MAX_CALC_SAL. Name the columns in the result JOB_TITLE and JOB_TOTAL, and sort the result on JOB_TOTAL in descending order.

Hint: Examine the jobs table. You will need to join JOBS and EMPLOYEES to display the job_title.

If I ignore the hint and job_total, which is a very ambiguous requirement here:
WITH max_calc_sal as (SELECT MAX(max_salary)/2 FROM jobs)
SELECT job_title
FROM jobs
WHERE jobs.max_salary > (SELECT * FROM max_calc_sal );
JOB_TITLE
President
Administration Vice President

But if I consider hints, I think might be job_total is not a total, but should have been called job_actual_max. Now there is a possibility that a job is there in job table but no employee is there for that job. For such case actual max could be 0.
WITH max_calc_sal AS (SELECT jobs.job_id , jobs.job_title, MAX(NVL(employees.salary,0)) AS job_actual_max FROM employees RIGHT OUTER JOIN   jobs  ON  employees.job_id =  jobs.job_id    GROUP BY jobs.job_id,jobs.job_title)
SELECT job_title, job_actual_max AS  job_total
FROM max_calc_sal
WHERE job_actual_max > (SELECT MAX(job_actual_max)/2 FROM max_calc_sal)
ORDER BY job_total  DESC;
JOB_TITLE
JOB_TOTAL
President
24000
Administration Vice President
17000
Marketing Manager
13000



No comments:

Post a Comment