Using a set function
as a value in the
WHERE clause
- Introductory
query:
- Find the
average salary of
all employees in the
company
|
Solution:
select avg(salary)
from employee
avg(salary)
-------------
36500.500000
|
|
Using a set function
as a value in the
WHERE clause
- Query:
- Find fname, lname of
employees who
earn
> average salary level
in the company
|
- Answer:
|
Using a set function
as a value in the
WHERE clause
- Query:
- Find fname, lname of
employees who
earn
> average salary level
in the company
|
-
Suppose we
know that
the average salary of
all employees is equal to
$36500.50:
select fname, lname
from employee
where salary > 36500.50
|
|
Using a set function
as a value in the
WHERE clause
- Query:
- Find fname, lname of
employees who
earn
> average salary level
in the company
|
- We can
compute the
average salary value with
a
subquery:
select fname, lname
from employee
where salary > ALL ( select avg(salary)
from employee )
Note: the SQL tuple condition requires keyword ALL or ANY
because subquery returns a set of values
|
|
Using a set function
as a value in the
WHERE clause
- Query:
- Find fname, lname of
employees who
earn
> average salary level
in the company
|
-
Simplication:
if subquery returns
1 tuple, we can
omit the
ALL or
ANY
keyword:
select fname, lname
from employee
where salary > ( select avg(salary)
from employee )
|
|
❮
❯