SQL Handling Duplicate
Removing Duplicates using DISTINCT Keyword:
By using the DISTINCT keyword in SQL we can remove duplicate characters from tables or databases.
A table contains more duplicate values and duplicate values can cause redundancy.
To remove duplicates we use the DISTINCT keyword in sql.
The syntax for DISTINCT Keywords in SQL:
Select DISTINCT column1, column2, column3, …….., column from Table_name where (condition);
Example:
Student Table
| sid | sname | sage | sgender | Phonenumber |
| 1 | Abhinav | 22 | Male | 9895678909 |
| 2 | Ramya | 24 | Female | 6687654634 |
| 3 | Preetham | 21 | Male | 9867546453 |
| 4 | Nethranand | 21 | Male | 7675643423 |
| 5 | Naveen | 23 | Male | 6567784532 |
| 6 | Harshita | 22 | Female | 9867546231 |
| 7 | Bindu | 26 | Female | 6563412768 |
| 8 | Nandhini | 23 | Female | 6785674839 |
| 9 | Hashish | 22 | Male | 9453215052 |
| 10 | Rahul | 21 | Male | 9998989898 |
1.Display the table with the removal of duplicates in the sgender column of the Student Table.
Code:
Select DISTINCT sgender from Student;
Output:
Number of records: 2
| sgender |
| Male |
| Female |
Note: From the above student table it removes duplicates, such as male and female from the gender column and prints the male and female as unique.
2. Display the table with the removal of duplicates in a sage column of the Student Table.
Code:
Select DISTINCT sage from Student;
Output
Student Table
Number of records: 5
| sage |
| 22 |
| 24 |
| 21 |
| 23 |
| 26 |
Note: From the above student table it removes duplicates, such as 22, 24, 21, and 23 from the sage column and prints the values 22, 24, 21, 23, and 26.
3. Display the table with the removal of duplicates in the sname column of the Student Table.
Code:
Select DISTINCT sname from Student;
Output:
Student Table
Number of records: 10
| sname |
| Abhinav |
| Ramya |
| Preetham |
| Nethranand |
| Naveen |
| Harshita |
| Bindu |
| Nandhini |
| Hashish |
| Rahul |
Note: From the above student table, it does not remove duplicates in the sname column, because the sname column does not have any duplicates. Hence it prints all the names present in the sname column.
4. Display the table with the removal of duplicates in the sname column of the Student Table.
Code:
Select DISTINCT sid from Student;
Output:
Student Table
Number of records: 10
| sid |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
Note: From the above student table, it does not remove duplicates in the sage column, because the sage column does not have any duplicates. Hence it prints all the names present in the age column.
5. Display the table with the removal of duplicates in the Phonenumber column of the Student Table.
Code:
Select DISTINCT Phonenumber from Student;
Output:
Student Table
Number of records: 10
| Phonenumber |
| 9895678909 |
| 6687654634 |
| 9867546453 |
| 7675643423 |
| 6567784532 |
| 9867546231 |
| 6563412768 |
| 6785674839 |
| 9453215052 |
| 9998989898 |
Note: From the above student table, it does not remove duplicates in the Phonenumber column, because the Phonenumber column does not have any duplicates.
6. Hence it prints all the names present in the Phonenumber column. 6. Display the table with the removal of duplicates in the sname and id column of the table.
Code:
Select DISTINCT sid,sname from Student;
Output:
Student Table
Number of records: 10
| sid | sname |
| 1 | Abhinav |
| 2 | Ramya |
| 3 | Preetham |
| 4 | Nethranand |
| 5 | Naveen |
| 6 | Harshita |
| 7 | Bindu |
| 8 | Nandhini |
| 9 | Hashish |
| 10 | Rahul |
Note: From the above student table, it does not remove duplicates in the sid and sname columns, because the sname and sid columns do not have any duplicates. Hence it prints all the names and id’s present in the sid and sname column.
7.Display the table with the count of removal of duplicates in the sgender column of the Student Table.
Code:
Select count (DISTINCT sgender) from Student;
Output:
Number of records: 1
| COUNT (Distinct Sgender) |
| 2 |
Note: The count is used to count the number of records in a column or table, But here it counts the distinct values present in a column or table. From the above table, the output is 2. Because the distinct values are 2.
8.Display the table with the count of removal of duplicates in a sage column of the Student Table.
Code:
Select count (DISTINCT sage) from Student;
Output:
Number of records: 1
| COUNT (DISTINCT sage) |
| 5 |
Note: From the above table, the output is 5. Because the distinct values present in the sage column are 5.
9. Display the table with the count of removal of duplicates in the sname column of the Student Table.
Code:
Select count (DISTINCT sname) from Student;
Output:
Number of records: 1
| COUNT (DISTINCT sname) |
| 10 |
Note: From the above table, the output is 10. Because the distinct values present in the sname column are 10.
10. Display the table with the count of removal of duplicates in the sid column of the Student Table.
Code:
Select count (DISTINCT sid) from Student;
Output:
Number of records: 1
| COUNT (DISTINCT sid) |
| 10 |
Note: From the above table, the output is 5. Because the distinct values present in the sid column are 10.
11. Display the table with the count of removal of duplicates in the Phonenumber column of the Student Table.
Code:
Select count (DISTINCT Phonenumber) from Student;
Output:
Number of records: 1
| COUNT (DISTINCT Phonenumber) |
| 10 |
Note: From the above table, the output is 10.
Because the distinct values present in the Phonenumber column are 10.