Skip to main content

Relational prediction practice

Attempt before opening calibration or reference code.

  1. Two customers share a name. One has paid orders of 7 and 7 cents; the other has none. Predict REPORT grouped by ID, then grouped by name. Explain the lost distinction.
  2. Add an open order to the second customer. Predict the effect of placing the paid filter in ON versus WHERE. Show the intermediate unmatched row explicitly.
  3. Add a guest paid order. Compare NOT IN and NOT EXISTS for customers without orders. Write the three-valued comparison that causes the discrepancy.
  4. Two paid orders tie at 7 cents. State the selected order ID, then reverse insertion order. Explain why insertion order must not change the answer.
  5. Predict each constraint failure separately: negative cents, null cents, fractional cents, orphan customer, invalid status and duplicate order ID. Supply a valid zero-cent order as a positive control.
  6. Draw the states after each create_order statement and each injected failure. Include an unrelated committed order and an already-active caller transaction.
  7. Transfer to an invoice with two line items: show how a join can double-count an invoice total. Repair by choosing the right grain, not arbitrary DISTINCT.

Preserve first predictions, observed results and explanations. Add three independent cases to your learner packet without modifying the supplied checker to accept incorrect results. One must change the domain assumption, such as guest orders becoming forbidden; explain the required schema and query changes.