Example 1: querying with set functions  

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

  • Solution:

  Example 1: querying with set functions  

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

    (1) Find the salaries for the employees in the "Research" department: 

           SELECT salary	
       FROM   employee, department	
       WHERE  dno = dnumber	
   	 AND  dname = 'Research'

  Example 1: querying with set functions  

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

    (2) Compute the max( ) and avg( ) on this set of values: 

           SELECT salary	
       FROM   employee, department	
       WHERE  dno = dnumber	
   	 AND  dname = 'Research'


    
       SELECT SUM(salary), AVG(salary)
       FROM   employee, department			      
       WHERE  dno = dnumber	      
   	 AND  dname = 'Research'

  Example 2: querying with set functions  

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

  • Solution:

  Example 2: querying with set functions  

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

    (1) This query finds the employees who are in the Research department: 

        SELECT fname, lname
    FROM   employee
    WHERE  dno IN ( SELECT dnumber
                    FROM   department
		    WHERE  dname = 'Research' )

  Example 2: querying with set functions  

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

    (2) Finds the employees (in the Research) who also earn more than the average salary: 

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

  Example 2: querying with set functions  

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

    Note: the keyword ALL can be omitted because the subquery returns a single value: 

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

  Example 3: querying with set functions  

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

  • Solution:

  Example 3: querying with set functions  

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

  • Wrong solution: 

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



 Problem:

    The employee may not work in the Research dept

  Example 2: querying with set functions  

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

    (1) This query finds the employees who are in the Research department: 

        SELECT fname, lname
    FROM   employee
    WHERE  dno IN ( SELECT dnumber
                    FROM   department
		    WHERE  dname = 'Research' )

  Example 2: querying with set functions  

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

    (2) This subquery finds the average salary of employees in in the Research department: 

        SELECT fname, lname
    FROM   employee
    WHERE  dno IN ( SELECT dnumber
                    FROM   department
		    WHERE  dname = 'Research' )

 Avg salary of employees in Research dept:

                        ( SELECT  AVG(salary)
                          FROM   employee, department 
			  WHERE  dno = dnumber
			    AND  dname = 'Research' )

  Example 2: querying with set functions  

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

    (3) Solution: 

        SELECT fname, lname
    FROM   employee
    WHERE  dno IN ( SELECT dnumber
                    FROM   department
		    WHERE  dname = 'Research' )

    AND

           salary   >   ( SELECT  AVG(salary)
                          FROM   employee, department 
			  WHERE  dno = dnumber
			    AND  dname = 'Research' )