It returns row combinations that satisfy the join condition. If several rows match, the result can contain several rows per key.
Illustrative scenario
An order joined to three order lines produces three rows, not one.
Explain your reasoning
Apply the answer to this scenario: How would you aggregate to one row per order? State your assumptions, the expected outcome and how you would check it before changing a real system.
It preserves all left-side rows and supplies null right-side columns where no match exists.
Illustrative scenario
A customer list must include customers without orders.
Explain your reasoning
Apply the answer to this scenario: What happens when a right-table filter is put in WHERE? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
What happens when a right-table filter is put in WHERE?
3. What is the difference between WHERE and HAVING?
WHERE filters input rows; HAVING filters groups after aggregation.
Illustrative scenario
Filter paid orders first, then retain customers whose total exceeds a threshold.
Explain your reasoning
Apply the answer to this scenario: When can a HAVING predicate be moved into WHERE? State your assumptions, the expected outcome and how you would check it before changing a real system.
Use IS NULL or IS NOT NULL. Equality with null does not evaluate to true.
Illustrative scenario
Find orders whose dispatch timestamp is missing.
Explain your reasoning
Apply the answer to this scenario: Why can NOT IN behave unexpectedly when its subquery contains null? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
Why can NOT IN behave unexpectedly when its subquery contains null?
It identifies a row uniquely and cannot contain null values. Choose it to match a stable identity requirement.
Illustrative scenario
An order identifier distinguishes two purchases made by the same customer.
Explain your reasoning
Apply the answer to this scenario: How is a business key different from a surrogate key? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
How is a business key different from a surrogate key?
It enforces a relationship to a referenced key, subject to its definition and null semantics.
Illustrative scenario
An order must refer to an existing customer.
Explain your reasoning
Apply the answer to this scenario: What are the consequences of deleting the parent? State your assumptions, the expected outcome and how you would check it before changing a real system.
It is a query used inside another statement. Its cardinality and correlation determine how it behaves.
Illustrative scenario
Use EXISTS to find customers with at least one order.
Explain your reasoning
Apply the answer to this scenario: When is a join more appropriate than EXISTS? State your assumptions, the expected outcome and how you would check it before changing a real system.
It is an access structure that can reduce work for suitable predicates, but adds storage and write maintenance.
Illustrative scenario
A selective lookup by order identifier may benefit from an index.
Explain your reasoning
Apply the answer to this scenario: Why might an optimizer still choose a full scan? State your assumptions, the expected outcome and how you would check it before changing a real system.
It represents a SQL result set and its processing state. A cursor loop processes retrieved rows in PL/SQL.
Illustrative scenario
A procedural task examines each validation failure.
Explain your reasoning
Apply the answer to this scenario: When would set-based SQL be better? State your assumptions, the expected outcome and how you would check it before changing a real system.
It is a named PL/SQL subprogram used to perform an operation, optionally with IN and OUT parameters.
Illustrative scenario
A service invokes a procedure to validate an application.
Explain your reasoning
Apply the answer to this scenario: Who should control the transaction boundary? State your assumptions, the expected outcome and how you would check it before changing a real system.
It is a subprogram that returns a value. Calling it from SQL brings additional usage and side-effect constraints.
Illustrative scenario
A reusable calculation returns a normalized score.
Explain your reasoning
Apply the answer to this scenario: Why can per-row function calls be expensive? State your assumptions, the expected outcome and how you would check it before changing a real system.
It groups related declarations and implementations behind a specification and body.
Illustrative scenario
A customer API exposes lookup and validation operations through one package.
Explain your reasoning
Apply the answer to this scenario: What changes can invalidate callers? State your assumptions, the expected outcome and how you would check it before changing a real system.
It runs automatically for a defined database event. Use it carefully because callers may not see the side effects directly.
Illustrative scenario
A row change triggers an audit action.
Explain your reasoning
Apply the answer to this scenario: Why should complex business workflows often live elsewhere? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
Why should complex business workflows often live elsewhere?
It signals a PL/SQL runtime error or application-defined condition and transfers control to a matching handler.
Illustrative scenario
A scalar lookup returns no rows.
Explain your reasoning
Apply the answer to this scenario: Should the caller receive a not-found outcome or an error? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
Should the caller receive a not-found outcome or an error?
A scalar SELECT INTO returned more than one row. Fix the cardinality rule rather than selecting an arbitrary row.
Illustrative scenario
A customer reference appears twice in imported data.
Explain your reasoning
Apply the answer to this scenario: How would you diagnose join multiplication? State your assumptions, the expected outcome and how you would check it before changing a real system.
It completes the current transaction and makes its changes durable according to database guarantees.
Illustrative scenario
A completed order transaction becomes visible to later readers.
Explain your reasoning
Apply the answer to this scenario: Why should a helper procedure avoid unexpected commits? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
Why should a helper procedure avoid unexpected commits?
It undoes uncommitted work in the transaction, or work after a chosen savepoint when used that way.
Illustrative scenario
A failed multi-step update is abandoned.
Explain your reasoning
Apply the answer to this scenario: What cannot be undone after commit? State your assumptions, the expected outcome and how you would check it before changing a real system.
It anchors a PL/SQL variable type to a column or another supported declaration.
Illustrative scenario
A customer-id variable follows the database column type.
Explain your reasoning
Apply the answer to this scenario: Does it automatically copy every column constraint? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
Does it automatically copy every column constraint?
It numbers rows within a partition according to an ordering. A stable tie-breaker is necessary for deterministic selection.
Illustrative scenario
Choose the latest change for each business key.
Explain your reasoning
Apply the answer to this scenario: How is RANK different when values tie? State your assumptions, the expected outcome and how you would check it before changing a real system.
It constructs or executes SQL whose full structure is determined at runtime. Bind data values and validate identifiers.
Illustrative scenario
A controlled reporting tool chooses an approved table.
Explain your reasoning
Apply the answer to this scenario: Why cannot a bind variable replace an arbitrary table name? State your assumptions, the expected outcome and how you would check it before changing a real system.
Common follow-up
Why cannot a bind variable replace an arbitrary table name?