Syntax of the complete SELECT command

 

  • The SELECT command that we have learned has 2 more optional clauses

  • The complete syntax of the SQL SELECT command is: 

      SELECT    <list of attributes 1>      
  FROM      <list of relations>
 [WHERE     <tuple-condition>]       ([ ] means: optional)  
 [GROUP BY  <list of attributes 2>]
 [HAVING    <group-condition>]

    We will discuss the HAVING clause in this class.

Review:   the effect of the GROUP BY operation

The effect of the GROUP BY DNO operation is:

It separates the tuples in different groups based on the value in the DNO attribute

What can you do after forming groups ?

After forming groups:

We can apply set functions on each group separately !!!

What can you do after forming groups ?

Example: total salary paid to employees per department:

We can also use set functions to select certain groups !!!

How can you select groups ?

Example: find departments with payroll > $100,000

You can see here how we used a set function to select certain groups !!!

How can you select groups ?

Example: find departments with payroll > $100,000

Expressed in SQL: 

 SELECT    dno, sum(salary)
 FROM      employee
 GROUP BY  dno
 HAVING    sum(salary) > 100000

Practice SQL queries that uses the HAVING clause

 

  • Find the name of departments that have at least 3 employees

  • Find the name of departments that have at least 2 female employees

  • Find the name of employees that have at least 2 dependents

  • Find the dno and its payroll to male employees (= total salary paid to male employees) for departments that have more than 2 male employees

    I will explain how SQL processes the last query next.

The meaning of the SQL SELECT command

  • The meaning of the SQL SELECT command: 

      SELECT    <list of attributes 1>      
  FROM      <list of relations>

 [WHERE     <tuple-condition>]       (pick out only tuples
                                     that meet this condition)  
 [GROUP BY  <list of attributes 2>]  (group the tuples with
                                     same values in attr's)
 [HAVING    <group-condition>]       (pick out only groups 
                                     that meet this condition)

    Let's look how SQL executes a specific SELECT command next.

How SQL processes a SELECT query 

    SELECT    dno, sum(salary)                   
    FROM      employee          
    WHERE     sex = 'M'
    GROUP BY  dno
    HAVING    count(*) > 2

 Result so far:

       ssn       fname  lname    sex dno salary        
       --------- ------ -------- --- --- -------- 
       123456789 John   Smith    M     5 30000.00
       333445555 Frankl Wong     M     5 40000.00
       999887777 Alicia Zelaya   F     4 25000.00
       987654321 Jennif Wallace  F     4 43000.00
       666884444 Ramesh Narayan  M     5 38000.00
       453453453 Joyce  English  F     5 25000.00
       987987987 Ahmad  Jabbar   M     4 25000.00
       888665555 James  Borg     M     1 55000.00

How SQL processes a SELECT query 

    SELECT    dno, sum(salary)                   
    FROM      employee          
    WHERE     sex = 'M'
    GROUP BY  dno
    HAVING    count(*) > 2

 Result so far:

       ssn       fname  lname    sex dno salary        
       --------- ------ -------- --- --- -------- (non-relevant
       123456789 John   Smith    M     5 30000.00  attributes
       333445555 Frankl Wong     M     5 40000.00  omitted)
       999887777 Alicia Zelaya   F     4 25000.00
       987654321 Jennif Wallace  F     4 43000.00
       666884444 Ramesh Narayan  M     5 38000.00
       453453453 Joyce  English  F     5 25000.00
       987987987 Ahmad  Jabbar   M     4 25000.00
       888665555 James  Borg     M     1 55000.00

How SQL processes a SELECT query 

    SELECT    dno, sum(salary)                   
    FROM      employee          
    WHERE     sex = 'M'         
    GROUP BY  dno
    HAVING    count(*) > 2

 Result so far:

       ssn       fname  lname    sex dno salary        
       --------- ------ -------- --- --- -------- (non-relevant
       123456789 John   Smith    M     5 30000.00  attributes
       333445555 Frankl Wong     M     5 40000.00  omitted)
 
 
       666884444 Ramesh Narayan  M     5 38000.00
   
       987987987 Ahmad  Jabbar   M     4 25000.00
       888665555 James  Borg     M     1 55000.00

How SQL processes a SELECT query 

    SELECT    dno, sum(salary)                   
    FROM      employee          
    WHERE     sex = 'M'         
    GROUP BY  dno               
    HAVING    count(*) > 2

 Result so far:

       ssn       fname  lname    sex dno salary          
       --------- ------ -------- --- --- -------- 
       123456789 John   Smith    M     5 30000.00
       333445555 Frankl Wong     M     5 40000.00  (1 group)
       666884444 Ramesh Narayan  M     5 38000.00
   
       987987987 Ahmad  Jabbar   M     4 25000.00  (1 group)   
   	
       888665555 James  Borg     M     1 55000/00  (1 group)

How SQL processes a SELECT query 

    SELECT    dno, sum(salary)                   
    FROM      employee          
    WHERE     sex = 'M'         
    GROUP BY  dno               
    HAVING    count(*) > 2      

 Result so far:

       ssn       fname  lname    sex dno salary          
       --------- ------ -------- --- --- -------- 
       123456789 John   Smith    M     5 30000.00
       333445555 Frankl Wong     M     5 40000.00  (1 group)
       666884444 Ramesh Narayan  M     5 38000.00
   
       987987987 Ahmad  Jabbar   M     4 25000.00  (1 group)    
   	
       888665555 James  Borg     M     1 55000/00  (1 group)

How SQL processes a SELECT query 

    SELECT    dno, sum(salary)     ===>     dno   sum(salary)          
    FROM      employee                      ---   ---------------
    WHERE     sex = 'M'                       5   108000.00  
    GROUP BY  dno               
    HAVING    count(*) > 2      

 Result so far:

       ssn       fname  lname    sex dno salary          
       --------- ------ -------- --- --- -------- 
       123456789 John   Smith    M     5 30000.00
       333445555 Frankl Wong     M     5 40000.00  (1 group)
       666884444 Ramesh Narayan  M     5 38000.00
   
       987987987 Ahmad  Jabbar   M     4 25000.00  (1 group)    
   	
       888665555 James  Borg     M     1 55000/00  (1 group)