ITPEC FE Morning April 2018 Question 29

3 — three employee rows join to a department on floor 2.

The WHERE clause joins each employee to its department and keeps only departments on floor 2:

• •Floor 2 departments: Information systems, Accounting, Legal affairs, Procurement
• •Matching employees: 11002 (Accounting), 11005 (Information systems), 12002 (Information systems)
• •Legal affairs and Procurement have no employees, so they add no rows to the join

COUNT(*) counts the rows of the joined result: 3.

Answer (c)

Why not others:

• •(a) 1 — too few: three employee rows satisfy both conditions, one in Accounting and two in Information systems
• •(b) 2 — counts the distinct departments that have matching employees (Accounting and Information systems) instead of the employee rows
• •(d) 4 — counts the floor 2 departments, including the two that have no employees and so drop out of the join

Key rule: COUNT(*) on a join counts the joined rows: a department with two employees contributes two rows, and a department with none contributes zero.

Databases · AI-written, may contain errors