---

Meaning of an SQL query with all its clauses

---

• Processing of a SQL query that has group by and having clauses
  • 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

    ---

  • The processing of the SQL SELECT command with GROUP BY-HAVING claus is as follows:

    Execute the basic query with the tuple condition: SELECT ... FROM relation-list WHERE tuple-boolean-condition This query will select a set of tuples that satisfy the tuple (WHERE) condition Group the selected tuples according to their values in the grouping attributes : The grouping attributes are given in the GROUP BY clause: GROUP BY grouping-attribute-list Select the groups that satisfies the group condition given in the HAVING clause: HAVING group-condition The group condition is applied to each group: Groups that do not satisfy the HAVING group condition are removed) The SELECT clause may contain set functions: These set functions are applied to the selected groups

    ---

    ---

  • Example Query processing:

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

  • How is the query processed:

    The employee relation: ssn fname lname sex dno --------- ------ -------- --- ----------- 123456789 John Smith M 5 333445555 Frankl Wong M 5 999887777 Alicia Zelaya F 4 987654321 Jennif Wallace F 4 666884444 Ramesh Narayan M 5 453453453 Joyce English F 5 987987987 Ahmad Jabbar M 4 888665555 James Borg M 1 1. Execute the basic query SELECT * FROM employee WHERE sex = 'M' Result: ssn fname lname sex dno --------- ------ -------- --- ----------- 123456789 John Smith M 5 333445555 Frankl Wong M 5 666884444 Ramesh Narayan M 5 987987987 Ahmad Jabbar M 4 888665555 James Borg M 1 2. Group the selected tuples by DNO: ssn fname lname sex dno --------- ------ -------- --- ----------- 123456789 John Smith M 5 333445555 Frankl Wong M 5 666884444 Ramesh Narayan M 5 987987987 Ahmad Jabbar M 4 888665555 James Borg M 1 3. Select the groups that satisfy: "having count(*) > 2 ssn fname lname sex dno --------- ------ -------- --- ----------- 123456789 John Smith M 5 333445555 Frankl Wong M 5 666884444 Ramesh Narayan M 5 987987987 Ahmad Jabbar M 4 (COUNT(*) <=2) 888665555 James Borg M 1 (COUNT(*) <=2) 4. The SELECT expression are computed: SELECT dno, sum(salary) Result: dno sum(salary) ----------- ------------------------------- 5 108900.00