SQL for QA: Tutorial#5 – DELETE Statement

DELETE Statement is used to permanently removes existing records from a given table.

Most of the time QAs won’t have permission to perform this command on Development or Production environment. But they can do it on their won Testing environment.

By using DELETE command, we can delete one or more records in a table. WHERE clause is used with DELETE to remove only specific records.  If not using WHERE clause with DELETE , it deletes all the records from table.

Syntax:

We can combine N number of conditions using AND or OR operators in WHERE clause.

Lets take an example of Employee Table

Now let’s see how we can use DELETE command with some examples.

Example 1:

If you want to delete single record.

This query will delete a single employee from above table whose ID is 102.

Example 2:

You can also delete multiple records with providing conditions.

This query will delete multiple records from a Employee . ( ID 102 and 103 will get deleted)

Example 3:

You can also use < , > , <=, >= , AND , OR , IN , LIKE operators in Where condition to delete specific records

This query will delete all those records whose salary is greater than 70000. ( ID 101 will get deleted)

Example 4:

This query will DELETE employees from above table, with IDs 104,105,106.

Example 5:

This query will DELETE employee from above table with IDs 104.

Example 6:

This query will DELETE 2 employees from above table with First_Name as Rosy.

Example 7:

Output: All rows are deleted from the Employee. This is because the condition in the WHERE clause is true for all rows.

Example 8:

This query will delete all the records from employee table.

NOTE: Please note that the DELETE command cannot delete any rows of the table that would violate FOREIGN KEY or other constraints.

TRUNCATE Command:

TRUNCATE command will delete entire table from database along with it’s records.

Being a QA is very rare you will use this command. As Testing team wont have permission to delete entire table, not even in Testing environment.

By using TRUNCATE Statement one can delete ALL the rows from a table and free the space containing the table

Syntax:

For Example:

If we need to delete our sample table ‘Employee’

This will delete entire Employee table from database.

 

Difference between DELETE & TRUNCATE commands

 

DELETE	TRUNCATE
Delete command come under DML (Data Manipulation Language)	Truncate command come under DDL (Data Definition Language)
By using DELETE command we can delete specific tuple.	By using TRUNCATE entire table will get deleted.
By using DELETE Statement we can delete rows from a table, without freeing the space containing the table	By using TRUNCATE Statement we can delete ALL the rows from a table and free the space containing the table(de allocating the data pages)
Syntax: DELETE FROM table_name [WHERE condition]; // where condition is optional	Syntax: TRUNCATE TABLE table_name;
In Delete statement, we can delete ALL records in table  or we can delete specific rows.   Ex:  delete from employee; // entire table will get deleted (where condition is optional, without where condition entire table will get deleted.)   Delete from employee where eid=1; // only 1 record will get deleted.	Truncate statement is use to delete all the rows from table, Ex : TRUNCATE TABLE employee;
In Delete statement, we can use where condition.	We can’t use where condition with it.
After performing a DELETE operation we can do COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.	TRUNCATE statement will remove all rows from a table; This operation cannot be rolled back.
Speed of execution is slow because it delete row by row data from table	Speed of execution is fast
By using DELETE command we are allowed to delete records that have foreign key constraints defined. (or we can say  delete can be use with indexed view)	But this is not possible with TRUNCATE. We can’t execute TRUNCATE if foreign key constraints are defined. (or we can say  truncate can’t be use with indexed view)
We can fire a trigger with DELETE statement because it deletes the data tuple wise from table. So with delete we are modifying the data after delete operation.	We can’t fire a trigger in TRUNCATE because the operation doesn’t delete individual tuple, it de allocates entire table or data page. So there will be no modification in the table.
If we are deleting a data using DELETE statement, we can recover that data.	But we need to use TRUNCATE very carefully because we can’t recover the data deleted using this statement.

Tutorial Index:

1. SQL For QA : Tutorial#1 – Select Query
2. SQL For QA : Tutorial#2 – Where Clause
3. SQL for QA: Tutorial#3 – INSERT INTO Statement
4. SQL for QA: Tutorial#4 – UPDATE Statement
5. SQL for QA: Tutorial#5 – DELETE Statement
6. SQL for QA: Tutorial#6 – ORDER BY Clause
7. SQL for QA: Tutorial#7 – GROUP BY Clause
8. SQL for QA: Tutorial#8 – UNION Clause
9. SQL for QA: Tutorial#9 – JOIN Clause