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
| Command | Description |
|---|---|
| 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
Key TakeawaysAlways 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. |
