You are working with a Databricks SQL table named salesdata that contains the columns region, sales, and category. You want to create a new column performance based on the following rules: - If sales are greater than 1000, set performance to 'High'. - If sales are between 500 and 1000 (inclusive), set performance to 'Medium'. - Otherwise, set performance to 'Low'. Which SQL query correctly implements this logic using a CASE/WHEN statement?

A. SELECT , CASE WHEN sales 1000 THEN 'High' WHEN sales BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS performance FROM salesdata

B. SELECT , CASE WHEN sales 1000 THEN 'High' WHEN sales = 500 AND sales <= 1000 THEN 'Medium' WHEN sales < 500 THEN 'Low' END AS performance FROM salesdata

C. SELECT , CASE WHEN sales 1000 THEN 'High' WHEN sales = 500 THEN 'Medium' ELSE 'Low' END AS performance FROM salesdata

D. SELECT , CASE WHEN sales 1000 THEN 'High' ELSE 'Medium' END AS performance FROM salesdata

Databricks Data Engineer Associate Question 213: You are working with a Databricks SQL table named salesdata that... | Databricks Data Engineer Associate Dumps

Databricks Data Engineer Associate practice question 213 of 532

Databricks Certified Data Engineer Associate. Associate level, Databricks. Free question with the correct answer and a full explanation.

Databricks Data Engineer Associate Question 213

Single answer

You are working with a Databricks SQL table named sales_data that contains the columns region, sales, and category. You want to create a new column performance based on the following rules:

If sales are greater than 1000, set performance to 'High'.
If sales are between 500 and 1000 (inclusive), set performance to 'Medium'.
Otherwise, set performance to 'Low'. Which SQL query correctly implements this logic using a CASE/WHEN statement?

A
SELECT *, CASE WHEN sales > 1000 THEN 'High' WHEN sales BETWEEN 500 AND 1000 THEN 'Medium' ELSE 'Low' END AS performance FROM sales_data
B
SELECT *, CASE WHEN sales > 1000 THEN 'High' WHEN sales >= 500 AND sales <= 1000 THEN 'Medium' WHEN sales < 500 THEN 'Low' END AS performance FROM sales_data
C
SELECT *, CASE WHEN sales > 1000 THEN 'High' WHEN sales >= 500 THEN 'Medium' ELSE 'Low' END AS performance FROM sales_data
D
SELECT *, CASE WHEN sales > 1000 THEN 'High' ELSE 'Medium' END AS performance FROM sales_data

Show answer and explanation

Correct answer: A

Explanation

The correct use of CASE/WHEN ensures that each condition is evaluated in the specified order, and the first matching condition determines the output. The ELSE clause acts as a catch-all for any values not explicitly handled by the preceding conditions. The first query properly implements all the rules as described in the scenario.

A. Correct.
Correct. This query uses the CASE/WHEN syntax correctly and implements the specified logic for all conditions, including the ELSE clause for sales values below 500.
B. Incorrect.
Incorrect. While functionally this would work, the condition 'WHEN sales < 500 THEN 'Low'' is redundant because the ELSE clause already handles this case.
C. Incorrect.
Incorrect. This condition would assign 'Medium' to all values greater than or equal to 500, ignoring the upper limit of 1000 for 'Medium'.
D. Incorrect.
Incorrect. This query does not account for the 'Low' performance category and only evaluates two conditions, which is incomplete.

Databricks Data Engineer Associate practice question 213 of 532

Databricks Data Engineer Associate Question 213

Take a Databricks Data Engineer Associate practice test under exam conditions

More Databricks Data Engineer Associate practice questions