SQL database queries basic question

Question

Relational Schema:

• Employee (Enum, Ename)
• VentingMac (Enum, Cokename, day)

The attribute Enum in VentingMac relation is a foreign key referencing the relation Employee.

I want to list out all the employee names (Ename), who drink more than 3 (distinct) coke on the same day (assuming they wont drink the same coke each day)

Answer 1

Using EXISTS

SELECT e.ename
  FROM EMPLOYEE e
 WHERE EXISTS(SELECT NULL
                FROM VENTINGMAC vm
               WHERE vm.enum = e.enum
            GROUP BY vm.day, vm.enum
              HAVING COUNT(vm.coke) > 3)

USING IN

SELECT e.ename
  FROM EMPLOYEE e
 WHERE e.enum IN (SELECT vm.enum
                    FROM VENTINGMAC vm
                GROUP BY vm.day, vm.enum
                  HAVING COUNT(vm.coke) > 3)

Using a JOIN:

SELECT e.ename
  FROM EMPLOYEE e
  JOIN (SELECT vm.enum
          FROM VENTINGMAC vm
      GROUP BY vm.day, vm.enum
        HAVING COUNT(vm.coke) > 3) y ON y.enum = e.enum

Breakdown

The meat of the query lies in checking the VENTINGMAC table, particularly with aggregate functions (which means needing a GROUP BY clause). I'm assuming VENTINGMAC.day contains a DATE (year, month, day) only -- no time portion to complicate things. You need to group on the person, and day in the table to be able to count the coke values like you see in the HAVING clause.