- ·
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);
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:
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;
