In sql server -2008 only

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

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

from T.T group by EID)



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



  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”



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


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%


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


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


    • 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


    • it performs Loading and unloading data

  10. MACROS

    • Collection of statement for implementing as simple process, Generally Recommended frequently used processes and 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



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


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)


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



  1. PI<B.PI

















  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(-) 



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

Select column/* From Table B



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


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


Multiple set operation

Sel EID,E Name From emp


Sel empid, emp name From emp-Address


Sel EID, Ename from emp10


Sal eid, e name from emp