How do you list all the objects available in given database?

Run the below any of the query, it will display the all objects in given database.
SELECT * FROM DBC.TABLES WHERE DATABASENAME=<'DATABASENAME'>
OR
Run the normal help command
HELP DATABASE '<DATABASENAME>'

How to rename columns using views ?

I have a emp table.I want to display columns empno as emp_number,ename as emp_name,job as emp_job,mgr as emp_manager,hiredate as emp_hiredate,sal as emp_salary,comm as emp_commission,deptno as emp_department in view. Renaming columns in views will help increase security of sensitive tables and hiding columns under alias names.In this scenario we have to create view like below.

empno	ename	job	mgr	hiredate	sal	comm	deptno
7788	SCOTT	ANALYST	7566	19/04/1987	3000	0	20
7782	CLARK	MANAGER	7839	09/06/1981	2450	0	10
7521	WARD	SALESMAN	7698	22/02/1981	1250	500	30
7698	BLAKE	MANAGER	7839	01/05/1981	2850	0	30
7902	FORD	ANALYST	7566	03/12/1981	3000	0	20
7839	KING	PRESIDENT	0	17/09/1981	5000	0	10
7900	JAMES	CLERK	7698	03/12/1981	950	0	30
7654	MARTIN	SALESMAN	7698	28/09/1981	1250	1400	30
7934	MILLER	CLERK	7782	23/01/1982	1300	0	10
7566	JONES	MANAGER	7839	02/04/1981	2975	0	20
7844	TURNER	SALESMAN	7698	08/09/1981	500	0	30
7499	ALLEN	SALESMAN	7698	20/02/1981	1600	300	30
7876	ADAMS	CLERK	7788	23/05/1987	1100	0	20

CREATE VIEW EMP_V (emp_number,emp_name,emp_job,emp_manager,emp_hiredate,emp_salary, emp_commission,emp_department)

AS SELECT 

EMPNO

,ENAME

,JOB

,MGR

,HIREDATE

,SAL

,COMM

,DEPTNO FROM EMP;

run the below query, only alias columns are displayed.

	emp_number	emp_name	emp_job	emp_manager	emp_hiredate	emp_salary	emp_commission	emp_department
	7788	SCOTT	ANALYST	7566	19/04/1987	3000.00	0	20
	7782	CLARK	MANAGER	7839	09/06/1981	2450.00	0	10
	7521	WARD	SALESMAN	7698	22/02/1981	1250.00	500.00	30
	7698	BLAKE	MANAGER	7839	01/05/1981	2850.00	0	30
	7902	FORD	ANALYST	7566	03/12/1981	3000.00	0	20
	7839	KING	PRESIDENT	0	17/09/1981	5000.00	0	10
	7900	JAMES	CLERK	7698	03/12/1981	950.00	0	30
	7654	MARTIN	SALESMAN	7698	28/09/1981	1250.00	1400.00	30
	7934	MILLER	CLERK	7782	23/01/1982	1300.00	0	10
	7566	JONES	MANAGER	7839	02/04/1981	2975.00	0	20
	7844	TURNER	SALESMAN	7698	08/09/1981	500.00	0.00	30
	7499	ALLEN	SALESMAN	7698	20/02/1981	1600.00	300.00	30
	7876	ADAMS	CLERK	7788	23/05/1987	1100.00	0	20

What is View ?

VIEW:

• It is just window for a table
• It is the logical object for the physical collection of tables

Mainly we use views for

• Limiting the no of rows and columns
• reducing network
• better security and encapsulation

Restrictions on view:

• An index cannot be created on a view
• A view cannot be contain on ORDER BY clause
• Derived and aggregated columns must be assigned a alias name
• A view cannot be used for UPDATE operations, if it contains
• Data from more then one table (join view)
• The same column specified twice
• Derived columns (sal/12)
• A Distinct clause
• A group by clause

Creating View : 
CREATE VIEW <view_name> AS 
SELECT <column-name> [AS <alias-name>]
           [....,<column-name> [AS <alias-name>]]
           FROM <table-name>
           [WHERE <conditional-tests>]
           [WITH CHECK OPTION];
OR
CREATE VIEW may be abbreviated as CV
CV <view-name> [(<alias-name>, <alias-name>,...)]  AS
SELECT <column-name> [AS <alias-name>]
           [....,<column-name> [AS <alias-name>]]
           FROM <table-name>
           [WHERE <conditional-tests>]
           [WITH CHECK OPTION];
The employee table is used to demonstrate the use of views
Emp table - contains 13 rows.

empno	ename	job	mgr	hiredate	sal	comm	deptno
7788	SCOTT	ANALYST	7566	19/04/1987	3000	0	20
7782	CLARK	MANAGER	7839	09/06/1981	2450	0	10
7521	WARD	SALESMAN	7698	22/02/1981	1250	500	30
7698	BLAKE	MANAGER	7839	01/05/1981	2850	0	30
7902	FORD	ANALYST	7566	03/12/1981	3000	0	20
7839	KING	PRESIDENT	0	17/09/1981	5000	0	10
7900	JAMES	CLERK	7698	03/12/1981	950	0	30
7654	MARTIN	SALESMAN	7698	28/09/1981	1250	1400	30
7934	MILLER	CLERK	7782	23/01/1982	1300	0	10
7566	JONES	MANAGER	7839	02/04/1981	2975	0	20
7844	TURNER	SALESMAN	7698	08/09/1981	500	0	30
7499	ALLEN	SALESMAN	7698	20/02/1981	1600	300	30
7876	ADAMS	CLERK	7788	23/05/1987	1100	0	20

The following creates a view to return the emp table in deptno 20. It limits the view to and empno,ename,and sal. This view restricts columns that is job,mgr,hiredate,comm and rows not for deptno 20.

CREATE VIEW emp_20_v as 

SELECT empno as empolyeenumber,ename as employeename,sal*12 as "Annual Salary" from emp where deptno=20

empolyeenumber	employeename	Annual Salary
7788	SCOTT	36000
7566	JONES	35700
7902	FORD	36000
7876	ADAMS	13200

In the above view creation, we written alias name all columns in select statement. But this is another way to written alias names in crate view statement. Let us see how we write,

CREATE VIEW EMP_10_V (EMP_NO,EMP_NAME,EMP_SAL) AS

SELECT EMPNO,ENAME,SAL FROM EMP 

WHERE DEPTNO=10 
These two tables are used in the following examples

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17/12/1980	800	0	20
7499	ALLEN	SALESMAN	7698	20/02/1981	1600	300	30
7521	WARD	SALESMAN	7698	22/02/1981	1250	500	30
7566	JONES	MANAGER	7839	02/04/1981	2975	0	20
7654	MARTIN	SALESMAN	7698	28/09/1981	1250	1400	30
7698	BLAKE	MANAGER	7839	01/05/1981	2850	0	30
7782	CLARK	MANAGER	7839	09/06/1981	2450	0	10
7788	SCOTT	ANALYST	7566	19/04/1987	3000	0	20
7839	KING	PRESIDENT	0	17/11/1981	5000	0	10
7844	TURNER	SALESMAN	7698	08/09/1981	1500	0	30
7876	ADAMS	CLERK	7788	23/05/1987	1100	0	20
7900	JAMES	CLERK	7698	03/12/1981	950	0	30
7902	FORD	ANALYST	7566	03/12/1981	3000	0	20
7934	MILLER	CLERK	7782	23/01/1982	1300	0	10

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

The following view performs the join
create view emp_dept_v as

select ename as emp_name

,sal as emp_sal

,dname as dept_name

,loc as dept_loc from emp inner join dept

on emp.deptno=dept.deptno

The next select references the view to perform the join 

select * from emp_dept_v order by emp_sal

emp_name	emp_sal	dept_name	dept_loc
TURNER	500	SALES	CHICAGO
JAMES	950	SALES	CHICAGO
ADAMS	1100	RESEARCH	DALLAS
WARD	1250	SALES	CHICAGO
MARTIN	1250	SALES	CHICAGO
MILLER	1300	ACCOUNTING	NEW YORK
ALLEN	1600	SALES	CHICAGO
CLARK	2450	ACCOUNTING	NEW YORK
BLAKE	2850	SALES	CHICAGO
JONES	2975	RESEARCH	DALLAS
FORD	3000	RESEARCH	DALLAS
SCOTT	3000	RESEARCH	DALLAS
KING	5000	ACCOUNTING	NEW YORK

Another common use for views is to summarize data. Instead of creating an actual table and storing the data twice.

The following creates a view to perform the aggregation of emp by each department.

CREATE VIEW AGG_V AS
SELECT DEPTNO
,SUM(SAL) TOTALSAL
,AVG(SAL) AVARAGESAL
,MIN(SAL) MINIMUMSAL
,MAX(SAL) MAXIMUSAL 
FROM EMP
GROUP BY DEPTNO
The view can then be used to aggregate the columns that are created as a result of an aggregate,as seen below
SEL SUM(TOTALSAL) FROM AGG_V
SEL AVG(AVARAGESAL) FROM AGG_V
Deleting View : 
DROP VIEW [<database-name>]<View-name>
It removes the view name, column names and SELECT from the DD
Modifying View:
The syntax of the REPLACE VIEW follows
REPLACE VIEW [<database-name>]<view-name>
AS SELECT <column-name>
                 [...<column-name>]
FROM <table-name>
[WHERE <conditional-tests>]
[WITH CHECK OPTION];
REPLACE VIEW AGG_V AS
SELECT DEPTNO
,SUM(SAL) TOTALSAL
,AVG(SAL) AVARAGESAL
,MIN(SAL) MINIMUMSAL
,MAX(SAL) MAXIMUSAL
FROM EMP
GROUP BY DEPTNO
WHERE DEPTNO BETWEEN 10 AND 20
Notice that the keyword REPLACE appears instead of the original CREATE and the WHERE clause is changed form the original CREATE VIEW statement.

How to check the table size in Teradata ?

The following syntax is used to find the table size in Teradata 

Syntax: Select databasename,tablename,sum(currentperm) from   dbc.tablesize where databasename='<databasename>' and  tablename='<tablename>' group by databasename,tablename;

While running the above query, type your databasename in place of <databasename> and type your tablename in place of <tablename>

What Is macro ?

Macro : 

Macros are SQL statements stored in the Data Directory.

Collection of statements which does simple tasks.

A macro can store one or more SQL statements.

For frequent request we go for it.

We cannot take procedural control statements in side macro (If-Else,For)

Only one DDL(Create,Alter,Drop) statement we can take here

With in macro we can write a DML(SELECT,INSERT,UPDATE,DELETE Statements)

It takes only input parameters

All statements inside macro acts like transactions.

All updates with in a macro are considered a transaction
           If all steps work, all work committed.
           If a single step fails, all the updated rows are automatically rollback         to their original values prior to the macro executing.

We execute macro with EXEC command

It cannot support store procedure language.

CREATE MACRO : Each SQL statement within a macro must have its own semi-colon to help the optimizer delineate one SQL statement from another. All the SQL statements must be enclosed in parentheses to be created and treated as a single transaction.

The following syntax for a CREATE MACRO

CREATE MACRO <macro-name> AS 
(  [ SELECT........; ]
   [ INSERT........; ]
   [ UPDATE.......; ]
   [ DELETE.........;] )

Example :

CREATE MACRO emp1_m AS
( UPDATE emp1 SET job='po' WHERE job='clerk';
SELECT empno,ename,sal,deptno FROM emp1 WHERE job='po';);

In the above example macro contains two SQL statements:one is UPDATE and other one is SELECT.Both statements are enclosed in the parentheses, each of the statements ends with a semi-colon (;)        

The following syntax for a EXECUTE MACRO

EXEC <macro-name> [ (<Parameter-value-list>) ];
                           or
EXECUTE <macro-name> [ (<Parameter-value-list>) ];

The REPLACE MACRO statement is used to modify an existing macro.It replaces an existing macro with a new macro, in its entirety. There fore, the name must be exactly the same.It is very good idea to do a HELP DATABASE before replacing a macro.

The following syntax for REPLACE MACRO

REPLACE MACRO <macro-name> AS
(  [INSERT ......;]
   [UPDATE......;]
   [DELETE.......;]
   [SELECT ......;]
);

Ex: REPLACE MACRO MM AS(SELECT DEPTNO,EMPNO,SAL FROM EMP WHERE DEPTNO=10;);

It is the time to execute them.

DROP MACRO: 

The DROP MACRO statement has only one function. It deletes a macro out of the DD. Therefore, it is a very powerful and easy command to use. Additionally, there is no question that asks if you are sure you want to DROP THE MACRO and there is no undo functionality. If a user has the privilege to DROP a macro and executes a DROP MACRO command, the macro is gone. The following is the syntax of the DROP MACRO command.

An example:

DROP MACRO Myfirst_macro ;

Unlike the CREATE MACRO that had to establish the parameters and provide the SQL, the DROP MACRO does not care. The name is all it needs to eliminate the macro from the DD.

Since there is no undo function for the DROP MACRO, it is a good idea to have the CREATE MACRO statement stored somewhere on disk available for recovery. If it is not saved at creation, before dropping the macro, a SHOW MACRO can be executed to return the CREATE MACRO statement for saving on disk. However, if a large macro is being built, it should be saved initially. Otherwise, if the CREATE MACRO is too large to store in the DD, part of it may be lost using the SHOW MACRO.

Difference between ROW_NUMBER(),RANK(),DENSE_RANK

Let us assume, i have a emp table columns ename,sal. The emp table shows below.

ENAME	SAL
SMITH	800
ALLEN	1600
WARD	1250
JONES	2975
MARTIN	1250
BLAKE	2850
CLARK	2450
SCOTT	3000
KING	5000
TURNER	1500
ADAMS	1100
JAMES	950
FORD	3000
MILLER	1300

Now lets query the table to get the salaries of all employee name with their salaries in descending order.

Query like this.

SELECT ENAME,SAL,ROW_NUMBER() OVER(ORDER BY SAL DESC) ROW_NUMBER, RANK() OVER(ORDER BY SAL DESC)RANK, DENSE_RANK() OVER(ORDER BY SAL DESC) DENSE_RANK FROM EMP

Out put

ENAME	SAL	ROW_NUMBER	RANK	DENSE_RANK
KING	5000	1	1	1
SCOTT	3000	2	2	2
FORD	3000	3	2	2
JONES	2975	4	4	3
BLAKE	2850	5	5	4
CLARK	2450	6	6	5
ALLEN	1600	7	7	6
TURNER	1500	8	8	7
MILLER	1300	9	9	8
WARD	1250	10	10	9
MARTIN	1250	11	10	9
ADAMS	1100	12	12	10
JAMES	950	13	13	11
SMITH	800	14	14	12

So question is which one to use? 

Its all depends on your requirement and business rule you are following.
1. Row_number to be used only when you just want to have serial number on result set. It is not as intelligent as RANK and DENSE_RANK.
2. Choice between RANK and DENSE_RANK depends on business rule you are following. Rank leaves the gaps between number when it sees common values in 2 or more rows. DENSE_RANK don't leave any gaps between ranks.
So while assigning the next rank to the row RANK will consider the total count of rows before that row and DESNE_RANK will just give next rank according to the value.
So If you are selecting employee’s rank according to their salaries you should be using DENSE_RANK and if you are ranking students according to there marks you should be using RANK(Though it is not mandatory, depends on your requirement.)

How to get max sal,ename,dname,sal,deptno from emp, dept table ?

Hi friends the above question asked by one of CMM 5 level company.Using row_number we will find the each department wise maximum salary.

Query : SELECT E.ENAME,D.DNAME,E.DEPTNO,E.SAL FROM(

SELECT E.ENAME,E.SAL,E.DEPTNO,ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN

FROM EMP E) E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

WHERE RN=1;

The same approach using DENSE_RANK() 

Query :  SELECT E.ENAME,D.DNAME,E.SAL,E.DEPTNO FROM(

 SELECT E.ENAME,E.SAL,E.DEPTNO,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) DR 

 FROM EMP E) E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE DR=1;

Using rank() 

Query : SELECT E.ENAME,D.DNAME,E.SAL,E.DEPTNO FROM(
SELECT E.ENAME,E.SAL,E.DEPTNO,RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN FROM EMP E)
E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE RAN=1

Please comments here if you know any other methods regarding this topic.

How to copy stucture from a table ? How to copy stucture and data from a table ?

This article i help to you how to copy metadata(stucture) or metadata and data from a table. I will explain in oracle and teradata.

Oracle : Here i have a emp table. I want to copy emp data and stucture. Here is the solution to copy emp data and stucture.

Query: Create table emp_t as select * from emp where 1=1  ( in oracle)

Query: create table emp_t as (select * from emp) with data  ( in Teradata )

if you want copy only structure of emp table.Run the below query.

Query: create table emp_t as select * from emp where 1=2    ( in oracle )

Query: create table emp_t as (select * from emp) with no data (in Teradata)


drop your comments here if you know any other way to copy stucture and data.

How to create a user in Teradata and oracle

Syntax :Create user <username> from<dbc>
as
password=<password>
spool=200000
temporary=200000
permanent=200000;



Query : CREATE USER sai
   FROM dbc
AS
   PASSWORD = ***
   PERM=2000000
   SPOOL=5000000
   TEMPORARY = 5000000;


In oracle first create user then take permission from dba.

Syntax :create user <username> identified by <password>

Query : create user john identified by black;

Then take permission from dba

Query: grant dba to john;

If you are facing any problem in creating user in scott login, try below userid

username:sqlplus
password:as sysdba

First connect sqlplus user then after create user. 


Drop comment here, if any find any bugs in above query.

How to delete duplicate records in a table ?

For deleting duplicate records we have different ways.I have a Product_table.

Table name: product_table

product_id product_name

       1             xyz

       1             xyz

      30            abc

      30            abc

       5             ttt

Using rowid remove the duplicate records.This query will work only SQL

Syntax: delete from <table name> where rowid not in( select max(rowid) from <table_name> group by <col>);

Query: delete from product_table where rowid not in(select max(rowid) from

product_table group by product_id);

This is another way to delete duplicates. This will work in teradata.

here first create a set table with the definition  then insert the records into set table. Al ready we know the set table does not allow duplicate records. 

create set table product_table_t as (select * from product_table) with no data;

insert into product_table_t select * from product_table;

drop table product_table;

rename table product_table_t as product_table

using distinct, eliminating duplicate records

create temporary table with no data

Query : create table product_t as (select * from product_table) with no data

insert records in to table product_t with using distinct

insert into product_t select distinct * from product_table

drop the the product_table

rename table

rename table product_t as product

Please comment here if you know any other method of removing duplicates.

How to identify a duplicate records in a table ?

Let us assume, i have a product_table with columns product_id, product_desc

Table name : product_table

Product_id	Product_desc
10	XYZ
10	XYZ
20	ABC
20	ABC
30	TTT

by using group by and having we can find the duplicate records


Query : select product_id from product_table group by product_id having count(*)>1

Another way to find the duplicate records

Query : sel * qualify row_number() over(partition by product_id order by product_desc)>1 from product_table

or

Query: select product_id,product_desc,row_number() over(partition by product_id order by product_desc) r from product_table qualify r>1


Please drop comments here any other way is there to find the duplicate records.

Difference between Subquery and Corelated subquary ?

Sub query: 

1) Inner query executed first

2) Inner query executed only once

Corelated subquery:

1) Outer query executed first

2) Inner query executed many times

Please comment here for any mistake in above concept.

Importent Quaries

How to find max sal from emp table ?

Select max(sal) from emp

How to find 2nd max sal from emp ?

select max(sal) from emp where sal not in(select max(sal) from emp)

How to find 3rd max sal from emp table ?

select sal from(select sal from(select distinct sal from emp order by sal desc)where rownum<=3 order by sal asc)where rownum=1

How can view the data distribution in Teradata ?

By using hash function we can view the data distribution of rows for a choosen primary index.

Syntax : select hashamp(hashbucket(hashrow(<primary key>) "amp#", count(*) from <tablename> group by 1 order by 2 desc

Run the below query and you will see the data distribution. the below example is " emp " table

Query: select hashamp(hashbucket(hashrow(empno))) "amp#", count(*) from emp group by 1 order by 2 desc

How can view the 2763 errormessage in Teradata ?

Run the below query to find the error description

Query :select * from dbc.errormsgs where errorcode='2763'

How can get the all tables in a specific database ?

Select * from dbc.tables where databasename=<'database'>

Run the below query, you will get the all tables in specified database.

Select * from dbc.tables where databasename='sai'

How can we know collect statistics are done or not ?

Run the HELP STATISTICS on the table

Syntax : help statistics <table name>

This will give data and time when statistics were last collected. You will also see the statistics for the column

Query : help statistics emp

What is the difference between group by clause and having clause ?

Group by Clause:  It displays the data in specified group and mainly used in aggregation functions.

Having Clause: It filter the data based on group specified.