CS457 Syllabus & Progress

Nesting set-functions

Nesting set functions

Consider the following query that find the number of employees in each department:

SELECT dno, COUNT(ssn) FROM employee GROUP BY dno dno COUNT(ssn) ----------- --------------------- 1 1 4 3 5 4

Question:

Find the most number of employees in a department
(In other words: return 4 for the above result)

Answer:

Fact: The result of a sub-query is a relation In other words: (SELECT dno, COUNT(ssn) FROM employee GROUP BY dno) is a relation
We can use this relation in the FROM clause: select * from (SELECT dno, COUNT(ssn) FROM employee GROUP BY dno) A // We must give a temp. relation an alias // See: click here dno COUNT(ssn) ----------- --------------------- 1 1 4 3 5 4
Let's remove the (unnecessary) DNO attribute: select * from (SELECT COUNT(ssn) FROM employee GROUP BY dno) A COUNT(ssn) --------------------- 1 3 4
Fact: The answer can be obtained by applying the MAX() set function of this result... However: the name (COUNT(ssn)) of the attribute prevents us from doing so select max(COUNT(ssn)) // COUNT(ssn) is not an identifier !!! from (SELECT COUNT(ssn) FROM employee GROUP BY dno) A
Solution: rename the attribute !!! select max(NumEmpInDept) from (SELECT COUNT(ssn) NumEmpInDept FROM employee GROUP BY dno) A Result: max(NumEmpInDept) --------------------- 4

Using the nested set function technique in a query

Query:

Find the name of the departments with most number of employees

Solution:

From the above discussion, we can find the most number of employees in any dept as follows: select max(NEmps) from (select count(ssn) NEmps from employee group by dno) A max(NEmps) --------------- 4
Here is the employees and their department: select fname, lname, dname from employee, department where dno = dnumber 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 ONLY those groups that has the most number of employees: SELECT dname FROM department, employee WHERE dno = dnumber GROUP BY dname HAVING COUNT(ssn) = (select max(NEmps) from (select count(ssn) NEmps from employee group by dno) A ) DNAME --------------- Research

Query:

Find the fname and lname of the employees with the most number of dependents

Solution - part 1: we first find the most number of dependents of any employee

1. Here are the dependents of each employee: SELECT * FROM dependent essn name sex bdate relationship --------- ---------- ------ ---------- ------------ 123456789 Micheal M 01-JAN-78 SON 123456789 Alice F 31-DEC-78 DAUGHTER 123456789 Elizabeth F 05-MAY-57 SPOUSE 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
2. Find the # dependents for each employee: SELECT COUNT(name) NDeps FROM dependent GROUP BY essn NDeps --------------------- 3 3 1
3. Use the above result as temporal relation to find the max # dependents: SELECT MAX( NDeps ) FROM ( SELECT COUNT(name) NDeps FROM dependent GROUP BY essn ) A MAX( NDeps ) --------------------- 3

Solution - part 2: find employees who has the most number of dependents

1. Here are the employees and their dependents: select fname, lname, name from employee, dependent where ssn=essn fname lname name ------ -------- ---------- Frankl Wong Alice Frankl Wong Theodore Frankl Wong Joy Jennif Wallace Abner John Smith Alice John Smith Elizabeth John Smith Micheal
2. Find the # dependents for each employee: select fname, lname, count(name) from employee, dependent where ssn=essn group by fname, lname fname lname count(name) ------ -------- --------------------- Frankl Wong 3 Jennif Wallace 1 John Smith 3
3. Select ONLY those with the most number of dependents: select fname, lname, count(name) from employee, dependent where ssn=essn group by fname, lname having count(name) = (SELECT MAX( NDeps ) FROM (SELECT COUNT(name) NDeps FROM dependent GROUP BY essn ) A ) fname lname count(name) ------ -------- --------------------- Frankl Wong 3 John Smith 3 (I left the count(name) attribute in the select list to show you their max number of dependents)