Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
Which of the following code blocks uses SQL DDL commands to create an empty Delta table in the above format regardless of whether a table already exists with this name?
Real Exam
Community
KKeng
A
CREATE TABLE IF NOT EXISTS table_name (
employeeId STRING,
startDate DATE,
avgRating FLOAT
)
CREATE TABLE IF NOT EXISTS table_name (
employeeId STRING,
startDate DATE,
avgRating FLOAT
)
B
CREATE OR REPLACE TABLE table_name AS
SELECT
employeeId STRING,
startDate DATE,
avgRating FLOAT
USING DELTA
CREATE OR REPLACE TABLE table_name AS
SELECT
employeeId STRING,
startDate DATE,
avgRating FLOAT
USING DELTA
C
CREATE OR REPLACE TABLE table_name WITH COLUMNS (
employeeId STRING,
startDate DATE,
avgRating FLOAT
) USING DELTA
CREATE OR REPLACE TABLE table_name WITH COLUMNS (
employeeId STRING,
startDate DATE,
avgRating FLOAT
) USING DELTA
D
CREATE TABLE table_name AS
SELECT
employeeId STRING,
startDate DATE,
avgRating FLOAT
CREATE TABLE table_name AS
SELECT
employeeId STRING,
startDate DATE,
avgRating FLOAT
E
CREATE OR REPLACE TABLE table_name (
employeeId STRING,
startDate DATE,
avgRating FLOAT
)
CREATE OR REPLACE TABLE table_name (
employeeId STRING,
startDate DATE,
avgRating FLOAT
)
Explanation:
Explanation
Option E is correct because:
-
CREATE OR REPLACE TABLE- This syntax creates a table regardless of whether it already exists. If the table exists, it will be replaced; if it doesn't exist, it will be created. -
Column definition syntax - The parentheses with explicit column definitions
(employeeId STRING, startDate DATE, avgRating FLOAT)properly defines the table schema. -
Creates an empty Delta table - By default in Databricks, when you create a table using SQL DDL without specifying a data source, it creates a Delta table.
Let's analyze why the other options are incorrect:
Option A: CREATE TABLE IF NOT EXISTS - This only creates the table if it doesn't exist, but the question asks for code that works "regardless of whether a table already exists."
Option B: Uses AS SELECT syntax which requires data to populate the table, not creating an empty table. Also includes USING DELTA which is unnecessary as Delta is the default.
Option C: Invalid syntax - WITH COLUMNS is not valid SQL syntax in Databricks.
Option D: CREATE TABLE table_name AS SELECT - This creates a table with data from the SELECT statement, not an empty table. Also lacks the OR REPLACE clause.
Key points:
CREATE OR REPLACE TABLEhandles both creation and replacement scenarios- Explicit column definitions create an empty table
- Delta is the default table format in Databricks, so no explicit
USING DELTAis needed - The syntax in Option E follows standard SQL DDL conventions for Databricks
Comments
Loading comments...