TeraData in sql server -2008

Delete  from TT Where T.T.%% PHYSLOC%%

Not in(Select min(%%PHYSLOC%%)

from T.T group by EID)

Note

PHYSLOC: Physical location of row[Just like row id]

Tera data

1. Here we must use in intermediate table to delete the duplicate, Because row id, row number does not work in fera data
2. We cannot we order analytical function such as Rank and Qualify Row- number & Qualify to delete the duplicate

Steps

1. Take duplicate into an intermediate table(intermediate table should have similar Structure like main table)
2. Delete the main table.
3. Rename intermediate table to main table.

So tera data

The below 2 ways are recommended

1. Case of complete row duplicate
2. Take a new table with set option
3. load the duplicate table data, so unique ready loaded
4. Drop duplicate table and rename set table name to old table name
5. In case of same column are duplicate
6. a) Take a new table
7. b) Load the distinct resource group by into it so unique rows loaded
8. c) Drop duplicate table and rename new table name to old table name

1.Create B like A

2. Load duplicate of “A” TO “B”
3. Drop ”A”
4. Rename “B” TO “A”

Note

If we want to completes row duplications, then take set table

Creating sequence Tera data

We use identify column to generate sequence in tera data and so server create set table test

Create set table test

(party ID Integer Generated always as identify

Start with 1 Increment by1

Min value 2147483647

Max value 2147483647

No cycle), party name varchar (30))

Primary index(party id);

Fir testing: Insert into test2(‘vinay’)

Generate always As identify, it will not in insert user given input value

Generated by default as identify, it takes user given input value also.

Create set table test 11

(party id integer generated by default as identify(Max value3),

Party name varchar(30))

Primary index(party id);

Conditional expressions

Generally this conditional expression only where and having clauses

(In, not in, BETWEEN AND, Not BETWEEN AND, IS NULL, IS NOT NULL, EXISTS, NOT EXISTS, LIKE, ALL, SAMS, ANY etc---)

Among in and exists which gives good performance

Ans: Always use exists, because it gives good performance (once finds the match it store the process)

Display All the name where % is the 2^nd character

Ans - Select * From party where party name like –z %% ESCAPE ‘Z’

Select * From party where party name like –z-%

Select * From party where party name like%

in

Select * From party where party id in (1,2,3)

(or)

Sel * From party where party id = 1 or party id = 2 or party id =3

Exist not Exists

To the values existed it display the result

Working with multiple table : Rows and columns

1. Sub Queries:

   • Its operation is Query wise based on one query result it Fetch the other Query

2. Set operations:

   • It performance operations on row wise

3. Joint

   •  It performance operations on column wise

4.  VIEWS

   • It is logical object for the physical collection of takes generally for retrieval perspective

5. Procedure

   • it is pre compile collection of SQL Statements, which performs as particular process it can pass multiple values out side

6. Function

   •  it is also for implementing as particular process, bul it returns as single value as result

7.  Trigger

   •  it performs an actions, when there is event occurred

8.  Cursers

   •  it is stored memory area where the navigation and operations of rows if possible in take area

9. UTILITIES

   • it performs Loading and unloading data

10. MACROS

    • Collection of statement for implementing as simple process, Generally Recommended frequently used processes and queries.

Sub-Queries

Query inserts a query sub query, in this case one query is outer Query, the other is inner Query

Always we refer inner Queries by using conditional expressions, like, in, Exists, not in, not Exists etc….. Operators.

Types of sub-Queries

1. Simple sub- Query
2. Nested sub- Query
3.  Correlated sub- Query

1. Simple sub- Query

Query insert another Query is called simple sub – Query

Ex

Display employee details, if the dept id available in department  table

Select* From emp where exists (select dept no from dept):

Select* From emp where dept id In (select dept id from dept):

Select* From emp where exists (select dept no from dept where emp. dept no= dept.dept no):

2.Nested sub- Query

Sub- Query with in another sub- Query is called Nested sub- Query

Ex

Display dept details where Dept ids available in employee table where empid available in employee address table and those address are valid address in party address table

Select* From dept where deptid in

(Select dept id from emp where empid in)

(Select dept id from emp _address where emp address  in)

(Select address from party _Address)

)

)

Note

For with max salary need to place n-1 in place of 1

Collated

Note

1. We need avoid the correlate sub-Query some time in Real time Because it may decrease performance of the Query by occupying more spool memory and operating many rows.
2. For identify with max salary Top 10 Salary we can use Rank Qualify, Row- Number Quality (or)Top N Functions

3. Sub Query return always single table columns only (counter QUERY COLUMN)

Set operations

1. It performs operations Row-wise between multiple result sets
2. It is completely set theory based
3. It uses where as set operator for it’s process
4. Union
5. Union All(v)
6. Intersects(n)
7. Except(-) 

Syntax

Select column/* From Table A union/union All/Intersect/except

Select column/* From Table B

Limitations

The number of columns and order of the column data type should be same in both the data sets it doesn’t bother about the column name

Union -  it merges the rows from to data sets including duplicate

Union all - merges the rows from to data sets  including duplicate

Intersects - Take common rows from path the data set

Except - Take exclusive rows from the 1^st dataset[Not including matching or other data set row]

Note - Union doesn’t allow duplicate value union all allows duplicate value

Sel EID,EName From emp

Union ok union All or intersect or except

Sel empid, empname From emp-Address

Ex: Using Group by clause

We have to take group by clause individual Query wise rather than entire set operations wise

Sel Eid, ename from emp group by eid, ename

UNION

Sel empid, exlame From emp- Address group by empid; emp name

Multiple set operation

Sel EID,E Name From emp

UNION

Sel empid, emp name From emp-Address

Intersects

Sel EID, Ename from emp10

Except

Sal eid, e name from emp