Where clause
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
- It displays the data in the specified order ascending/desired
- We use column names (or)Numbers for storing the data
Group by clause
- It displays the data in group-wise.
- Generally recommended to go for the group by clause, when we are going to perform AGGREGATE operation, a sum, Avg, Min, Max, etc—Operations.
- We use column names(or) Numbers to represent the data.
Party id | Party Code | Party income |
1 | 10 | 10000 |
2 | 30 | 20000 |
3 | 20 | 40000 |
1 | 12 | 20000 |
2 | 30 | 60000 |
4 | 10 | 20000 |
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training
Select*from Party order by Party id (OR) Order by 1àfirst column of*
Party id | Party Code | Party income |
1 | 10 | 10000 |
1 | 30 | 10000 |
2 | 20 | 40000 |
2 | 12 | 20000 |
3 | 30 | 60000 |
4 | 10 | 20000 |
Select Party id, Sum(Party income) from party group By1
Party id | Sum |
1 | 30000 |
2 | 80000 |
3 | 40000 |
4 | 20000 |
Select Party id(Aggregate), Party code Party income (Non- aggregate)from party group By1
Party id |
1 |
2 |
3 |
4 |
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
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
- Select pid, p-code, PI From party group by 1,2,3
- 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 read 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 from 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 the 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) from the group by pid having count(*)>1)
[Deleting old row by keeping new row]
For an in-depth knowledge, click on below