Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 38 ยท Getting Started

Your First Queries in Worksheets

Write your first Snowflake SQL: create a table, insert rows, select, filter, sort and aggregate. Use sample data, context functions and query history effectively.

With an account and a warehouse ready, it is time to run SQL. A worksheet is just a text editor connected to your session: you type statements separated by semicolons, run the one under the cursor (or all of them), and see results in a grid below. This lesson walks through the basic statements you will use daily. Everything here is standard SQL, so the runnable examples work in your browser and in Snowflake alike.

Create a table and insert rows

A table has typed columns. Snowflake accepts the usual types: NUMBER, VARCHAR, DATE, TIMESTAMP_NTZ, BOOLEAN and more (Data Types and Semi-structured Values covers the special one, VARIANT).

SQL
-- Snowflake SQL (read-only)
CREATE OR REPLACE TABLE books (
  id        NUMBER AUTOINCREMENT,
  title     VARCHAR NOT NULL,
  genre     VARCHAR,
  price     NUMBER(8,2),
  published DATE
);

INSERT INTO books (title, genre, price, published) VALUES
  ('The Salt Road',   'Fiction', 14.99, '2015-03-01'),
  ('Paper Lanterns',  'Poetry',   9.99, '2021-09-15');

CREATE OR REPLACE drops and recreates in one step, handy while learning. AUTOINCREMENT fills in id for you. Note that Snowflake accepts PRIMARY KEY and FOREIGN KEY declarations for documentation and tooling, but does not enforce them (only NOT NULL is enforced on standard tables), so your loading process must prevent duplicates.

Here is the portable equivalent you can run now:

SQL Create, insert and select

Press Run (or Ctrl + Enter) to see the result here.

Selecting, filtering and sorting

The core of querying is SELECT โ€ฆ FROM โ€ฆ WHERE โ€ฆ ORDER BY. In Snowflake the identifiers are case-insensitive unless you wrap them in double quotes, and unquoted names are stored in uppercase. So books, Books and BOOKS are the same table, but "books" is a different one. Beginners who quote names by habit get confusing "object does not exist" errors, so avoid quotes unless you really need them.

SQL Filter, sort and limit ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Useful extras you will meet in Snowflake: TOP 5 works like LIMIT 5, and ILIKE is a case-insensitive LIKE. A column alias can be reused later in the same SELECT (a convenient Snowflake feature):

SQL
-- Snowflake SQL (read-only)
SELECT title,
       price,
       price * 0.8      AS sale_price,
       sale_price * 1.1 AS sale_with_tax   -- reuses the alias above
FROM books
WHERE title ILIKE '%river%';

Standard databases reject the reused alias, so write the full expression if you need portability.

Aggregating

GROUP BY collapses rows into groups; HAVING filters the groups.

SQL Group and filter groups ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Snowflake also has GROUP BY ALL, which groups by every non-aggregated column in the select list, a shortcut for long queries:

SQL
-- Snowflake SQL (read-only)
SELECT genre, published, COUNT(*) AS titles
FROM books
GROUP BY ALL;

Sample data to practice on

Every account comes with a shared database called SNOWFLAKE_SAMPLE_DATA. It holds TPC-H and TPC-DS benchmark tables with millions of rows, so you can try real-size queries without loading anything. You only need a warehouse and the privilege to use it:

SQL
-- Snowflake SQL (read-only)
USE WAREHOUSE learn_wh;
SELECT c_mktsegment, COUNT(*) AS customers
FROM snowflake_sample_data.tpch_sf1.customer
GROUP BY c_mktsegment
ORDER BY customers DESC;

The name has three parts: database, schema, table. The tpch_sf1 schema is a scale factor 1 (about a gigabyte) dataset; tpch_sf100 and larger ones exist for testing performance.

Helpful context functions and result handling

Snowflake has a few tiny functions you will use constantly:

Function Returns
CURRENT_USER() Your login name
CURRENT_ROLE() The active role
CURRENT_WAREHOUSE() The warehouse the session uses
CURRENT_TIMESTAMP() The time now
LAST_QUERY_ID() The id of your previous statement

LAST_QUERY_ID() pairs with RESULT_SCAN, which turns the previous result into a table you can query again. This is a neat Snowflake trick for post-processing output of commands such as SHOW TABLES:

SQL
-- Snowflake SQL (read-only)
SHOW TABLES;
SELECT "name", "rows"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "rows" > 1000
ORDER BY "rows" DESC;

The double quotes appear because SHOW output columns are lowercase, so they have to be quoted.

The standard-SQL way to chain results is a CTE or subquery. This runs anywhere:

SQL Stand-in for RESULT_SCAN: a CTE ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Query history and result cache

Every query you run appears in Query History, with its duration, rows, bytes scanned and the warehouse used. If you rerun the exact same query and the underlying data has not changed, Snowflake can return the previous result instantly from a cache without using a warehouse. Caching Layers explains when that happens. For now, if a repeat run takes 20 ms, that is why.

Run SELECT * FROM big_table LIMIT 10 to peek at a table. It is cheap because Snowflake can stop early, whereas SELECT * without LIMIT on a billion rows will ship a huge result to your screen.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You created a table with CREATE TABLE "orders" (...) using double quotes. Later SELECT * FROM orders fails. Why?

    Unquoted identifiers are stored uppercase. A table created as lowercase "orders" must always be referenced with the same quotes.
  2. 2. Which constraint is actually enforced on a standard Snowflake table?

    Primary, unique and foreign keys are informational only. NOT NULL is enforced.
  3. 3. What is SNOWFLAKE_SAMPLE_DATA?

    It is a read-only shared database with TPC-H and TPC-DS data, available in every account.
  4. 4. What does GROUP BY ALL do?

    GROUP BY ALL is a Snowflake shortcut that groups by every select-list item that is not an aggregate.

Summary

  • A worksheet runs SQL under your current role, warehouse, database and schema.
  • Standard CREATE TABLE, INSERT, SELECT, WHERE, GROUP BY and ORDER BY work as you expect.
  • Unquoted identifiers become uppercase; quote them only when necessary.
  • Only NOT NULL is enforced; key constraints are informational.
  • SNOWFLAKE_SAMPLE_DATA, LAST_QUERY_ID(), RESULT_SCAN and GROUP BY ALL are handy Snowflake extras.

Next: Databases, Schemas and Objects