SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL What are single row and multiple row subqueries?

SQL Handling Duplicate
2022-09-18 00:11:20

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

sidsnamesagesgenderPhonenumber
1Abhinav22Male9895678909
2Ramya24Female6687654634
3Preetham21Male9867546453
4Nethranand21Male7675643423
5Naveen23Male6567784532
6Harshita22Female9867546231
7Bindu26Female6563412768
8Nandhini23Female6785674839
9Hashish22Male9453215052
10Rahul21Male9998989898

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

sidsname
1Abhinav
2Ramya
3Preetham
4Nethranand
5Naveen
6Harshita
7Bindu
8Nandhini
9Hashish
10Rahul

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.