TO CHECK WHETHER THE TWO TABLES HAVE THE IDENTICAL DATA OR NOT

Create Two tables with Similar Structure and same number of rows but with different values as shown below

Now to Check Whether two tables have the Same data or not am using 'UNION'

OUTPUT:

If all the Counts are Similar then we can say that the records are identical or same, if the Count  is different then two table have different data. According to the O/P Count is not Similar, so two tables are not having the identical data.

Eliminating Duplicate Records From a Table

CREATE A TABLE EMPLOYEE WITH SOME DUPLICATE RECORDS AS SHOWN BELOW

ID 2,3 and 4 are repeated Multiple times. Now let's see the different ways to delete the duplicate Records.

First Method:

Using 'Distinct' Keyword copy the distinct records to temporary place, after that delete the total records from the table. Now copy the distinct data from temporary place to the table. Here am storing the data temporarily in 'Temporary Table' as Shown below.

Second Way:

First we need to add an Identity Column to the Table. Use Rank Function with partition By EMPID and NAME.  And now if the Rank > 1 that means it's a duplicate record, So delete those records. Once the Operation is completed remove identity column from the table.

Third Way:

Using Co-related Sub-Query. In this case also we need Identity Column.

Fourth Way:

If you want to delete all the Rows if the Record is repeated more than 1 time.