Answer-first summary for fast verification
Answer: ```sql CREATE FUNCTION combine_nyc (city STRING) RETURNS STRING RETURN CASE WHEN city = "brooklyn" THEN "new york" ELSE city END; ```
## Explanation Option A is the correct syntax for creating a SQL user-defined function (UDF) in Databricks: 1. **Correct Syntax**: `CREATE FUNCTION` is the proper SQL command to create a user-defined function. 2. **Parameter Definition**: `(city STRING)` correctly defines the input parameter. 3. **Return Type**: `RETURNS STRING` properly specifies the return type. 4. **Function Body**: The `RETURN CASE...END;` structure correctly implements the function logic. 5. **Complete Statement**: The statement ends with a semicolon, making it syntactically complete. Option B is incorrect because: 1. **Wrong Command**: Uses `CREATE UDF` instead of `CREATE FUNCTION`. 2. **Incomplete Syntax**: Missing the `RETURN` keyword before the CASE statement. 3. **Missing END**: The CASE statement doesn't have an `END` keyword. 4. **Missing Semicolon**: The statement is incomplete without a closing semicolon. In Databricks SQL, user-defined functions are created using the `CREATE FUNCTION` syntax, which allows you to define reusable SQL logic that can be applied at scale across multiple queries and tables.
Author: Keng Suppaseth
Ultimate access to all questions.
A data engineer needs to apply custom logic to string column city in table stores for a specific use case. In order to apply this custom logic at scale, the data engineer wants to create a SQL user-defined function (UDF). Which of the following code blocks creates this SQL UDF?
A
CREATE FUNCTION combine_nyc (city STRING)
RETURNS STRING
RETURN CASE
WHEN city = "brooklyn" THEN "new york"
ELSE city
END;
CREATE FUNCTION combine_nyc (city STRING)
RETURNS STRING
RETURN CASE
WHEN city = "brooklyn" THEN "new york"
ELSE city
END;
B
CREATE UDF combine_nyc(city STRING)
RETURNS STRING
CASE
WHEN city = "brooklyn" THEN "new york"
ELSE city
CREATE UDF combine_nyc(city STRING)
RETURNS STRING
CASE
WHEN city = "brooklyn" THEN "new york"
ELSE city
(Note: Option B is incomplete and syntactically incorrect for standard SQL UDF creation.)
No comments yet.