SOL-C01 practice question 26 of 30

What happens when you execute the following command in Snowflake? DROP SCHEMA IF EXISTS marketing_schema;

Full set: 214 questions
Answer options
A: An error is returned if the schema does not exist
B: The schema "marketing_schema" is created if it does not exist
C: The schema "marketing_schema" is dropped only if it exists Correct answer
D: The schema "marketing_schema" is dropped even if it contains tables
Correct Answer:
C. The schema "marketing_schema" is dropped only if it exists
Explanation

The IF EXISTS clause is an optional part of the DROP SCHEMA command. Its purpose is to prevent the command from failing if the specified schema does not exist in the current database. If marketingschema exists, it will be dropped. If it does not exist, the command will execute successfully without performing any action or returning an error. This makes DDL scripts more robust and idempotent.

Why Incorrect

A. The IF EXISTS clause specifically prevents an error from being returned if the schema does not exist.
B. This describes the behavior of CREATE SCHEMA IF NOT EXISTS, which is the opposite of the DROP command.
D. By default, DROP SCHEMA uses RESTRICT behavior and will fail if the schema contains any objects; CASCADE is required to drop a non-empty schema.

References

1. Snowflake Documentation. (2024). DROP SCHEMA. In SQL Command Reference. Retrieved from https://docs.snowflake.com/en/sql-reference/sql/drop-schema. See the "Optional Parameters" section for IF EXISTS.

2. Snowflake Documentation. (2024). Understanding Databases, Tables and Views. Retrieved from https://docs.snowflake.com/en/user-guide/tables-intro. The documentation on DDL commands for schemas confirms the RESTRICT vs. CASCADE behavior.

Scroll to Top