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:

 Is considered as the same as an atomic value (= scalar)

• Therefore, in this case, we can also write:

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