Analysts repeatedly write the same expression to convert a price and a currency code into US dollars, and they need to use it inside the SELECT lists and WHERE clauses of their own queries. Which object should the DBA create?
Select an answer to reveal the explanation.
Short Explanation
If you want to drop a calculation right into a SELECT like it's a built-in, you want a scalar function: values in, one value out. A stored procedure is called on its own, so you can't tuck it inside a WHERE clause. That's the trap the stem is setting for you.
Full Explanation
A user-defined scalar function accepts input parameters and returns a single value, and it can be invoked anywhere an expression is allowed: in a SELECT list, a WHERE predicate, an ORDER BY clause or a computed column definition. Wrapping the currency conversion in a function such as to_usd(price, currency_code) gives analysts one consistent definition they can reuse across their own queries, and a change to the conversion logic is made in one place. A stored procedure also encapsulates logic, but it is executed as a standalone call; it cannot be embedded in an expression inside another query, so analysts could not filter or project with it. A trigger fires on data changes to a specific table, so it would only affect newly inserted rows in that table and offers nothing for ad hoc queries across other tables or existing data. A CHECK constraint enforces a rule on values being written; it returns pass or fail for a row and cannot produce a converted amount for a query. Exam caveat: a scalar function invoked for every row of a very large result can add noticeable overhead, so performance should be reviewed when it is used in filters on large tables. Operational check: create the function in a test schema, use it in both a SELECT list and a WHERE clause, and compare its results with the analysts' existing hand-written expression on sample rows.