between truncate and delete in SQL or discovering second highest wage of
worker. Each of those SQL queries are should know for anyone who’s showing
on any programming an interview the place some questions on database and SQL are anticipated. So as
to discover duplicate data within the database
desk you’ll want to affirm the definition of duplicates, for instance in beneath contact desk
which is suppose to retailer identify and cellphone quantity of the contact,
a document is taken into account to be duplicate if each identify and cellphone quantity is similar
however distinctive if both of them varies.
Issues of duplicates in databases come up
when you do not have a major key or distinctive key on the database and that is why it is advisable to have a key
column in desk. Anyway its simple to search out duplicate data within the desk through the use of group
by clause of ANSI SQL.
Group by clause is used to group information based mostly upon any
column or quite a few columns. Right here as a way to find duplicate data, we
have to use group by clause on each identify and cellphone as proven
within the second SQL SELECT question instance.
You possibly can see within the first question that it listed Ruby as a replica
document despite the fact that each Ruby has a distinct cellphone quantity as a result of we solely
carried out group by on identify. Upon getting grouped information you may filter out
duplicates through the use of having clause.
Having clause is the counterpart of the place
clause for aggregation queries. Simply keep in mind to supply a short lived identify to rely() information in
order to make use of them in having clause.
SQL Question to search out duplicate data in a desk in MySQL
On this part we’ll see SQL question which can be utilized to findduplicate data in desk. As defined in earlier part, definition of
duplicate relies upon upon enterprise guidelines which have to be utilized in group by clause. In
following question we’ve got used SELECT
question to pick out all data from Contacts desk. Right here
James, Johnny, Harry and Ron are duplicated 4 occasions.
mysql> choose * from Contacts;
+——-+———-+
| identify |
cellphone |
+——-+———-+
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron
| 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron
| 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron |
44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron |
44446666 |
| Ruby | 8965342 |
| Ruby | 6888342 |
+——-+———-+
18 rows in set (0.00 sec)
Following SELECT question will solely discover duplicates data based mostly on the identify
which could not be right if two contacts of identical however completely different numbers are
saved, as within the following end result set Ruby is proven as duplicate which is
incorrect.
mysql> choose identify, rely(identify) from contacts group by identify;
+-------+-------------+
| identify | rely(identify) |
+-------+-------------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
| Ruby | 2 |
+-------+-------------+
5 rows in set (0.00 sec)
That is the right approach of discovering duplicate contacts at it look each
identify and cellphone quantity and solely print duplicate if each identify and cellphone is
identical.
mysql> choose identify, rely(identify) from contacts group by identify, cellphone;
+-------+-------------+
| identify | rely(identify) |
+-------+-------------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
| Ruby | 1 |
| Ruby | 1 |
+-------+-------------+
clause in SQL question will filter duplicate data from distinctive data. As within the following question, it prints all duplicate data and what number of
occasions they’re duplicated within the desk.
mysql> choose identify, rely(identify) as occasions
from contacts
group by identify, cellphone having occasions>1;
+-------+-------+
| identify | occasions |
+-------+-------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
+-------+-------+
4 rows in set (0.00 sec)
will work on all database like MySQL,
Oracle,
SQL Server and Sybase because it solely makes use of ANSI SQL and does not use any database particular
function. One other attention-grabbing SQL question interview query is “How you can
delete duplicate data from the desk” which we’ll see in one other
submit.
Different SQL and database articles chances are you’ll discover helpful
