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



Monday, October 17, 2016

28 Multiple-Row Subqueries

  • ·         An inner query that compares multiple columns at the same time- Pair-wise multiple-column subquery
  • ·         An inner query that compares the multiple columns one at a time in different subqueries- Non-pair-wise multiple-column subquery
  • ·         An inner query that returns one or more rows to the outer query-multiple-row subquery


1.  What will be returned by a query if it has a subquery that returns a null?
a) Say it is a single row inner query, And I try to use  =, !=  etc :-There are no rows returned here.
Not even the [ NULL = NULL  |##|
  NULL != NULL]  could be evaluated here. For this case I will have to use [column IS NULL |##|  COLUMN IS NOT NULL]
b) Say it is a single row inner query, And I try to use  >, >=,<, <=  etc:- There are no rows returned here.
I may have to use NVL to execute comparison with something valid. Based on what is the need of my query, I may use NVL in case a) above also.
c) Say it is multiple row subquery and I am using IN, ANY, ALL: The items in the collection returned by subquery are ignored which are NULL- in the individual internal comparisons.
d) When a multiple-row subquery uses the NOT IN operator (equivalent to <>ALL), if one of the values returned by the inner query is a null value, the entire query returns: No rows returned
Example to support d:
SELECT commission_pct  FROM employees WHERE department_id  IS NULL  OR  department_id  = 50 
gives

SELECT *  FROM  employees  WHERE  commission_pct  IN (SELECT commission_pct  FROM employees WHERE department_id  IS NULL  OR  department_id  = 50)
gives

SELECT *  FROM  employees  WHERE  commission_pct  NOT IN (SELECT commission_pct  FROM employees WHERE department_id  IS NULL  OR  department_id  = 50)
gives

SELECT *  FROM  employees  WHERE  commission_pct  = 0.2
gives



Now execute SELECT *  FROM  employees  WHERE  commission_pct  NOT IN (SELECT NVL(commission_pct,0)  FROM employees WHERE department_id  IS NULL  OR  department_id  = 50)

gives



e) Say it is multiple row subquery and I am using NOT EXISTS / EXISTS: EXISTS and NOT EXISTS target presence of one or more rows, rather than its value.
Below mentioned two queries gives same result set:
SELECT * FROM wf_countries otter WHERE EXISTS (SELECT NULL FROM dual);
SELECT * FROM wf_countries otter;
And below mentioned gives no data!
SELECT * FROM wf_countries otter WHERE NOT EXISTS (SELECT NULL FROM dual);
2.  Write a query that returns jazz and pop songs. Write a multi-row subquery and use the d_songs and d_types tables. Include the id, title, duration, and the artist name.
SELECT id, title, duration, artist
FROM d_songs
WHERE type_code IN ( SELECT code FROM d_types WHERE description IN ('Jazz', 'Pop'));
ID
TITLE
DURATION
ARTIST
45
Its Finally Over
5 min
The Hobbits
46
Im Going to Miss My Teacher
2 min
Jane Pop
48
Meet Me At the Altar
6 min
Bobby West

Gives similar result as:
SELECT d_songs.id, d_songs.title, d_songs.duration, d_songs.artist
FROM d_songs INNER JOIN d_types ON d_songs.type_code = d_types.code
WHERE d_types.description in ('Jazz', 'Pop');
3.  Find the last names of all employees whose salaries are the same as the minimum salary for any department.
SELECT last_name
FROM employees
WHERE salary in ( SELECT MIN(salary) FROM employees GROUP BY department_id);
LAST_NAME
Kochhar
De Haan
Whalen
Gietz
Taylor
Grant
Vargas
Ernst
Lorentz
Fay

IF I assume here that, when I say xyz employee has minimum salary, this problem wants me to say xyz has minimum salary in his own department.  So, I am supposed to write multiple-column pair- wise subquery.

SELECT last_name, NVL(department_id,-1),salary
FROM employees
WHERE (NVL(department_id,-1), salary) IN ( SELECT NVL(department_id,-1), MIN(salary) FROM employees GROUP BY NVL(department_id,-1));
LAST_NAME
NVL(DEPARTMENT_ID,-1)
SALARY
Kochhar
90
17000
De Haan
90
17000
Whalen
10
4400
Gietz
110
8300
Taylor
80
8600
Grant
-1
7000
Vargas
50
2500
Lorentz
60
4200
Fay
20
6000

Similar results are also retrieved from correlated subquery: SELECT oe.last_name, NVL(oe.department_id,-1), oe.salary
FROM employees oe
WHERE  oe.salary = ( SELECT MIN(sube.salary)  FROM employees sube WHERE NVL(sube.department_id,-1) = NVL(oe.department_id,-1));
I preferred multiple-column pair- wise subquery over correlated subquery here because correlated subquery is executed again and again for each intermediate result from outer query and then included in final results if subquery is satisfied.

4.  Which Global Fast Foods employee earns the lowest salary? Hint: You can use either a single-row or a multiple-row subquery.


SELECT last_name
FROM f_staffs
WHERE NVL(salary,0) = ( SELECT MIN(NVL(salary,0)) FROM f_staffs);
LAST_NAME
Doe

5.  Place the correct multiple-row comparison operators in the outer query WHERE clause of each of the following:

a.  Which CDs in our d_cds collection were produced before “Carpe Diem” was produced?
WHERE year __________ (SELECT year ...
YEAR is VARCHAR2(4), but results with TO_NUMBER and without it are the same.
SELECT *
FROM d_cds
WHERE TO_NUMBER(year) < ( SELECT TO_NUMBER(year) FROM d_cds where title = 'Carpe Diem');

b.  Which employees have salaries lower than any one of the programmers in the IT department?
WHERE salary __________(SELECT salary ...
SELECT last_name, salary
FROM employees
WHERE salary < ANY ( SELECT salary FROM employees where department_id  = (SELECT department_id FROM departments WHERE department_name = 'IT'));
LAST_NAME
SALARY
Whalen
4400
Gietz
8300
Taylor
8600
Grant
7000
Mourgos
5800
Rajs
3500
Davies
3100
Matos
2600
Vargas
2500
Ernst
6000
Lorentz
4200
Fay
6000

Please note here, pink portion here gives results:
SALARY
9000
6000
4200


c.  What CD titles were produced in the same year as “Party Music for All Occasions” or “Carpe Diem”?
WHERE year __________(SELECT year ...
SELECT title
FROM d_cds
WHERE TO_NUMBER(year) IN ( SELECT  TO_NUMBER(year) FROM d_cds where title IN ( 'Carpe Diem', 'Party Music for All Occasions'));
d.  What song title has a duration longer than every type code 77 title?
WHERE duration _________(SELECT duration ...
duration is VARCHAR2(20)
SELECT title, duration
FROM d_songs
WHERE TO_NUMBER(REPLACE(duration,' min','')) > ALL ( SELECT  TO_NUMBER(REPLACE(duration,' min','')) FROM d_songs where type_code = 77);
6.  If each WHERE clause is from the outer query, which of the following are true?

____a.  WHERE size > ANY -- If the inner query returns sizes ranging from 8 to 12, the value 9 could be returned in the outer query. TRUE. Everything above 8 is ok


____b.  WHERE book_number IN -- If the inner query returns books numbered 102, 105, 437, and 225 then 325 could be returned in the outer query. FALSE. Only 102, 105, 437, and 225 are OK

____c.  WHERE score <= ALL -- If the inner query returns the scores 89, 98, 65, and 72, then 82 could be returned in the outer query. FALSE.  Anything below and equal to 65 is OK

____d.  WHERE color NOT IN -- If the inner query returns red, green, blue, black, and then the outer query could return white. TRUE

____e.  WHERE game_date = ANY -- If the inner query returns 05-Jun-1997, 10-Dec-2002, and 2-Jan-2004, then the outer query could return 10-Sep-2002. FALSE


7.  The goal of the following query is to display the minimum salary for each department whose minimum salary is less than the lowest salary of the employees in department 50. However, the subquery does not execute because it has five errors. Find them, correct them, and run the query.

SELECT department_id
FROM employees
WHERE MIN(salary)
HAVING MIN(salary) >
GROUP BY department_id
SELECT MIN(salary)
WHERE department_id < 50;

a)
ORA-00934: group function is not allowed here
Remove WHERE MIN(salary)
b)
ORA-00936: missing expression
This is from HAVING
Move the subquery in having and change the sign.
c)
ORA-00923: FROM keyword not found where expected
Put FROM employees in subquery.
d)
But I want minimum salary of department 50, change the where clause in subquery.
e)
But I want minimum salary instead of department no.
Change SELECT of outer query.

Even after correction, there are no results, because 2500 is the lowest salary and that employee is of department_id = 50  J
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) < (SELECT MIN(salary) FROM employees WHERE department_id = 50);

8.  Which statements are true about the subquery below?

SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

______ a.  The inner query could be eliminated simply by changing the WHERE clause to WHERE MIN(salary). FALSE

______ b.  The query wants the names of employees who make the same salary as the smallest salary in any department. TRUE

______ c.  The query first selects the employee ID and last name, and then compares that to the salaries in every department.
FALSE – subquery is executed first
______ d.  This query will not execute.
TRUE, it won’t execute. This will require multiplerow subquery as in example 3 above.
9.  Write a pair-wise subquery listing the last_name, first_name, department_id, and manager_id for all employees that have the same department_ id and manager_id as employee 141. Exclude employee 141 from the result set.
SELECT last_name, first_name, department_id, manager_id
FROM employees
WHERE (NVL(department_id,-1), NVL(manager_id,-1)) = (SELECT NVL(department_id,-1), NVL(manager_id,-1) FROM employees WHERE employee_id = 141) AND employee_id != 141
10. Write a non-pair-wise subquery listing the last_name, first_name, department_id, and manager_id for all employees that have the same department_ id and manager_id as employee 141.
Let’s assume that I have to exclude employee with id 141 here too:
SELECT last_name, first_name, department_id, manager_id
FROM employees

WHERE NVL(department_id,-1) = (SELECT NVL(department_id,-1) FROM employees WHERE employee_id = 141)

AND NVL(manager_id,-1) = (SELECT NVL(manager_id,-1) FROM employees WHERE employee_id = 141)


 AND employee_id != 141;