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