Netezza sql cheat sheet v.1.4.0

-- File: NetezzaCheatSheet.sql v.1.4.0 docs at the end 

-- how-to list the columns of a table 

SELECT ',' || NAME || '.' || ATTNAME  from _V_RELATION_COLUMN

WHERE 1=1

AND NAME='ST_SOR_OFFICE'

ORDER BY ATTNUM

; 

-- 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 SVOC_OWNER.ExampleTable

(

    ByteIntCol        byteint            NOT NULL    

  , SmallIntCol       smallint           NOT NULL    

  , IntegerCol        integer            NOT NULL    

  , BigIntCol         bigint             NOT NULL    

  , NumericPSCol      numeric(38,38)     NOT NULL    

  , NumericPCol       numeric(38,0)      NOT NULL    

  , NumericCol        numeric            NOT NULL    

  , DecimalCol        numeric            NOT NULL    

  , FloatCol          float(15)          NOT NULL    

  , RealCol           real               NOT NULL    

  , DoubleCol         double             NOT NULL    

  , CharCol           char(1)        NOT NULL    

  , VarcharCol        varchar(1)     NOT NULL    

  , NcharCol          nchar(1)       NOT NULL    

  , NvarcharCol       nvarchar(1)    NOT NULL    

  , BooleanCol        boolean            NOT NULL    

  , DateCol           date               NOT NULL    

  , TimeCol           time               NOT NULL    

  , TimeTzCol         timetz             NOT NULL    

  , TimestampCol      timestamp          NOT NULL    

 )

DISTRIBUTE ON RANDOM

;

-- how-to copy table 

CREATE TABLE NewTable AS SELECT * FROM TableToCopy ; 

-- how-to or 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 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;

?

--- HOW-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;

-- START SELECT INTO

INSERT INTO DatabaseNameTarget.SchemaNameTarget.TableNameTarget 

SELECT ColumnName1 , ColumnName2

FROM DatabaseNameSource.SchemaNameSource.TableNameSource

;

-- STOP SELECT INTO 

-- how-to remove duplicates

delete from TableWithDuplicates 

where rowid not in 

(

  select min(rowid) from TableWithDuplicates 

  group by (DuplicateDefiningCol1 , DuplicateDefiningCol2 , DuplicateDefiningCol3) 

);

-- 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.4.0 ---  2012-05-18 15:26:35 --- how-to remove duplicates

-- 1.3.0 ---  2012-05-18 13:44:02 --- added how-to copy table 

-- 1.2.0 --- ysg --- added list cols of a table 

-- 1.1.1 --- ysg --- create table example

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

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