CS457 Syllabus & Progress

## Example of queries with a having clause

• Example of group by - having queries

• Query 1:

 Finds the SSN of employees that have 2 or more dependents.

• Solution:

 ``` SELECT essn, name FROM dependent +-----------+-----------+ | essn | name | +-----------+-----------+ | 333445555 | Alice | | 333445555 | Theodore | | 333445555 | Joy | | 987654321 | Abner | | 123456789 | Micheal | | 123456789 | Alice | | 123456789 | Elizabeth | +-----------+-----------+ SELECT essn, COUNT(name) FROM dependent GROUP BY essn +-----------+-------------+ | essn | COUNT(name) | +-----------+-------------+ | 123456789 | 3 | | 333445555 | 3 | | 987654321 | 1 | +-----------+-------------+ SELECT essn FROM dependent GROUP BY essn HAVING count(name) >= 2 ESSN --------- 123456789 333445555 ```

• Query 1 B:

 Find fname and lname of employees who have 2 or more dependents

• Simple solution: use subquery !!!

 ``` SELECT fname, lname FROM employee WHERE ssn IN ( SELECT essn FROM dependent GROUP BY essn HAVING count(name) >= 2 ) FNAME LNAME ------ -------- John Smith Frank Wong ```

• Notice the inner query is not correlated

• Alternate solution without using having:

(but we do need a correlated inner query):

 ``` SELECT fname, lname FROM employee E WHERE #{dependents of employee E} >= 2 SELECT fname, lname FROM employee E WHERE COUNT (set of dependents of employee E) >= 2 SELECT fname, lname FROM employee E WHERE ( SELECT count(name) FROM dependent WHERE essn = E.ssn ) >= 2 +--------+-------+ | fname | lname | +--------+-------+ | John | Smith | | Frankl | Wong | +--------+-------+ ```

This is just to show you there is more than one way to skin a cat...

• Query 2:

 Finds the dnumber of departments that have 3 or more employees.

• Solution:

 ``` Consider the employees: +-----------+--------+---------+-----+ | ssn | fname | lname | dno | +-----------+--------+---------+-----+ | 888665555 | James | Borg | 1 | | 999887777 | Alicia | Zelaya | 4 | | 987654321 | Jennif | Wallace | 4 | | 987987987 | Ahmad | Jabbar | 4 | | 123456789 | John | Smith | 5 | | 333445555 | Frankl | Wong | 5 | | 666884444 | Ramesh | Narayan | 5 | | 453453453 | Joyce | English | 5 | +-----------+--------+---------+-----+ Group the employees by their dno and count each group: select dno, count(ssn) from employee group by dno +-----+------------+ | dno | count(ssn) | +-----+------------+ | 1 | 1 | | 4 | 3 | | 5 | 4 | +-----+------------+ Select only department with >= 3 employees: select dno, count(ssn) from employee group by dno having count(*) >= 3 +-----+------------+ | dno | count(ssn) | +-----+------------+ | 4 | 3 | | 5 | 4 | +-----+------------+ ```

• Query 2 B:

 Finds the dname of departments that have 3 or more employees

• Simple solution: use subquery !!!

 ``` SELECT dname FROM department WHERE dnumber IN (select dno from employee group by dno having count(*) >= 3 ) +----------------+ | dname | +----------------+ | Research | | Administration | +----------------+ ```

• Notice the inner query is not correlated

• Alternate solution without using having:

(but we do need a correlated inner query):

 ``` SELECT dname FROM department D WHERE #{employees in department D} >= 3 SELECT dname FROM department D WHERE COUNT (set of employees in department D) >= 3 SELECT dname FROM department D WHERE ( SELECT count(ssn) FROM employee WHERE dno = D.dnumber ) >= 3 +----------------+ | dname | +----------------+ | Research | | Administration | +----------------+ ```

There are different ways to solve a query...