CS457 Syllabus & Progress

Example of queries with a `having` clause (plus alternative ways to formulate the same query)

Example of group by - having queries

Query 1:

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
Solution: 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

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:

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 | +-----+------------+
Solution: SELECT dname FROM department WHERE dnumber IN (select dno from employee group by dno having count(*) >= 3 ) +----------------+ | dname | +----------------+ | Research | | Administration | +----------------+

Alternate solution without using having:

(Again, we use 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 | +----------------+

So there are different ways to solve a query...

Example of queries with a having clause (plus alternative ways to formulate the same query)

Example of queries with a `having` clause (plus alternative ways to formulate the same query)