Group Discounts available for 3+ students and Corporate Clients

TeraData in sql server -2008

In sql server -2008 only

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

  1. Load duplicate of “A” TO “B”
  2. Drop ”A”
  3. 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’)

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

2Generated 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—)

2Among in and exists which gives good performance

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

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

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

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

2Select * From party where party name like%

2in

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

 

Screenshot_75

 

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)

)

)

Simple sub QueryCorrelated sub Query
1.Inner Query executed First1.Out Query executed First
  1. Inner Query executed only one time

Ex: Finding 2nd Maximum

 

Select max(Party income)From party where party income not in(Select mad(Party income)from party)

  1. Inner Query executed many times (According out Query)

[Based on outer Query (for loop)]

Ex: Finding 2nd maximum select a party income from party a where 1st (Select count(Distinct(b: party income))from party b where a. party income(b. party income)

Note

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

Collated

A30000
  1. PI<B.PI

<(F)

<(F)

<(F)

COUT0B30000

20000

25000

20000<T<F

<T

23000020000

25000

250000<T<F

<T

13000020000

25000

 

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

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on TeraData in sql server -2008"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Support


    Please leave a message and we'll get back to you soon.
    Three + 6