---

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 in the Research dept */ FROM department WHERE dname = 'Research' ) AND salary > (SELECT AVG(salary) FROM employee, department WHERE dno = dnumber AND dname = 'Research')