What are the different SQL queries which can be run for corresponding table and schema in Snowflake?

In Snowflake, you can run a wide range of SQL queries on specific tables and schemas to explore, manage, and manipulate data. Below is a categorized list of common SQL queries you can run on a given table and schema:

1. Metadata & Structure Queries

These help you understand the schema and table structure.

-- List all schemas in a database
SHOW SCHEMAS IN DATABASE your_database;

-- List all tables in a schema
SHOW TABLES IN SCHEMA your_database.your_schema;

-- Describe structure of a specific table
DESCRIBE TABLE your_database.your_schema.your_table;

-- View column metadata
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_schema'
AND TABLE_NAME = 'your_table';

2. Data Retrieval Queries

These are used to retrieve data from a specific table.

-- Retrieve all records
SELECT * FROM your_database.your_schema.your_table;

-- Retrieve specific columns
SELECT column1, column2 FROM your_database.your_schema.your_table;

-- Conditional retrieval
SELECT * FROM your_database.your_schema.your_table
WHERE column1 = 'value';

3. Aggregate and Analytical Queries

Used for summary statistics and data analysis.

-- Aggregate functions
SELECT COUNT(*) FROM your_database.your_schema.your_table;

SELECT AVG(column1) FROM your_database.your_schema.your_table;

SELECT SUM(column1) FROM your_database.your_schema.your_table;

SELECT MAX(column1), MIN(column1) FROM your_database.your_schema.your_table;

4. Data Manipulation Queries (DML)

Used to insert, update, or delete data.

-- Insert new row
INSERT INTO your_database.your_schema.your_table (col1, col2)
VALUES ('value1', 'value2');

-- Update existing rows
UPDATE your_database.your_schema.your_table
SET col1 = 'new_value'
WHERE col2 = 'some_condition';

-- Delete rows
DELETE FROM your_database.your_schema.your_table
WHERE col1 = 'value';

5. Utility & Maintenance Queries

Used for performance optimization or administrative purposes.

-- Clone a table
CREATE TABLE new_table_name CLONE your_database.your_schema.your_table;

-- Copy structure only (no data)
CREATE TABLE empty_copy LIKE your_database.your_schema.your_table;

-- Truncate table (delete all rows)
TRUNCATE TABLE your_database.your_schema.your_table;

6. Permission & Access Control

Check or modify who can access a table or schema.

-- Show privileges on a table

SHOW GRANTS ON TABLE your_database.your_schema.your_table;

-- Grant select permission

GRANT SELECT ON TABLE your_database.your_schema.your_table TO ROLE your_role;

7. Advanced: CTEs, Joins, and Subqueries

Combine or transform data across multiple tables.

-- Common Table Expression (CTE)
WITH recent_data AS (
SELECT * FROM your_database.your_schema.your_table
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT * FROM recent_data;

-- Join with another table
SELECT a.*, b.other_column
FROM your_database.your_schema.table_a a
JOIN your_database.your_schema.table_b b
ON a.id = b.foreign_id;

Testing and Development Tips

• Use LIMIT to restrict row output when experimenting.

• Run EXPLAIN before complex queries to check execution plan.

CREATE TEMPORARY TABLE temp_table AS SELECT * FROM your_table LIMIT 100;

For further assistance with Snowflake query syntax or use cases related to RChilli data stored in Snowflake, you can contact support@rchilli.com.