SQL DELETE
The DELETE command is used to delete existing records in a table.Generally DELETE statement removes one or more records from a table.
DELETE Syntax:
DELETE FROM table_name [WHERE condition]; Here table_name is the table which has to be deleted.
The WHERE clause in the SQL DELETE statement is optional and it identifies the rows in the column that gets deleted.
SQL DELETE Example
Let us take a “EMPLOYEE” table.
| ID | EMP_NAME | CITY | SALARY |
| 101 | Manohar Joshi | Pune | 20000 |
| 102 | sandeep Ahuja | Mumbai | 21000 |
| 103 | Pallavi Patil | Raipur | 25000 |
| 104 | Esha Gupta | Jharkhand | 26000 |
For Example, of delete with WHERE clause is given below:
WHERE clause is used to prevent the deletion of all the rows in the table, If you don’t use the WHERE clause you might loss all the rows.
DELETE FROM EMPLOYEE WHERE ID=101; Output table after the query:
It will delete the all the records of EMPLOYEE table where ID is 101.
| ID | EMP_NAME | CITY | SALARY |
| 102 | sandeep Ahuja | Mumbai | 21000 |
| 103 | Pallavi Patil | Raipur | 25000 |
| 104 | Esha Gupta | Jharkhand | 26000 |
If you want to delete all rows than you can use the following example of delete statement is given below
DELETE FROM EMPLOYEE; Output table after the query:
It will delete all the records of EMPLOYEE table.
| ID | EMP_NAME | CITY | SALARY |
Difference between DELETE and TRUNCATE statements And Difference DELETE b/w DROP
There is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not specified.
But it does not free the space containing by the table.
The TRUNCATE statement: it is used to delete all the rows from the table and free the containing space but the table structure remains the same.
Difference b/w DROP
The DROP Statement is used to drop or delete a database. Dropping of the database will drop all database objects (tables, views, procedures etc.) inside it
SQL DELETE DUPLICATE ROWS
Sometimes you have got multiple duplicate records in a table, so at that time you should be more careful because you are fetching unique records instead of fetching duplicate records.
To overcome with this problem we use DISTINCT keyword.
It is used along with SELECT statement to eliminate all duplicate records and fetching only unique records.
SYNTAX:
SELECT DISTINCT column1, column2,....columnN
FROM table _name
WHERE [conditions] EXAMPLE:
Let us take an example of STUDENT table.
| ROLL_NO | NAME | PERCENTAGE | ADDRESS |
| 1 | Manohar Joshi | 72.8 | ALLAHABAD |
| 2 | sandeep Ahuja | 63.5 | MATHURA |
| 3 | Pallavi Patil | 72.3 | VARANASI |
| 4 | Esha Gupta | 72.3 | Jharkhand |
| 5 | RAVI Dubye | 75.5 | HAPUR |
| 6 | SONU Nigam | 71.2 | GHAZIABAD |
First we should check the SELECT query and see how it returns the duplicate percentage records.
SELECT PERCENTAGE FROM STUDENTS
ORDER BY PERCENTAGE; Output:
| PERCENTAGE |
| 63.5 |
| 71.2 |
| 72.3 |
| 72.3 |
| 72.8 |
| 75.5 |
Now let us use SELECT query with DISTINCT keyword and see the result. This will eliminate the duplicate entry.
SELECT DISTINCT PERCENTAGE FROM STUDENTS
ORDER BY PERCENTAGE; | PERCENTAGE |
| 63.5 |
| 71.2 |
| 72.3 |
| 72.8 |
| 75.5 |