2

Data Definition Language

by

The Education Machine

[support@theeducationmachine.com]



Data Definition language(DDL) consists of the SQL commands that can be used to define or make changes in the database objects. Let us understand what are database objects:

and many more...

It simply deals with definitions of above objects and is used to create & modify the structure of objects.

Below are the DDL commands that we are going to learn:
  1. CREATE – is used to create the database or its objects (like table, index, function, views, triggers and store procedure).
  2. DROP – is used to delete objects from the database.
  3. ALTER - is used to alter the structure of the database.
  4. TRUNCATE – is used to remove all records from a table, including all spaces allocated for the records are removed.
  5. COMMENT – is used to add comments to the data dictionary.
  6. RENAME - is used to rename a database object name

CREATE


As name suggests, it is used to create database or its objects (warehouse, database, schema, table, stage, file format, role, user, -- function, views, triggers and store procedure).

1.) Create Virtual Warehouse


Snowflake is offered as SaaS service on Cloud, it does not utilize your machine's resources for processing but use Virtual Warehouses provided by snowflake itself. They are compute engines which are responsible for all processing tasks.

- By default it creates XS-Extra Small size warehouse
- Warehouse sizes are similar to T-SHIRT sizes from XS to 4X-LARGE
- It is mandatory to mention warehouse name

Below is the syntax to create a virtual warehouse:
CREATE WAREHOUSE <warehouse_name>;

  create warehouse my_wh;


Output: All warehouses with their status(suspended/on) can be seen under context menu on the top right of worksheet create_warehouse

Optional Parameters:
   WAREHOUSE_SIZE = XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE
   MAX_CLUSTER_COUNT = <num>
   MIN_CLUSTER_COUNT = <num>
   SCALING_POLICY = STANDARD | ECONOMY
   AUTO_SUSPEND = <num> | NULL
   AUTO_RESUME = TRUE | FALSE
   INITIALLY_SUSPENDED = TRUE | FALSE
   RESOURCE_MONITOR = <monitor_name>
   COMMENT = '<string_literal>

To know more about the usage of above additional parameters, refer to below URL:
https://docs.snowflake.com/en/sql-reference/sql/create-warehouse.html


To list all warehouses:

  show warehouses;


Output: show_warehouses


If there are multiple warehouses available and you want to use specific warehouse in worksheet:

Below is the syntax to use a specific virtual warehouse:
USE WAREHOUSE <warehouse_name>;

  use warehouse my_wh;


Output: use_warehouse

2.) Create Database



A database is a data structure that stores organized information. Most databases contain multiple tables, which may each include several different fields. For example, a company database may include tables for products, employees, and financial records.

Below is the syntax to create a database:
CREATE DATABASE <database_name>;

  create database company;


Output: company database is created. It can be verified from left navigation area of worksheet create_database


To list all databases:

  show databases;


Output: show_databases


To list databases which follows a pattern:
Below code will fetch databases whose name starts from 'co'

  show databases like 'co%';


Output: show_databases_pattern


If there are multiple databases available and you want to use specific database in worksheet:

Below is the syntax to use a specific database:
USE DATABASE <database_name>;

  use database demo_db;


Output: use_databases

3.) Create schema



A schema is a collection of database objects including tables, views, triggers, stored procedures, indexes, etc. A schema is associated with a username which is known as the schema owner, who is the owner of the logically related database objects. A schema always belongs to one database.

Below is the syntax to create a schema:
CREATE SCHEMA <schema_name>;

  create schema department;


Output: department schema is created. It can be verified from left navigation area of worksheet. create_schema


To list all schemas:

  show schemas;


Output: show_schemas


To list schemas which follows a pattern:
Below code will fetch schemas whose name starts from 'co'

  show schemas like 'dep%';


Output: show_schemas_pattern


If there are multiple schemas available and you want to use specific schema in worksheet:

Below is the syntax to use a specific schema:
USE SCHEMA <schema_name>;

  use schema department;


Output: use_schemas

4.) Create Table



Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record.
Creation of tables require more efforts as compared to creation of databases and schemas. To create a table, you should create a blueprint in your mind or on a paper to list down the columns/fields that you want to have in a table.

Example: If we think of creating a 'details' table under department schema, we would create columns such as deprtment_id, department_name, etc.. After listing down all the column names, next step is to assign a datatype for each column. Let us understand what is a datatype.

SQL Data Type is an attribute that specifies the type of data of any object. Each column, variable and expression has a related data type in SQL. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement.

There are different categories of datatypes and in each category, there can be multiple datatypes such as:
Above are just some of the examples of datatypes available in Snowflake SQL. To view all available datatypes in snowflake, visit below documentation:
https://docs.snowflake.com/en/sql-reference/data-types.html

Below is the syntax to create a table:
CREATE TABLE <table_name>;

  create schema department;


Output: department schema is created. It can be verified from left navigation area of worksheet. create_schema