Top SQL Commands

SQL to changing the default Database
DATABASE EMP_DATA_BASE;

SQL to find Information about a Database
HELP DATABASE EMP_DATA_BASE;

SQL to get Sample number of rows       
SELECT * FROM EMP_TBL SAMPLE 10;

SQL to get a sample Percentage of rows
SELECT * FROM EMP_TBL SAMPLE .50;

SQL to find information about a Table
SHOW TABLE EMP_TBL;

SQL to Use an Access Locked Table
LOCKING ROW FOR ACCESS SELECT * FROM EMP_TBL;

SQL Keywords that describe you
SELECT DATABASE, USER, SESSION,
ACCOUNT,
PROFILE,
ROLE;"
"SELECT DATE,
CURRENT_DATE,
TIME,
CURRENT_TIME,
CURRENT_TIMESTAMP;

SQL to Use Aggregates functions
SELECT TOP 10  STUDENT_NO, FIRST_NAME, LAST_NAME, CLASS_CODE
FROM STUDENT_TBL
ORDER BY GRADE DESC;"
"SELECT DEPT_NO
,MAX(SALARY) AS ""MAXIMUM""
,MIN(SALARY) AS ""MINIMUM""
,AVG(SALARY) AS ""AVERAGE""
,SUM(SALARY) AS ""SUM""
,COUNT(*) AS ""COUNT""
FROM EMP_TBL
GROUP BY DEPT_NO
ORDER BY DEPT_NO;

SQL to Select TOP Rows in a Rank Order
SELECT TOP 10  STUDENT_NO
,FIRST_NAME
,LAST_NAME
,CLASS_CODE
FROM STUDENT_TBL
ORDER BY GRADE DESC;

SQL Using Date, Time and Timestamp
SELECT
CALENDAR_DATE                
,DAY_OF_WEEK                  
,DAY_OF_MONTH                 
,DAY_OF_YEAR                  
,DAY_OF_CALENDAR              
,WEEKDAY_OF_MONTH             
,WEEK_OF_MONTH                
,WEEK_OF_YEAR                 
,WEEK_OF_CALENDAR             
,MONTH_OF_QUARTER             
,MONTH_OF_YEAR                
,MONTH_OF_CALENDAR            
,QUARTER_OF_YEAR              
,QUARTER_OF_CALENDAR          
,YEAR_OF_CALENDAR             
FROM SYS_CALENDAR.CALENDAR;


SQL to Find out how much Space a USER have
SELECT
USERNAME                     
,CREATORNAME                  
,PERMSPACE                    
,SPOOLSPACE                   
,TEMPSPACE                    
,LASTALTERNAME                
,LASTALTERTIMESTAMP           
FROM DBC.USERS
WHERE USERNAME='USER';

SQL to find how much Space left Per AMP in database
SELECT
VPROC                        
,DATABASENAME                 
,ACCOUNTNAME                  
,MAXPERM
,MAXSPOOL                     
,MAXTEMP                      
 FROM DBC.DISKSPACE
WHERE DATABASENAME='EMP_DB'   ;        

SQL to finding USER Space
SELECT
 MAX(MAXPERM)
,MAX(MAXSPOOL)
,MAX(MAXTEMP)
 FROM DBC.DISKSPACE
WHERE DATABASENAME='USER' ;

SQL to find Space Skew in Tables in a Database
SELECT VPROC
,CAST(TABLENAME AS CHAR(20))
,CURRENTPERM
,PEAKPERM
FROM DBC.TABLESIZEV
WHERE DATABASENAME='USER'
ORDER BY TABLENAME, VPROC;

SQL to Find Table Skew
SELECT
TABLENAME,
SUM(CURRENTPERM) /(1024*1024) AS CURRENTPERM,
(100 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)*100)) AS SKEWFACTOR
FROM
DBC.TABLESIZE
WHERE DATABASENAME=
AND TABLENAME =
GROUP BY 1;

SQL to Find AMP Skew
SELECT DATABASENAME
,TABLENAME
,VPROC
,CURRENTPERM
,PEAKPERM
FROM DBC.TABLESIZE
WHERE
DATABASENAME=
AND
TABLENAME=
ORDER BY VPROC ;

SQL to find number of rows per AMP for a Column
SELECT HASHAMP(HASHBUCKET( HASHROW(EMP_NO))) AS ""AMP"" , COUNT(*)
FROM EMP_TABLE
GROUP BY 1
ORDER BY 1;

SQL to Identify  duplicate records
SELECT COLUMN1, COLUMN2, COLUMN3, COUNT(*)
FROM
DATABASE.TABLE
GROUP BY COLUMN1, COLUMN2, COLUMN3
HAVING COUNT(*) >1;

SQL to Delete Duplicate records
CREATE TABLE TABLE1_BACKUP AS (SELECT * FROM TABLE1 QUALIFY ROW_NUMBER() OVER (PARTITION BY COLUMN1 ORDER BY COLUMN1 DESC )=1) WITH DATA;
DELETE FROM TABLE1;
INSERT INTO TABLE1 SELECT * FROM TABLE_BACKUP;

SQL below to find TOP Databases by space occupied
SELECT
DatabaseName
,MAX(CurrentPerm) * (HASHAMP()+1)/1024/1024 AS USEDSPACE_IN_MB
FROM DBC.DiskSpace
GROUP BY DatabaseName
ORDER BY USEDSPACE_IN_MB DESC;

SQL to find TOP Tables by space occupied
SELECT DATABASENAME
,TABLENAME
,SUM(CurrentPerm)/1024/1024 AS TABLESIZE_IN_MB
FROM DBC.TableSize
GROUP BY DATABASENAME,TABLENAME
ORDER BY TABLESIZE_IN_MB DESC;

SQL to find out list of nodes
SELECT DISTINCT NODEID FROM DBC.RESUSAGESPMA;

SQL to find Account Information
SELECT * FROM DBC.AccountInfoV ORDER BY 1;

Continue Reading...

CUBE Function

I hope you all familiar with ROLLUP Function now. If not please read before get into CUBE Function.

• The CUBE function can be called as Superset of RollUP function.
• Because It returns the same result set as ROLLUP along with provides some additional information.
• Instead producing a 'hierarchy' of results, CUBE looks at all permutations of the specified dimensions.

Let take an example , Produce a 'CUBE' report showing employee salaries across the dimensions of department and manager.

SELECT DEPT_NO AS DEPTNO
 ,MGR_EMP_NO AS MANAGER
 ,SUM(SALARY_AMOUNT) AS SUMSAL
 FROM EMPLOYEE
 WHERE DEPT_NO < 402
GROUP BY CUBE (DEPT_NO,MGR_EMP_NO)
 ORDER BY 1,2;

DEPTNO	MANAGER	SUMSAL
?	?	591925	(all depts, all managers)
?	801	286750	(all depts, one manager)
?	1003	207725
?	1019	58700
?	1025	38750
100	?	100000	(one dept, all managers)
100	801	100000	(one dept, one manager)
201	?	73450	(one dept, all managers)
201	801	34700
201	1025	38750
301	?	116400
301	801	57700
301	1019	58700
302	?	56500
302	801	56500
401	?	245575
401	801	37850
401	1003	207725

If you observe there are four additional rows (green) are included in the output.
These four rows represent the totals by manager.This report shows all possible permutations of the two specified dimensions.

 Reversing CUBE Parameters:
 What happens when we flip the sequence of the CUBE parameters? Consider the following:

 SELECT DEPT_NO AS DEPTNO
 ,MGR_EMP_NO AS MANAGER
 ,SUM(SALARY_AMOUNT) AS SUMSAL
 FROM EMPLOYEE
 WHERE DEPT_NO < 402
GROUP BY CUBE (MGR_EMP_NO,DEPT_NO)
 ORDER BY 1,2;

Yes, The output is exactly  returns same.There is no difference in output becacuse CUBE function looks at all permutations of the specified parameters, order of the arguments doesn't matter - unlike ROLLUP where it does matter.

 we can use the GROUPING and COALESCE functions to make the report more readable.

 SELECT CASE GROUPING (department_number)
 WHEN 1 THEN 'All Depts'
 ELSE (COALESCE (department_number,'Null Dept') ) END AS Deptno
 ,CASE GROUPING (manager_employee_number)
 WHEN 1 THEN 'All Managers'
 ELSE manager_employee_number END AS Mngr
 ,SUM(salary_amount) AS SumSal
 FROM employee
 WHERE department_number < 402
 GROUP BY CUBE (manager_employee_number,department_number)
 ORDER BY 1,2;
 

Deptno	Mngr	SumSal
100	801	100000
100	All Managers	100000
201	801	34700
201	1025	38750
201	All Managers	73450
301	801	57700
301	1019	58700
301	All Managers	116400
302	801	56500
302	All Managers	56500
401	801	37850
401	1003	207725
401	All Managers	245575
All Depts	801	286750
All Depts	1003	207725
All Depts	1019	58700
All Depts	1025	38750
All Depts	All Managers	591925

As more dimensions are added to the cube, more permutations and thus more rows will result.

 If n is the number of dimensions in a cube, then 2**n represents the number of permutations generated:
 2 dimensions - 4 permutations
 3 dimensions - 8 permutations
 4 dimensions - 16 permutations
 etc.
Example

Produce a 'cube' report showing employee salaries across the three dimensions of department, manager and job code for department numbers less than 302.


SELECT CASE GROUPING (department_number)
 WHEN 1 THEN 'All Depts'
 ELSE (COALESCE (department_number,'Null Dept') ) END AS Deptno

,CASE GROUPING (manager_employee_number)
 WHEN 1 THEN 'All Managers'
 ELSE manager_employee_number END AS Mngr

 ,CASE GROUPING (job_code)
 WHEN 1 THEN 'All Job Codes'
 ELSE (COALESCE (job_code,'Null Job Code') ) END AS Job

,SUM(salary_amount) AS SumSal
 FROM employee
 WHERE department_number < 302
GROUP BY CUBE (manager_employee_number,department_number, job_code)
 ORDER BY 1,2;

Deptno	Mngr	Job	SumSal
100	801	111100	100000
100	801	All Job Codes	100000
100	All Managers	All Job Codes	100000
100	All Managers	111100	100000
201	801	211100	34700
201	801	All Job Codes	34700
201	1025	All Job Codes	38750
201	1025	222101	38750
201	All Managers	211100	34700
201	All Managers	All Job Codes	73450
201	All Managers	222101	38750
301	801	All Job Codes	57700
301	801	311100	57700
301	1019	All Job Codes	58700
301	1019	312101	29450
301	1019	312102	29250
301	All Managers	312102	29250
301	All Managers	311100	57700
301	All Managers	All Job Codes	116400
301	All Managers	312101	29450
All Depts	801	All Job Codes	192400
All Depts	801	111100	100000
All Depts	801	211100	34700
All Depts	801	311100	57700
All Depts	1019	312101	29450
All Depts	1019	All Job Codes	58700
All Depts	1019	312102	29250
All Depts	1025	All Job Codes	38750
All Depts	1025	222101	38750
All Depts	All Managers	All Job Codes	289850
All Depts	All Managers	311100	57700
All Depts	All Managers	211100	34700
All Depts	All Managers	111100	100000
All Depts	All Managers	312101	29450
All Depts	All Managers	312102	29250
All Depts	All Managers	222101	38750
All Depts	All Managers	312102	29250
All Depts	All Managers	222101	38750

To help interpret the output, use the following color-coding scheme:

(department,manager, job_code)       Each unique combo of dept/mgr/job
(department, manager)                        Each unique combo of dept/mgr
(department, job_code)                        Each unique combo of dept/job
(manager, job_code)                             Each unique combo of mgr/job
(department)                                         All depts
(manager)                                             All mgrs
(job_code)                                             All jobs
( )                                                           Grand Total

Continue Reading...

ROLLUP Function

The teradata ROLLUP function is used, when ever an aggregation is required across all levels of a hierarchy within a single dimension.

Lets take geography is a dimension. You might have 3 levels of hierarchy - city, county and state. You may wish to produce sum of revenue amounts across all three levels.

we can produce below samples 

• Produce a sum of revenue amounts across all three levels.
• Aggregate at the lowest level (city) and 'rollup' those aggregations to the next level (county) and finally rollup to the top level (state).
• Provide a grand total across all three levels.

All of these are easily done with the ROLLUP feature.

Example1:
Using the GROUP BY function, produce a total of salaries by department for department numbers less than 402.

SELECT department_number
       ,SUM(salary_amount)
FROM employee WHERE department_number < 402
GROUP BY 1
ORDER BY 1;

department_number	Group Sum(salary_amount)
100	100000
201	73450
301	116400
302	56500
401	245575

ROLLUP Function - One Level
Using the ROLLUP function, produce salary totals by department.

SELECT department_number
,SUM(salary_amount)
FROM employee
WHERE department_number < 402
GROUP BY ROLLUP (department_number)
ORDER BY 1;

department_number	Group Sum(salary_amount)
?	591925
100	100000
201	73450
301	116400
302	56500
401	245575

There is an additional row with a '?' department number. This row represents the rollup of all department salaries which is the top (and only) level of the hierarchy in this case. The '?' does not represent a null, rather it represents the 'total' of all department salaries.

ROLLUP Function - Two Levels

Produce a hierarchy of total salaries by department within manager.

SELECT manager_employee_number AS Mgr
,department_number AS Dept
,SUM(salary_amount) AS SumSal
FROM employee
WHERE department_number < 402
GROUP BY ROLLUP (manager_employee_number,department_number)
ORDER BY 1,2;

Mgr	Dept	SumSal
?	?	591925	(all mgrs,all depts,all emps)
801	?	286750	(mgr 801,all depts, 5 emps)
801	100	100000	(mgr 801,dept 100, 1 emp)
801	201	34700	(mgr 801,dept 201, 1 emp)
801	301	57700	(mgr 801,dept 301, 1 emp)
801	302	56500	(mgr 801,dept 302, 1 emp)
801	401	37850	(mgr 801,dept 401, 1 emp)
1003	?	207725	(mgr 1003,all depts, 6 emps)
1003	401	207725	(mgr 1003,dept 401, 6 emps)
1019	?	58700	(mgr 1019,all depts, 2 emps)
1019	301	58700	(mgr 1019,dept 301, 2 emps)
1025	?	38750	(mgr 1025,all depts, 1 emp)
1025	201	38750	(mgr 1025,dept 201, 1 emp)

Three separate aggregations taking place in the preceding query.

Totals for each dept/manager combo (black).
This is represented as the set '(manager,department )' and is the bottom of the hierarchy.

Totals for each manager across all departments (red).
This is represented as the set '(manager )' which is the middle of the hierarchy.

A total for all depts across all managers (blue).
This is represented as the set '( )' and is the top of the hierarchy.

ROLLUP WITH NULL
As previously indicated, the row with the '?' represents the grand total of all salaries across all departments.
But what if there was a department number with a null? How would we distinguish between it and a 'total' row?

Add an employee with a null department and a null job code:

INSERT INTO employee VALUES
(1050,801,NULL,NULL,'LaCoste','Jason',780415,480816,60000.00);

SELECT department_number
,SUM(salary_amount)
FROM employee
GROUP BY ROLLUP (department_number)
ORDER BY 1;

department_number	Group Sum(salary_amount)
?	651925
?	60000
100	100000
201	73450
301	116400
302	56500
401	245575

While it may seem obvious that the row with the larger sum will represent the 'total' row,
a function exists which permits the report to be more easily read.

Continue Reading...

Rank Function

The teradata RANK function permits a column to be ranked, either based on high or low order, against other rows in the answer set. You may specify the order of ranking by use of the ORDER BY clause within the RANK function. Descending order will provide traditional ranking wherein the largest value is assigned a rank of one. Ascending order will associate the largest value with the highest rank value (i.e., typically a value > 1).

The syntax for the RANK function is:

RANK( ) OVER (ORDER BY sales DESC);

Example
Show the ranking of product sales for store 1001.

SELECT storeid
   ,prodid
   ,sales
   ,RANK( ) OVER (ORDER BY sales DESC) AS "Rank"
   FROM salestbl
   WHERE storeid = 1001;
 
Result

Storeid         prodid         sales            Rank
----------------------------------------------------------------
1001             F                 150000.00      1
1001            A                 100000.00       2
1001            C                  60000.00        3
1001            D                  35000.00        4


Things To Notice:
WHERE clause qualifies rows to be ranked.
When the order of sales is DESC, the highest sales amount is rank #1

Example
Show the lowest ranking of product sales for store 1001.

SELECT storeid
   ,prodid
   ,sales
   ,RANK( ) OVER (ORDER BY sales ASC) AS "Rank"
   FROM salestbl
   WHERE storeid = 1001;


Storeid         prodid         sales            Rank
----------------------------------------------------------------
1001             D               35000.00       1
1001             C               60000.00        2
1001             A               100000.00      3
1001             F                150000.00     4
     

Things To Notice:
When the order of sales is ASC, the lowest sales amount is rank #1
Rank #1 always appears at top of list unless overridden.

Rankings With Qualification

The QUALIFY clause allows restriction of which rankings will be output in the final result.
The QUALIFY clause allows restriction of which rankings will be output in the final result.

QUALIFY performs like the HAVING clause by requesting a specific range in the output.


Example
Get top 3 sales - any product in any store:

SELECT storeid
      ,prodid
      ,sales
       ,RANK( ) OVER (ORDER BY sales DESC)
            AS "Ranking"
FROM salestbl
QUALIFY Ranking <= 3;

Storeid         prodid         sales            Rank
----------------------------------------------------------------
1001             F                150000.00      1
1001             A                100000.00      2
1003             B                 65000.00       3
     
Things To Notice:
QUALIFY shows the ranking for the top 3 sales amounts only.

The RANK function produces an ordered sequence.  ORDER BY can override the normal sequencing.

Example
Reverse the ranking sequence in the previous example.

SELECT storeid
       ,prodid
       ,sales
       ,RANK( ) OVER (ORDER BY sales DESC) AS "Ranking"
FROM salestbl
QUALIFY Ranking <= 3
ORDER BY 4 DESC;


Storeid         prodid         sales            Rank
----------------------------------------------------------------
1003            B               65000.00            3
1001            A             100000.00            2
1001            F             150000.00            1


Things To Notice:
The ORDER BY clause in the SELECT statement may always be used to control the final order of the result set.
When the order of sales is DESC, the highest sales amount is always rank #1
After the ranking is applied, the output results are produced based on the ORDER BY sequence in the SELECT statement, if specified.

Ranking With PARTITION BY

The PARTITION BY clause may be used in conjunction with a RANK function to change the scope of the ranking. Without a PARTITION BY clause, the scope defaults to the RANK column.

Whereas the RANK( ) ORDER BY clause controls the default sort key, the PARTITION BY clause adds another level of sort to the output.


Example
Get the top three selling products in each store.

SELECT   storeid
   ,prodid
   ,sales
   ,RANK( ) OVER (PARTITION BY storeid
   ORDER BY sales DESC) AS "Ranking"
FROM salestbl
QUALIFY Ranking <= 3;


Storeid         prodid         sales            Rank
----------------------------------------------------------------
1001            F               150000.00           1
1001            A               100000.00           2
1001            C                60000.00            3
1002            A                40000.00            1
1002            C                35000.00            2
1002            D                25000.00            3
1003            B                65000.00            1
1003            D                50000.00            2
1003            A                30000.00            3 


Things To Notice:
PARTITION BY clause controls scope, i.e., rank sales within store.
Without PARTITION BY, scope would default to sales only.
QUALIFY the ranking for the top 3 sales amounts per store.
Sort sequence of output is sales descending.
Due to PARTITION BY, sort is by sales (DESC) within store (ASC).

Note -- no aggregation is done in this query.

Continue Reading...