SQL Quiz: Integer Division In Metrics
10 questions · exam conditions
0:00
Integer Division In MetricsQuestion 1 of 10

A dashboard runs on a database where integer divided by integer returns an integer. The columns successful_jobs and attempted_jobs are integers, and a row contains 77 successful jobs out of 1212 attempts. The dashboard should display a percentage with fractional precision and return NULL when the attempt count is zero.

Which expression best implements the required percentage?

CAST(100 * successful_jobs / NULLIF(attempted_jobs, 0) AS DECIMAL(6,2))
100 * CAST(successful_jobs / NULLIF(attempted_jobs, 0) AS DECIMAL(6,2))
100.0 * successful_jobs / NULLIF(attempted_jobs, 0)
100 * successful_jobs / CAST(NULLIF(attempted_jobs, 0) AS INTEGER)
← Back to quizzes

SQL Quiz

SQL Quiz: Integer Division In Metrics

Practice Integer Division In Metrics in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Integer Division In Metrics, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A dashboard runs on a database where integer divided by integer returns an integer. The columns successful_jobs and attempted_jobs are integers, and a row contains 77 successful jobs out of 1212 attempts. The dashboard should display a percentage with fractional precision and return NULL when the attempt count is zero.

Which expression best implements the required percentage?

  1. CAST(100 * successful_jobs / NULLIF(attempted_jobs, 0) AS DECIMAL(6,2))
  2. 100 * CAST(successful_jobs / NULLIF(attempted_jobs, 0) AS DECIMAL(6,2))
  3. 100.0 * successful_jobs / NULLIF(attempted_jobs, 0) (correct answer)
  4. 100 * successful_jobs / CAST(NULLIF(attempted_jobs, 0) AS INTEGER)
Explanation: When working with arithmetic in SQL, operator precedence and data type promotion are the two forces you must track simultaneously. SQL evaluates expressions left-to-right among operators of equal precedence, and the data type of the first operand in a chain often determines whether the entire calculation stays in integer arithmetic. Option C, 100.0 * successful_jobs / NULLIF(attempted_jobs, 0), is correct because multiplying by the floating-point literal 100.0 immediately promotes the entire expression to a decimal context. From that point forward, 7 / 12 never happens as integer division — instead you get 100.0×7÷12=58.33...100.0 \times 7 \div 12 = 58.33... with fractional precision. The NULLIF also correctly returns NULL when attempted_jobs is zero. Option A fails because the multiplication 100 * successful_jobs happens first in integer space, giving 700, and then that integer is divided by the integer from NULLIF. Integer division truncates: 700÷12=58700 \div 12 = 58, not 58.3358.33. The outer CAST only converts the already-truncated integer to decimal — too late. Option B makes the same sequencing mistake but earlier: successful_jobs / NULLIF(attempted_jobs, 0) is evaluated first as integer division (7÷12=07 \div 12 = 0), and the CAST then converts that 0 to decimal. Multiplying by 100 gives 0.00, a completely wrong result. Option D explicitly casts to INTEGER, ensuring truncation. This satisfies neither the fractional precision requirement nor any safety improvement. Remember: In SQL, data type promotion travels left-to-right. If you need decimal output, introduce a floating-point value (like 1.0 or 100.0) as early as possible in the expression chain.

Question 2

A report must calculate the overall click-through rate across all campaigns as total clicks divided by total impressions. Both source columns are integers, and the SQL dialect performs integer division for integer operands.

Which expression calculates the required overall rate while avoiding both integer truncation and unintended equal weighting of campaigns?

  1. AVG(1.0 * clicks / NULLIF(impressions, 0))
  2. CAST(SUM(clicks) / NULLIF(SUM(impressions), 0) AS DECIMAL(12,6))
  3. 1.0 * SUM(clicks) / NULLIF(SUM(impressions), 0) (correct answer)
  4. SUM(CAST(clicks / NULLIF(impressions, 0) AS DECIMAL(12,6)))
Explanation: When calculating a ratio across grouped data, you need to ask two questions: (1) should I aggregate first or compute per-row, and (2) will integer division silently truncate my result? These two concerns pull the answer choice selection apart cleanly. The true click-through rate is total clicks across all campaigns divided by total impressions across all campaigns — a pooled ratio, not an average of per-campaign ratios. Answer C, 1.0 * SUM(clicks) / NULLIF(SUM(impressions), 0), nails both requirements. Multiplying SUM(clicks) by 1.0 promotes the integer to a float before division occurs, giving you decimal precision. NULLIF(SUM(impressions), 0) safely returns NULL instead of triggering a divide-by-zero error. Here's why each alternative fails. A computes a per-row ratio first (clicks / impressions), then averages those ratios — this equally weights every campaign regardless of size, which is exactly the "unintended equal weighting" the question warns against. A campaign with 10 impressions influences the result as much as one with 10 million. B is conceptually correct in pooling with SUM, but it performs integer division before the CAST — the truncation already happened, so casting afterward just dresses up a wrong number. D has both problems: it divides per row (wrong aggregation level) and casts after integer division has already truncated each row's result. As a study tip, remember the order-of-operations trap: type casting or floating-point promotion must happen before the division operator is evaluated, not after. Ask yourself, "at what point does division actually execute?"

Question 3

A validation script executes SELECT 5 / 2 AS metric_value; without explicit casts. On SQL Server, integer operands use integer division. On MySQL, the / operator performs ordinary division, while DIV is the integer-division operator.

Which outcome and migration conclusion are correct?

  1. Both systems return 22, so the expression is portable without an explicit numeric cast.
  2. SQL Server returns 22, MySQL returns 2.52.5, so an explicit decimal cast improves portability. (correct answer)
  3. SQL Server returns 2.52.5, MySQL returns 22, so replacing / with DIV is portable.
  4. Both systems return 2.52.5, so only the output column's display format needs adjustment.
Explanation: When working with arithmetic expressions across database platforms, you need to ask: does this operator behave the same way on every system? Division is a classic portability trap. In SQL Server, dividing two integers uses integer division — remainders are discarded. So 5 / 2 returns 22, not 2.52.5. In MySQL, the / operator always performs floating-point division regardless of operand types, so 5 / 2 returns 2.52.5. This difference means the same query produces different results on each platform, which is exactly what answer B describes — making it the correct choice. The fix is straightforward: cast at least one operand to a decimal type (e.g., CAST(5 AS DECIMAL) / 2) so both systems perform floating-point division consistently. A is wrong because the two systems do not return the same result — SQL Server returns 22 while MySQL returns 2.52.5, so the expression is not portable as-is. C reverses the behavior of each platform. SQL Server returns 22 (integer), not 2.52.5, and MySQL returns 2.52.5, not 22. Additionally, MySQL's DIV operator performs integer division, so switching to DIV would force integer division on MySQL — it would not improve portability toward the decimal result. D is wrong because both systems do not return 2.52.5; SQL Server returns an integer 22, which is a computational difference, not merely a display-formatting issue. For migration or cross-platform SQL work, always verify how each target database handles arithmetic operators — especially integer division — and use explicit casts to enforce the intended numeric behavior.

Question 4

An analyst needs the arithmetic mean of employee completion rates, giving each employee equal weight. The integer columns are completed_tasks and assigned_tasks, and employees with zero assigned tasks should be excluded from the average. The current expression is CAST(AVG(completed_tasks / NULLIF(assigned_tasks, 0)) AS DECIMAL(8,4)).

Which replacement preserves each employee's fractional rate before AVG is evaluated?

  1. AVG(CAST(completed_tasks AS DECIMAL(12,4)) / NULLIF(assigned_tasks, 0)) (correct answer)
  2. CAST(AVG(completed_tasks) / AVG(NULLIF(assigned_tasks, 0)) AS DECIMAL(8,4))
  3. AVG(CAST(completed_tasks / NULLIF(assigned_tasks, 0) AS DECIMAL(12,4)))
  4. CAST(SUM(completed_tasks) / NULLIF(SUM(assigned_tasks), 0) AS DECIMAL(8,4))
Explanation: Whenever you see SQL averaging a ratio, ask yourself: at what point does division happen? If integer division occurs before AVG, you lose all fractional precision — for example, 3 / 5 returns 0 in integer arithmetic, not 0.6. The goal here is to preserve each employee's individual rate before aggregation. Option A is correct because it casts completed_tasks to DECIMAL(12,4) before the division. This forces floating-point arithmetic at the row level, so each employee's rate like 0.6 or 0.833... is computed correctly. Then AVG averages those accurate fractional values — giving every employee equal weight in the final mean. Option B divides two separate averages: AVG(completed_tasks) / AVG(assigned_tasks). This calculates a weighted mean (effectively SUM(completed) / SUM(assigned)), not an equal-weight average of individual rates. An employee with 1000 tasks would dominate over one with 10. Option C looks tempting, but the CAST is applied after the integer division happens: completed_tasks / NULLIF(assigned_tasks, 0) is already evaluated as integer math (truncating to 0 or 1), and then cast to decimal. Casting after truncation doesn't recover the lost precision. Option D is SUM(completed) / SUM(assigned), which is a weighted ratio, not an arithmetic mean of individual rates — same conceptual flaw as B. Study tip: In SQL, precision must be introduced before the dividing operation. If you cast after division, you're just converting an already-truncated integer. Always cast the numerator (or denominator) first.

Question 5

A nightly SQL Server process creates a reporting table with SELECT order_count / shopper_count AS orders_per_shopper INTO nightly_metrics FROM daily_totals;. Both source columns are integers. After noticing truncated values, an engineer alters nightly_metrics.orders_per_shopper to DECIMAL(10,2) but does not reload the table.

What is the correct diagnosis and remediation?

  1. Changing the destination type restores the discarded fractions automatically because the stored integers are reinterpreted as decimals.
  2. Reloading is unnecessary if both source columns are cast to integers before the reporting table is queried.
  3. The source calculation already retains fractions; only the SELECT INTO type-inference rule needs to be disabled.
  4. Changing the destination type affects future formatting only; the table must be reloaded using a cast before the source division. (correct answer)
Explanation: Whenever you see a question about integer division in SQL, the key concept to anchor on is when the calculation happens relative to where the result is stored. SQL evaluates expressions using the data types of the source operands — not the destination column. Here's what's actually happening: when SQL Server divides two integers, it performs integer division immediately, discarding any remainder before the result ever reaches the destination column. So 5 / 2 yields 2, not 2.5. That truncated integer is what gets written into nightly_metrics. Altering the destination column to DECIMAL(10,2) afterward changes how future inserts will be formatted, but the already-stored truncated integers remain as-is — they don't get reinterpreted or recalculated. The correct fix, answer D, is to reload the table using an explicit cast in the source query, such as CAST(order_count AS DECIMAL(10,2)) / shopper_count, so the division itself produces a decimal result before any data is written. A is wrong because SQL doesn't reinterpret stored integers when you change a column's type — existing data stays as the values that were written. B is wrong and actually makes things worse: casting to integers before querying the reporting table would further discard precision, not restore it. C is wrong because the source calculation does not retain fractions — that's the whole problem. Integer division discards the fractional part at calculation time, and there's no "type-inference rule" toggling that behavior. The study tip: always ask yourself, "At what data type does the arithmetic actually execute?" In SQL, the answer is determined by the operands, not the destination — so fix the source, not just the storage.

Question 6

A report must show a return rate as a percentage rounded to two decimal places. The integer columns returned_units and sold_units contain 77 and 1212. The dialect performs integer division for integer operands, supports ROUND, and treats 100.0 as a noninteger numeric literal.

Which expression produces approximately 58.3358.33 rather than rounding an already truncated value?

  1. ROUND(100.0 * returned_units / NULLIF(sold_units, 0), 2) (correct answer)
  2. 100 * ROUND(returned_units / NULLIF(sold_units, 0), 2)
  3. ROUND(100 * returned_units / NULLIF(sold_units, 0), 2)
  4. CAST(ROUND(100 * returned_units / NULLIF(sold_units, 0), 2) AS DECIMAL(8,2))
Explanation: Whenever you see a percentage calculation in SQL involving integer columns, the critical question is: at what point does the division happen, and does it produce a decimal result? Most SQL dialects perform integer division when both operands are integers, meaning 7/12=07 / 12 = 0 — not 0.58330.5833. Option A, ROUND(100.0 * returned_units / NULLIF(sold_units, 0), 2), is correct because 100.0 is a noninteger literal. Multiplying first — 100.0×7=700.0100.0 \times 7 = 700.0 — promotes the result to a numeric type, so the subsequent division 700.0/12=58.3333...700.0 / 12 = 58.3333... is decimal division. ROUND(..., 2) then produces 58.3358.33. The NULLIF guard prevents division by zero cleanly. Option B fails immediately: returned_units / NULLIF(sold_units, 0) performs integer division first, yielding 00, and then 100×0=0100 \times 0 = 0. Rounding zero gives 0.000.00 — you're just rounding an already-ruined value. Option C uses 100 (an integer literal), not 100.0. So 100 * returned_units gives the integer 700700, and 700/12700 / 12 is still integer division, yielding 5858 (truncated). ROUND(58, 2) returns 58.0058.00, not 58.3358.33. Option D wraps the same broken expression from C in a CAST, which doesn't recover the lost decimal precision — it just formats the already-truncated 5858 as $$58.00$. The pattern to remember: promote to decimal before dividing, not after. Using a float/numeric literal like 100.0 or an explicit CAST on the numerator before division is the reliable technique for percentage calculations on integer columns.

Question 7

A metric should return a decimal ratio for nonzero denominators and 0.00.0 when the denominator is zero. Both event_count and user_count are integers in a dialect with integer division. A developer proposes COALESCE(event_count / NULLIF(user_count, 0), 0.0).

Which statement best evaluates the proposal and supplies an appropriate correction?

  1. It is correct because COALESCE converts the division to decimal before either argument is evaluated.
  2. It truncates only the zero case; cast the final COALESCE result to a wider integer type.
  3. It preserves fractions but still raises on zero; replace NULLIF with an integer cast on user_count.
  4. It handles zero safely but truncates nonzero ratios; use COALESCE(1.0 * event_count / NULLIF(user_count, 0), 0.0). (correct answer)
Explanation: When working with arithmetic in SQL, you need to think about two separate problems: division by zero and integer truncation. A complete solution must handle both. The proposal COALESCE(event_count / NULLIF(user_count, 0), 0.0) correctly addresses division by zero — NULLIF(user_count, 0) returns NULL when user_count is zero, causing the division to produce NULL, which COALESCE then replaces with 0.0. That part works. The fatal flaw is that in a dialect with integer division, event_count / NULLIF(user_count, 0) is still integer divided by integer, producing a truncated integer before COALESCE ever sees it. For example, 7/37 / 3 yields 22, not 2.333...2.333... The 0.0 fallback in COALESCE doesn't retroactively fix the already-truncated result. The correct fix, answer D, multiplies by 1.0 first: COALESCE(1.0 * event_count / NULLIF(user_count, 0), 0.0). Multiplying event_count by the decimal literal 1.0 promotes the entire expression to decimal arithmetic, preserving the fractional result. Answer A is wrong because COALESCE is not a type-conversion mechanism — it simply returns the first non-NULL value and has no effect on how earlier expressions are evaluated. Answer B misidentifies the problem as being only in the zero case and wrongly suggests casting to a wider integer, which still truncates. Answer C inverts the actual bugs — the proposal does not raise on zero (that's handled correctly), and casting user_count to integer changes nothing since it's already an integer. A useful rule of thumb: whenever you want decimal output from integer columns, force decimal promotion early — multiply by 1.0 or cast the numerator before dividing, not after.

Question 8

In a SQL dialect where dividing two integer expressions performs integer division, both resolved_tickets and total_tickets are integer columns. For one team, their values are 77 and 1212, respectively.

Which expression computes the team's resolution rate without losing the fractional portion before conversion?

  1. CAST(resolved_tickets / total_tickets AS DECIMAL(8,4))
  2. CAST(resolved_tickets AS DECIMAL(8,4)) / total_tickets (correct answer)
  3. CAST(resolved_tickets / total_tickets AS INTEGER) * 1.0
  4. CAST(resolved_tickets AS INTEGER) / CAST(total_tickets AS INTEGER)
Explanation: When working with integer division in SQL, the key question to ask yourself is: at what point does the division happen? If both operands are integers when the division occurs, the database truncates the result before you ever get a chance to convert it. Consider the values 77 and 1212. True division gives 7÷12=0.5833...7 \div 12 = 0.5833... But integer division gives 7÷12=07 \div 12 = 0 — the fractional part is gone permanently. No amount of casting afterward can recover it. Option B is correct because it casts resolved_tickets to DECIMAL(8,4) before the division happens. Now the engine is dividing a decimal by an integer, which produces a decimal result: 7.0000÷12=0.58337.0000 \div 12 = 0.5833. The fractional portion is preserved exactly as intended. Option A is the classic trap: it performs integer division first (7÷12=07 \div 12 = 0), then casts that already-truncated zero to DECIMAL(8,4), giving you 0.00000.0000 — precise but wrong. Option C makes the same mistake and compounds it by casting to INTEGER explicitly before multiplying by 1.0, which still starts from 00. Option D casts both columns to INTEGER, which they already are, so nothing changes — you still get integer division and a result of 00. The pattern to remember: cast before you divide, not after. Converting at least one operand to a decimal or numeric type prior to the division operator is the only way to force floating-point arithmetic. If you cast the result, you're just decorating an already-broken value.

Question 9

In a dialect with integer division, passed_checks and total_checks are integer columns. A developer uses CASE WHEN total_checks = 0 THEN 0.0 ELSE passed_checks / total_checks END and observes that nonzero rates such as 33 out of 88 are reported as 00.

Which revision fixes the fractional-rate problem while retaining the zero-denominator rule?

  1. CASE WHEN total_checks = 0 THEN 0.0 ELSE 1.0 * passed_checks / total_checks END (correct answer)
  2. CASE WHEN total_checks = 0 THEN 0.0 ELSE CAST(passed_checks / total_checks AS DECIMAL(8,4)) END
  3. CAST(CASE WHEN total_checks = 0 THEN 0 ELSE passed_checks / total_checks END AS DECIMAL(8,4))
  4. CASE WHEN total_checks = 0 THEN 0.0 ELSE passed_checks / CAST(total_checks AS INTEGER) END
Explanation: When you see integer columns in a division expression, your first instinct should be: what type does this division produce? In dialects with integer division, dividing two integers truncates the result — so 3/83 / 8 becomes 00, not 0.3750.375. The fix must force floating-point arithmetic before the division happens, not after. Option A works because multiplying passed_checks by 1.0 promotes it to a float first, so the subsequent division by total_checks becomes float division: 1.0×3/8=0.3751.0 \times 3 / 8 = 0.375. The CASE still returns 0.0 when total_checks = 0, preserving the zero-denominator rule perfectly. Option B is the classic trap. CAST(passed_checks / total_checks AS DECIMAL(8,4)) casts after the division, so integer division already produced 0 — you're just casting 0 to 0.0000. The damage is already done before the cast runs. Option C makes the same mistake as B, just moving the cast outside the entire CASE expression. The inner division is still integer, so you again cast a truncated result rather than preventing truncation. Option D is a red herring: CAST(total_checks AS INTEGER) is a no-op since total_checks is already an integer. This changes nothing about how the division is computed and still produces integer results. The key study tip: type promotion must happen before the operation, not after. Multiplying by 1.0 (or casting a numerator to float/decimal before dividing) is the reliable pattern. Casting the result of integer division is always too late.

Question 10

A regression suite checks numerator / denominator in a dialect with integer division. Existing nonzero fixtures are 0/50/5, 5/55/5, and 10/510/5. They all pass even after a decimal cast is accidentally removed from the production query.

Which additional fixture is most likely to expose the missing pre-division cast directly?

  1. Use 2/12/1 and assert that the result is numerically equal to 2.02.0.
  2. Use 1/21/2 and assert that the result is numerically equal to 0.50.5. (correct answer)
  3. Use 0/10/1 and assert that the result is numerically equal to 0.00.0.
  4. Use 6/36/3 and assert that the result is numerically equal to 2.02.0.
Explanation: When testing for a missing decimal cast, you need to find a fixture where integer division and true division produce different results. That's the core diagnostic question: does this input expose a discrepancy between the two behaviors? In integer division, any fractional result is truncated toward zero. So 1÷2=01 \div 2 = 0 under integer rules, but 1.0÷2=0.51.0 \div 2 = 0.5 under decimal rules. If your assertion expects 0.50.5 and the cast is missing, the query returns 00 — the test fails, exposing the bug. That's exactly why B is the correct answer: 1/21/2 is the simplest input that produces a non-integer quotient, making it impossible for truncating integer division to accidentally return the correct value. The existing fixtures — 0/50/5, 5/55/5, and 10/510/5 — all divide evenly, so integer division and decimal division yield identical results. The cast's removal goes undetected. The new fixture must break that pattern. A uses 2/12/1, which equals 22 under both integer and decimal division. No discrepancy exists, so no bug is exposed. C uses 0/10/1, which returns 00 regardless of cast behavior — zero divided by anything is zero. D uses 6/3=26/3 = 2, another clean division that passes under both regimes, just like the original suite. The strategic takeaway: when auditing tests for a cast-related bug, always ask "does this input produce a remainder?" Only non-integer quotients can distinguish integer division from decimal division. Look for numerators that aren't multiples of the denominator.