Where clause and Group by clause in TeraData

To filter the data while retrieving we use where clause

Ex

Display employee details, who are having department number 10

Select*from emp where DEPITD=10

ORDER BY CLAUSE

1. It displays the data in the specified order ascending/desired
2. We use column names (or)Numbers for storing the data

Group by clause

1. It displays the data in group wise
2. Generally recommended to go for group by clause, when we are going to perform AGGREGATE operation, sum as Sum, Avg, Min, max etc—Operations.
3. We use column names(or) Numbers to represent the data

Select*from Party order by Party id

(OR)

Order by 1àfirst column of*

Select Party id, Sum(Party income) from party group By1

Select Party id(Aggregate), Party code Party income (Non- aggregate)from party group By1

We can’t use aggregate and non-aggregate files in the same select column

Select Party id, sum (Party income) Party loc

from party group By Party loc, Party id(or) Select min(Party id) sum (Party income) Party loc

from party group By Party loc

Identity Duplicate

a) In case single column employee id having duplicate

Select Party id, Count(*)from party group By Party id having –count(*)>1;

Wrong answer - Using distinct clause

Correct - Group by, having clause

Select PID, from party group by pid, pc having count(*)>1;

Select party id, party name, count(*) From party group by party I party name having count(*)>1,b) In case of multiple – column duplication

Correct query

1. Select pid, p code, PI From party group by 1,2,3
2. Select pid, max(PC)Sum(PI)From party group by 1

Distinct clause -  It displays distinct values of columns district max

Select Distinct party id from partyà select party id group part id

Note

To eliminate duplicate while retrieval it is useful

Always use group by, it is used red time

Never use distinct clause

Note

 Performance wise group by gives good performance because it occupies less spool memory

Display locations in ascending order and within the location names in descending order

Select*From party order by

Inserting duplicate into Another table

B                                             A

PID   PNAME              PID   PNAME

Insert into B(Select pid, pname from a group by pid,pno having count(*)>1) - -> Wrong

Insert into  B select pid, pname form a group by pid, pname having count(*)>1) - -> correct

Deleting the duplicate in the table

In oracle:- There are 3 ways

1) By using rank, row number, dense rank functions

2) By using an intermediate table

A

1

2

1

2

3

4

Intermediate table Create table B as(Select pid from A group by pid having count(*)>1)

B

1

2

Deletion

Delete from A,B Where A. Pid, B. Pid;

Removing either latest rows(or)old rows

Delete from a where row id>(Select min(row id)from

A group by pid having count(*)

[Deleting recent duplicates]

Delete from A where row id<(Select max(rowid) rom group by pid having count(*)>1)

[Deleting old row by keeping new row]