Example query 1 - Bottom up solution  

  • Finds the fname and lname of employees that have ≥ 2 dependents.

    Sample inputs for concreteness: 

     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       |
       +-----------+-----------+------+-----------+--------------+


 Answer:   employees  333445555 and 123456789

  Example query 1 - Bottom up solution  

  • Finds the fname and lname of employees that have ≥ 2 dependents.

    (1) Find the # dependents for each employee: 

        SELECT    essn, COUNT(name)
    FROM      dependent
    GROUP BY  essn


    +-----------+-------------+                   
    | essn      | COUNT(name) |
    +-----------+-------------+
    | 123456789 |           3 |
    | 333445555 |           3 |
    | 987654321 |           1 |
    +-----------+-------------+

  Example query 1 - Bottom up solution  

  • Finds the fname and lname of employees that have ≥ 2 dependents.

    (2) Find the employee (SSN) who has # dependents ≥ 2: 

        SELECT    essn, COUNT(name)
    FROM      dependent
    GROUP BY  essn
    HAVING    count(name) >= 2

    +-----------+-------------+                   
    | essn      | COUNT(name) |
    +-----------+-------------+
    | 123456789 |           3 |
    | 333445555 |           3 |
    +-----------+-------------+

  Example query 1 - Bottom up solution  

  • Finds the fname and lname of employees that have ≥ 2 dependents.

    (3) Get the fname and lname using the foreign key essn: 

        SELECT    essn, COUNT(name)
    FROM      dependent
    GROUP BY  essn
    HAVING    count(name) >= 2

    +-----------+-------------+                   
    | essn      | COUNT(name) |
    +-----------+-------------+
    | 123456789 |           3 |
    | 333445555 |           3 |
    +-----------+-------------+


    SELECT fname, lname
    FROM   employee
    WHERE  ssn IN ( SELECT    essn
    		    FROM      dependent
    		    GROUP BY  essn
    		    HAVING    count(name) >= 2 )

  Example query 1 - Top down solution  

  • Finds the fname and lname of employees that have ≥ 2 dependents.

    High level SQL query solution: 

        SELECT fname, lname
    FROM   employee E
    WHERE  #{dependents of employee E} >= 2

  Example query 1 - Top down solution  

  • Finds the fname and lname of employees that have ≥ 2 dependents.

    # elements in a set   ≡   count(set) in SQL: 

        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

  Example query 1 - Top down solution  

  • Finds the fname and lname of employees that have ≥ 2 dependents.

    Use a SELECT COUNT( ) query to find # dependents of employee 'E': 

        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

  Example query 2 - Bottom up solution  

  • Find dname of departments that have 3 or more employees.

    Sample inputs for concreteness: 

     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 |
     +-----------+--------+---------+-----+




 Answer:   names of departments  4 and 5

  Example query 2 - Bottom up solution  

  • Find dname of departments that have 3 or more employees.

    (1) First, we find the # employees in each department: 

         select dno, count(ssn)
     from employee
     group by dno


         +-----+------------+
         | dno | count(ssn) |
         +-----+------------+
         |   1 |          1 |
         |   4 |          3 |
         |   5 |          4 |
         +-----+------------+

  Example query 2 - Bottom up solution  

  • Find dname of departments that have 3 or more employees.

    (2) Then, we find the departments (dno) that have ≥ 3 employees: 

         select dno, count(ssn)
     from employee
     group by dno
     having count(*) >= 3

         +-----+------------+
         | dno | count(ssn) |
         +-----+------------+
         |   4 |          3 |
         |   5 |          4 |
         +-----+------------+

  Example query 2 - Bottom up solution  

  • Find dname of departments that have 3 or more employees.

    (3) Get the department name using the foreign key essn: 

         select dno, count(ssn)
     from employee
     group by dno
     having count(*) >= 3

         +-----+------------+
         | dno | count(ssn) |
         +-----+------------+
         |   4 |          3 |
         |   5 |          4 |
         +-----+------------+


    SELECT dname
    FROM   department
    WHERE  dnumber  IN  
                (select dno           
	         from employee
	         group by dno
	         having count(*) >= 3 )

  Example query 2 - Top down solution  

  • Find dname of departments that have 3 or more employees.

    High level SQL query solution: 

        SELECT dname
    FROM   department D
    WHERE  #{employees in department D} >= 3

  Example query 2 - Top down solution  

  • Find dname of departments that have 3 or more employees.

    # elements in a set   ≡   count(set) in SQL: 

        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

  Example query 2 - Top down solution  

  • Find dname of departments that have 3 or more employees.

    Use a SELECT COUNT( ) query to find # employees in department 'D': 

        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