Review: the result type of a SELECT command in SQL

  • The type of the result of a SELECT command is a bag of tuples

  • A bag is like a set, but allows for duplicate values

    Example: 

       select dno             +-----+
 from   employee        | dno |
                        +-----+
			|   5 |
			|   5 |
			|   4 |
			|   4 |
			|   5 |
			|   5 |
			|   4 |
			|   1 |
			+-----+

Set functions

  • Set function:   (should be called bag functions ?)

      • A set function is a function on a bag of values

      • A set function returns one scalar value computed on the bag of values

  • SQL has the following set functions: 

     SUM(bag of numerical values):  computes the sum of the values
 AVG(bag of numerical values):  computes the average of the values
 MAX(bag of numerical values):  finds the maximum of the values
 MIN(bag of numerical values):  finds the minimum of the values
 COUNT(bag of tuples):          computes the cardinaility of the bag 
 COUNT(DISTINCT set of tuples): computes the cardinatity
                                after removing duplicates

Syntax on using set functions in a SELECT command

 

  • Syntax of the SELECT command without set functions: 

         select <list of attributes>      
   from   <list of relations>
  [where  <tuple-condition>]

  • Syntax when you use a set function in the SELECT list: 

         select ... , SETFUNC(attr),  ...  
   from   <list of relations>
  [where  <tuple-condition>]

Example of the use of set functions

 

  • Query to find all salary values of employees: 

       SELECT salary
 FROM   employee

  • Query to find the total salary, average salary, highest salary, lowest salary, number of salary values of employees: 

       SELECT sum(salary), avg(salary), max(salary),    
        min(salary), count(salary), count(distinct salary)  
 FROM   employee

Set functions in subqueries

  • Set functions are often used in subqueries

  • A subquery is a SQL query used within another SQL query

  • A subquery is always enclosed between brackets: 

        ( SELECT ....
    FROM ... )

  • In general, a subquery will return a bag/set of values

    Therefore: we will normally use the IN clause with subqueries

  • However:   a subquery that returns one value can be used to replace a number

How to use set functions in subqueries

  • Find the highest salary paid to all employees

  • Find the fname, lname of employee(s) that earn $55000

  • Find the fname, lname of employee(s) that earn the highest salary among all employees (use a subquery)

  • Find the highest salary paid to employees in the Research department

  • Find the fname, lname of employee(s) in the Research department that earn $40000

  • Find the fname, lname of employee(s) in the Research department that earn the highest salary among all employees in the Research department (use a subquery)

More practice queries

 

  • Find the number of employees in the company

  • Find the number of employees in the Research department

  • Find the information on the oldest employee in the company

  • Find the information on the oldest employee in the Research department

A very advanced and challenging query

  • Find the fname, lname, dno, salary of the employee that earn the highest salary in dno=4

  • Find the fname, lname, dno, salary of the employee that earn the highest salary in dno=5

    Observe the similarity of the 2 queries....

    Try to use this knowledge to solve the following tough query....

  • Find the fname, lname, dno, salary of the employee that earn the highest salary in his/her department

    Hint: you can use the dno from the outer SELECT query inside the inner SELECT query