Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use Database in Snowflake: Syntax and Examples

In Snowflake, you use a database by first creating it with CREATE DATABASE, then selecting it with USE DATABASE. After that, you can create tables and run queries inside that database.
📐

Syntax

Here are the main commands to work with databases in Snowflake:

  • CREATE DATABASE <database_name>; - creates a new database.
  • USE DATABASE <database_name>; - switches your session to the specified database.
  • SHOW DATABASES; - lists all databases you have access to.
sql
CREATE DATABASE my_database;
USE DATABASE my_database;
SHOW DATABASES;
💻

Example

This example shows how to create a database, switch to it, create a table, insert data, and query it.

sql
CREATE DATABASE example_db;
USE DATABASE example_db;

CREATE TABLE users (
  id INT,
  name STRING
);

INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob');

SELECT * FROM users;
Output
ID | NAME ---|------- 1 | Alice 2 | Bob
⚠️

Common Pitfalls

Common mistakes when using databases in Snowflake include:

  • Trying to use a database before creating it.
  • Not switching to the correct database before creating tables or running queries.
  • Confusing schemas with databases; schemas are inside databases.

Always USE DATABASE before working with objects inside it.

sql
/* Wrong: Trying to create table without selecting database */
CREATE TABLE users (id INT);

/* Right: Select database first */
USE DATABASE example_db;
CREATE TABLE users (id INT);
📊

Quick Reference

CommandDescription
CREATE DATABASE ;Create a new database.
USE DATABASE ;Switch to a database for your session.
SHOW DATABASES;List all accessible databases.
CREATE TABLE (...);Create a table inside the current database.
SELECT ... FROM ;
Query data from a table.
✅

Key Takeaways

Always create a database before using it with CREATE DATABASE.
Switch to your database using USE DATABASE before creating tables or querying.
Databases contain schemas, and schemas contain tables; know the hierarchy.
Use SHOW DATABASES to see which databases you can access.
Avoid running commands in the wrong database by always confirming your current database.