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 

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

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.