Snowflake SQL

Question 1

How do you create a database in Snowflake?

Answer 1

To create a database in Snowflake, use the SQL command: CREATE DATABASE database_name;

Example: CREATE DATABASE my_database;

Question 2

What is the syntax for creating a table in Snowflake?

Answer 2

The syntax for creating a table is: CREATE TABLE table_name (column1 datatype, column2 datatype, …);

Example: CREATE TABLE my_table (id INT, name VARCHAR);

Question 3

Explain how to insert data into a Snowflake table.

Answer 3

To insert data into a Snowflake table, use the INSERT INTO statement: INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

Example: INSERT INTO my_table (id, name) VALUES (1, ‘John’);

Question 4

How do you perform a SELECT query in Snowflake?

Answer 4

To perform a SELECT query, use the SELECT statement: SELECT column1, column2 FROM table_name WHERE condition;

Example: SELECT id, name FROM my_table WHERE id = 1;

Question 5

What are the different types of joins available in Snowflake?

Answer 5

The types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

Example: SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

Question 6

Describe how to use the UPDATE statement in Snowflake.

Answer 6

The UPDATE statement modifies existing data in a table: UPDATE table_name SET column1 = value1 WHERE condition;

Example: UPDATE my_table SET name = ‘Jane’ WHERE id = 1;

Question 7

What is the purpose of the MERGE statement?

Answer 7

The MERGE statement allows you to perform insert, update, or delete operations based on a join condition between two tables.

Example: MERGE INTO target_table USING source_table ON target_table.id = source_table.id WHEN MATCHED THEN UPDATE SET target_table.name = source_table.name;

Question 8

How do you delete data from a table in Snowflake?

Answer 8

To delete data, use the DELETE statement: DELETE FROM table_name WHERE condition;

Example: DELETE FROM my_table WHERE id = 1;

Question 9

Explain the concept of window functions in Snowflake.

Answer 9

Window functions perform calculations across a set of table rows related to the current row, using an OVER clause.

Example: SELECT id, name, SUM(salary) OVER (PARTITION BY department) FROM employees;

Question 10

How do you handle NULL values in Snowflake SQL?

Answer 10

NULL values can be handled using functions like COALESCE, NVL, or IS NULL/IS NOT NULL conditions.

Example: SELECT * FROM my_table WHERE name IS NULL;

Question 11

What are the aggregate functions supported by Snowflake?

Answer 11

Aggregate functions include COUNT, SUM, AVG, MIN, MAX, etc.

Example: SELECT COUNT(*), AVG(salary) FROM employees;

Question 12

How do you create a stored procedure in Snowflake?

Answer 12

Stored procedures can be created using the CREATE PROCEDURE statement, specifying the procedure logic in JavaScript.

Example: CREATE PROCEDURE my_procedure() RETURNS STRING LANGUAGE JAVASCRIPT AS $$ return ‘Hello’; $$;

Question 13

What is the role of user-defined functions (UDFs) in Snowflake?

Answer 13

UDFs allow you to create custom functions in SQL or JavaScript to encapsulate reusable logic.

Example: CREATE OR REPLACE FUNCTION my_function(param INT) RETURNS INT LANGUAGE JAVASCRIPT AS $$ return param * 2; $$;

Question 14

How can you optimize a query in Snowflake?

Answer 14

Query optimization can be done by analyzing the query plan, using appropriate indexes, partitioning, and optimizing SQL logic.

Example: EXPLAIN SELECT * FROM my_table WHERE id = 1;

Question 15

What is the use of the ANALYZE statement?

Answer 15

The ANALYZE statement is used to gather statistics on tables and indexes to help optimize query performance.

Example: ANALYZE TABLE my_table COMPUTE STATISTICS;

Question 16

Describe the use of materialized views in Snowflake.

Answer 16

Materialized views store the results of a query physically, allowing faster query performance on complex calculations.

Example: CREATE MATERIALIZED VIEW my_view AS SELECT id, COUNT(*) FROM my_table GROUP BY id;

Question 17

How do you grant and revoke privileges in Snowflake?

Answer 17

Privileges can be granted and revoked using the GRANT and REVOKE statements.

Example: GRANT SELECT ON my_table TO role_name;

Question 18

What is the purpose of the SHOW command?

Answer 18

The SHOW command is used to display information about Snowflake objects like tables, views, and schemas.

Example: SHOW TABLES;

Question 19

How do you handle transactions in Snowflake?

Answer 19

Transactions in Snowflake can be managed using the BEGIN, COMMIT, and ROLLBACK statements.

Example: BEGIN TRANSACTION; INSERT INTO my_table (id, name) VALUES (1, ‘Alice’); COMMIT;

Question 20

What are the best practices for writing efficient SQL queries in Snowflake?

Answer 20

Best practices include using proper indexing, avoiding SELECT *, minimizing data transfer, and writing optimized SQL logic.

Example: SELECT id, name FROM my_table WHERE id = 1;