SnowFlake University - Code

Question 1

What symbol would look for characters in each cell, regardles sof where they are (beginning, ending or in middle of a cell)?

Answer 1

But % before and after the characters you are looking for.

EX:
SELECT R_REASON_DESC
FROM “SNOWFLAKE_SAMPLE_DATA”.”TPCDS_SF100TCL”.”REASON”
WHERE R_REASON_DESC LIKE ‘%it%’;

Question 2

What is the syntax of REPLACE function? and define each of the parameters.

Answer 2

REPLACE(subject, pattern [,replacement])

Subject - where to look
Pattern - What to look for
Replacement - what to replace it with (optional)

Question 3

What to use to assign a name to a column?

Answer 3

AS

Not permanent

Question 4

Replace function change the table permanently?

Answer 4

No

Question 5

What to use to permanently change a table?

Answer 5

UPDATE and SET functions

Question 6

Syntax of the UPDATE and SET functions:

Answer 6

UPDATE

SET column = REPLACE(subject, pattern [,replacement])

Question 7

What to put in front of a comment?

Answer 7

// or –

Question 8

What are the two ways of CONCATENATION

Answer 8

CONCAT function - linking only two values at a time

DOUBLE-PIPE - using a double-pipe symbol

Question 9

Syntax of CONCAT function:

Answer 9

CONCAT(pattern, subject)

Question 10

Nest a CONCAT function to…

Answer 10

concate mare than two values.

Basically one inside another

Question 11

Double-pipe syntax:

Answer 11

pattern || subject || pattern

Question 12

VARCHAR mean:

Answer 12

Alphanumeric data

Question 13

How to define a CET

Answer 13

with a WITH clause

Question 14

What does a CET clause do?

Answer 14

Precedes the body of the SELECT statement, and defines one or more CTEs the can be used later in the statement.

Question 15

What does SELECT as a statement do?

Answer 15

It queries the database and retrieves a set of rows (most used statement)

Question 16

What does SELECT as a clause do?

Answer 16

it defines the set of columns returned by a query

Question 17

What does the COPY INTO command do?

Answer 17

Like LIST command, it is not actually SQL, and it is used to move data from an External Stage file into a Snowflake table

Question 18

What database objects need to be defined to use a COPY INTO command?

Answer 18

1. A table
2. A stage
3. File Format - structure of the file we want to load
4. Identify the file you want to upload

Question 19

What is the LIST command used for?

Answer 19

It is used to explore the data within a stage

Question 20

To select files by directory, and down into any number of sub-directory levels, when using the LIST command, you can…

Answer 20

simply add the path

ex. LIST @MY_S3_BUCKET/load/d..

Question 21

Is the LIST command SQL?

Answer 21

No

It is a lot more like a LINUX directory command

Question 22

What does this symbol mean:

::

Answer 22

Data type conversion
ex:
select ‘2016-12-25’::date as christmas