Tuesday 22 April 2014

Basic UNIX commands

How to create a new file ?

syntax : cat>filename
file data
file data
fild data

CTTL+D

How to rename existing file name ?

Syntax : mv oldfilename >> newfilename

How to change existing file content ?

Syntax: vi filename 

do all changes, if you want to save the file type the below command

:wq!

How to see the content of file ?

Syntax : cat <file name >

How to identify duplicate records in file ?(any file like DAT,CSV,TXT)

Syntex: cat <file name> | uniq -d

How to fetch unique records in file ?(any file like DAT,CSV,TXT)

Syntex: cat <file name> | uniq 







Tuesday 15 April 2014

What is the difference between union and union all ?    
Let us assume, i have two tables 
Table 1
Table 2 
Table 1 have the following records
1
2
3
4
5

Table 2 have the following records
5
6
7
8
9
10

Now fire the below query in SQL Assistant

Select * from table1 
Union
Select * from table2

Now we will get below records..
1
2
3
4
5
6
7
8
9
10

So we come to know, when ever we use union key word, it fetches only unique records.

Select * from table1
Union all
Select * from table 2

We well get the below records 

1
2
3
4
5
5
6
7
8
9
10

If you use union all keyword between two or more SQL statements, we will get the unique records as well as duplicates will fetches.

If any concern please drop a mail..



Friday 12 April 2013

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>'

Thursday 11 April 2013

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.

empnoenamejobmgrhiredatesalcommdeptno
7788SCOTTANALYST756619/04/19873000020
7782CLARKMANAGER783909/06/19812450010
7521WARDSALESMAN769822/02/1981125050030
7698BLAKEMANAGER783901/05/19812850030
7902FORDANALYST756603/12/19813000020
7839KINGPRESIDENT017/09/19815000010
7900JAMESCLERK769803/12/1981950030
7654MARTINSALESMAN769828/09/19811250140030
7934MILLERCLERK778223/01/19821300010
7566JONESMANAGER783902/04/19812975020
7844TURNERSALESMAN769808/09/1981500030
7499ALLENSALESMAN769820/02/1981160030030
7876ADAMSCLERK778823/05/19871100020
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_numberemp_nameemp_jobemp_manageremp_hiredateemp_salaryemp_commissionemp_department
7788SCOTTANALYST756619/04/19873000.00020
7782CLARKMANAGER783909/06/19812450.00010
7521WARDSALESMAN769822/02/19811250.00500.0030
7698BLAKEMANAGER783901/05/19812850.00030
7902FORDANALYST756603/12/19813000.00020
7839KINGPRESIDENT017/09/19815000.00010
7900JAMESCLERK769803/12/1981950.00030
7654MARTINSALESMAN769828/09/19811250.001400.0030
7934MILLERCLERK778223/01/19821300.00010
7566JONESMANAGER783902/04/19812975.00020
7844TURNERSALESMAN769808/09/1981500.000.0030
7499ALLENSALESMAN769820/02/19811600.00300.0030
7876ADAMSCLERK778823/05/19871100.00020

Tuesday 9 April 2013

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.

empnoenamejobmgrhiredatesalcommdeptno
7788SCOTTANALYST756619/04/19873000020
7782CLARKMANAGER783909/06/19812450010
7521WARDSALESMAN769822/02/1981125050030
7698BLAKEMANAGER783901/05/19812850030
7902FORDANALYST756603/12/19813000020
7839KINGPRESIDENT017/09/19815000010
7900JAMESCLERK769803/12/1981950030
7654MARTINSALESMAN769828/09/19811250140030
7934MILLERCLERK778223/01/19821300010
7566JONESMANAGER783902/04/19812975020
7844TURNERSALESMAN769808/09/1981500030
7499ALLENSALESMAN769820/02/1981160030030
7876ADAMSCLERK778823/05/19871100020
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

empolyeenumberemployeenameAnnual Salary
7788SCOTT36000
7566JONES35700
7902FORD36000
7876ADAMS13200
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

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK790217/12/1980800020
7499ALLENSALESMAN769820/02/1981160030030
7521WARDSALESMAN769822/02/1981125050030
7566JONESMANAGER783902/04/19812975020
7654MARTINSALESMAN769828/09/19811250140030
7698BLAKEMANAGER783901/05/19812850030
7782CLARKMANAGER783909/06/19812450010
7788SCOTTANALYST756619/04/19873000020
7839KINGPRESIDENT017/11/19815000010
7844TURNERSALESMAN769808/09/19811500030
7876ADAMSCLERK778823/05/19871100020
7900JAMESCLERK769803/12/1981950030
7902FORDANALYST756603/12/19813000020
7934MILLERCLERK778223/01/19821300010

DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
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_nameemp_saldept_namedept_loc
TURNER500SALESCHICAGO
JAMES950SALESCHICAGO
ADAMS1100RESEARCHDALLAS
WARD1250SALESCHICAGO
MARTIN1250SALESCHICAGO
MILLER1300ACCOUNTINGNEW YORK
ALLEN1600SALESCHICAGO
CLARK2450ACCOUNTINGNEW YORK
BLAKE2850SALESCHICAGO
JONES2975RESEARCHDALLAS
FORD3000RESEARCHDALLAS
SCOTT3000RESEARCHDALLAS
KING5000ACCOUNTINGNEW 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.










Monday 8 April 2013

How to check the table size in Teradata ?

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

SyntaxSelect 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>



Wednesday 6 March 2013

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.