Databases and schemas in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a new database named 'sales_db'.

Snowflake

CREATE DATABASE [1];

Drag options to blanks, or click blank then click option'

ADROP

BCREATE

CUSE

Dsales_db

Attempts:

3 left

💡 Hint

Common Mistakes

Using keywords like CREATE or USE instead of the database name.

Forgetting to specify the database name.

The CREATE DATABASE command followed by the database name creates a new database.

2fill in blank

medium

Complete the code to switch to the 'sales_db' database.

Snowflake

USE [1];

Drag options to blanks, or click blank then click option'

ACREATE

BDROP

Csales_db

DSCHEMA

Attempts:

3 left

💡 Hint

Common Mistakes

Using CREATE instead of USE.

Using SCHEMA instead of the database name.

The USE command followed by the database name switches the current context to that database.

3fill in blank

hard

Fix the error in the code to create a schema named 'marketing' inside the 'sales_db' database.

Snowflake

CREATE SCHEMA [1].marketing;

Drag options to blanks, or click blank then click option'

Asales_db

BCREATE

CUSE

DDROP

Attempts:

3 left

💡 Hint

Common Mistakes

Using keywords like CREATE or USE instead of the database name.

Omitting the database name before the schema.

To create a schema inside a database, prefix the schema name with the database name separated by a dot.

4fill in blank

hard

Fill both blanks to switch to the 'marketing' schema inside the 'sales_db' database.

Snowflake

USE [1].[2];

Drag options to blanks, or click blank then click option'

Asales_db

Bmarketing

Cfinance

DCREATE

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the order of database and schema.

Using CREATE instead of schema name.

The USE command can switch to a specific schema by specifying database.schema.

5fill in blank

hard

Fill all three blanks to create a table named 'customers' with columns 'id' (integer) and 'name' (string) inside the 'marketing' schema of 'sales_db'.

Snowflake

CREATE TABLE [1].[2].[3] (id INT, name STRING);

Drag options to blanks, or click blank then click option'

Asales_db

Bmarketing

Ccustomers

DCREATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using CREATE as a name instead of database, schema, or table.

Mixing the order of database, schema, and table.

To create a table inside a schema and database, specify database.schema.table.

Practice

1. What is the main purpose of a schema in Snowflake?

easy

A. To store user login credentials

B. To organize tables and other data objects within a database

C. To manage network security settings

D. To create virtual machines

Solution

1. Step 1: Understand the role of a schema

   A schema is a container inside a database that holds tables and other data objects.

2. Step 2: Differentiate schema from other components

   User credentials, security settings, and virtual machines are unrelated to schemas in Snowflake.

3. Final Answer:

   To organize tables and other data objects within a database -> Option B

4. Quick Check:

   Schema = container for tables [OK]

Hint: Schemas hold tables inside databases [OK]

Common Mistakes:

• Confusing schemas with user accounts
• Thinking schemas manage security
• Mixing schemas with infrastructure components

2. Which of the following is the correct syntax to create a new schema named sales_data in Snowflake?

easy

A. CREATE SCHEMA sales_data;

B. MAKE SCHEMA sales_data;

C. NEW SCHEMA sales_data;

D. CREATE DATABASE sales_data;

Solution

1. Step 1: Recall Snowflake schema creation syntax

   The correct command to create a schema is CREATE SCHEMA schema_name;.

2. Step 2: Identify incorrect options

   Options A and B use invalid keywords. CREATE DATABASE sales_data; creates a database, not a schema.

3. Final Answer:

   CREATE SCHEMA sales_data; -> Option A

4. Quick Check:

   CREATE SCHEMA = correct syntax [OK]

Hint: Use CREATE SCHEMA to make schemas [OK]

Common Mistakes:

• Using MAKE or NEW instead of CREATE
• Confusing CREATE SCHEMA with CREATE DATABASE
• Missing semicolon at the end

3. Given the following commands executed in order:

CREATE DATABASE company_db;
CREATE SCHEMA hr_schema;
USE DATABASE company_db;
USE SCHEMA hr_schema;

What is the current working database and schema?

medium

A. Database: company_db, Schema: public

B. Database: hr_schema, Schema: company_db

C. Database: default, Schema: hr_schema

D. Database: company_db, Schema: hr_schema

Solution

1. Step 1: Analyze the commands step-by-step

   CREATE DATABASE company_db; creates the database (current context unchanged).
   CREATE SCHEMA hr_schema; creates hr_schema in the current database (which is the default database).
   USE DATABASE company_db; sets current database to company_db (schema: public).
   USE SCHEMA hr_schema; attempts to switch to hr_schema schema in company_db. Since hr_schema was created in the default database, this will fail unless hr_schema exists in company_db. However, if hr_schema was created after switching to company_db, it would exist there.
   Given the commands, CREATE SCHEMA hr_schema; was run before switching to company_db, so hr_schema is in the default database, not company_db. Therefore, USE SCHEMA hr_schema; will fail and schema remains public.

2. Step 2: Determine current context

   After execution: Database: company_db, Schema: public.

3. Final Answer:

   Database: company_db, Schema: public -> Option A

4. Quick Check:

   CREATE SCHEMA uses current DB context [OK]

Hint: Schemas created in current database context [OK]

Common Mistakes:

• Mixing database and schema names
• Assuming schema changes database
• Forgetting USE commands set context
• Not USE DATABASE before CREATE SCHEMA

4. You run the command USE SCHEMA analytics; but get an error saying the schema does not exist. What is the most likely cause?

medium

A. Schemas cannot be switched using USE SCHEMA

B. The syntax of the command is incorrect

C. The current database does not contain a schema named analytics

D. You need to create a database before using a schema

Solution

1. Step 1: Understand USE SCHEMA behavior

   The USE SCHEMA command switches to a schema within the current database context.

2. Step 2: Identify cause of error

   If the schema does not exist in the current database, Snowflake returns an error. Syntax is correct, and schemas can be switched. Creating a database is unrelated if one is already in use.

3. Final Answer:

   The current database does not contain a schema named analytics -> Option C

4. Quick Check:

   Schema must exist in current database [OK]

Hint: Schema must exist in current database to use it [OK]

Common Mistakes:

• Assuming USE SCHEMA creates schema
• Thinking syntax is wrong
• Ignoring current database context

5. You want to organize your data by creating a database sales_db with two schemas: domestic and international. Which sequence of commands correctly achieves this?

hard

A. CREATE SCHEMA domestic; CREATE SCHEMA international; CREATE DATABASE sales_db;

B. USE DATABASE sales_db; CREATE DATABASE sales_db; CREATE SCHEMA domestic; CREATE SCHEMA international;

C. CREATE DATABASE sales_db; CREATE SCHEMA domestic; USE SCHEMA international; CREATE SCHEMA international;

D. CREATE DATABASE sales_db; USE DATABASE sales_db; CREATE SCHEMA domestic; CREATE SCHEMA international;

Solution

1. Step 1: Create the database first

   You must create the database sales_db before creating schemas inside it.

2. Step 2: Set the database context and create schemas

   Use USE DATABASE sales_db; to set the context, then create schemas domestic and international inside it.

3. Step 3: Verify command order

   CREATE DATABASE sales_db; USE DATABASE sales_db; CREATE SCHEMA domestic; CREATE SCHEMA international; follows the correct order: create database, switch to it, then create schemas. Other options create schemas before database or misuse commands.

4. Final Answer:

   CREATE DATABASE sales_db; USE DATABASE sales_db; CREATE SCHEMA domestic; CREATE SCHEMA international; -> Option D

5. Quick Check:

   Create DB, USE DB, then CREATE SCHEMA [OK]

Hint: Create DB first, then schemas inside it [OK]

Common Mistakes:

• Creating schemas before database
• Not switching to database before schemas
• Using USE DATABASE before creating it