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;

27 Single-Row Subqueries

•          An inner query that returns only one row to the outer query-single-row subquery         

1.  Write a query to return all those employees who have a salary greater than that of Lorentz and are in the same department as Abel.

SELECT  first_name, last_name

FROM EMPLOYEES

WHERE salary >  (SELECT salary FROM employees WHERE last_name = 'Lorentz') AND department_id = (SELECT department_id FROM employees WHERE last_name = 'Abel');

FIRST_NAME	LAST_NAME
Eleni	Zlotkey
Ellen	Abel
Jonathon	Taylor

2.  Write a query to return all those employees who have the same job id as Rajs and were hired after Davies.

SELECT  first_name, last_name

FROM EMPLOYEES

WHERE  job_id = (SELECT job_id  FROM employees WHERE last_name = 'Rajs') AND hire_date >  (SELECT hire_date FROM employees WHERE last_name = 'Davies') ;

FIRST_NAME	LAST_NAME
Randall	Matos
Peter	Vargas

3.  What DJs on Demand events have the same theme code as event ID = 100?

SELECT id, name

FROM d_events

WHERE theme_code =  (SELECT theme_code FROM d_events WHERE id = 100);

ID	NAME
100	Peters Graduation
105	Vigil wedding

4.  What is the staff type for those Global Fast Foods jobs that have a salary less than those of any Cook staff-type jobs?

SELECT staff_type, MAX(salary)

FROM f_staffs

GROUP BY staff_type

HAVING MAX(salary) < (SELECT MAX(SALARY) FROM f_staffs WHERE staff_type = 'Cook');

STAFF_TYPE	MAX(SALARY)
Order Taker	6.75

5.  Write a query to return a list of department id’s and average salaries where the department’s average salary is greater than Ernst’s salary.

SELECT department_id, TO_CHAR(ROUND(AVG(salary),2),'$999999.99') "Average Salary"

FROM employees

GROUP BY department_id

HAVING AVG(salary) > ( SELECT salary from employees WHERE last_name = 'Ernst');

DEPARTMENT_ID	Average Salary
	$7,000.00
90	$19,333.33
20	$9,500.00
110	$10,150.00
80	$10,033.33
60	$6,400.00

6.  Return the department ID and minimum salary of all employees, grouped by department ID, having a minimum salary greater than the minimum salary of those employees whose department ID is not equal to 50.

SELECT department_id, TO_CHAR(ROUND(MIN(salary),2),'$999999.99') "Minimum Salary"

FROM employees

GROUP BY department_id

HAVING MIN(salary) > ( SELECT MIN(salary) from employees WHERE department_id != 50);

DEPARTMENT_ID	Minimum Salary
	$7,000.00
90	$17,000.00
20	$6,000.00
110	$8,300.00
80	$8,600.00
10	$4,400.00

26 Fundamentals of Subqueries

·         It accepts a value from the inner query to complete its SELECT statement.-outer query

·         An inner query that is nested within an outer query-subquery

·         Another name for a subquery-inner query

1.  What is the purpose of using a subquery?

To find the intermediate information we need to extract information we want. E.g. extracting right part in WHERE/HAVING/FROM clause.

2.  What is a subquery?

An inner query that is nested within an outer query

3.  What DJs on Demand d_play_list_items song_id’s have the same event_id as song_id 45?

SELECT song_id

FROM d_play_list_items

WHERE event_id IN(SELECT event_id FROM d_play_list_items WHERE song_id =45);

4.  Which events in the DJs on Demand database cost more than event_id = 100?

SELECT id, name

FROM d_events

WHERE cost > (SELECT cost FROM d_events WHERE id = 100);

5.  Find the track number of the song that has the same CD number as “Party Music for All Occasions.”

SELECT track

FROM d_track_listings

WHERE cd_number = (SELECT cd_number FROM d_cds WHERE title = 'Party Music for All Occasions');

6.  List the DJs on Demand events whose theme code is the same as the code for “Tropical.”

SELECT id, name

FROM d_events

WHERE theme_code = (SELECT code FROM d_themes WHERE description = 'Tropical');

7.  What are the names of the Global Fast Foods staff members whose salaries are greater than the staff member whose ID is 12?

SELECT first_name,last_name

FROM f_staffs

WHERE salary > (SELECT salary FROM f_staffs WHERE id = 12);

8.  What are the names of the Global Fast Foods staff members whose staff types are not the same as Bob Miller’s?

SELECT first_name,last_name

FROM f_staffs

WHERE staff_type != (SELECT staff_type FROM f_staffs WHERE first_name = 'Bob' AND last_name ='Miller');

9.  Which Oracle employees have the same department ID as the IT department?

SELECT first_name,last_name

FROM employees

WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

10. What are the department names of the Oracle departments that have the same location ID as Seattle?

SELECT department_name

FROM departments

WHERE location_id = ( SELECT location_id FROM locations WHERE city = 'Seattle');

11. Indicate whether the statement regarding subqueries is True or False.

a.  It is good programming practice to place a subquery on the right side of the comparison operator.

TRUE- this is a guideline, but it doesn’t break it.

b.  A subquery can reference a table that is not included in the outer query’s FROM clause.

TRUE

c.  Single-row subqueries can return multiple values to the outer query.

FALSE

25 Set Operators

• ·         operator that returns all rows from both tables and eliminates duplicates-UNION
• ·         columns that were made up to match queries in another table that are not in both tables-TO_CHAR(NULL) or TO_DATE(NULL) or TO_NUMBER(NULL) –matching the select list
• ·         operator that returns all rows from both tables, including duplicates-UNION ALL
• ·         used to combine results into one single result from multiple SELECT statements-set operators
• ·         operator that returns rows that are unique to each table-MINUS
• ·         operator that returns rows common to both tables-INTERSECT

1.  Name the different Set operators?

UNION

UNION ALL

MINUS

INTERSECT

2.  Write one query to return the employee_id, job_id, hire_date, and department_id of all employees and a second query listing employee_id, job_id, start_date, and department_id from the job_hist table and combine the results as one single output. Make sure you sup-press duplicates in the output.

SELECT employee_id, job_id, hire_date, TO_DATE(NULL)  start_date, department_id

FROM employees

UNION

SELECT employee_id, job_id, TO_DATE(NULL), start_date,  department_id

FROM job_history

ORDER BY employee_id, hire_date, start_date NULLS FIRST;

But after reading next problem I changed it to:

SELECT employee_id, job_id, hire_date, department_id

FROM employees

UNION

SELECT employee_id, job_id,  start_date,  department_id

FROM job_history

ORDER BY employee_id, hire_date;

29 rows returned

3.  Amend the previous statement to not suppress duplicates and examine the output. How many extra rows did you get returned and which were they? Sort the output by employee_id to make it easier to spot.

SELECT employee_id, job_id, hire_date, department_id

FROM employees

UNION ALL

SELECT employee_id, job_id,  start_date,  department_id

FROM job_history

ORDER BY employee_id, hire_date;

30 rows returned

The duplicate:

This can be verified by:

SELECT employee_id, job_id, hire_date, department_id, COUNT(*)

FROM

(SELECT employee_id, job_id, hire_date, department_id

FROM employees

UNION ALL

SELECT employee_id, job_id,  start_date,  department_id

FROM job_history)

GROUP BY employee_id, job_id, hire_date, department_id

HAVING COUNT(*) > 1;

4.  List all employees who have not changed jobs even once. (Such employees are not found in the job_history table)

SELECT DISTINCT employee_id

FROM employees

MINUS

SELECT DISTINCT employee_id

FROM job_history;

15 rows returned

5.  List the employees that HAVE changed their jobs at least once.

SELECT DISTINCT employee_id

FROM employees

INTERSECT

SELECT DISTINCT employee_id

FROM job_history;

5 rows returned

6.  Using the UNION operator, write a query that displays the employee_id, job_id, and salary of ALL present and past employees. If a salary is not found, then just display a 0 (zero) in its place.

SELECT employee_id, job_id, NVL(salary, 0)

FROM employees

UNION

SELECT employee_id, job_id, 0

FROM job_history

ORDER BY employee_id;