---

Doing group by queries in another way: using a sub-query

---

• Alternative way to formualte "group by" queries
  • Fact:

    We can often formulate a query that uses a group by clause as a sub-query without using a group by clause

    I will illustrate this technique with some examples

  ---

  ---

  ---

  ---

• Formulating group by and related queries

  1. Query:

     Find the fname and lname of employees who have more than 2 dependents

     Solution:

     The depedent relation: select * from dependent +-----------+-----------+------+-----------+--------------+ | essn | name | sex | bdate | relationship | +-----------+-----------+------+-----------+--------------+ | 333445555 | Alice | F | 05-APR-76 | DAUGHTER | | 333445555 | Theodore | M | 25-OCT-73 | SON | | 333445555 | Joy | F | 03-MAY-48 | SPOUSE | | 987654321 | Abner | M | 29-FEB-32 | SPOUSE | | 123456789 | Micheal | M | 01-JAN-78 | SON | | 123456789 | Alice | F | 31-DEC-78 | DAUGHTER | | 123456789 | Elizabeth | F | 05-MAY-57 | SPOUSE | +-----------+-----------+------+-----------+--------------+ Add in the fname, lname: select fname, lname, name, relationship from dependent, employee where ssn = essn +--------+---------+-----------+--------------+ | fname | lname | name | relationship | +--------+---------+-----------+--------------+ | John | Smith | Micheal | SON | | John | Smith | Alice | DAUGHTER | | John | Smith | Elizabeth | SPOUSE | | Frankl | Wong | Alice | DAUGHTER | | Frankl | Wong | Theodore | SON | | Frankl | Wong | Joy | SPOUSE | | Jennif | Wallace | Abner | SPOUSE | +--------+---------+-----------+--------------+ Employees with > 2 dependents: (count per employee) select fname, lname, count(*) from dependent, employee where ssn = essn group by fname, lname having count(name) > 2 +--------+-------+----------+ | fname | lname | count(*) | +--------+-------+----------+ | Frankl | Wong | 3 | | John | Smith | 3 | +--------+-------+----------+

     ---

     ---

     ---

     Alternative solution using a sub-query:

     Logic: select fname, lname from employee E where # { dependents of E } > 2 To find # (cardinality) of a set, we use count( ): select fname, lname from employee E where count { dependents of E } > 2 Work ou the {dependents of E} using SQL: select fname, lname from employee E where count ( select * from dependent where essn = E.ssn ) > 2 We must re-write "count( select * )" in correct SQL syntax: select fname, lname from employee E where ( select count(*) from dependent where essn = E.ssn ) > 2 +--------+-------+ | fname | lname | +--------+-------+ | John | Smith | | Frankl | Wong | +--------+-------+

     ---

     ---

     ---

     Comment:

     The outer query select one employee at a time For each employee in the outer query, the inner query will find the group of dependents for that employee Hence: The nested query works like a group by clause !!!

     ---

     ---

     ---

     ---

  2. Query:

     Find the fname and lname of employee(s) who work less than 40 hours

     Solution:

     Works_on relation: +-----------+-----+-------+ | essn | pno | hours | +-----------+-----+-------+ | 123456789 | 1 | 32.5 | | 123456789 | 2 | 7.5 | | 333445555 | 2 | 10.0 | | 333445555 | 3 | 10.0 | | 333445555 | 10 | 10.0 | | 333445555 | 20 | 10.0 | | 999887777 | 30 | 30.0 | | 999887777 | 10 | 10.0 | | 987654321 | 30 | 20.0 | | 987654321 | 20 | 15.0 | | 666884444 | 3 | 40.0 | | 453453453 | 1 | 20.0 | | 453453453 | 2 | 20.0 | | 987987987 | 30 | 5.0 | | 987987987 | 10 | 35.0 | | 888665555 | 20 | 1.0 | +-----------+-----+-------+ Total # hours worked by each employee: select essn, sum(hours) from works_on group by essn +-----------+------------+ | essn | sum(hours) | +-----------+------------+ | 123456789 | 40.0 | | 333445555 | 40.0 | | 453453453 | 40.0 | | 666884444 | 40.0 | | 888665555 | 1.0 | <--- | 987654321 | 35.0 | <--- | 987987987 | 40.0 | | 999887777 | 40.0 | +-----------+------------+ Solution: select fname, lname from employee, works_on where ssn = essn group by ssn, fname, lname having sum(hours) < 40 +--------+---------+ | fname | lname | +--------+---------+ | James | Borg | | Jennif | Wallace | +--------+---------+

     ---

     ---

     ---

     Alternative solution using a sub-query:

     select fname, lname from employee E where sum(hours worked by employee E) < 40 select fname, lname from employee E where ( select sum(hours) from works_on where essn = E.ssn ) < 40 +--------+---------+ | fname | lname | +--------+---------+ | Jennif | Wallace | | James | Borg | +--------+---------+