1. Query Primer

Question 1

create a new empty database with name “personaldb”

Answer 1

create database personaldb;

Question 2

specify the
database and schema in which you will be working

Answer 2

use schema personaldb.public;

Question 3

Create a new table named “region” by copying all data from (table in another db) snowflakesampledata.tpch_sf1.region table.

Answer 3

CREATE TABLE region 
AS SELECT * FROM snowflake_sample_data.tpch_sf1.region;

Question 4

Does creating new table by copying data from other table, also copies constraints or keys (i.e., primary keys, foreign keys, or indexes)

Answer 4

No

Question 5

Create a new (empty) table “region” or replace an existing one with the same name.

Answer 5

CREATE OR REPLACE TABLE PERSONALDB.PUBLIC.REGION

If a table named REGION already exists, it will be deleted and recreated with this new structure.

Question 6

What is a common datatype used for IDs or keys of a table?

Answer 6

NUMBER(38,0)

This is a whole number column (no decimals), allowing up to 38 digits. Ideal for IDs or keys.

Question 7

Snowflake SQL command to get current date

Answer 7

select current_date();

by default returns the
date in YYYY-MM-DD format

Question 8

Check what columns are available in the table “Region”.

Answer 8

describe table region;

result set shows: name, datatype, kind, null?

Question 9

get information about all tables available in the database personaldb

only 5 out of 18 possible columns

Answer 9

show terse tables in personaldb.public;

the output of show tables will only include tables that you’ve been given the privilege to see.

Question 10

Write snowflake sql query to show “Welcome!” in first column, 5*PI value as circumference in second column, name of current day in third column.

Answer 10

select ‘Welcome!’ as greeting,
5 * 3.14159 as circumference,
dayname(current_date()) as day_of_week;

Question 11

get the current date.

Answer 11

select current_date();

Question 12

1. Alias the table snowflake_sample_data.tpch_sf1.supplier with s.
2. Alias the column r_name with region_name.

Answer 12

1. snowflake_sample_data.tpch_sf1.supplier s
2. r_name as region_name

Question 13

Suppliers and PartsSupplied are two tables, with PKs as s_suppkey and ps_partkey respectively. ps_suppkey acts as foreign key in PartsSupplied table.
Write a Query to get suppliers who have supplied at least once.

Answer 13

select * from suppliers s
inner join (select distinct ps_suppkey from partssupplied) ps
on s.suppkey = ps.suppkey;