Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
A data architect has determined that a table of the following format is necessary:
| employeeId | startDate | avgRating |
|---|---|---|
| a1 | 2009-01-06 | 5.5 |
| a2 | 2018-11-21 | 7.1 |
| ... | ... | ... |
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 (CREATE OR REPLACE TABLE table_name (...)) is correct because:
-
CREATE OR REPLACE TABLEensures the table will be created regardless of whether a table with the same name already exists. If the table exists, it will be replaced; if not, it will be created. -
The syntax correctly defines the table schema with column names and data types in parentheses.
-
In Databricks, when using
CREATE OR REPLACE TABLEwithout specifying a format, it defaults to creating a Delta table.
Why other options are incorrect:
-
Option A:
CREATE TABLE IF NOT EXISTSonly creates the table if it doesn't exist. If the table already exists, nothing happens, which doesn't meet the requirement of "regardless of whether a table already exists with this name." -
Option B: Uses
AS SELECTsyntax which requires a valid SELECT statement. The syntaxemployeeId STRING, startDate DATE, avgRating FLOATis not valid SELECT syntax. -
Option C: Uses incorrect syntax
WITH COLUMNSwhich is not valid SQL DDL in Databricks. -
Option D: Uses
CREATE TABLE table_name AS SELECTwhich will fail if the table already exists, and also has the same invalid SELECT syntax issue as Option B.
Key Learning Points:
CREATE OR REPLACE TABLEis used when you want to ensure a table is created with the specified schema, replacing any existing table with the same name.CREATE TABLE IF NOT EXISTSis used when you want to create a table only if it doesn't already exist.- In Databricks SQL, when no format is specified, tables are created as Delta tables by default.
Comments
Loading comments...