Locate the fact before choosing a table
Start with the meaning of an attribute. A current supplier telephone number belongs to the supplier. A historical delivery address used for one completed order belongs to that order event. Repeated text is therefore a clue to examine, not automatic proof that one copy must be deleted. Ask whether changing one value should change all its occurrences. If the answer is yes because they are copies of the same current fact, separate the owning entity and reference its key. If the occurrences describe different historical facts, removing them can destroy required information. A normalization decision should preserve meaning before it reduces repeated storage.
Distinguish an identifier from a convenient label
A key identifies the row under declared uniqueness rules. A supplier name or telephone may look readable, yet two suppliers can share a contact and contact details can change. A repair identifier and a supplier identifier have different scopes. In a fictional Repairs table, RepairId identifies a repair, while SupplierId records its relationship to a supplier. The database must actually declare and enforce the relevant constraints; a column name ending in Id is only a name. A nullable field also needs its own rule. Do not infer uniqueness or mandatory values merely from a sample that happens to contain no duplicate or missing values.
Worked example: enumerate a left join
Use Teams T1, T2 and T3 with visits V1→T1, V2→T1 and V3→T3. For Teams LEFT JOIN Visits on TeamId, T1 produces two matching pairs. T2 produces one retained row whose visit fields are NULL. T3 produces one pair. There are four joined rows. An inner join would retain only the three matching pairs. Counting teams alone gives three, which accidentally agrees with that inner-join count for a different reason. Write the output pairs before calculating any total: (T1,V1), (T1,V2), (T2,NULL), (T3,V3). This makes both the duplication and the unmatched row visible.
Count the intended unit
On those four output rows, COUNT(*) counts four rows. COUNT(Visits.VisitId) counts the three non-NULL visit identifiers. COUNT(DISTINCT Teams.TeamId) concerns three distinct team values. These expressions answer different questions even though they operate on the same joined result. Decide whether the requested unit is a joined row, a known visit, or a distinct team before writing the aggregate. A NULL value is not the numeric value zero and not the text string 'NULL'. When a table can contain missing measures, also state how the requested calculation treats those missing values rather than silently assigning a value to them.
Transfer check: change the relationship
Add V4→T2 while retaining all previous rows. Enumerate the new output and explain what changes. The left join still has four output rows because T2's unmatched placeholder is replaced by one real visit match. COUNT(Visits.VisitId) increases from three to four. If you instead add V5→T1, the extra right-side match adds an output row. These are deliberately different changes: filling an unmatched left row and adding another match to an already matched row do not have the same effect on result size. Check the supplied relationships rather than applying a memorized 'add one row' shortcut.
Keep transaction scope explicit
A correct query result does not prove a write workflow is safe. If a transfer must debit one wallet and credit another atomically, name that unit before describing failure. A failed attempt before commit must not retain only one half. Earlier unrelated committed transfers remain separate units. This is a small reasoning model, not an instruction to run a financial service or a promise about every database API. Before using a real service, check its transaction boundary, operation support and configuration. The learning task is to identify which stated guarantee actually supports the proposed conclusion, and to stop when the proposal extends beyond that guarantee.
Complete fictional tables contain Teams(TeamId): T1,T2,T3 and Visits(VisitId,TeamId): (V1,T1),(V2,T1),(V3,T3). TeamId is unique in Teams. What is the row count of SELECT Teams.TeamId, Visits.VisitId FROM Teams LEFT JOIN Visits ON Teams.TeamId=Visits.TeamId, without DISTINCT or further filtering?
- Enumerate the matches for each left-side row. T1 matches V1 and V2 and therefore contributes two joined rows. T2 has no matching visit, so the LEFT JOIN retains one T2 row with NULL visit fields. T3 matches V3 and contributes one row. The total is 2+1+1=4.
- A left join retains every left row, not exactly one output row per left key. Multiple right-side matches still multiply that key in the result. There is no DISTINCT or aggregation to remove the two T1 outputs. The supplied values determine this result; it is a small portable SQL semantics exercise, not a claim about a service performance guarantee.
Compare the other answer choices
Counting only the three visit matches silently changes the LEFT JOIN into the matched portion of an inner join; the unmatched T2 must also be retained.
Two teams have matches, but the query returns joined rows, not a count of distinct teams with visits.
Nine is the Cartesian product of three teams and three visits. The equality join condition admits only matching pairs plus the unmatched left row.
Try it, then explain it.
Complete a short set without looking at the example. After each answer, explain which evidence or rule supports your decision and why the alternatives do not fit.
Sources & authorship
Original OpenExam explanations and examples. References support the concepts; they are not the source of copied questions. Independent educator review is pending.
Microsoft Learn: rd-dp-normalizationMicrosoft Learn: rd-dp-sqlMicrosoft Learn: rd-dp-joinsMicrosoft Learn: rd-dp-countMicrosoft Learn: rd-dp-transactionsMicrosoft Learn: rd-dp-pk