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.