Snowflake - Creating warehouse in Snowflake

Creating warehouse

Login to Snowflake and click on warehouse and click on Create

fill the necessary details and click Finish

We can also create warehouse using SQL in worksheets, the typical SQL looks like below

 

CREATE WAREHOUSE LEARN_WH WITH WAREHOUSE_SIZE = 'XSMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 600 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = 'STANDARD' COMMENT = 'LEARNING WAREHOUSE';

Get the report list from SSRS ( Sql Server Reporting Server)

SELECT 

SUBSTRING([PATH],1,LEN([PATH])-CHARINDEX('/',REVERSE([PATH]))) AS REPORT_FOLDER,

NAME

FROM CATALOG

WHERE TYPE = 2

ORDER BY [PATH],[NAME]

Get the report list from SSRS ( Sql Server Reporting Server)

SELECT 

SUBSTRING([PATH],1,LEN([PATH])-CHARINDEX('/',REVERSE([PATH]))) AS REPORT_FOLDER,

NAME

FROM CATALOG

WHERE TYPE = 2

ORDER BY [PATH],[NAME]

Useful Queries

To find the column name from data dictionary Sql Server

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%COLUMNNAME%';

Oracle Db

SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%column%;

Useful Queries

To find the column name from data dictionary Sql Server

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%COLUMNNAME%';

Oracle Db

SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%column%;

ListAgg function in Oracle SQL 12c

The LISTAGG analytic function was introduced in Oracle 11g Release 2.

the function is used to do string aggregations.

suppose I have data in employee table like this.

and we wanted display department_id and all the related employee first_name in one row , like below.

then we can use LISTAGG function to aggregate the strings.

here is the query.

select department_id,LISTAGG(first_name||',') within group (order by first_name) as Employee from EMPLOYEES

group by DEPARTMENT_ID;

Note: I have used EMPLOYEES table from HR schema in Oracle 12c.

ListAgg function in Oracle SQL 12c

The LISTAGG analytic function was introduced in Oracle 11g Release 2.

the function is used to do string aggregations.

suppose I have data in employee table like this.

and we wanted display department_id and all the related employee first_name in one row , like below.

then we can use LISTAGG function to aggregate the strings.

here is the query.

select department_id,LISTAGG(first_name||',') within group (order by first_name) as Employee from EMPLOYEES

group by DEPARTMENT_ID;

Note: I have used EMPLOYEES table from HR schema in Oracle 12c.

To find the difference between two dates in SQL Server

To find the difference between two dates.


SELECT DATEDIFF(DAY, '4/29/2019', '6/14/2019');

result : 46

SELECT DATEDIFF(DAY, '5/09/2019', '6/14/2019');

Result: 36

SELECT DATEDIFF(DAY, '4/29/2019', SYSDATETIME());

Result: 78

To find the difference between two dates in SQL Server

To find the difference between two dates.


SELECT DATEDIFF(DAY, '4/29/2019', '6/14/2019');

result : 46

SELECT DATEDIFF(DAY, '5/09/2019', '6/14/2019');

Result: 36

SELECT DATEDIFF(DAY, '4/29/2019', SYSDATETIME());

Result: 78

SQL Server DATENAME Function

DATENAME – returns a string corresponding to the datepart specified

SELECT DATENAME(YEAR, GETDATE())        AS 'Year';        
SELECT DATENAME(QUARTER, GETDATE())     AS 'Quarter';     
SELECT DATENAME(MONTH, GETDATE())       AS 'Month';       
SELECT DATENAME(DAYOFYEAR, GETDATE())   AS 'DayOfYear';   
SELECT DATENAME(DAY, GETDATE())         AS 'Day';         
SELECT DATENAME(WEEK, GETDATE())        AS 'Week';        
SELECT DATENAME(WEEKDAY, GETDATE())     AS 'WeekDay';     
SELECT DATENAME(HOUR, GETDATE())        AS 'Hour';        
SELECT DATENAME(MINUTE, GETDATE())      AS 'Minute';      
SELECT DATENAME(SECOND, GETDATE())      AS 'Second';      
SELECT DATENAME(MILLISECOND, GETDATE()) AS 'MilliSecond'; 
SELECT DATENAME(MICROSECOND, GETDATE()) AS 'MicroSecond'; 
SELECT DATENAME(NANOSECOND, GETDATE())  AS 'NanoSecond';  
SELECT DATENAME(ISO_WEEK, GETDATE())    AS 'Week';        

SQL Server DATENAME Function

DATENAME – returns a string corresponding to the datepart specified

SELECT DATENAME(YEAR, GETDATE())        AS 'Year';        
SELECT DATENAME(QUARTER, GETDATE())     AS 'Quarter';     
SELECT DATENAME(MONTH, GETDATE())       AS 'Month';       
SELECT DATENAME(DAYOFYEAR, GETDATE())   AS 'DayOfYear';   
SELECT DATENAME(DAY, GETDATE())         AS 'Day';         
SELECT DATENAME(WEEK, GETDATE())        AS 'Week';        
SELECT DATENAME(WEEKDAY, GETDATE())     AS 'WeekDay';     
SELECT DATENAME(HOUR, GETDATE())        AS 'Hour';        
SELECT DATENAME(MINUTE, GETDATE())      AS 'Minute';      
SELECT DATENAME(SECOND, GETDATE())      AS 'Second';      
SELECT DATENAME(MILLISECOND, GETDATE()) AS 'MilliSecond'; 
SELECT DATENAME(MICROSECOND, GETDATE()) AS 'MicroSecond'; 
SELECT DATENAME(NANOSECOND, GETDATE())  AS 'NanoSecond';  
SELECT DATENAME(ISO_WEEK, GETDATE())    AS 'Week';        

SQL server date functions

To find current date and time : select SYSDATETIME();

To display only date : select convert(date,SYSDATETIME());

To display only time : select convert(time,SYSDATETIME());

Here is the query to display all this above.

select SYSDATETIME() as Timestamp,convert(date,SYSDATETIME()) as Date,CONVERT(time,SYSDATETIME()) as Time;

SQL server date functions

To find current date and time : select SYSDATETIME();

To display only date : select convert(date,SYSDATETIME());

To display only time : select convert(time,SYSDATETIME());

Here is the query to display all this above.

select SYSDATETIME() as Timestamp,convert(date,SYSDATETIME()) as Date,CONVERT(time,SYSDATETIME()) as Time;