Execution of a general SQL query  

  • The general SQL query with all 5 clauses: 

       SELECT    grouping-attributes or set-functions     
   FROM      relation-list		     
   WHERE     tuple-boolean-condition	     
   GROUP BY  grouping-attribute-list
   HAVING    group-boolean-condition

    Question:
     

    • How is the general form of the SQL query executed ?

  Execution of a general SQL query  

  • The general SQL query with all 5 clauses: 

       SELECT    grouping-attributes or set-functions     
   FROM      relation-list		          
   WHERE     tuple-boolean-condition	          
   GROUP BY  grouping-attribute-list
   HAVING    group-boolean-condition

    (1) Execute the following basic query with the tuple condition:
      

      SELECT  *
  FROM   relation-list
  WHERE  tuple-boolean-condition

    This query will retrieve all attribute values of the set of tuples that satisfy the tuple (WHERE) condition

  Execution of a general SQL query  

  • The general SQL query with all 5 clauses: 

       SELECT    grouping-attributes or set-functions 
   FROM      relation-list		          
   WHERE     tuple-boolean-condition	          
   GROUP BY  grouping-attribute-list               
   HAVING    group-boolean-condition

    (2) Group the selected tuples according to their values in the grouping attributes :
      

       GROUP BY  grouping-attribute-list

    The result set consists of the set of tuples that satisfy the tuple (WHERE) condition separated into groups based on group attribute values

  Execution of a general SQL query  

  • The general SQL query with all 5 clauses: 

       SELECT    grouping-attributes or set-functions 
   FROM      relation-list		          
   WHERE     tuple-boolean-condition	          
   GROUP BY  grouping-attribute-list
   HAVING    group-boolean-condition

    (3) Groups that satisfies the group condition in the HAVING clause are selected
      

       HAVING    group-boolean-condition

    The result set consists of the set of tuples that satisfy the tuple (WHERE) condition , separated into groups based on group attribute values that satisfies the group condition

  Execution of a general SQL query  

  • The general SQL query with all 5 clauses: 

       SELECT    grouping-attributes or set-functions  
   FROM      relation-list		          
   WHERE     tuple-boolean-condition	          
   GROUP BY  grouping-attribute-list
   HAVING    group-boolean-condition

    (4) The set function value is computed for each group and the selected grouping attribute values are output: 

       SELECT    grouping-attributes or set-functions

     

    We will examine a concrete example next

  Example processing of a general SQL query  

  • Query (find dno and total salary of male employees for depts with > 2 male employees) 

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

    Input relation: 

     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

  Example processing of a general SQL query  

  • Query (find dno and total salary of male employees for depts with > 2 male employees) 

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

    (1): process the basic SQL query 

     ssn       fname  lname    sex dno  salary    
 --------- ------ -------- --- ---  --------
 123456789 John   Smith    M     5  30000.00
 333445555 Frankl Wong     M     5  40000.00

 
 666884444 Ramesh Narayan  M     5  38000.00

 987987987 Ahmad  Jabbar   M     4  25000.00
 888665555 James  Borg     M     1  55000.00

  Example processing of a general SQL query  

  • Query (find dno and total salary of male employees for depts with > 2 male employees) 

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

    (2): Group the selected tuples by their dno values: 

     ssn       fname  lname    sex dno  salary    
 --------- ------ -------- --- ---  --------
 123456789 John   Smith    M     5  30000.00
 333445555 Frankl Wong     M     5  40000.00    Group 1
 666884444 Ramesh Narayan  M     5  38000.00

 987987987 Ahmad  Jabbar   M     4  25000.00    Group 2

 888665555 James  Borg     M     1  55000.00    Group 3

  Example processing of a general SQL query  

  • Query (find dno and total salary of male employees for depts with > 2 male employees) 

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

    (3): Select the groups that satisfy the HAVING group condition: 

     ssn       fname  lname    sex dno  salary    
 --------- ------ -------- --- ---  --------
 123456789 John   Smith    M     5  30000.00
 333445555 Frankl Wong     M     5  40000.00    Group 1 (count(*) = 3)
 666884444 Ramesh Narayan  M     5  38000.00

  Example processing of a general SQL query  

  • Query (find dno and total salary of male employees for depts with > 2 male employees) 

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

    (4): Select the dno and compute sum(salary) for qualified groups: 

     ssn       fname  lname    sex dno  salary    
 --------- ------ -------- --- ---  --------
 123456789 John   Smith    M     5  30000.00
 333445555 Frankl Wong     M     5  40000.00    Group 1 (count(*) = 3)
 666884444 Ramesh Narayan  M     5  38000.00

 Result:
     	     dno    sum(salary)                            
     	     --- ---------------        
               5    108000.00