-- File: NetezzaCheatSheet.sql docs at the end 

-- how-to create a database 

CREATE DATABASE DatabaseName

;

-- how-to rename a database 

ALTER DATABASE OldDatabaseName RENAME TO NewDatabaseName 

;

-- how-to create a synomim 

CREATE SYNONYM synonym_name FOR DatabaseName.SchemaName.TableName 

;

-- how-to create a table 

CREATE TABLE ExampleTableName (

   ColVarchar varchar(80)     -- a column of the type Varchar

 , ColInt int                                 -- a column of int type

 , ColDate date                      -- a column of the type date

)

;

-- how-to insert data from non-current db to current db table

INSERT INTO TableName SELECT * FROM DatabaseName..TableName

;

-- how-to drop a table 

DROP TABLE DatabaseName..TableName 

; 

-- how-to change the ownership of a table 

ALTER TABLE TableName OWNER TO NewOwner 

;

-- how-to perform a simple select 

SELECT * FROM TableName 

WHERE 

AND 1=1 

AND WhereColumnName = 'WhereCondition' 

AND GreaterThanColumnName > 0.0

ORDER BY WhereColumnName

;

-- how-to delete from table 

DELETE FROM TableNameToDeleteFrom 

WHERE FilterColumnName = 'FilterValue'

;

-- how-to call a stored procedure 

CALL ProcName ; 

EXEC ProcName ; 

EXECUTE ProcName ; 

-- example stored procedure 

CREATE OR REPLACE PROCEDURE ProcName() 

RETURNS INT4 LANGUAGE NZPLSQL AS

BEGIN_PROC

  DECLARE

    StrVar varchar;

  BEGIN

    StrVar := 'This string is quoted';

  END;

END_PROC

; --END PROC

-- a single line comment

/*

a multi-line comment

*/

-- example proc with parameters

CREATE OR REPLACE PROCEDURE ProcName (int, varchar(ANY)) RETURNS int

LANGUAGE NZPLSQL AS

BEGIN_PROC

  DECLARE

    pId ALIAS FOR $1;

    pName ALIAS FOR $2;

  BEGIN

    INSERT INTO t1 SELECT * FROM t2 WHERE id = pId;

  END; 

END_PROC 

; 

-- Control structure

IF movies.genre = 'd' THEN

  film_genre := 'drama';

ELSIF movies.genre = 'c' THEN

  film_genre := 'comedy';

ELSIF movies.genre = 'a' THEN

  film_genre := 'action';

ELSIF movies.genre = 'n' THEN

  film_genre := 'narrative';

ELSE

-- An uncategorized genre form has been requested.

film_genre := 'Uncategorized';

END IF;

-- how-to list all stored procedures 

SHOW PROCEDURE ALL 

; 

-- how-to document a stored procedure

COMMENT ON PROCEDURE customer() IS 'Author: bsmith

Version: 1.0 Description: A procedure that writes a customer name to

the database log file.';

-- how-to convert date str into nzdate

select  to_date(substring(20090731 from 1 for 8),'YYYYMMDD') as NZDATE

-- select top 

select a.* from some_schema.some_table a limit 10

-- START how to remove duplicates =================================

CREATE TABLE TmpTableDuplicates as

    SELECT col11,col2,col3 from DuplicatesContainingTable 

    where FilterCol = 'FilterValue'

    group by 1,2,3

; 

DELETE FROM DuplicatesContainingTable where FilterCol = 'FilterValue'

; 

INSERT INTO Source_table select * from TmpTableDuplicates

; 

DROP TABLE TmpTableDuplicates

; 

-- STOP how to remove duplicates =================================

-- Query to get a list of views and thier definitions in a database:

SELECT VIEWNAME,OWNER,CREATEDATE,DEFINITION FROM _V_VIEW WHERE OBJTYPE='VIEW';

-- Query to get a list of tables in a database:

SELECT TABLENAME,OWNER,CREATEDATE FROM _V_TABLE WHERE OBJTYPE='TABLE';

-- Query to get a list of columns from a table or a view:

SELECT ATTNUM,ATTNAME FROM _V_RELATION_COLUMN WHERE NAME=UPPER('<TABLE NAME>')

 ORDER BY ATTNUM ASC;

-- Query to get list of user groups on the box:

SELECT GROUPNAME,OWNER,CREATEDATE,ROWLIMIT,SESSIONTIMEOUT,

 QUERYTIMEOUT,DEF_PRIORITY,MAX_PRIORITY FROM _V_GROUP;

-- Query to get list of users and the groups they are in, on the box:

 SELECT GROUPNAME,OWNER,USERNAME FROM _V_GROUPUSERS;

-- (Does not give any LDAP users in this query)

--Query to find the number of rows in a table without actually querying the table:

-- (Sometimes needed for some really huge tables of rowcount > 80 Billion)

SELECT RELNAME TABLE_NAME,

 CASE

 WHEN RELTUPLES < 0

 THEN ((2^32) * RELREFS) + ((2^32) + RELTUPLES )

 ELSE ((2^32) * RELREFS) + ( RELTUPLES )

 END NUM_ROWS

 FROM

 _T_CLASS,

 _T_OBJECT

 WHERE

 _T_OBJECT.OBJID=_T_CLASS.OID AND

 _T_OBJECT.OBJCLASS=4905 — DISPLAY ONLY TABLES

 AND RELNAME = UPPER('<TABLE NAME>');

--Query to check if any of the SPU's are running slower than the rest:

--  (This actually gives the read-write speed of each SPU that is online)

SELECT HWID, BYTE_COUNT/TOTAL_MSEC

 FROM

 _VT_DISK_TIMING

 ORDER BY 2;

--- One more query.. To get the list of tables and thier skew and size:

 SELECT TABLENAME,OBJTYPE,OWNER,CREATEDATE,USED_BYTES,SKEW

 FROM _V_TABLE_ONLY_STORAGE_STAT

 WHERE OBJCLASS = 4905 OR OBJCLASS = 4911

 ORDER BY TABLENAME;

-- Use \dt in nzsql session to get the list tables

/*

\dv to get list of views

 \dmv - list of materialized views

 \l - list of databases

 \dg - list of groups

 \du - list of users

 \dpu - permissions set to a user

 \dT - list of datatypes

 \d <tablename> - describes the table

 \act - show current active sessions

 \d - describe table(or view,sequence)

 \dt , \dv , \ds , \de - list tables,views,sequences,temp tables

 \dSt , \dSv - list system tables and views

 \df - list functions

 \l - list databases

 \dT - list data types

 \du - list users

 \dg - list groups

 \dpu - list permissions granted to a user

 \dpg - list permissions granged to a group

*/

-- Purpose:

-- To provide a simple cheat sheet for netezza 

-- VersionHistory

-- 1.1.0 --- ysg --- added duplicates removal , date conversion

-- 1.0.0 --- ysg --- Initial creation