Summary of Commands

Snowflake supports most of the commands and statements defined in SQL:1999.

In this Topic:

DDL (Data Definition Language) Commands

Account-based and Session-based DDL

Commands

Notes

ALTER ACCOUNT

Used to set account-level parameters (only by users with ACCOUNTADMIN role).

ALTER SESSION

Used to set parameters in the current session.

SHOW FUNCTIONS

Displays system-defined functions, as well as user-defined functions.

SHOW PARAMETERS

Used to view parameters settings for the account or current session. Also can be used to view parameter settings for objects that have parameters.

SHOW VARIABLES

Used to view SQL variables in the current session.

SET , UNSET

Used to set and unset SQL variables in the current session.

USE { DATABASE | ROLE | SCHEMA | WAREHOUSE }

The USE command sets the database, role, schema, or warehouse for the current session.

Account Object DDL

Commands

Notes

{ ALTER | CREATE | DESC | DROP | UNDROP | USE } DATABASE

CREATE also supports cloning existing databases.

{ ALTER | CREATE | DESC | DROP } RESOURCE MONITOR

{ ALTER | CREATE | DESC | DROP | USE } ROLE

{ ALTER | CREATE | DESC | DROP } SHARE

{ ALTER | CREATE | DESC | DROP } USER

{ ALTER | CREATE | DESC | DROP | USE } WAREHOUSE

COMMENT

This command can be used with any account object; also, comments be set using CREATE or ALTER.

{ GRANT | REVOKE } PRIVILEGE

Privileges are not strictly a first-class object; however, these commands are used for enabling and managing access control for all first-class objects in Snowflake.

SHOW { DATABASES | PRIVILEGES | RESOURCE MONITORS | ROLES | SHARES | USERS | WAREHOUSES }

Database / Schema Object DDL

Commands

Notes

{ ALTER | CREATE | DESC | DROP } FILE FORMAT

CREATE also supports cloning existing file formats.

{ ALTER | CREATE | DESC | DROP } FUNCTION

Used for creating and managing UDFs (user-defined functions).

{ ALTER | CREATE | DESC | DROP } PIPE

{ ALTER | CREATE | DESC | DROP } PROCEDURE

Used for creating and managing stored procedures.

{ ALTER | CREATE | DESC | DROP } SEQUENCE

CREATE also supports cloning existing sequences.

{ ALTER | CREATE | DESC | DROP } STAGES

CREATE also supports cloning existing named stages (external only).

{ ALTER | CREATE | DESC | DROP | UNDROP | USE } SCHEMA

CREATE also supports cloning existing schemas.

{ ALTER | CREATE | DESC | DROP | TRUNCATE | UNDROP } TABLE

CREATE also supports cloning existing tables, creating populated tables (using CTAS), and creating empty tables (from existing tables).

{ ALTER | CREATE | DESC | DROP | UNDROP } VIEW

COMMENT

This command can be used with any database/schema object; also, comments be set using CREATE or ALTER.

SHOW { FILE FORMATS | PIPES | PROCEDURES | SCHEMAS | SEQUENCES | STAGES | TABLES | USER FUNCTIONS | VIEWS }

DML (Data Manipulation Language) Commands

Commands

Notes

DELETE , INSERT , MERGE , REPLACE , UPDATE

Used for general DML. INSERT includes support for multi-table inserts.

COPY INTO { <table> | <location> }

Used for loading/unloading data (i.e. bulk import/export).

GET , LIST , PUT , REMOVE

These commands do not perform DML; they are used to stage and manage files for data loading and unloading.

Query Syntax & Operators

Category

Constructs/Operators

Notes

Query Syntax

[ WITH ]

SELECT [ DISTINCT ]

Required.

[ FROM ]

[ AT | BEFORE ]

For querying using Time Travel.

[ SAMPLE ] / [ TABLESAMPLE ]

TABLESAMPLE is a synonym for SAMPLE.

[ JOIN ]

Support for inner, left outer, right outer, and full joins.

[ PIVOT | UNPIVOT ]

[ WHERE ]

[ GROUP BY ]

Columns used in GROUP BY must be in SELECT list.

[ HAVING ]

[ ORDER BY ]

[ VALUES ]

[ LIMIT ]

Arithmetic Operators

+ , - , * , \ , %

Comparison Operators

= , != , < > , > , >= , < , <=

Logical/Boolean Operators

AND , NOT , OR

Set Operators

INTERSECT [ ALL ] , MINUS [ ALL ] / EXCEPT , UNION [ ALL ]

Subquery Operators

ANY / ALL , [ NOT ] EXISTS , [ NOT ] IN

Predicates

BETWEEN , [ NOT ] EXISTS , [ NOT ] IN , LIKE

TCL (Transaction Control Language) Commands

Commands

Notes

BEGIN , COMMIT , ROLLBACK

Used for multi-statement transactions in the current session.

SHOW { LOCKS | TRANSACTIONS }