CS457 Syllabus & Progress

## Using Set Functions in the WHERE clause

• Using the result of set functions in the WHERE clause of queries

• Query:

 Find fname, lname of employees who earn more than the average salary level in the company

• Solution:

• We can find the average salary of all employees as follows:

 ``` select avg(salary) from employee avg(salary) ------------- 36500.500000 ```

• IF we knew that the average salary of the employee were \$36500.50, we could use the following query to find employee that earn more than the average:

 ``` select fname, lname from employee where salary > 36500.50 ```

Of course, we do not know the average...

• However, we can replace the average value with a sub-query that computes the average value:

 ``` select fname, lname from employee where salary > ALL (select avg(salary) from employee) ```

We need to use the keyword ALL (or ANY) per the syntax in SQL....

Note:

• Recall that when a sub-query returns a single value, then:

 ``` > ALL (select .... ) Can be shorted to: > (select .... ) ```

• Therefore, we can also write:

 ``` select fname, lname, salary from employee where salary > (select avg(salary) from employee) ```

using the fact that when the result set of the SELECT command consists of one (1) tuple, the use of ALL (or ANY) can be omitted.