Answer-first summary for fast verification
Answer: CREATE OR REPLACE TABLE sales AS SELECT *, salesAmt/unitsSold as avgPrice FROM sales
The correct answer is **C** because `CREATE OR REPLACE TABLE` allows for the modification of the table's schema, including adding new columns. This is essential for adding the 'average price' column. While `INSERT OVERWRITE` can overwrite data, it does not inherently allow for schema modifications unless specific configurations like `spark.databricks.delta.schema.autoMerge.enabled` are set to true. The other options either do not support schema modifications or are not the correct syntax for this operation.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
The data science team has reported that a column named 'average price' is missing from the 'sales' table. This column can be calculated using the 'units sold' and 'sales amount' columns. Which of the following SQL statements would correctly reload the data with the additional 'average price' column?
A
INSERT OVERWRITE sales SELECT *, salesAmt/unitsSold as avgPrice FROM sales
B
COPY INTO SALES AS SELECT *, salesAmt/unitsSold as avgPrice FROM sales
C
CREATE OR REPLACE TABLE sales AS SELECT *, salesAmt/unitsSold as avgPrice FROM sales
D
MERGE INTO sales USING (SELECT *, salesAmt/unitsSold as avgPrice FROM sales)
E
OVERWRITE sales AS SELECT *, salesAmt/unitsSold as avgPrice FROM sales
No comments yet.