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?
Exam-Like
Community
KKeng
A
CREATE OR REPLACE TABLE table_name AS
SELECT
id STRING,
birthDate DATE,
avgRating FLOAT
USING DELTA
CREATE OR REPLACE TABLE table_name AS
SELECT
id STRING,
birthDate DATE,
avgRating FLOAT
USING DELTA
B
CREATE OR REPLACE TABLE table_name (
id STRING,
birthDate DATE,
avgRating FLOAT
)
CREATE OR REPLACE TABLE table_name (
id STRING,
birthDate DATE,
avgRating FLOAT
)
C
CREATE TABLE IF NOT EXISTS table_name (
id STRING,
birthDate DATE,
avgRating FLOAT
)
CREATE TABLE IF NOT EXISTS table_name (
id STRING,
birthDate DATE,
avgRating FLOAT
)
D
CREATE TABLE table_name AS
SELECT
id STRING,
birthDate DATE,
avgRating FLOAT
CREATE TABLE table_name AS
SELECT
id STRING,
birthDate DATE,
avgRating FLOAT
E
CREATE OR REPLACE TABLE table_name WITH COLUMNS (
id STRING,
birthDate DATE,
avgRating FLOAT
) USING DELTA
CREATE OR REPLACE TABLE table_name WITH COLUMNS (
id STRING,
birthDate DATE,
avgRating FLOAT
) USING DELTA
Explanation:
Explanation
Let's analyze each option:
Option A: This uses CREATE OR REPLACE TABLE ... AS SELECT which creates a table from a SELECT statement, not an empty table with just the schema. It would create a table with data from the SELECT query, not an empty table.
Option B: This is the correct answer. CREATE OR REPLACE TABLE table_name (columns) creates an empty Delta table with the specified schema, and the OR REPLACE clause ensures it will replace any existing table with the same name, regardless of whether it already exists.
Option C: CREATE TABLE IF NOT EXISTS only creates the table if it doesn't exist. If the table already exists, it does nothing, so it doesn't meet the requirement of creating the table "regardless of whether a table already exists with this name."
Option D: CREATE TABLE ... AS SELECT creates a table from a SELECT statement without the OR REPLACE clause. If the table already exists, this will fail with an error.
Option E: This syntax is invalid in Databricks SQL. The WITH COLUMNS clause is not valid SQL syntax for creating tables.
Key points:
CREATE OR REPLACE TABLEwith explicit column definitions creates an empty table with the specified schema- The
OR REPLACEclause ensures the table is created even if one already exists (it replaces the existing one) - Delta is the default table format in Databricks, so no explicit
USING DELTAis needed - The syntax in Option B is the standard Databricks SQL DDL for creating an empty Delta table that will replace any existing table with the same name
Comments
Loading comments...