---

Closer look at the grouping condition

---

• A closer look at the goruping condition
  • In this webpage, we will look at 3 queries:

    Find the name of the departments with >= 2 employees Find the name of the departments with >= 2 female employees Find the name of the departments with >= 2 employees who earn > $30000

    and see how we can form different groups.

    ---

    ---

  • You will observe that:

    To form a specific group, you must: select the set of tuple that satisfies the specific tuple condition !!!

1. Query:

   Find the name of the departments with 2 or more employees

   Solution:

   Here are the employees and their department: select fname, lname, dname from employee, department where dno=dnumber order by dname fname lname dname ------ -------- --------------- Alicia Zelaya Administration Jennif Wallace Administration Ahmad Jabbar Administration James Borg Headquarters Frankl Wong Research Ramesh Narayan Research Joyce English Research John Smith Research We need to form groups based on common dname value and count the members of each group: select dname, count(ssn) from employee, department where dno=dnumber group by dname dname count(ssn) --------------- --------------------- Administration 3 Headquarters 1 Research 4 Finally: select the groups with 2 or more members: select dname from employee, department where dno=dnumber group by dname having count(ssn) >= 2 dname --------------- Administration Research

   ---

   ---

   ---

2. Query:

   Find the name of the departments with 2 or more female employees

   Solution:

   Here are the employees and their department: select fname, lname, sex, dname from employee, department where dno=dnumber fname lname sex dname ------ -------- ------ --------------- Alicia Zelaya F Administration Jennif Wallace F Administration Ahmad Jabbar M Administration James Borg M Headquarters Frankl Wong M Research Ramesh Narayan M Research Joyce English F Research John Smith M Research These are the female employees and their department: select fname, lname, sex, dname from employee, department where dno=dnumber and sex = 'F' fname lname sex dname ------ -------- ------ --------------- Jennif Wallace F Administration Alicia Zelaya F Administration Joyce English F Research We need to form groups based on common dname value and count the members of each group: SELECT dname, count(ssn) FROM department, employee WHERE dnumber = dno AND sex = 'F' GROUP BY dname dname count(ssn) --------------- --------------------- Administration 2 Research 1 Finally, we select only those groups with >=2 members SELECT dname FROM department, employee WHERE dnumber = dno AND sex = 'F' GROUP BY dname HAVING COUNT(ssn) >= 2 DNAME --------------- Administration

   ---

   ---

   ---

   ---

3. Query:

   Find the name of the departments with 2 or more employees who earn more than $30000

   Solution:

   Employees, their dept and salary: select ssn, fname, dname, salary from employee, department where dnumber = dno ssn fname dname salary --------- ------ --------------- --------- 999887777 Alicia Administration 25000.00 987654321 Jennif Administration 43000.00 987987987 Ahmad Administration 25000.00 888665555 James Headquarters 55000.00 333445555 Frankl Research 40000.00 666884444 Ramesh Research 38000.00 453453453 Joyce Research 25000.00 123456789 John Research 30000.00 Employees who earn over 30000 and their department names: select ssn, fname, dname, salary from employee, department where dnumber = dno and salary > 30000 ssn fname dname salary --------- ------ --------------- --------- 987654321 Jennif Administration 43000.00 888665555 James Headquarters 55000.00 666884444 Ramesh Research 38000.00 333445555 Frankl Research 40000.00 From the above result, we form groups based on common dname value and count the members of each group: select dname, count(ssn) from employee, department where dnumber = dno and salary > 30000 group by dname dname count(ssn) --------------- --------------------- Administration 1 Headquarters 1 Research 2 Finally, select only the departments with 2 or more members: SELECT dname FROM department, employee WHERE dnumber = dno AND salary > 30000 GROUP BY dname HAVING COUNT(ssn) >= 2 DNAME --------------- Research