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
- Here we must use in the intermediate table to delete the duplicate, Because row id, row number does not work in tera data
- We cannot we order analytical function such as Rank and Qualify Row- number & Qualify to delete the duplicate
Steps
- Take duplicate into an intermediate table(intermediate table should have a similar Structure to the main table)
- Delete the main table.
- Rename the intermediate table to the main table.
So tera data The below 2 ways are recommended
- Case of complete row duplicate
- Take a new table with the set option
- load the duplicate table data, so unique ready loaded
- Drop duplicate table and rename set table name to old table name
- In case of the same column are duplicate
- Take a new table
- Load the distinct resource group by into it so unique rows loaded
- 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 complete row duplications, then take a set table
Inclined to build a profession as SQL Server Developer? Then here is the blog post on, explore SQL Server Training
Creating sequence Tera data
We use the identify column to generate the sequence in tera data and so the server 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);
For testing:
Insert into test2(‘Vinay’)
Generate always As identify, it will not in insert user given input value
Generated by default as identity, 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 a good performance (once finds the match it stores the process)
Display All the name where % is the 2nd 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 names 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 tables
Rows and columns
1. Sub Queries:
- Its operation is Query wise based on one query result it Fetches the other Query
2. Set operations:
- It performs operations on row-wise
3. Joint
- It performs operations on column-wise
4. Views
- It is a logical object for the physical collection of takes generally for retrieval perspective
5. Procedure
- it is a pre-compile collection of SQL Statements, which performs as a particular process it can pass multiple values outside
6. Function
- it is also for implementing a particular process, but it returns as a single value as a result
7. Trigger
- it performs an action when there is an event that occurred
8. Cursers
- it is a stored memory area where the navigation and operations of rows if possible intake area
9. Utilities
- it performs Loading and unloading data
10. Macros
- Collection of statements for implementing a simple process, Generally Recommended frequently used processes and queries.
Sub-Queries
Query inserts a query subquery, in this case, one query is outer Query, the other is inner Query
Always we refer to inner Queries by using conditional expressions, like, in, Exists, not in, not Exists, etc….. Operators.
Types of sub-Queries
- Simple sub- Query
- Nested sub- Query
- 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 within another sub- Query is called Nested sub- Query
Ex: Display dept details where Dept ids available in employee table where empid available in the 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 Query | Correlated sub Query |
1.Inner Query executed First | 1.Out Query executed First |
2.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 the party) | 2.Inner Query executed many times (According to Query) [Based on outer Query (for loop)] Ex: Finding 2nd maximum select a party income from the party a were 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 |
<(F) <(F) <(F) | COUT0 | B30000 20000 25000 |
20000 | <T<F <T | 2 | 3000020000 25000 |
250000 | <T<F <T | 1 | 3000020000 25000 |
Note:
- We need to avoid the correlate sub-Query sometime in Real-time Because it may decrease the performance of the Query by occupying more spool memory and operating many rows.
- To identify with max salary Top 10 Salary we can use Rank Qualify, Row- Number Quality (or)Top N Functions
- Sub Query return always single table columns only (counter QUERY COLUMN)
Set operations
- It performs operations Row-wise between multiple result sets
- It is completely set theory based
- It uses where a set operator for its process
- Union
- Union All(v)
- Intersects(n)
- 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 the 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 the 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, emp name From emp-Address
Ex: Using Group by clause We have to take the 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
Get In-Depth Knowledge on SQL Server Click on Here:
- Transfer SQL server Objects Task
- Performing Data Manipulations using Disconnected Model
- TeraData in sql server -2008
- SQL Server Integration