CS457 Syllabus & Progress

## Example SQL queries using Set Functions

• Example Queries with Set Function

• Query 1:

 Find the total salary and the average salary of the salary paid to employees in the "Research" department.

• Solution:

 ``` 1. Find the salaries for the employees in the "Research" department: SELECT salary FROM employee, department WHERE dno = dnumber AND dname = 'Research' 2. Apply max and avg on this set of values: SELECT SUM(salary), AVG(salary) FROM employee, department WHERE dno = dnumber AND dname = 'Research' ```

• Query 2:

 Find the fname and lname of the employee in the Research department that earns more than the average salary in the (whole) company

• Solution:

 ``` This query finds the average salary paid to employees in the whole company: SELECT AVG(salary) FROM employee This query finds the employees in the Research department: SELECT fname, lname FROM employee WHERE dno IN ( SELECT dnumber FROM department WHERE dname = 'Research' ) This query finds the employees in the Research department who earn more than the average salary in company: SELECT fname, lname FROM employee WHERE dno IN ( SELECT dnumber FROM department WHERE dname = 'Research' ) AND salary > ALL (SELECT AVG(salary) FROM employee) ```

Simplification ---- because:

 ``` SELECT AVG(salary) FROM employee ```

returns one single value, we can omit the ALL keyword:

 ``` SELECT fname, lname FROM employee WHERE dno IN ( SELECT dnumber FROM department WHERE dname = 'Research' ) AND salary > ALL (SELECT AVG(salary) FROM employee) ```

• Query 3:

 Find the fname and lname of the employees in the 'Research' department that earns more than the average salary within the 'Research' department

• Wrong solution:

 ``` The following query finds the average salary paid to employees in the 'Research' department: SELECT AVG(salary) FROM employee, department WHERE dno = dnumber AND dname = 'Research' WRONG solution: SELECT fname, lname FROM employee WHERE salary > (SELECT AVG(salary) FROM employee, department WHERE dno = dnumber AND dname = 'Research') ```

Reason: :

• The employee whose salary is greater than the average salary of the Research department, may:

 not work for the Research department !!!

• Correct solution:

 ``` SELECT fname, lname FROM employee WHERE dno IN ( SELECT dnumber /* Emp must be in the Research dept */ FROM department WHERE dname = 'Research' ) AND salary > (SELECT AVG(salary) FROM employee, department WHERE dno = dnumber AND dname = 'Research') ```