Top 10 commonly used commands in Snowflake

        Are you looking for details about commonly used commands in Snowflake? Are you also interested in knowing what are DML, DDL snowflake commands? Then you have reached the right place. In this article, we will explore more about snowflake commands.

A) Types of commands in Snowflake

          There are four types of commands present in snowflake and those are 

          1. DDL - Data Definition Language

          2. DML - Data Manipulation Language

          3. DCL - Data Control Language

          4. TCL - Transaction Control Language

         In this article, we will focus on DDL and DML commands use in Snowflake

B) What commonly used DDL commands in Snowflake?

            Here is the list of DDL commands used in snowflake

           1. ALTER

           2. CREATE

           3. DROP 

           4. USE

           5. SHOW

           Let's see each of these commands one by one -

1. AFTER - AFTER command is used to modify metadata of on account level, parameters for session or metadata of the database object

Syntax : 

              AFTER <object_type> <object_name> <actions>

e.g  AFTER SESSION SET < params >

2. CREATE - CREATE command is used to create new object

Syntax : 

              CREATE <object_type> <object_name>

e.g.  CREATE  DATABASE ABC

3. DROP - DROP command is used for removing object from system.

Syntax : DROP <object_type> [IF EXISTS] <identifier>

e.g  DROP USER [IF EXISTS] abc_ user

4. USE - USE command is used to specify role, warehouse database ,or schema for current session

Syntax : USE WAREHOUSE <name>

e.g   USE WAREHOUSE xyz 

5. SHOW - SHOW command is used to provide metadata for the object.

Syntax : SHOW <object_ type_plural> [LIKE '<pattern>']

e.g  SHOW PARAMETERS [LIKE '<pattern>']

C) What are commonly used DML commands in Snowflake?

         Here is the list of DDL commands used in Snowflake

          1. INSERT

          2. MERGE

          3. UPDATE

          4. DELETE

          5. TRUNCATE

             Let's see each of these commands one by one.

1. INSERT - INSERT command is used to insert one or more rows into the table.

       Syntax :

       INSERT INTO <table_name> [< column_name>]

           VALUES (<value>|DEFAULT|NULL,...)

        

       e.g

        INSERT INTO TAB_ABC  (id, name)

         VALUES ( 100 , ' DRONA')

2) MERGE - MERGE command is used to Insert, delete and update values in a table based on values in a subquery or another table.

         Syntax : 

         MERGE INTO <table_name> USING <source> ON <join_exp>L

         e.g

         MERGE INTO TAB_ABC USING TAB_PQR ON TAB_ABC.ID=TAB_PQR.ID WHEN MATCHED Then 

         Update set TAB_ABC .NAME = TAB_PQR.NAME

3. UPDATE: The UPDATE command is used to update rows in the table.

       Syntax : 

              UPDATE <table_name> SET <field>=<value>

       e.g 

            UPDATE TAB_ABC SET NAME = 'XYZ'

4. DELETE : DELETE command is used to delete records from the table.

         Syntax : 

              DELETE FROM <table_name > WHERE <condition>

          e.g 

              DELETE FROM TAB_ABC WHERE NAME='BOB'

5) TRUNCATE: TRUNCATE command is used to remove records from the table including privileges and constraints.

          Syntax :

              TRUNCATE TABLE <table _name>

          e.g 

              TRUNCATE TABLE TAB_ ABC.